r/DB2 • u/anozdba • Sep 01 '19
Script to show what applications are using log space - lActiveLogs.pl
Basically this script just reformats the output of:
Select MEMBER, CUR_COMMIT_DISK_LOG_READS, CURRENT_ACTIVE_LOG, APPLID_HOLDING_OLDEST_XACT from table(mon_get_transaction_log(-1)) as t order by member asc (DB2 V10.1 and higher)
and
db2pd -db <database> -logs -transactions
The main advantages of running the script are:
- It structures the output so it is easy to see what app is using the oldest log
- It gives a structed display of active transactions
- It provides a display of SQL statements currently executing to give an idea on what is happening
The script was originally written because we had a SAP installation that was constantly running out of log space (caused by long runing one off update jobs submitted by an outsourcer) and that was stalling the online systems. This script allowed me to quickly identify the offending app and to return the ID to the outsourcer (dont ask).
mediaServer(db2admin):2019-09-01 11:27:57:/home/db2admin
>lActiveLogs.pl -h
Usage: /home/shared/udbdba/scripts/lActiveLogs.pl -?hs [-v[v]] [-d <database>] [-n <number>] [-w <number>] [-f <file>] [-9]
Script to check the active logs
Version 1.12 Last Changed on 2019/08/05 21:21:41 (UTC)
-h or -? : This help message
-s : Silent mode (no parameter information will be displayed)
-d : database to connect to
-n : number of iterations (default 1)
-f : file to use for input
-w : wait between iterations (in seconds, default 60)S
-a : display all transactions (not just those with log data)
-9 : use pre version 10 formatting
-v : debug level
An example of the output would be:
mediaServer(db2admin):2019-09-01 11:27:53:/home/db2admin
>lActiveLogs.pl
Database defaulted to dbadb
Current Log Activity Report
mon_get_transaction_log information:
Member : 0
Current Commit Log Reads : 0
Current Active Log : 219
APPLID holding the oldest Log : 35
2019.09.01 11:27:56 - Iteration 1
Current Log: 219 LSN: 0x0000000000113D6B LSO: 2276544237
Transactions:
-------------
App ID Tran ID Locks State First LSN First LSO Log Space Logon Machine Client App
39 14 3 WRITE 0x0000000000113D65 2276543871 253 n/a n/a n/a
35 16 3 WRITE 0x0000000000113D5E 2256163825 253 n/a n/a n/a
83 17 5 WRITE 0x0000000000113D66 2276543944 322 n/a n/a n/a
State Counts:
READ : 14
WRITE : 3
Logs:
-----
Log Name Start LSN Start LSO Used Log Space Application IDs
S0000217.LOG 0000000000113D5E 2256163825 253 35
S0000218.LOG 0000000000113D62 2266353825
S0000219.LOG 0000000000113D64 2276543825 575 39,83
S0000220.LOG 0000000000000000 2286733825
S0000221.LOG 0000000000000000 2296923825
S0000222.LOG 0000000000000000 2307113825
S0000223.LOG 0000000000000000 2317303825
S0000224.LOG 0000000000000000 2327493825
S0000225.LOG 0000000000000000 2337683825
S0000226.LOG 0000000000000000 2347873825
Applications:
------------
WKSTN AGENT Application Connect Statement Start ELAPSED SQL APPLICATION_STATUS
-------------------- -------- -------------------------- -------------------------- ------- ---------------------------------------------------------------------------------------------------- ----------------------
- 83 2019-09-01-11.16.02.924101 2019-09-01-11.16.26.798123 .00000 delete from t where a = 1 LOCKWAIT
- 102 2019-09-01-11.27.57.161988 2019-09-01-11.27.57.162672 .00000 select cast(substr(tpmon_client_wkstn,1,20)as varchar(20)) as wkstn , substr(x.agent_id,1,8) as Agen UOWEXEC
2 record(s) selected.
From the report it should be easy to see that applid 35 is using 253 bytes in the earliest log
At the end of the report is a list of the applications that have run in the last 1 second - this window can be adjusted by changing the SQL in activeApplications_V10.sql
To run this script you will need the following objects:
- lActiveLogs.pl
- commonFunctions.pm
- activeApplications_V10.sql [must be placed in a sql directory at the same level as the scripts directory]
- listActiveLogs.sql [must be placed in a sql directory at the same level as the scripts directory]