Weekly Tips from DB2 Experts: Log full condition and indoubt transactions

Body

Hello DB2 DBAs!

Hope you are having a nice time during this holiday season!  

I recently helped a customer who was running into the disk full condition causing the database to hang with a twist :).  So wanted to share with you all!!

Typically it would be due to a open transaction which caused the transactions to exceeed log primary + log secondary logs.  db2diag.log would have details about that errant application handle which caused the log full condition to occur.  A typical entry would be like this.

2009-12-01-03.46.51.774000-360 E33840833H507 LEVEL: Error
PID : 3972 TID : 6112 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : SUDB
APPHDL : 0-923 APPID: *LOCAL.DB2.091201094635
FUNCTION: DB2 UDB, data protection, sqlpgResSpace, probe:2860
MESSAGE : ADM1823E The active log is full and is held by application
handle "923". Terminate this application by COMMIT, ROLLBACK or
FORCE APPLICATION.

However with this customer I did not see the details of the application handle that needs to either commit or rollback.  Application handle was an empty string. 

db2diag.log showed the following message:

2007-05-22-08.31.03.362272-420 E299516664C499 LEVEL: Error
PID : 17848 TID : 1 PROC : db2agntp (SAMPLE) 4
INSTANCE: db2inst11 NODE : 004 DB : SAMPLE
APPHDL : 0-298 APPID: *N0.db2prod1.070522153100
FUNCTION: DB2 UDB, data protection, sqlpgResSpace, probe:2850
MESSAGE : ADM1823E The active log is full and is held by application
handle "". Terminate this application by COMMIT, ROLLBACK or FORCE
APPLICATION.

2007-05-22-08.31.03.363753-420 I299517164C466 LEVEL: Error
PID : 17848 TID : 1 PROC : db2agntp (SAMPLE) 4
INSTANCE: db2inst11 NODE : 004 DB : SAMPLE
APPHDL : 0-298 APPID: *N0.db2prod1.070522153100
FUNCTION: DB2 UDB, data protection, sqlpWriteLR, probe:6680
RETCODE : ZRC=0x85100009=-2062548983=SQLP_NOSPACE
"Log File has reached its saturation point"
DIA8309C Log file was full.

It turned out cust was using a transaction manager and they had encountered a indoubt transaction which he was not aware of.  That caused the log full condition to occur but no details about the application handle!!

Walked customer through resolving the indoubt transaction using the
db2 list indoubt transactions with prompting command.

and used the c,r,f to try commit, rollback and forget in that order.  Here is the link to this document.
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.2pc.doc/doc/t0004636.html

After the indoubt transaction was resolved, log full condition went away and cust was back online!.  Hope you find this useful in your day to day life as a DB2 DBA :).

Murali

DB2 LUW Technical Support