Fixing “The transaction log for the database is full” problem in DB2

[IBM][CLI Driver][DB2/AIX64] SQL0964C The transaction log for the database is full. SQLSTATE=57011

The above mentioned error occured when database log file is not large enough for the application to create log files. The solution for this problem is to increase LOGFILSIZ, LOGPRIMARY and LOGSECOND parameters. When you will be going to increase the values for these parameters, you must keep in mind no to over allocate the space.

Calculate the required disk space

Following formula is useful for calculating the required disk space.

Disk space required: logfilsiz * (logprimary + logsecond) * page_size

Check DB2 settings

Connect to command prompt or shell with DB2 user and issue following command, replace DBNAME with your DB2 database.

db2 get db cfg for dbname

My log file setting was

Log file size (4KB) (LOGFILSIZ) = 61440
Number of primary log files (LOGPRIMARY) = 13
Number of secondary log files (LOGSECOND) = 4

I can check with the above formula, how much disk space currently it’s taking.

Disk space before configuration update: 61440 * (13 + 4) * 4 kB = 3.9 GB

Update DB2 configuration

I planned to increase LOGFILSIZ to 81920, LOGPRIMARY to 15 and LOGSECOND to 10.

Disk space after configuration update: 81920 * (15 + 10) * 4 kB = 7.8 GB

Issue following command, where you will replace dbname with your DB2 database

db2 update db cfg for dbname using logfilsiz 81920 logprimary 15 logsecond 10

Restart DB2

Restart DB2 using db2stop and db2start.