Blog
DB2utor

Categories

April 27, 2010

Generating Utility JCL with SQL

Having consulted with companies of all sizes through the years, including many that did not provide their DBAs with management tools, I've long appreciated the power of SQL. SQL is especially helpful when you need to generate JCL. This week we'll look at SQL that generates a runstats utility job.

For the Job Card and any JCL statement that I want to appear once, I select from SYSIBM.SYSDUMMY1. When I want to build utility control cards, I select from the catalog table containing the information I want to process. In this case, it's SYSTABLESPACE, which contains the database and table space names I want to run the runstats on. For this example I'm choosing the catalog table spaces with a creator of SYSIBM and database name starting with DSN.

In the days prior to recursive SQL, my output would contain a prefix on each column used to sort the JCL statements, with line 1, line 2, line 3 and so on. Now various options exist. For instance, I recently read a post by Peter Schwarcz on the DB2 mailing list. Peter provided an example of how to generate utility control cards using the WITH format and removing the prefix sort information from the final output. I've taken his idea and expanded it to include the JCL that's needed to actually execute the utility.

WITH T1 ( DBNAME, NAME, TYPE)                  
AS ( SELECT  STRIP(TS.DBNAME)                  
           , TS.NAME                           
           , TS.TYPE                           
     FROM SYSIBM.SYSTABLESPACE TS              
     WHERE TS.DBNAME LIKE 'DSN%'               
       AND TS.CREATOR = 'SYSIBM'              
   )                                           
SELECT CAST(CMD AS CHAR(80)) FROM (            
  SELECT 1 AS SEQ, ' ' AS DBNAME, ' ' AS NAME  
       , '//COLTR05S JOB '                     
       CONCAT ''''                             
       CONCAT '129700000'                      
       CONCAT '''' CONCAT ',CLASS=A,MSGCLASS=X,'
    AS CMD                                     
  FROM SYSIBM.SYSDUMMY1 SD                     
    UNION ALL                              
SELECT 2 AS SEQ, ' ' AS DBNAME, ' ' AS NAME                      
     , '//             SCHENV=DA0G' AS CMD                       
FROM SYSIBM.SYSDUMMY1 SD                                         
  UNION ALL                                                      
SELECT 3 AS SEQ, ' ' AS DBNAME, ' ' AS NAME                      
     , '//* RS       PTIPROD.RD14SP0.CDBAMDL() COLTR05' AS CMD   
FROM SYSIBM.SYSDUMMY1 SD                                         
  UNION ALL                                                      
SELECT 4 AS SEQ, ' ' AS DBNAME, ' ' AS NAME                      
     , '//UTIL0001  EXEC PGM=DSNUTILB,REGION=4096K,PARM='        
       CONCAT '''' CONCAT 'DA0G' CONCAT '''' AS CMD              
FROM SYSIBM.SYSDUMMY1 SD                                         
  UNION ALL                                                      
SELECT 5 AS SEQ, ' ' AS DBNAME, ' ' AS NAME                      
     , '//STEPLIB  DD DISP=SHR,DSN=D91A.PRIVATE.SDSNEXIT' AS CMD 
FROM SYSIBM.SYSDUMMY1 SD                                         
  UNION ALL                                                      
   SELECT 6 AS SEQ, ' ' AS DBNAME, ' ' AS NAME                   
        , '//         DD DISP=SHR,DSN=DB2.DB2910.SDSNLOAD' AS CMD
   FROM SYSIBM.SYSDUMMY1 SD                                      
     UNION ALL                                                   
   SELECT 7 AS SEQ, ' ' AS DBNAME, ' ' AS NAME                   
        , '//SYSPRINT DD SYSOUT=*' AS CMD                        
   FROM SYSIBM.SYSDUMMY1 SD                                      
     UNION ALL                                                   
   SELECT 8 AS SEQ, ' ' AS DBNAME, ' ' AS NAME                   
        , '//UTPRINT  DD SYSOUT=*' AS CMD                        
   FROM SYSIBM.SYSDUMMY1 SD                                      
     UNION ALL                                                   
   SELECT 9 AS SEQ, ' ' AS DBNAME, ' ' AS NAME                   
        , '//STPRIN01 DD SYSOUT=*' AS CMD                        
   FROM SYSIBM.SYSDUMMY1 SD                                      
     UNION ALL                                                   
   SELECT 10 AS SEQ, T1.DBNAME, T1.NAME                  
        , ' RUNSTATS TABLESPACE ('                       
          CONCAT T1.DBNAME CONCAT '.' CONCAT T1.NAME     
          CONCAT ')' AS  CMD                             
   FROM T1                                               
     UNION ALL                                           
   SELECT 11 AS SEQ, T1.DBNAME, T1.NAME                  
        , '          SHRLEVEL REFERENCE UPDATE ALL' AS CMD
   FROM T1                                               
                                                         
 ) T                                                     
 ORDER BY  DBNAME, NAME, SEQ #                           

Sample Output
//COLTR05S JOB '129700000',CLASS=A,MSGCLASS=X,           
//             SCHENV=DA0G                               
//* RS       PTIPROD.RD14SP0.CDBAMDL() COLTR05           
//UTIL0001  EXEC PGM=DSNUTILB,REGION=4096K,PARM='DA0G'   
//STEPLIB  DD DISP=SHR,DSN=D91A.PRIVATE.SDSNEXIT         
//         DD DISP=SHR,DSN=DB2.DB2910.SDSNLOAD           
//SYSPRINT DD SYSOUT=*                                   
//UTPRINT  DD SYSOUT=*                                   
//STPRIN01 DD SYSOUT=*                                   
 RUNSTATS TABLESPACE (DSNDB06.SYSALTER)                  
          SHRLEVEL REFERENCE UPDATE ALL                  
 RUNSTATS TABLESPACE (DSNDB06.SYSCONTX)                  
          SHRLEVEL REFERENCE UPDATE ALL                  
 RUNSTATS TABLESPACE (DSNDB06.SYSCOPY )                  
          SHRLEVEL REFERENCE UPDATE ALL                  
. . . . .

For those DBAs who do their jobs without the aid of management tools, I hope this sample SQL helps. And for everyone, I'll hope you'll chip in with any sample SQL you use to automate common tasks. Please share your ideas in Comments.