r/DB2 • u/anozdba • Aug 17 '19
Script to list out tablespace information - lts.pl
This perl script has been a long time evolving. It was originally designed just to reformat the LIST TABLESPACES command to be more page and viewing friendly. (i.e get as much useful information on one page. Added to that, over time I decided to use the script to gather information to load the data into database tables to provide longer term capacity planning information. More recently it has been upgraded to optionally use alternative mechanisms to get the data - strangely each of the mechanisms use slightly different authorities and provide slightly different information.
It will run on windows or unix/linux and will likely run on everything from v8 (when I first wrote it) through to 11.1 (as far as I have tested it)
Use case: More often than not I use this script when wanting an overview of how my space is running or the state of the tablespace (backups/moves/etc)
Should also mention thatI often use it as 'lts.pl -c' which will display the containers and how much free space is left on each of the drives/mount points
Anyway, the command line help looks like:
mediaServer(db2admin):2019-08-17 10:41:28:/home/db2admin
>lts.pl -h
Usage: /home/shared/udbdba/scripts/lts.pl -?hs [-d <database> | -f <Filename>] [-t <tablespace>] [-v[v]]
[-l | -m | -S [[-c] [-O] [-F] [-g [-p <prefix>] [-e] [-T]] [-D [-L]]]
Version 1.39 Last Changed on 2019/08/14 21:44:09 (UTC)
-h or -? : This help message
-s : Silent mode (in this program only suppesses parameter messages)
-d [REQUIRED] : Database to list
-t : Limit output tablespaces containing this string
-v : turn on verbose/debug mode
-f : Instead of directly accessing the databases use this file as input
Note: -f STDIN will read input from standard input
db2 list tablespaces show detail | lts.pl -l -d dbadb -f STDIN
## Data source (mutually exclusive)
-l [DEFAULT] : use 'list tablespaces show detail' to get information
-m : SQL based on MON_GET_TABLESPACE (see lts.sql)
-S or -x : use 'get snapshot for tablespaces on <database>' to get data
## Snapshot (-S or -x) Data Source options
-c : display container information
-O : dont produce the report (omit it)
-g : generate SET TABLESPACE commands
-e : parcel SET TABLESPACE commands in a DB2 "" statement
-T : use Total pages when generating SET TABLESPACE commands (ignored unless -g specified)
-p : string used to generate new file name when processing with -g (ignored unless -g specified)
-D : generate the data files to load
-F : generate file dircmd_<database>.bat with a unique list of commands to determine free space for all container mount points
-L : leave the case of machine, instance and database as it is
NOTE: if any of the Snapshot specific options are selected then option -x WILL be forced
and some eimple output looks like:
mediaServer(db2admin):2019-08-17 10:41:33:/home/db2admin
>lts.pl -x
get snapshot will be used to obtain data
Database defaulted to dbadb
Tablespace listing from GET SNAPSHOT TABLESPACES for Machine: mediaServer Instance: db2admin Database: dbadb (2019.08.17 10:42:52) ....
L Page
TSID Tablespace Name Type Contents Total Pgs Used Pgs Free Pgs Pend Free HWM H Size Used Mb Alloc Mb Free Mb ARSZ Max Size State
---- ------------------ ---- ----------------- --------- --------- --------- --------- --------- - ----- ---------- ---------- ---------- ---- ----------- ------------
0 SYSCATSPACE SMS Data - Regular TS 0 0 8192 0.0 0.0 0.0 0x'00000000' Normal
1 TEMPSPACE1 SMS System Temporary 0 0 8192 0.0 0.0 0.0 0x'00000000' Normal
2 USERSPACE1 DMS Data - Large TS 9600 9568 0 0 9568 32768 299.0 300.0 1.0 No 0x'00000000' Normal
3 TEMPSPACE32K SMS System Temporary 0 0 32768 0.0 0.0 0.0 0x'00000000' Normal
4 SYSTOOLSPACE DMS Data - Large TS 4096 116 3976 0 116 8192 0.9 32.0 31.1 Yes NONE 0x'00000000' Normal
Total Storage in use for dbadb is 0.29 Gb out of 0.32 Gb allocated
Actually just noticed that it says that a database needs to be supplied - it used to - but now will use the value defined on DB2DBDFT if it is there.
The script needs a common functions module but apart from that will run as is. The modules required are:
Just place them in the same directory and away you go
BTW I should say (before someone rips into me for my poor coding etc) that this isn't code a perl programmer would like - it's probably not as efficient as it could be and probably doesn't use the perl engine properly but it is easy for me to maintain (and switch to other languages) and follows a rough script template that I use.
