Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2007
    Posts
    84

    Unanswered: How to know when reorg is complete

    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.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    db2pd -d sample -reorgs

    or

    SNAP_GET_TAB_REORG

    If doing manually, the first approach is recommended.
    Last edited by sathyaram_s; 02-23-11 at 14:16. Reason: corrected db2pd command option
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    You can also see start/stop time in the list history output.

  4. #4
    Join Date
    Feb 2007
    Posts
    84
    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

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •