First, I wish to say that I have very little experience with DB2. However, I've been tasked with tracking down the problem and solution to this problem.
This is a DB2 database, and our testers are saying that they get:
The transaction log for the database is full.. SQLCODE=-964, SQLSTATE=57011, DRIVER=4.13.127 SQL Code: -964, SQL State: 57011
I did increase the number of LOGPRIMARY to '40'. However, it appears that the transaction logs are still full. I then decided to 'prune' the transaction logs. The procedure I have is to Prune all prior to the active transaction log. However, I cannot determine the active log:
$ db2 get db config for MyDB | grep -i 'log'
Log retain for recovery status = NO
User exit for logging status = NO
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 278
Log buffer size (4KB) (LOGBUFSZ) = 1997
Log file size (4KB) (LOGFILSIZ) = 1024
Number of primary log files (LOGPRIMARY) = 40
Number of secondary log files (LOGSECOND) = 12
Changed path to log files (NEWLOGPATH) =
Path to log files = /home/db2/db2inst1/db2inst1/NODE0000/SQL00001/LOGSTREAM0000/
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) =
First active log file =
Block log on disk full (BLK_LOG_DSK_FUL) = NO
Block non logged operations (BLOCKNONLOGGED) = NO
Percent max primary log space by transaction (MAX_LOG) = 0
Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0
Percent log file reclaimed before soft chckpt (SOFTMAX) = 520
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
HADR spool log data limit (4KB) (HADR_SPOOL_LIMIT) = 0
HADR log replay delay (seconds) (HADR_REPLAY_DELAY) = 0
First log archive method (LOGARCHMETH1) = OFF
Archive compression for logarchmeth1 (LOGARCHCOMPR1) = OFF
Options for logarchmeth1 (LOGARCHOPT1) =
Second log archive method (LOGARCHMETH2) = OFF
Archive compression for logarchmeth2 (LOGARCHCOMPR2) = OFF
Options for logarchmeth2 (LOGARCHOPT2) =
Failover log archive path (FAILARCHPATH) =
Number of log archive retries on error (NUMARCHRETRY) = 5
Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20
Log pages during index build (LOGINDEXBUILD) = OFF
Log DDL Statements (LOG_DDL_STMTS) = NO
Log Application Information (LOG_APPL_INFO) = NO
$
I do note that the transaction log files do appear in /home/db2/db2inst1/db2inst1/NODE0000/SQL00001/LOGSTREAM0000/.
Is transaction logging correctly setup ? Would increasing the logs be the answer? Or should I push back on the users to encourage them to do more commits?
Best Answer
You really should be reading manuals, not "pruning logs". Your database is set up for circular logging, which means there is nothing to "prune". If transactions occasionally require extra log space, increase the value of LOGSECOND. Too many primary logs will slow down the database startup, as they are preallocated. Secondary logs are allocated as needed, they will remain in the active log directory until the database is restarted.
"Pruning logs" will not affect the amount of active log space (with one exception that I will mention later): it is dictated purely by the sum of LOGPRIMARY and LOGSECOND, multiplied by LOGFILSIZ. If you were using the archive log mode, you would be able to remove older archived log files to free up some disk space, but the amount of active log space would not change. Even then, you would delete logs created prior to the previous backup, not prior to the first active log file.
Only if your archived logs and active logs share the same file system (which is not a good idea in itself), the uncontrolled accumulation of archived logs may prevent the active log space to grow, when necessary, to its configured maximum size.