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

do not forget to set-up archival logging
perform an offline reorg when possible: less logging & 20x faster

Archival logging enabled(logarchmeth1 to tsm) and i did offline reorg only.

Thanks

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.

Thanks But its a HADR Built one.

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..

Please chek diag carefully for any application handle.
May be any transaction is holding active log and it is not releasing it.

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.

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.