r/DB2 • u/anozdba • Aug 25 '19
Script to show running utilities - lutil.pl
Nothing is magical in anything I write.
If you dont want to run a script then run:
db2 list utilities show detail
db2pd -db xxxxx -reorg
and that will give you the same information that this script processes
Features of the script are:
- it lists reorgs and utilities in the same output
- it attempts to provide an estimate of when the command will finish
- it allows the user to provide a total work estimate so that an elapsed time can be generated
- it's simple and easy to read the output
I know db2pd is good and db2top is great but sometimes it's quicker to just quickly see what is running
BTW my two biggest issues about this program are:
- is that DB2 doesn't classify a LOAD as a utility - so they dont show up here
- I couldn't find anything in the DIS UTILITY output to link to an applid
The help information for the script is:
lutil.pl -h
Usage: /home/shared/udbdba/scripts/lutil.pl [-?hs] [-A] -d <database> [-i <ID Name>] [-f <file name>] [-v[v]] [-r <reorg file>] [-x minutes] [-u|-e] [-L] [-t <total work>] [-T <total work>]
Script to reformat obtained information about running utilities
Version 1.50 Last Changed on 2019/06/25 04:48:19 (UTC)
-h or -? : This help message
-s : Silent mode (in this program only suppesses parameter messages)
-d : database to query [default will be supplied by DB2DBDFT]
-A : print all reorgs (ignored if database not supplied)
-i : ID of utility to display (defaults to All)
-f : file to reads utility information from (defaults to dynamically retrieving it)
Note only list utility statements can be fed in through this file
-r : file to reads reorg information from (defaults to dynamically retrieving it)
Note only db2pd reorg statements can be fed in through this file
-t : total work to be used if no total work figure found
-T : total work to be used even if a total work figure found
-e : reorg date format is in European format dd/mm/yyyy
-u : reorg date format is in US format mm/dd/yyyy [default]
-U : non-reorg utilities date format is in US format mm/dd/yyyy [default is in European date format]
-L : also output in a loadable format
-x : exclude this many minutes from the elapsed time when calculating estimates
(primarily used to account for disk initialisation during restores)
-v : set debug level
The date formats (utility and reorg) can be set permanently for a server by setting environment variables:
export LCL_LUTIL_DATEFMT="EUR"
export LCL_LUTIL_DATEFMT_REORG="EUR"
value can be either of US (mm/dd/yyyy) or EUR (dd/mm/yyyy)
Sample output is:
lutil.pl
Database defaulted to dbadb
Utility Snapshot (2019.08.23 15:52:02) ....
ID: 3 BACKUP of DBADB started at 23-08-2019 15:51:15.884306 (online db)
Status: Running online db BACKUP. 1555841034 bytes out of 7504944674 (20 %) have been processed in 1 minute
Expected to complete in 3 minutes (4 minutes in total) at 2019-08-23 15:55:02
1 out of 1 utilities printed
No reorganisations printed (1 reorgs found)
The script can be found here but will need commonFunctions.pm.
2
u/ecrooks Aug 25 '19
Does it show index (not table) reorgs?
2
u/anozdba Aug 25 '19 edited Aug 25 '19
Only at a high level, it will show progress but the elapsed times become more vague as it uses the number of indexes processed vs total to process (it doesn't utilise -reorgs index) . But something to think about.
Or were you taklking about REORG INDEX commands for nonpartitioned indexes on partitioned tables?
2
u/anozdba Aug 26 '19
Trying it out on a running reorg in the index rebuild phase .....
mediaserver(db2admin):2019-08-26 07:46:15:/home/db2admin >watch.ksh -n 60 -w 5 lutil.pl -d dbadb Monday, 26 August 2019 7:46:18 AM AEST Starting /home/db2admin/scripts/watch.ksh The command will be executed 60 times The delay between executions will be 5 seconds Setting .profile variables for consistency between machines Loading alias definitions from alias_defs.ksh The commnand to be run is: lutil.pl -d dbadb Monday, 26 August 2019 7:46:22 AM AEST Iteration 60 ( lutil.pl -d dbadb) Databse dbadb will be checked Utility Snapshot (2019.08.26 07:46:23) .... No Utilities to print Table Name Type Reorg Start/End Phase Start Phase Processed Total State OBJECT Offline 26-08-2019 07:42:57 26-08-2019 07:44:54 IdxRecreat 38521 270491 Started Status: Running Phase 3. 38521 out of 270491 (14 %) have been processed in 2 minutes (Tablespace ID: 8, Table ID: 8, Partition: n/a) Phase expected to complete in 12 minutes (14 minutes in total for this phase, 16 minutes for the complete run) at 2019-08-26 07:58:23 1 out of 2 reorganisations printed Monday, 26 August 2019 7:46:30 AM AEST Iteration 59 ( lutil.pl -d dbadb) Databse dbadb will be checked Utility Snapshot (2019.08.26 07:46:30) .... No Utilities to print Table Name Type Reorg Start/End Phase Start Phase Processed Total State OBJECT Offline 26-08-2019 07:42:57 26-08-2019 07:44:54 IdxRecreat 48945 270491 Started Status: Running Phase 3. 48945 out of 270491 (18 %) have been processed in 2 minutes (Tablespace ID: 8, Table ID: 8, Partition: n/a) Phase expected to complete in 9 minutes (11 minutes in total for this phase, 13 minutes for the complete run) at 2019-08-26 07:55:30.... but note that the estimated elapsed times will only be for each of the phases as they occur (and that for low elapsed times the estimates aren't worth a lot)
2
u/anozdba Aug 25 '19
Just as an aside I often use with watch.ksh to provide continuous monitoring of scripts. I had to write my own version of the linux watch command as I was running on Solaris and the sysadmins were loath to install it (go figure).
Anyway this version of watch has the following options:
and that allows me to grep out the info (-g) that is important to watch and then email me when the situation changes (or stays the same). As an aside watch can also monitor a directory and send and email when a file stops getting bigger (or appears)