I can tell that something is hogging the transaction log on the DB2 for one of our Websphere Commerce installations. Basically I have a burst of transaction log files that are timestamped 5 days ago. I expect that this means that something was started 5 days ago and its transaction is still active.
How do I debug this issue? All the hints I can find are about increasing the log size, but in this case it would just be a band aid…
EDIT:
Using db2pd as suggested by mustachio I get the output blow. The way I read it whatever issue was hogging the log is now over, as all live transactions report using 0 log space.
Is that correct?
Further, when I check the log directory (see below), there's been no real change since yesterday (see list of log files below). I take that as supporting evidence that the log hogging issue is no longer in effect.
Log file directory contents:
Directory of E:\db2log\BK_PROD
04-10-2013 06:14 <DIR> .
04-10-2013 06:14 <DIR> ..
16-12-2012 20:03 16.756.736 S0026878.LOG
28-09-2013 17:52 40.968.192 S0037690.LOG
28-09-2013 17:52 40.968.192 S0037691.LOG
28-09-2013 17:55 40.968.192 S0037692.LOG
28-09-2013 17:56 40.968.192 S0037693.LOG
28-09-2013 17:57 40.968.192 S0037694.LOG
28-09-2013 21:44 40.968.192 S0037695.LOG
28-09-2013 23:11 4.526.080 S0037696.LOG
28-09-2013 23:35 40.968.192 S0037697.LOG
04-10-2013 06:14 40.968.192 S0037900.LOG
04-10-2013 06:04 40.968.192 S0037901.LOG
04-10-2013 06:04 40.968.192 S0037902.LOG
04-10-2013 06:05 40.968.192 S0037903.LOG
04-10-2013 06:06 40.968.192 S0037904.LOG
04-10-2013 06:07 40.968.192 S0037905.LOG
04-10-2013 06:08 40.968.192 S0037906.LOG
04-10-2013 06:09 40.968.192 S0037907.LOG
04-10-2013 06:10 40.968.192 S0037908.LOG
04-10-2013 06:12 40.968.192 S0037909.LOG
04-10-2013 06:13 40.968.192 S0037910.LOG
04-10-2013 06:14 40.968.192 S0037911.LOG
10-07-2013 13:55 512 SQLLPATH.TAG
22 File(s) 799.678.976 bytes
2 Dir(s) 18.659.753.984 bytes free
Output from db2pd:
Database Partition 0 -- Database BK_PROD -- Active -- Up 85 days 18:10:58 -- Date 10/04/2013 08:06:25
Transactions:
Address AppHandl [nod-index] TranHdl Locks State Tflag Tflag2 Firstlsn Lastlsn SpaceReserved LogSpace TID AxRegCnt GXID ClientUserID ClientWrkstnName ClientApplName ClientAccntng
0x000007FFF935FF00 7189 [000-07189] 3 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006BFFDC579 1 0 n/a n/a n/a n/a
0x000007FFF9360E00 6536 [000-06536] 4 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006C00F7169 1 0 n/a n/a n/a n/a
0x000007FFF9361D00 8434 [000-08434] 5 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006AD6FD534 1 0 n/a n/a n/a n/a
0x000007FFF9362C00 5356 [000-05356] 6 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006C00F71BB 1 0 n/a n/a n/a n/a
0x000007FFF9363B00 7769 [000-07769] 7 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006AD6FD536 1 0 n/a n/a n/a n/a
0x000007FFF9364A00 6860 [000-06860] 8 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006AD6FD537 1 0 n/a n/a n/a n/a
0x000007FFF9365900 49089 [000-49089] 9 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006AD6FD538 1 0 n/a n/a n/a n/a
0x000007FFF9366800 5929 [000-05929] 10 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006AD6FD539 1 0 n/a n/a n/a n/a
0x000007FFF9367700 5501 [000-05501] 11 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006AD6FD53A 1 0 n/a n/a n/a n/a
0x000007FFF9368600 6206 [000-06206] 12 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006AD6FD53B 1 0 n/a n/a n/a n/a
0x000007FFF9369500 7663 [000-07663] 13 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006AD6FD53C 1 0 n/a n/a n/a n/a
0x000007FFF936A400 7083 [000-07083] 14 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006AD6FD53D 1 0 n/a n/a n/a n/a
0x000007FFF936B300 5350 [000-05350] 15 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006AD6FD540 1 0 n/a n/a db2evml_DB2DETAILDEADLOCK n/a
0x000007FFF936C200 20935 [000-20935] 16 1 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006C00F78D9 1 0 n/a s-bkweb02-f n/a n/a
0x000007FFF936D100 42140 [000-42140] 17 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006BFE59045 1 0 n/a n/a n/a n/a
0x000007FFF936FE00 23187 [000-23187] 20 1 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006C00F7928 1 0 n/a s-bkweb02-f n/a n/a
0x000007FFF9373A00 41407 [000-41407] 24 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006C00F7932 1 0 n/a s-bkweb02-f n/a n/a
0x000007FFF9375800 59734 [000-59734] 26 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006C00F793B 1 0 n/a s-bkweb02-f n/a n/a
0x000007FFF9376700 11757 [000-11757] 27 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006C00F7645 1 0 n/a s-bkweb02-f n/a n/a
0x000007FFF9377600 22364 [000-22364] 28 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006C00F78D8 1 0 n/a s-bkweb02-f n/a n/a
0x000007FFF9379400 13091 [000-13091] 30 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006BF782A75 1 0 n/a n/a n/a n/a
0x000007FFF937A300 54834 [000-54834] 31 1 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006C00F791B 1 0 n/a s-bkweb02-f n/a n/a
0x000007FFF937C100 23530 [000-23530] 33 1 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006C00F792B 1 0 n/a s-bkweb02-f n/a n/a
0x000007FFF937D000 5248 [000-05248] 34 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006C00F792F 1 0 n/a s-bkweb02-f n/a n/a
0x000007FFF937DF00 60862 [000-60862] 35 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006C00F78D7 1 0 n/a s-bkweb02-f n/a n/a
0x000007FFF937EE00 26210 [000-26210] 36 1 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006C00F7926 1 0 n/a s-bkweb02-f n/a n/a
0x000007FFF9380C00 38911 [000-38911] 38 1 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006C00F791A 1 0 n/a s-bkweb02-f n/a n/a
0x000007FFF9387500 20973 [000-20973] 45 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006C00F77E2 1 0 n/a s-bkweb02-f n/a n/a
0x000007FFF9388400 35066 [000-35066] 46 3 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006C00F793F 1 0 n/a s-bkweb02-f n/a n/a
0x000007FFF938A200 10119 [000-10119] 48 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006BFB0556F 1 0 n/a n/a n/a n/a
0x000007FFF938CF00 4945 [000-04945] 51 0 READ 0x00000000 0x00000000 0x0000000000000000 0x0000000000000000 0 0 0x0006C00ED6D5 1 0 n/a s-bkweb02-f n/a n/a
Best Answer
If you're using a recent version of DB2 on Linux, Unix, or Windows (LUW), you can use the db2pd tool to view all current transactions and how much log space they use (the LogSpace column):