469,599 Members | 2,666 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,599 developers. It's quick & easy.

table index organized ?

Hi,
how to know for a particular table if it is organized by index, and which
one ?

reorg table index ind1 -> the table is organised by ind1

i wan't to know that for all table

thx

Nov 12 '05 #1
10 6137
Jean wrote:
Hi,
how to know for a particular table if it is organized by index, and which
one ?

reorg table index ind1 -> the table is organised by ind1

i wan't to know that for all table


Have a look at SYSSTAT.INDEXES.CLUSTERRATIO and CLUSTERFACTOR.

And if you have range clustered tables defined using the ORGANIZE BY KEY
SEQUENCE option, have a look at SYSCAT.INDEXES.INDEXTYPE. It will contain
"RCT" for such tables.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #2
"Jean" <te**@est.com> wrote in message news:<38**************************************@pro xyusinor.usinor.com>...
Hi,
how to know for a particular table if it is organized by index, and which
one ?

reorg table index ind1 -> the table is organised by ind1

i wan't to know that for all table

thx


If one of the indexes is defined as "clustered" then DB2 will use it
to order rows during a reorg and during an insert.

If no index is defined as clustered, then an index must be specified
at time of reorg to order the table rows, and DB2 inserts wherever in
the table it is convenient.
Nov 12 '05 #3
>
If one of the indexes is defined as "clustered" then DB2 will use it
to order rows during a reorg and during an insert.

If no index is defined as clustered, then an index must be specified
at time of reorg to order the table rows, and DB2 inserts wherever in
the table it is convenient.


i don't understand

example :
table1 has 3 indexes : index1, index2, index3

i issue the following command
db2 reorg table table1 index index2

after that, how can i retrieve the table has benn reorganised by using the
index2
I think that now index2 is a cluster index ?

If yes, :
in db2 v7, how can i reorganise all index without issuing all these commands
:
db2 reorg table table1 index index1
db2 reorg table table1 index index2
db2 reorg table table1 index index3


Nov 12 '05 #4
Jean wrote:

If one of the indexes is defined as "clustered" then DB2 will use it
to order rows during a reorg and during an insert.

If no index is defined as clustered, then an index must be specified
at time of reorg to order the table rows, and DB2 inserts wherever in
the table it is convenient.
i don't understand

example :
table1 has 3 indexes : index1, index2, index3

i issue the following command
db2 reorg table table1 index index2

after that, how can i retrieve the table has benn reorganised by using the
index2
I think that now index2 is a cluster index ?


It depends on what you mean with "cluster index". You can create an index
using the CLUSTER keyword
(http://publib.boulder.ibm.com/infoce...n/r0000919.htm)
or you can reorganize the table as you described above. In both cases, you
will find the information about the cluster index for the table using the
catalog table SYSCAT.INDEXES (or SYSSTAT.INDEXES). In particular, the
columns CLUSTERRATIO and CLUSTERFACTOR are of interest to you as I wrote
before.

If you have range clustered tables defined using the ORGANIZE BY KEY
SEQUENCE option, have a look at SYSCAT.INDEXES.INDEXTYPE.**It*will*contain
"RCT" for such tables.
If yes, :
in db2 v7, how can i reorganise all index without issuing all these
commands
You want to reorganize the table, not the index, right?
:
db2 reorg table table1 index index1
db2 reorg table table1 index index2
db2 reorg table table1 index index3


You have to run all the statements. You can query the DB2 catalog and
generate a script that way. Then you can simply execute the script...

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #5


Knut Stolze wrote:
Jean wrote:

......
If yes, :
in db2 v7, how can i reorganise all index without issuing all these
commands

You want to reorganize the table, not the index, right?

:
db2 reorg table table1 index index1
db2 reorg table table1 index index2
db2 reorg table table1 index index3

You have to run all the statements. You can query the DB2 catalog and
generate a script that way. Then you can simply execute the script...

I don't quite understand. I thought indexes get reorg'ed any way and
that specifying an index name with the index clause caused the table to
be roerg'ed in sequencng rows following that index sequence.
If he uses the script, the table will get sorted three times and end up
sequenced by index3. Yes, No??
Regards, Pierre.
--
Pierre Saint-Jacques - Reply to: sescons at attglobal dot net
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.
Nov 12 '05 #6
Pierre Saint-Jacques wrote:


Knut Stolze wrote:
Jean wrote:

.....
If yes, :
in db2 v7, how can i reorganise all index without issuing all these
commands

You want to reorganize the table, not the index, right?

:
db2 reorg table table1 index index1
db2 reorg table table1 index index2
db2 reorg table table1 index index3

You have to run all the statements. You can query the DB2 catalog and
generate a script that way. Then you can simply execute the script...

I don't quite understand. I thought indexes get reorg'ed any way and
that specifying an index name with the index clause caused the table to
be roerg'ed in sequencng rows following that index sequence.
If he uses the script, the table will get sorted three times and end up
sequenced by index3. Yes, No??


You are right, of course. I haven't read carefully enough and thought Jean
wanted to reorg different tables.
--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #7
> > If one of the indexes is defined as "clustered" then DB2 will use it
to order rows during a reorg and during an insert.

If no index is defined as clustered, then an index must be specified
at time of reorg to order the table rows, and DB2 inserts wherever in
the table it is convenient.


i don't understand

example :
table1 has 3 indexes : index1, index2, index3

i issue the following command
db2 reorg table table1 index index2

after that, how can i retrieve the table has benn reorganised by using the
index2
I think that now index2 is a cluster index ?

If yes, :
in db2 v7, how can i reorganise all index without issuing all these commands
:
db2 reorg table table1 index index1
db2 reorg table table1 index index2
db2 reorg table table1 index index3


In V6, if reorg the table, all the indexes get reorged. When you
specify the index in the reorg column, it is used to specify the order
of the rows in the table.

Specifying an index to use during the reorg will not make it the
clustering index (which is only specified in the create index
statement). It will order the rows during the reorg, but will have no
effect where DB2 inserts new rows (as would be the case if you
specified a clustering index).

When specifying a clustering index, you need to leave enough percent
free on the table (in-betwen reorgs) for DB2 to attempt to insert the
rows in the desired order within the table.
Nov 12 '05 #8
> In V6, if reorg the table, all the indexes get reorged. When you
specify the index in the reorg column, it is used to specify the order
of the rows in the table.

Specifying an index to use during the reorg will not make it the
clustering index (which is only specified in the create index
statement). It will order the rows during the reorg, but will have no
effect where DB2 inserts new rows (as would be the case if you
specified a clustering index).

When specifying a clustering index, you need to leave enough percent
free on the table (in-betwen reorgs) for DB2 to attempt to insert the
rows in the desired order within the table.

hi thx for confirmation
this is what i wanted to be confirmed
Nov 12 '05 #9

You are right, of course. I haven't read carefully enough and
thought Jean wanted to reorg different tables.

yes that reassures me
thx for the explanation anyway
Nov 12 '05 #10
"Jean" <te**@est.com> wrote in message news:<38**************************************@pro xyusinor.usinor.com>...
In V6, if reorg the table, all the indexes get reorged. When you
specify the index in the reorg column, it is used to specify the order
of the rows in the table.

Specifying an index to use during the reorg will not make it the
clustering index (which is only specified in the create index
statement). It will order the rows during the reorg, but will have no
effect where DB2 inserts new rows (as would be the case if you
specified a clustering index).

When specifying a clustering index, you need to leave enough percent
free on the table (in-betwen reorgs) for DB2 to attempt to insert the
rows in the desired order within the table.

hi thx for confirmation
this is what i wanted to be confirmed


BTW I meant V7, not V6.
Nov 12 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Ed_No_Spam_Please_Weber | last post: by
7 posts views Thread by Bing Wu | last post: by
3 posts views Thread by Prochot | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.