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.

How to Reset Real-Time Statistics

Last week I discussed the real-time statistics tables, where to find them and what information is stored in them.

The real-time stats, once you've located them, can be very useful. For instance, you can track the number of operations executed after you run a utility. Just reset the counters to establish a base value, and then run the appropriate utility: REORG, RUNSTATS, LOAD, REPLACE, REBUILD INDEX or COPY.

Recently I wanted to track the activity that occurred following a table and index space reorganization. So I reset the REORG counters and ran the REORG utility on a table space. Then I reorganized the table space and performed a few DML statements, deleting one row and inserting nine rows into the table stored in this table space. When I queried  SYSIBM.SYSTABLESPACESTATS, I found that these columns had been updated (click the following link to view the table I created: Download Db2utor table 4.20.10)

Hopefully this example helps you better understand not only how to reset the real-time statistics tables columns, but how the resulting information you collect can help you manage database objects.

To learn more about how DB2 utilities impact real-time stats, see this document in the IBM z/OS Solutions Information Center.

If you've used the information found in the real-time statistics tables to help you do your job as a DBA, please share your experiences in Comments.

Where are my Real-Time Statistics Tables?

If you're new to DB2 for z/OS, you may have only heard about the real-time statistics tables. But where are they?

The real-time statistics tables were introduced with DB2 Version 8 as optional user-defined tables. The purpose of the tables is to provide a foundation for future development that will take advantage of collection statistics about DB2 objects in real-time. You can write programs that query these tables to determine when to run utilities such as REORG and COPY. Learn more about these tables in the DB2 UDB for z/OS Version 8 Administration Guide.

To set these tables up in DB2 V8, submit the job DSNTESS, which is found in the DSN810.SDSNSAMP sample library. Click the following link to see the list of objects created: Download Mfblog_coleman table.

Now, with DB2 9 new-function mode, the real-time statistics tables are part of the DB2 catalog.  However, while in conversion mode (previous known as compatibility mode), the real-time statistics tables are still in the user-defined tables. When enabling new-function mode, the job DSNTIJEN is used to move the real-time statistics data into catalog database DSNDB06 with new table names SYSIBM.SYSTABLESPACESTATS and SYSIBM.SYSINDEXSPACESTATS.

Learn more about setting up real-time statistics in the DB2 for z/OS Utility Guide.

Rewarding DB2 Innovation

One of the first systems I designed from the ground up was back when packages first came out in DB2 V2.3. This application was designed for CICS and batch processing. While we needed several copies of the database in development, we didn't have the resources to bring up multiple CICS regions. So we designed the application to read from a DB2 table the names of user IDs to get the associated collection name. This allowed users to switch between different systems just by updating this one table. The collection name appeared in the top right corner of the CICS screen so users knew which set of DB2 tables they were processing. At the time it was a nice, less costly alternative to having to bring up multiple CICS regions so that users could do their work.

How about you? Has your company solved some complicated or technical challenges using DB2 for z/OS? Did you discover some innovative way to help the business using DB2 for z/OS? If so, here's a chance to win some great prizes for both you and your company. IDUG and CA are again teaming to recognize innovative applications of DB2 technologies.

Submit your application for the CA IDUG Award for Outstanding Work in DB2 by April 26. Those recognized will be eligible to receive valuable prizes, including an IDUG conference pass, half-day consulting engagements by each of the four consultant judges, or expense reimbursement up to $1,500 for travel to an IDUG conference or an onsite consulting engagement. The prizes are provided through IDUG and four independent DB2 consultants.

Incidentally, the award sponsor, CA, is my current employer.

The work you do on DB2 is important. It's time you were recognized for it.