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 in a loop

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.

When an index is organised first, you will read less pages, if you want
to cluster the table data based on that index.
There are also other advantage of doing the index reorg first, based on that you have a clean and concise index structure.

regards.

Mujeeb

Way I see it is if you reorg a table, page#,slot# are changed for some records, rendering all indexes invalid; that's why in a classic reorg, when you do a table reorg, inexes are also reorganized afterwards automatically.

Mota


I am testing using the syntax

db2 reorg table tablename allow no access

is that considered classic?

I then do a runstst with detailed and indexes all on the table and an index still shows as needing to be reorged.

according to the first example under REORG INDEXES/TABLE in the COMMAND REFERENCE yours is a classic reorg. Why do you say your index needs a reorg; could you please post the snippet of your indexes indicating a reorg, from the reorgcheck report.

Apparently the indexes would show an out of cluster situation iunless they were designated in the reorg command to be used to order the table data.
thanks

What I usually did , is that

1. run the reorgchk command, it will automatically update the statistics.

2. Reorg, if need arise.

2. run the reorgchk command without updating the statistics.

regards

Mujeeb

When you update stats, tables are locked out. My preference is RUNSTATS ON TABLE tab1 ALLOW WRTE ACCESS; REORGCHK CURRENT STATISTICS ON TABLE ......; then a classic reorg; and again RUNSTATS ON TABLE tab1 ALLOW WRTE ACCESS; because the table is reorged and indexes altered, particularly the TYPE2 indexes in V8, a final runstats is necessary.

When doing an offline reorg, and you have a clustering index

is it best or even necessary to speciify the clustering index when you do the reorg
'reorg table using index'

"For an inplace table reorg, if a clustering index is defined on the table and an index is specified, it must be clustering index. If the inplace option is not specified, any index specified will be used. If you do not specify the name of an index, the records are reorganized without regard to order. If the table has a clustering index defined, however, and no index is specified, then the clustering index is used to cluster the table. You cannot specify an index if you are reorganizing an MDC table."

Generally speaking, if you defined a clustering index, then that is the order you want to rows after a reorg and during insert operations, so I don't know why you would need to specify an index in the reorg statement if you have already defined a clustering index.