Weekly Tips from DB2 Experts: How to check what is taking up the LOG SPACE?

Body

1. The below command will help us identify which application is consuming the maximum LOG space:

db2 "SELECT SUBSTR(AI.PRIMARY_AUTH_ID,1,10) AS AUTHID, SUBSTR(AI.APPL_NAME,1,10) AS APPLNAME, SUBSTR(AI.APPL_STATUS,1,10) AS STATUS, INT(A.UOW_LOG_SPACE_USED) AS LOG_SPACE, A.UOW_START_TIME FROM SYSIBMADM.SNAPAPPL A, SYSIBMADM.SNAPAPPL_INFO AI WHERE A.AGENT_ID = AI.AGENT_ID AND A.UOW_LOG_SPACE_USED > 0 ORDER BY A.UOW_LOG_SPACE_USED DESC"

Example:

AUTHID     APPLNAME     STATUS     LOG_SPACE     UOW_START_TIME
----------     ----------     ----------     -----------     --------------------------
TESTUSER    db2bp         UOWWAIT     12552621     2014-03-06-15.36.12.891110


2. We can get  similar information using the below db2pd command ,where the column  'SpaceReserved' shows the log_space utilization
 

db2pd -db sample -transactions

Example:
Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 00:37:45 -- Date 2014-09-30-11.52.20.532941

Transactions:
Address            AppHandl [nod-index] TranHdl    Locks      State   Tflag      Tflag2     Firstlsn           Lastlsn            Firstlso             Lastlso              SpaceReserved   LogSpace        TID            AxRegCnt   GXID     ClientUserID                   ClientWrkstnName               ClientApplName                 ClientAccntng
0x00002AAAEDE2D080 2309     [000-02309] 3          3          SAVEPNT 0x00000000 0x00000000 0x0000000000215D52 0x00000000002A42FD 394837979            440494425            3255514         48912034        0x0000000900AE 1          0        n/a                            n/a                            n/a                            n/a