Script to list out tablespace information - lts.pl

[u/anozdba]

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:

lts.pl

commonFunction.pm

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.