IBM Support

Incase of high usage of transaction logs how to map Logspace usage with Application Handle

Question & Answer


Question

To understand why transaction log being consumed and we need to check which application is actually consuming the logspace.

Answer

We need to review following db2pd command in iteration.

db2pd -db <dbname> -file output.txt -logs -transactions

db2pd -logs
-This will give us, Current Log we are writing to and the number of pages being written into the
same,

Example
Logs:
Current Log Number 5844
Pages Written 144

SQL05844.log us current active log and 144 pages were written into same.

db2pd -transaction will give the details on
-What transaction running at that time ,
-Which transaction is performing WRITE operation
-Log space consumed by that Application Handle

Example:
Transactions:
Address AppHandl [nod-index] TranHdl Locks State Tflag Tflag2
Firstlsn Lastlsn Firstlso Lastlso SpaceReserved
LogSpace TID AxRegCnt GXID ClientUserID ClientWrkstnName ClientApplName ClientAccntng

0x00007F21146E7280 297 [000-00297] 28 11 WRITE 0x00000000 0x00000000 0x00000001111C57C7 0x00000001111D624F 722880295617 723897463760 3942 1017174741 0x0000025B5064 1 0 n/a 10.77.68.91 n/a
n/a


We need to review columns
LogSpace -Actual logspace used by Application
AppHandl -Application handle

Application Handle 297 is using logspace 1017174741 and performing WRITE operation.


Alternatively,
we can also use Application Snapshot when UOW LOG SPACE USED monitor switch is enabled.

Use following command to list Application handle and corresponding Logspace used.

db2 get snapshot for applications on sample | grep -i -e "Application handle" -e "UOW log space used"

Related Information

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Recovery - Logging","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.7;10.1;10.5","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21983541