DB2 v9.5.1 ESE on AIX v6.1
it is taking forever to reorganize one of the tables in our prod. database. Are there any ways to make it faster?

We do it in the maintenance window and use the following commands:

REORG INDEXES all for table <table_name> allow no access
and then
REORG TABLE <table_name> allow no access

Table has 39 cols (incl. 5 varchar cols up to 1024b length), 73,990,776 rows, 7 indexes (not cluster), no LOBs and it’s in the 16K-page size tablespace.

We are in v9.5 so making it partitioned will not help (as far as I know in v9.7 we can reorg table’s partitions separately).

Thanks in advance

Just "reorg table schema.table". The table will be reorganized offline AND all the indexes go along. The separate "reorg indexes" is superfluous.

Are you really still on fixpack 1 of Version 9.5? You certainly have a lot of guts.

hi
continue with above topic
wht is the major difference between
cleanup only all and cleanup only pages -- it relates to NUM_EMPTY_LEAF and NUMRIDS_DELETED
kindly suggest
regds
Paul

As per the information in the manual, the main difference is:

The CLEANUP ONLY PAGES option will search for and free committed pseudo empty pages. A committed pseudo empty page is one where all the keys on the page are marked as deleted and all these deletions are known to be committed. The number of pseudo empty pages in an indexes can be determined by running RUNSTATS and looking at the NUM EMPTY LEAFS column in SYSCAT.INDEXES. The PAGES option will clean the NUM EMPTY LEAFS if they are determined to be committed.

The CLEANUP ONLY ALL option will free committed pseudo empty pages, as well as remove committed pseudo deleted keys from pages that are not pseudo empty. < removed some info about merging pages > The number of pseudo deleted keys in an index , excluding those on pseudo empty pages, can be determined by running RUNSTATS and then selecting the NUMRIDS DELETED from SYSCAT.INDEXES. The ALL option will clean the NUMRIDS DELETED and the NUM EMPTY LEAFS if they are determined to be committed.

IBM DB2 9.5 Information Center for Linux, UNIX, and Windows



CLEANUP ONLY
- PAGES will cleanup committed pseudo empty pages (pages on which all keys are marked deleted and committed) -> NUM_EMPTY_LEAFS (if committed)
- ALL will do what PAGES option does plus cleanup committed pseudo deleted keys -> NUM_EMPTY_LEAFS + NUMRIDS_DELETED (if committed)

thks for the reply
i m sorry i still have a question i got abt pseudo empty pages
but not able to get pseudo deleted keys --
manual says that -- the total number of logically deleted RIDs stored in NUMRIDS_DELETED
kindly suggest
regds
Paul

fixpack 3a. My mistake

From the manual:
NUMRIDS_DELETED is number of pseudo-deleted RIDs


Regarding REORG with CLEANUP ONLY ALL:
Some sections of the manual mention that it does the cleanup of pseudo-deleted keys and some others mention that it does the cleanup of pseudo-deleted RIDs. I'm not sure why... Keys and RIDs are not the same thing. For non-unique index, a RID can be marked as pseudo-deleted but not the key. I think pseudo-deleted key is the key for which all RIDs are marked deleted, but I'll need to double check this info.

I verified that:

We don't keep a count of pseudo-deleted keys (keys in the index in which all the RIDs have been pseudo-deleted). We only have a count of pseudo-deleted RIDs (NUMRIDS_DELETED). If all the RIDs for a given key (assuming non-unique index) are deleted, then the key is removed from the index when the RIDs are all removed.