Results 1 to 6 of 6
  1. #1
    Join Date
    May 2003
    Posts
    57

    Unanswered: Reorg indexes lasts 24hours on one table?!

    Hello all!

    DB2 v8, AIX 5.2

    I've restored db from v7 to v8 and now I'm trying to restore indexes.
    I had table that is 24GB large (with 29 indexes on it) and the command
    "REORG INDEXES ALL FOR TABLE DB2ADMIN.table1 ALLOW NO ACCESS CONVERT" lasted 3 hours.
    Now I'm tring to do the same with a 13GB table (and 14 indexes) and it lasts over 24 hours now. And I can see that db2 is all the time changing that index file. (SQL00113.DAT, SQL00113.INX it is constantly modified according to "ls -al"). I don't how to check what happend, or should I stop it? I have 24 hours more to finish that... so I can still wait or do something. I don't want to stop it if there is possibility to restore that table again. There is nobody working on that machine during that time and db2 has all the resources for itself.

    Somebody has as advice?

    Thank you in advance :-)
    NNicole

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by NNicole
    Hello all!

    DB2 v8, AIX 5.2

    I've restored db from v7 to v8 and now I'm trying to restore indexes.
    I had table that is 24GB large (with 29 indexes on it) and the command
    "REORG INDEXES ALL FOR TABLE DB2ADMIN.table1 ALLOW NO ACCESS CONVERT" lasted 3 hours.
    Now I'm tring to do the same with a 13GB table (and 14 indexes) and it lasts over 24 hours now. And I can see that db2 is all the time changing that index file. (SQL00113.DAT, SQL00113.INX it is constantly modified according to "ls -al"). I don't how to check what happend, or should I stop it? I have 24 hours more to finish that... so I can still wait or do something. I don't want to stop it if there is possibility to restore that table again. There is nobody working on that machine during that time and db2 has all the resources for itself.

    Somebody has as advice?

    Thank you in advance :-)
    NNicole
    Take a snapshot... SNAPSHOT_TBREORG has all the information about the reorg status, including a progress indicator.

    db2 "select * from table(SNAPSHOT_TBREORG('MYDB', -1)) as snap "

    where MYDB is your database name.
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    May 2003
    Posts
    57
    Jonathan,

    The progres indicator showed that db2 was not doing aynthing so I killd the proces.

    Thank you
    :-)

  4. #4
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by NNicole
    Jonathan,

    The progres indicator showed that db2 was not doing aynthing so I killd the proces.

    Thank you
    :-)
    That's interesting, I wonder what hung it? Maybe DB2 support could help further.
    --
    Jonathan Petruk
    DB2 Database Consultant

  5. #5
    Join Date
    Dec 2010
    Posts
    123

    Reorg status

    Quote Originally Posted by J Petruk View Post
    Take a snapshot... SNAPSHOT_TBREORG has all the information about the reorg status, including a progress indicator.

    db2 "select * from table(SNAPSHOT_TBREORG('MYDB', -1)) as snap "

    where MYDB is your database name.
    Here progress indicator means REORG_STATUS ?????
    REORG_STATUS=1 means.....
    can anybody explain different values for REORG_STATUS
    How can i find weather locks are stopping Reorg?????????pls help me out Thnx in aadvance...

  6. #6
    Join Date
    Jan 2010
    Posts
    335
    Risen from the dead!

    SNAPSHOT_TBREORG - IBM DB2 9.7 for Linux, UNIX, and Windows
    Just check the sqlmon.h. You'll find it in the include-dir of the DB2-Install-Directory.

    You can check the data also from the Admin-View:
    sysibmadm.SNAPTAB_REORG

Posting Permissions

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