How do I script DB2 commands in z/OS?

Question

After 25 years of using DB2 on midrange systems (AS/400, IBM i, etc), I’m now being tasked with being a database administrator for DB2 on the mainframe (z/OS). I apologize for the “rookie questions” here, but I’m still getting my head around the mainframe.

Currently, every morning, I manually check the status of several tablespaces using the DISPLAY command in the “DB2 Commands” screen:

e.g.

Cmd 1       ===>       -DISPLAY DB(DN*) SPACENAM(*) USE LOCKS LIMIT(*)
Cmd 2       ===>       -DISPLAY DB(DP*) SPACENAM(*) USE LOCKS LIMIT(*)

This displays several pages of output on the screen, pausing after each screen-full. If any tablespaces are in an invalid status, I can do something about it.

Ultimately, I’d like to issue several of these DISPLAY statements, directing the output to an output dataset. Then, I can either inspect that dataset manually or possibly write a program to parse the output dataset and report (or fix) any anomalies.

This is probably super easy for all you mainframe DB2 DBA’s, but how do I script these commands? Do I use REXX? If so, how?

Thanks for your help!

Dave

Answer 1

In case there are users out there that prefer unix shell, you can ssh into unix system services and use the command line processor, and db2 supplied stored procedures.

.profile snippet to set environment variables:

# DB2 CLP
export JAVA_HOME=/usr/lpp/java/java180/J8.0_64
export CLASSPATH=$CLASSPATH:/usr/lpp/db2/db2c10/base/lib/clp.jar
export JCCJAR=$JCCJAR:/usr/lpp/db2/db2c10/jdbc/classes
export CLASSPATH=$CLASSPATH:$JCCJAR/db2jcc4.jar
export CLASSPATH=$CLASSPATH:$JCCJAR/db2jcc_license_cisuz.jar
export CLASSPATH=$CLASSPATH:$JCCJAR/db2jcc_license_cu.jar
export PATH=$PATH:$JAVA_HOME:$JAVA_HOME/bin
export CLPPROPERTIESFILE=~/clp.properties
# alias to allow using shorter db2clp versis long path.
alias  db2clp="/usr/lpp/java/java180/J8.0/bin/java com.ibm.db2.clp.db2"

Sample clp.properties file. Note - I use hash as SQL termination character. This allows me to create native stored procedures that use a semi-colon as a line terminator within the procedure, and a hash for procedure terminator code.

#Specify the value as ON/OFF or leave them blank
DisplaySQLCA=ON
AutoCommit=OFF
InputFilename=
OutputFilename=
DisplayOutput=ON
StopOnError=OFF
TerminationChar=#
Echo=ON
StripHeaders=OFF
MaxLinesFromSelect=ALL
MaxColumnWidth=132
IsolationLevel=UR

text.sql contains:

CONNECT TO localhost:446/STLEC1
CALL SYSPROC.ADMIN_COMMAND_DB2 ('-DISPLAY BPOOL(BP0)', 40, NULL, NULL,?, ?, ?, ?, ?, ?, ?, ?) #

Execute CLP in batch via shell command:

export IBM_DB_UID=your-userid-here
export IBM_DB_PM=your-password-here
db2clp -f text.sql -u $IBM_DB_UID/$IBM_DB_PWD

You could cat the results to a file and use python, node, or unix commands on the output.

Helpful links: Command line processor: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/comref/src/tpc/db2z_commandlineprocessor.html

Procedures delivered with Db2: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/sqlref/src/tpc/db2z_suppliedstoredprocedures.html

Answer 2

The easiest way would be to run the commands under the TSO terminal control program as a batch job.

This could look something like this (JOB-card omitted):

 //*----------------------------------------------                 
 //DISDB    EXEC PGM=IKJEFT01,DYNAMNBR=20                          
 //*----------------------------------------------                 
 //STEPLIB   DD DISP=SHR,DSN=DB2.DSNLOAD                           
 //SYSPRINT DD SYSOUT=*                                            
 //SYSTSPRT DD DISP=SHR,DSN=YOUR.OUTPUT.DATASET                                            
 //SYSTSIN  DD *                                                   
   DSN SYSTEM(DBSYS)                                                
   -DISPLAY DB(DN*) SPACENAM(*) USE LOCKS LIMIT(*)                                                  
   -DISPLAY DB(DP*) SPACENAM(*) USE LOCKS LIMIT(*)                 
 //                                                                

The only thing is that SYSTSPRT will contain a mix of your input-commands, the resulting output and a bit of other clutter but it is basically zero effort to implement.

Answer 3

A very basic REXX solution:

/* REXX*/                                                   
DB2NAME = 'DSN'        /* <== change db2name here */                                     
"DELSTACK"                                                  
QUEUE "-DISPLAY DB(DN*) SPACENAM(*) USE LOCKS LIMIT(*)"  
QUEUE "-DISPLAY DB(DP*) SPACENAM(*) USE LOCKS LIMIT(*)"
QUEUE "END"                                                 
X = OUTTRAP(IN.)                                            
"DSN SYSTEM("!!DB2NAME!!")"                                 
X = OUTTRAP(OFF)                                            
DO I = 1 TO IN.0                                            
  SAY IN.I                                                  
END                                                         

Answer 4

You say you manually issue the commands in the DB2 commands screen. In z/OS there is a similar, I think, menu in TSO. Issue the DB2I command in TSO and you should be presented a menu which has option 7 DB2 Commands. This is the manual way.

If you want to run a series of commands in batch, i.e. scripted, submit a job running the DSN TSO command.

//jobname JOB ...
//STEP01  EXEC PGM=IKJEFT01
//SYSTSPRT DD SYSYOUT=*
//SYSTSIN  DD *
 DSN SYSTEM(subsysid)
 -dis db(...) ....
... more commmands
/*

The output will be written to DD SYSTSPRT.

There is no need to write REXX scripts unless you want to programmatically interpret the result of the commands.

HTH

Answer 5

You can write the rexx script to build connection to Db2 subsystem and then execute the same command..Let me find the sample and send to you.. this is the command you can use to connect to DB2 from REXX ADDRESS DSNREXX 'CONNECT' 'DB01'