Question on reorgs

[datapro01]

I have a question concerning reorgs.

We have a relatively new DB2 system (8.1) on Aix 5.1 and we are trying
to institute regular reorgs via scripts.

Does it matter if the index for a table or the table itself is
reorganized first?

If the script reorganizes a table then follows that with a reorganize
indexes all for that table is that a problem?

Right now the script issues

db2 -v "reorg table SIEBEL.$i"

followed by

db2 -v "reorg indexes all for table SIEBEL.$i"
I'm wondering if that is a problem.

Thanks in advance.

[Sean McKeough]

The final phase of offline reorg (what you're using) will rebuild all
indexes from scratch, so the reorg indexes step is not needed.

If you were using online (inplace) reorg, you'd want to reorg the
indexes seperately if your goal is to reclaim space...

da*******@yahoo.com wrote:

I have a question concerning reorgs.

We have a relatively new DB2 system (8.1) on Aix 5.1 and we are trying
to institute regular reorgs via scripts.

Does it matter if the index for a table or the table itself is
reorganized first?

If the script reorganizes a table then follows that with a reorganize
indexes all for that table is that a problem?

Right now the script issues

db2 -v "reorg table SIEBEL.$i"

followed by

db2 -v "reorg indexes all for table SIEBEL.$i"
I'm wondering if that is a problem.

Thanks in advance.

[datapro01]

Sean:

If I have a table siebel.S_SRM_REQUEST with a number of associated
indexes.

The table and some of the indexes show in the results of a reorgchk,
that they need a reorg.

I do a db2 -v reorg on that table followed by a runstats, followed by
another reorgchk.
The table no longer shows as needing a reorg but the indexes do..

[Pierre Saint-Jacques]

Look at the structure of the reorg command: db2 ? reorg ..
You do not indicate and therefore I think you are not doing an IN PLACE
reorg.
So, your defaults are allow read and reclaim space.
You do not say you specify an index so I'll assume you do not specify one.

In this case the table is cleaned, the indexes are rebuilt and none of
them will force a sequencing of the rows. So, after reorg, if you
runstats it does not change anything as the indexes will show that they
do not cluster very well.

You would need to specify one and only one index to force the clustering
at reorg time. It would show, after runstats, 100% clustering and
obviously none of the ohers would be expected to. So they would show
that a reorg is indicated.

If you command:
db2 reorg table .... index .... allow read
You get a classic V7 reorg which causes the table to be cleaned and all
indexes to be rebuilt, while read access is allowed on the table.
If you command:
db2 reorg table ... index .... inplace allow (read or write)
You get the able cleaned and sequenced on the index. The indexes are
NOT rebuilt ever, they are updated/maintained. And NO space is reclaimed.
If you command:
db2 reorg table .... NO INDEX SPECIFIED inplace allow (read or write
You get no index rebuild and space reclaimed.

Doing: db2 -v "reorg indexes all for table SIEBEL.$i"
This has nothing to do with reorganizing or rebuilding indexes. All it
does is allows you to specify how your type 2 indexes are going to be
cleaned up or if you want to convert yout type 1 indexes to type 2.

HTH, Pierre.

da*******@yahoo.com wrote:

Sean:

If I have a table siebel.S_SRM_REQUEST with a number of associated
indexes.

The table and some of the indexes show in the results of a reorgchk,
that they need a reorg.

I do a db2 -v reorg on that table followed by a runstats, followed by
another reorgchk.
The table no longer shows as needing a reorg but the indexes do..

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