I'm using DB2 v9.1.4 LUW on AIX server. If one person submits a reorg on indexes and others are monitoring, how can you tell when the reorg is complete? We had a situation where it wasn't showing in db2top but it was still running. It's tough to tell when it's complete in the db2inst1.nfy file. Here is what it says at the end of the file below, but it doesn't tell you this is the last index.

Is there any commands or SQL to run to see if the reorg of the index has finished? We did this reorg offline.

Thanks.

ADM5540W Beginning index rebuild of "1" index(es) on table "SY811 .F950411".

2011-02-20-19.48.34.402386 Instance:db2inst1 Node:000
PID:5328972(db2agent (PSFT811) 0) TID:1 Appid:*LOCAL.db2inst1.110221034451
data management sqldIndexCreate Probe:1 Database:PSFT811

ADM5541W Rebuilding index with IID "1" in object with ID "777" and table space
ID "3" on table "SY811 .F950411".

2011-02-20-19.48.34.427919 Instance:db2inst1 Node:000
PID:5328972(db2agent (PSFT811) 0) TID:1 Appid:*LOCAL.db2inst1.110221034451
data management sqldEndIndexCreate Probe:1 Database:PSFT811

ADM5542W Index rebuild of index(es) on table "SY811 .F950411" completed
successfully.

db2pd -d sample -reorgs

or

SNAP_GET_TAB_REORG

If doing manually, the first approach is recommended.

You can also see start/stop time in the list history output.

Okay, I ran this reorg statement in my test environment:
db2 reorg indexes all for table TableName ALLOW NO ACCESS

Then I ran this to see if I could see the reorg running and it showed nothing:

db2pd - d databasename -reorgs

Database Partition 0 -- Database WSIOBJS -- Active -- Up 0 days 00:16:44

Table Reorg Information:
Address TbspaceID TableID PartID MasterTbs MasterTab TableName Type IndexID TempSpaceID

Table Reorg Stats:
Address TableName Start End PhaseStart MaxPhase Phase CurCount MaxCount Status Completion




Then I ran this SQL and it showed no rows while it was running:

SELECT SUBSTR(TABNAME, 1, 15) AS TAB_NAME,
SUBSTR(TABSCHEMA, 1, 15) AS TAB_SCHEMA,
REORG_PHASE,
SUBSTR(REORG_TYPE, 1, 20) AS REORG_TYPE,
REORG_STATUS,
REORG_COMPLETION,
DBPARTITIONNUM
FROM SYSIBMADM.SNAPTAB_REORG ORDER BY DBPARTITIONNUM



What am I doing wrong here? Someone please explain.

Thank you.

CC

You run REORG INDEXES, so clearly table reorganization information will be missing because tables are not being reorganized. You may be able to see some information by running "db2pd -reorgs index" while the REORG command is still running.