Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2006
    Posts
    105

    Unanswered: DB2V9.5 Reorg Issue.

    Hello All,

    In a newly built server, we installed db2v9.5, and then restored and migrated db2v8 backup image on the same. It was working fine.
    DB2level : DB2V9.5 FP4.

    We then ran reorg for all system tables and user tables of the database. After that , we are not able to select count(*) of the table , getting transaction log full. The above issue we faced for some set of tables.

    Able to see some errors like reorg of indexes not happened properly in the diag.log and notification log in addition to the transaction log full.

    ADM5572I Some index(es) on table are marked invalid and will require rebuilding. DB2 will automatically rebuilt the index(es) on this table, which will result in a super exclusive Z table lock for the duration of the rebuild and the unit of work.

    IS that transaction log full because of - reorging of index not happened properly.

    or any other related issue ?

    Did any one of you face this issue ?

    Please help me in identifying the root cause of this to avoid the same in future.

    Thanks for your help in advance.
    Meena.s

  2. #2
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    do not forget to set-up archival logging
    perform an offline reorg when possible: less logging & 20x faster

  3. #3
    Join Date
    Sep 2006
    Posts
    105
    Archival logging enabled(logarchmeth1 to tsm) and i did offline reorg only.

    Thanks

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Clearly, it's the index rebuild that fills the logs. If you don't use HADR, you could set LOGINDEXBUILD to OFF to avoid that. Be aware of the implications though.

  5. #5
    Join Date
    Sep 2006
    Posts
    105
    Thanks But its a HADR Built one.

  6. #6
    Join Date
    May 2010
    Location
    India
    Posts
    84
    Provided Answers: 2
    Check your configuration parameters for log space (LOGFILSIZ, LOGPRIMARY, LOGSECOND) and increase them. Otherwise, the same issue will arise when you run reorgs next time. Log space used by DB2 in 9.5 is more than log space used by 8.2

    Adjusting the log space size in migrated databases

    Satya..

  7. #7
    Join Date
    Jul 2011
    Location
    USA
    Posts
    80
    Please chek diag carefully for any application handle.
    May be any transaction is holding active log and it is not releasing it.

  8. #8
    Join Date
    Jun 2009
    Posts
    272
    I've corrected your sentence.

    Is that transaction log full because of - reorging of index not happened properly.

    It is actually the other way. Reorg on indexes got failed because the transaction log was full. As suggested by Satya, you may have to consider increasing your logprimary/logsecondary/logfilesz parameters or enable infinite logging by setting logsecond to -1 and revert back the changes once the reorg finishes.
    Last edited by blazer789; 07-10-11 at 11:14.

  9. #9
    Join Date
    Sep 2006
    Posts
    105
    Thanks all for your reply.

    For rerorg to complete, i have made logsecond value to 200 temporarily and then did reorg.
    After completion , i have set back to old values.
    I didnt had a chance to run reorg again to check whether the same issue pops up.
    As suggested by Satya, I will check the values of logprimary/logsecondary/logfilesz parameter and will let you know the updates.

    Thanks All.

Posting Permissions

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