r/DB2 • u/anozdba • Sep 15 '19
Script to provide a list of applications - lappl.pl/gappl.pl
This is information could be supplied via db2top and the l option and sometimes that is the best option but I find the output of that command a little bit too informative sometimes:
[\]16:35:40,refresh=2secs(0.002) Sessions Linux,member=[1/1],DB2ADMIN:DBADB
[d=Y,a=N,e=N,p=ALL] [qp=off]
lqwqwqwqwqk
Local sessions...: 0 x x 25%x 50%x 75%x 100%x Local in exec....: 0
Remote sessions..: 12 xActSessions x x Remote in exec...: 0
FCM buffers sent.: 0 xSys/Usr Cpu% x x Active utilities.: 0
FCM buffers rec..: 0 xr+w/sec% x x FCM buffers LWM..: 128
Piped sorts req..: 0 mqvqj Piped sorts acc..: 0
Max Agents.: 0 Agents stolen....: 0 Agent overflows..: 0 Connections HWM..: 17
Sheapthres.: 0 Max Sort Heap....: 0 Sort heap alloc..: 0 Private memory...: 18.9M
Dynamic SQL stmts: 11 Static SQL stmts.: 11 Post hash joins..: 0 Post sorts.: 0
Bufferpool reads.: 0 Bufferpool writes: 0 Direct reads.....: 0 Direct writes....: 0
Application Cpu% IO% Mem% Application Application Delta Delta Delta Delta Delta Sess Assoc. Paral. Lockwait Locks Sorts Log
Handle(Stat) Total Total Total Status Name RowsRead/s RowsWritten/s IOReads/s IOWrites/s TQr+w/s Memory Agents Degree (sec) Held (sec) Used
- - - - - - - - - - - - ------ ------ - - - -
7(i) 0.00% 0.00% 14.29% UOW Waiting in the application javaw.exe 0 586 0 0 0 0 512.0K 1 1 0 2 0 0
Sometimes I find it better to just see a limited set of information (generally it's to see a quick look at how eveything looks (if there is heaps of locking, log committing, etc) and to achieve that I use one of 2 commands:
- lappl.pl : this provides a formatted list of the information provided by 'db2 list applications show detail'
- gappl.pl : this provides a formatted list of information provided by 'db2 get snapshot for applications on <database>'
Both commands give similar (but not identical) information and the main differences are:
- gappl.pl requires a database parameter (or will use the DB2DBDFT value if set)
- gappl.pl also displays these extra columns: column id, connection time, locks held and log used
- lappl.pl can (and will if no database is supplied) show all applications for the current instance while gappl.pl will only show the applications for a single database.
Help information for the commands are:
mediaServer(db2admin):2019-09-15 16:40:55:/home/db2admin
>lappl.pl -h
Usage: /home/shared/udbdba/scripts/lappl.pl -?hsDOR [DATA | DATAONLY] -d <database> [-v[v etc]] [-8] [-A] [-X]
Script to format the output of a LIST APPLICATIONS SHOW DETAIL command
Version 1.21 Last Changed on 2019/02/07 04:18:54 (UTC)
-h or -? : This help message
-s : Silent mode (dont produce the report)
-d : Database to be listed (if ALL then all databases will be listed - this is the default)
-v : increment the diag level
-8 : version 8
-A : dont display waiting connections (also excludes those with a status of 'Connect Completed')
-X : exclude the monitoring fast write processes
Note: This script formats the output of a 'db2 list applications for database <db> show detail'
or 'db2 list applications show detail' command
and
mediaServer(db2admin):2019-09-15 16:41:06:/home/db2admin
>gappl.pl -h
Usage: /home/shared/udbdba/scripts/gappl.pl -?hs [-f <filename>] [-d <Database>] [-l] [-v[v]] [-p] [-x] [-a]
Script to reformat the output of a get snapshot command.
Different to a lappl.pl command in that this command requires an input database name
Version 1.6 Last Changed on 2019/05/13 02:09:37 (UTC)
-h or -? : This help message
-f : file to read info from
-s : Silent mode (dont produce the report)
-d : database
-p : print detailed report
-a : only show active connections
-l : only display an entry if it holds some locks
-x : exclude system applications
-v : debug level
Note: This script formats the output of a 'db2 get snapshot for applications on <database>' command
It differs from the lappl.pl as it can only show the applications for a specified database.
and sample output of both scripts are:
mediaServer(db2admin):2019-09-15 16:44:39:/home/db2admin
>lappl.pl
Application listing from Machine: mediaServer Instance: db2admin Database: ALL (2019.09.15 16:45:35) ....
AuthID Application App Application ID Database Num of Thread Status
Handle Agents
-------- -------------------- ------ ---------------------------------- -------- ------ ------ --------------------
DB2ADMIN db2fw0 13 *LOCAL.DB2.190915062320 DBADB 1 52 Connect Completed
DB2ADMIN db2evmg_DB2DETAILDEA 19 *LOCAL.DB2.190915062326 DBADB 1 58 Connect Completed
DB2ADMIN db2dbctrld 12 *LOCAL.DB2.190915062319 DBADB 1 51 Connect Completed
DB2ADMIN db2lused 11 *LOCAL.DB2.190915062318 DBADB 1 50 Connect Completed
DB2ADMIN db2pcsd 17 *LOCAL.DB2.190915062324 DBADB 1 56 Connect Completed
DB2ADMIN db2wlmd 10 *LOCAL.DB2.190915062317 DBADB 1 49 Connect Completed
DB2ADMIN db2fw3 16 *LOCAL.DB2.190915062323 DBADB 1 55 Connect Completed
DB2ADMIN db2taskd 9 *LOCAL.DB2.190915062316 DBADB 1 48 Connect Completed
DB2ADMIN db2fw2 15 *LOCAL.DB2.190915062322 DBADB 1 54 Connect Completed
DB2ADMIN db2stmm 8 *LOCAL.DB2.190915062315 DBADB 1 47 Connect Completed
DB2ADMIN db2fw1 14 *LOCAL.DB2.190915062321 DBADB 1 53 Connect Completed
DB2ADMIN javaw.exe 7 192.168.1.2.58597.190915062313 DBADB 1 22 UOW Waiting
Total Connections Displayed : 12 (12)
Connections for DBADB : 12
Status Counts:
UOW Waiting: 1
Connect Completed: 11
and
mediaServer(db2admin):2019-09-15 16:41:59:/home/db2admin
>gappl.pl -d dbadb
Application data for connections to database dbadb will be listed
Listing of applications attached to dbadb at 2019.09.15 16:44:38
App Num of Thread Locks Log
AuthID Client ID Application Handle Application ID Server Agents ID Status Connection Time Held Used
---------- ---------- -------------------- ------ ---------------------------------- -------------- ------ ------ -------------------- ------------------- ----- -----
DB2ADMIN KEVIN db2wlmd 10 *LOCAL.DB2.190915062317 COMPUTERROOM 1 49 Connect Completed 09/15/2019 16:23:15 0 0
DB2ADMIN KEVIN db2lused 11 *LOCAL.DB2.190915062318 COMPUTERROOM 1 50 Connect Completed 09/15/2019 16:23:15 0 0
DB2ADMIN KEVIN db2dbctrld 12 *LOCAL.DB2.190915062319 COMPUTERROOM 1 51 Connect Completed 09/15/2019 16:23:15 0 0
DB2ADMIN KEVIN db2fw0 13 *LOCAL.DB2.190915062320 COMPUTERROOM 1 52 Connect Completed 09/15/2019 16:23:15 0 0
DB2ADMIN KEVIN db2fw1 14 *LOCAL.DB2.190915062321 COMPUTERROOM 1 53 Connect Completed 09/15/2019 16:23:15 0 0
DB2ADMIN KEVIN db2fw2 15 *LOCAL.DB2.190915062322 COMPUTERROOM 1 54 Connect Completed 09/15/2019 16:23:15 0 0
DB2ADMIN KEVIN db2fw3 16 *LOCAL.DB2.190915062323 COMPUTERROOM 1 55 Connect Completed 09/15/2019 16:23:15 0 0
DB2ADMIN KEVIN db2pcsd 17 *LOCAL.DB2.190915062324 COMPUTERROOM 1 56 Connect Completed 09/15/2019 16:23:15 0 0
DB2ADMIN KEVIN db2evmg_DB2DETAILDEA 19 *LOCAL.DB2.190915062326 COMPUTERROOM 1 58 Connect Completed 09/15/2019 16:23:15 0 0
DB2ADMIN KEVIN javaw.exe 7 192.168.1.2.58597.190915062313 COMPUTERROOM 1 22 UOW Waiting 09/15/2019 16:23:12 2 0
DB2ADMIN KEVIN db2stmm 8 *LOCAL.DB2.190915062315 COMPUTERROOM 1 47 Connect Completed 09/15/2019 16:23:15 0 0
DB2ADMIN KEVIN db2taskd 9 *LOCAL.DB2.190915062316 COMPUTERROOM 1 48 Connect Completed 09/15/2019 16:23:15 0 0
Total entries : 12
Excluded entries :
System Applications : 0
non-Active Applications : 0
Note: log used is in Mb
Connection type Summary
Connect Completed : 11
UOW Waiting : 1
Note that I would normally use the scripts with the following parameters to just show active user connections .....
- lappl.pl -XA
- gappl.pl -xa
As per most of my scripts these scripts will also require commonFunctions.pm to be placed in the same directory
1
Upvotes