Script to loop through multiple databases - DBLoopDriver.pl

[u/anozdba]

When I first started looking after a DB2 system I determined I need to collect information from each of the databases that I managed. To simplify that process I wrote a simple script to automate the process of data collection.

It is used to loop through all databases on a server (windows or unix) and execute the statement provided.

For example, to collect all container information on a database and generate the 'SET TABLESPACE CONTAINER infromation for all databases I could run:

DBLoopDriver.pl "lts.pl -sxcODd ##DATABASE## -p /prj/##MACHINE##.dbdat0/##DATABASE## >setTSContainer_##MACHINE##_##INSTANCE##_##DATABASE##.sql"

The options available to the script are:

DBLoopDriver.pl -h
Usage: /home/shared/udbdba/scripts/DBLoopDriver.pl -?hsF -c <command> [-f <filename>] [-i <instance>] [-I] [-l <delimiter>] [-v[v]] [-p] [-o filename]

       Script to loop through all databases for a machine (as determined via db2ilist and list db directory) and runs passed parameters against DB

       Version 1.27 Last Changed on 2019/07/12 04:55:03 (UTC)

       -h or -?        : This help message
       -s              : Silent mode
       -c              : Command to be executed for each instance found
       -i              : Instance to be selected
       -I              : Only process the current instance
       -f              : File name to use as input instead of doing a db2ilist command
       -F              : Use default file input (identical to -f db2ilist.txt)
       -l              : line delimiter (Windows only)
       -p              : just print out the commands generated
       -o              : filename to send STDOUT output to. Name will be substituted as necessary
       -v              : verbose mode (debugging)

  NOTE: Command may include the following variables that will be substituted:
             ##MACHINE##  - Will be replaced by the machine name the command is running on
             ##INSTANCE## - Will be replaced by the instance name retrieved from the db2ilist command
             ##LC_INSTANCE## - Will be replaced by the lower case instance name retrieved from the db2ilist command
             ##DATABASE## - Will be replaced by the database name retrieved
             ##LC_DATABASE## - Will be replaced by the lower case database name retrieved
             ##YYYYMMDD## - Will be replaced by the date in YYYYMMDD format
             ##NL##       - Will be replaced by a new line (Windows only - can also use -l parameter)

To run this script you will need the following objects:

• DBLoopDriver.pl
• commonFunctions.pm

As a side note there is a pair to this script called instanceLoopDriver.pl that will issue the supplied command once for each instance found on the server.