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?