table index organized ?

[Jean]

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

[Knut Stolze]

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

[Mark]

"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.

[Jean]

>

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

[Knut Stolze]

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

[Pierre Saint-Jacques]

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.

[Knut Stolze]

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

[Mark]

> > 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.

[Jean]

> 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

[Jean]

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

[Mark]

"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.