table index organized ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jean - 27 Oct 2004 10:19 GMT

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

Reply to this Message

Knut Stolze - 27 Oct 2004 12:12 GMT

> Hi,
> how to know for a particular table if it is organized by index, and which
[quoted text clipped - 3 lines]
>
> 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.

Signature

Knut Stolze
Information Integration
IBM Germany / University of Jena

Reply to this Message

Mark - 27 Oct 2004 15:26 GMT

> Hi,
> how to know for a particular table if it is organized by index, and which
[quoted text clipped - 5 lines]
>
> 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.

Reply to this Message

Jean - 28 Oct 2004 14:33 GMT

> 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

Reply to this Message

Knut Stolze - 28 Oct 2004 16:13 GMT

>> If one of the indexes is defined as "clustered" then DB2 will use it
>> to order rows during a reorg and during an insert.
[quoted text clipped - 14 lines]
> 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/infocenter/db2help/topic/com.ibm.db2.udb.doc/admin
/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...

Signature

Knut Stolze
Information Integration
IBM Germany / University of Jena

Reply to this Message

Pierre Saint-Jacques - 28 Oct 2004 20:13 GMT

.....

>>If yes, :
>>in db2 v7, how can i reorganise all index without issuing all these
[quoted text clipped - 8 lines]
> 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.

Signature

Pierre Saint-Jacques - Reply to: sescons at attglobal dot net
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.

Reply to this Message

Knut Stolze - 28 Oct 2004 20:23 GMT

> .....
>>>If yes, :
[quoted text clipped - 15 lines]
> 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.

Signature

Knut Stolze
Information Integration
IBM Germany / University of Jena

Reply to this Message

Jean - 29 Oct 2004 09:06 GMT

> 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

Reply to this Message

Mark - 29 Oct 2004 05:57 GMT

> > If one of the indexes is defined as "clustered" then DB2 will use it
> > to order rows during a reorg and during an insert.
[quoted text clipped - 21 lines]
> 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.

Reply to this Message

Jean - 29 Oct 2004 08:54 GMT

> 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
[quoted text clipped - 9 lines]
> 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

Reply to this Message

Mark - 29 Oct 2004 20:54 GMT

> > 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
[quoted text clipped - 12 lines]
> hi thx for confirmation
> this is what i wanted to be confirmed

BTW I meant V7, not V6.

Reply to this Message