1

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
Improve this question
6
  • 1
    "this means that something was started 5 days ago and its transaction is still active" -- No, it does not mean that at all. It means that five days ago there was an activity that generated more than usual volume of logs, that's all.
    – mustaccio
    Commented Oct 3, 2013 at 11:51
  • OK. I was under the impression that the transaction log only contained records of active transactions, not records of old, completed transactions.
    – Anders Johansen
    Commented Oct 3, 2013 at 12:21
  • Which OS are you talking about? DB2 and WebSphere run on several platforms. Your solution will likely be platform dependent.
    – WarrenT
    Commented Oct 3, 2013 at 13:32
  • Running on Windows.
    – Anders Johansen
    Commented Oct 4, 2013 at 6:36
  • @mustaccio So the reason the log files are still around in the directory is that these are the primary log space, and they are pre-allocated?
    – Anders Johansen
    Commented Oct 4, 2013 at 7:06

1 Answer 1

Reset to default
4

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): 

$ db2pd -d tst1 -transactions

Database Member 0 -- Database TST1 -- Active -- Up 3 days 00:34:56 -- Date 2013-10-03-10.19.08.041274

Transactions:
Address            AppHandl [nod-index] TranHdl    Locks      State   Tflag      Tflag2     Firstlsn           Lastlsn            Firstlso             Lastlso              SpaceReserved   LogSpace        TID            AxRegCnt   GXID     ClientUserID                   ClientWrkstnName               ClientApplName                 ClientAccntng                  
...
0x00007F05AE48CA80 17       [000-00017] 686        5          WRITE   0x00000000 0x00000000 0x000000000007BF73 0x000000000007BF75 103504642            103504834            328             616             0x00000000F64C 1          0        n/a                            n/a                            n/a                            n/a
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.