DB2 LUW Error Message: SQL0964C

Error Message

SQL0964C  The transaction log for the database is full.  SQLSTATE=57011

or

DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0964C  The transaction log for the database is full.  SQLSTATE=57011

In the DB2 diagnostic log, this error often shows up as DIA8309C:

You can search for occurences of this issue in the DB2 diagnostic log using:

db2diag -e DIA8309C

When You Might Encounter This Error Message

This error message can occur with any statement that requires transaction logging – insert, update, import, etc.

What is Really Happening?

The active transaction logs for the database have become full. The disk that the transaction logs are on may or may not be full.

There are two primary scenarios with this error message. The first is that a transaction requiring more active log space than is available, given both the size of the transaction logs and the log utilization by other active connections currently executing. If this is the case, the transaction has likely filled up the transaction logs and rolled back.

The second is a scenario called log file saturation. This happens when a connection does something that requires logs without committing or rolling back, and is then idle for a long time – maybe even days. This scenario is somewhat more likely in a non-production environment. DB2 cannot release/archive that older log file until the transaction has committed or rolled back, so when it gets to the full size of LOGFILSIZ * (LOGPRIMARY + LOGSECOND) after that log record, it cannot allocate log files, even if all the files in between are completed and ready for archiving.

How to Resolve

If you have a single transaction that is eating up active log space, you need to address the transaction. Often it may be a large delete, but it may have other actions. The important thing to do is to break the transaction up into smaller pieces. This may involve breaking one DELETE up into many smaller DELETE statements, specifying a commitcount on an IMPORT, or taking other actions to break up the transaction and issue multiple commits. In some cases, you may want to consider increasing one of the logging parameters, but that is often a secondary solution for this problem rather than the primary preferred solution. Increasing logging parameters is more frequently an acceptable solution when a database has just recently gone live or recently seen a large increase in volume. When increasing logging parameters the frequency of log archives during normal activity should be taken into account.

If you have log file saturation, it likely will not clear itself up – a rollback will not be triggered for the problem connection. This means that you must find the problem connection and go force it off of the database. To find the application handle, you can parse the DB2 diagnostic log for ADM1823E. The results will look something like this:

$ db2diag -e ADM1823E
2015-11-12-09.24.28.952807-420 E90413E633            LEVEL: Error
PID     : 14896                TID : 140013098493696 PROC : db2sysc
INSTANCE: db2inst1             NODE : 000            DB   : SAMPLE
APPHDL  : 0-7                  APPID: REDACTED
AUTHID  : DB2INST1             HOSTNAME: REDACTED
EDUID   : 18                   EDUNAME: db2agent (SAMPLE)
FUNCTION: DB2 UDB, data protection services, sqlpgResSpace, probe:2860
MESSAGE : ADM1823E  The active log is full and is held by application handle 
          "0-7".  Terminate this application by COMMIT, ROLLBACK or FORCE 
          APPLICATION.

In this example, the application handle of the problem application is 7. You can also get this information using a snapshot:

$ db2 get snapshot for database on sample |grep oldest
Appl id holding the oldest transaction     = 7

Or using SQL against a monitoring table function:

$ db2 connect to sample

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.5
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE

$ db2 "select APPLID_HOLDING_OLDEST_XACT from table(mon_get_transaction_log(-2))"

APPLID_HOLDING_OLDEST_XACT
--------------------------
                         7

  1 record(s) selected.</code></pre>
Once you have that application handle, it is important to see how long it has been idle to determine if it might be something that is still active, indicating you instead have a problem with multiple applications, transaction size, or with transaction log file size:
<pre><code>select uow_start_time
	, timestampdiff(4,current timestamp - uow_start_time) idle_minutes 
from table(mon_get_connection(7,-2))

UOW_START_TIME             IDLE_MINUTES
-------------------------- ------------
2015-12-23-15.33.04.031182           16

  1 record(s) selected.

In this case, 16 minutes makes it likely that I have to check very closely with the user or application owner before forcing off the connection.

If I determine that the connection should be forced, this is how to force it off:

$ db2 "force application (7)"
DB20000I  The FORCE APPLICATION command completed successfully.
DB21024I  This command is asynchronous and may not be effective immediately.

Always be very cautious in forcing off connections, as it causes the transaction to be rolled back, and can cause issues with some applications.