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