How do I script DB2 commands in z/OS?

Greetings, experts! 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!
DaveSlash
LVL 18
Dave FordSoftware Developer / Database AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tomas Helgi JohannssonDatabase Administrator / Software EngineerCommented:
Hi,

What I do is I have some JCL jobs that I can run manually or shedule and it emails me the results.

This code are 2 steps in a JCL that you need to modify a litle bit :)
//REPORT1  EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(9,LT)       
//SYSTSPRT DD  DSN=&&DBMLRP01,DISP=(NEW,PASS),LRECL=133    
//SYSPRINT DD  SYSOUT=*                                    
//SYSUDUMP DD  SYSOUT=*                                    
//SYSTSIN  DD  *                                           
 DSN SYSTEM( YOURSUBSYSTEMID )                                           
-DISPLAY DB(DN*) SPACENAM(*) USE LOCKS LIMIT(*)
-DISPLAY DB(DP*) SPACENAM(*) USE LOCKS LIMIT(*)
/*                                                         
//EMAIL  EXEC XMITIP                                      
//SYSPRINT DD SYSOUT=*                                     
//SYSTSPRT DD SYSOUT=*                                     
//SYSTSIN  DD *                                            
 %xmitip (SOMEMAILADDR@DOMAIN)  -                                  
  SUBJECT 'REPORT FROM DB2' -                     
  nomsg filedd (DATAB ) -                                  
  From SOMEMAILADDR@DOMAIN                                         
  
//DATAB    DD DSN=&&DBMLRP01,DISP=(OLD,DELETE)     
/*                          

Open in new window


Regards,
    Tomas Helgi

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dave FordSoftware Developer / Database AdministratorAuthor Commented:
Thanks, Thomas! You're a talented man.

Can you please explain the last line to me? What is &&DBMLRP01?
Tomas Helgi JohannssonDatabase Administrator / Software EngineerCommented:
Hi,

The &&DBMLRP01 in line 19 is a temporary dataset that is declared in line 2 and holds the output of the displays. :)
Exchange the temporary dataset to a physical dataset  or SYSOUT=* to see the output and/or work further with the data. :)

Regards,
     Tomas Helgi
Dave FordSoftware Developer / Database AdministratorAuthor Commented:
Thank you, Tomas. The JCL is throwing an error that I'm unfamiliar with. Can you think of any reason why procedure XMITIP would not be found? (We're at DB2 verion 10 for z/OS.)

       7 //SYSTSIN  DD *                              
       8 //EMAIL  EXEC XMITIP                         
       9 //SYSPRINT DD SYSOUT=*                       
      10 //SYSTSPRT DD SYSOUT=*                       
      11 //SYSTSIN  DD *                              
      12 //DATAB   DD DSN=&&DBMLRP01,DISP=(OLD,DELETE)
         /*                                           
STMT NO. MESSAGE                                      
       8 IEFC612I PROCEDURE XMITIP WAS NOT FOUND      

Open in new window


Thanks!
Dave
Tomas Helgi JohannssonDatabase Administrator / Software EngineerCommented:
Hi Dave,

XMITIP is not part of DB2.
XMITIP is a mainframe SMTP application that my shop has installed to send emails from our mainframe. :)
http://www.lbdsoftware.com/XMITIP-Guide.pdf (you can also use google to search for XMITIP :) )

Regards,
    Tomas
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.