This post’s purpose is to share information on how to solve this problem, for those out there that uses DB2 as a development environment in their machines or even on servers without a DBA.
You can solve the following error "SQL0964C Transaction log for database is full" based on the following steps
- Connect to the DB
- Get the correct log configuration (please check if the current value and delayed value are equal)
Type the command below and check for these three parameters (logfilsiz,logprimary,logsecond)
db2 get db cfg for DBNAME show detail
db2 get db cfg for DBNAME show detail | grep -i LOG
- Decide if the log needs to be increased.
The parameter LOGFILSIZ shows the amount of 4k pages, so if is showing 1024 each log will be about 4MB. Multiply this by the number of primary and secondary logs, and you will have your maximum log size
- Update your parameters
You can choose between increasing one of the following parameters: logfilsiz,logprimary,logsecond
- LOGFILSIZ - Size of log files configuration parameter
This parameter defines the size of each primary and secondary log file. The size of these log files limits the number of log records that can be written to them before they become full and a new log file is required.
This size is measured in 4k pages
- LOGPRIMARY - Number of primary log files configuration parameter
This parameter allows you to specify the number of primary log files to be preallocated. The primary log files establish a fixed amount of storage allocated to the recovery log files.
- LOGSECOND - Number of secondary log files configuration parameter
This parameter specifies the number of secondary log files that are created and used for recovery log files (only as needed)
In the example below we are setting each log size to about 400MB, and 15 primary logs this means that every time there will be at least 15 log files available to this database and a maximum of 45 log files available.
db2 update db cfg for DBNAME using logprimary 15
db2 update db cfg for DBNAME using logsecond 30
db2 update db cfg for DBNAME using logfilsiz 100000
- Close your connection
- If there is no connections active you will receive the following message:
"SQL1611W No data was returned by Database System Monitor."
- If there are you will see a list of connections:
Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
------- ----------- ------ ----------------------- -------- -----
WFM db2bp.exe 1008 G902DA8E.H50D.01CD46194 WFM_HIST 1
WFM db2taskd 671 *LOCAL.DB2.080606190231 WFM_HIST 1
WFM db2stmm 675 *LOCAL.DB2.080606190230 WFM_HIST 1
- In this case we will need to force this connections off, using the following command:
db2 "force application (1008, 671, 675)"
- These numbers shown are from Appl. Handle column above, if you use force application all, all the connections in the instance will be force off.
- Double check if there are any active connection if not, just reconnect again, and the logs will be allocated in the new size. This process may take a couple of minutes depending of the system