Db2 Tools

 View Only

Lets generate IBM Db2 utilities efficiently with RC/Query

  • 1.  Lets generate IBM Db2 utilities efficiently with RC/Query

    Broadcom Employee
    Posted Nov 08, 2019 02:23 PM
    Let us start this with a question:
    How many times, that we tried to generate the utilities from RC/Query and thought the generated job should be generated with LISTDEF so the execution can be done in parallel. 
    How many times, we could have thought controlling the number of objects in every LISTDEF should be in our hands while generating a utility ?

    Well, now it is possible with RC/Query and we can do all these for almost all IBM utilities. Our motive is to run the tasks in parallel as much as possible in Db2 to save cost and job execution time. 

    With RC/Query while generating any utility, parallelism is maintained and the JCL will be generated with LISTDEF format across all the objects with many extra features added. 

    With PTF SO10430, CA RC/Query has been enhanced to now generate utility jobs for the most frequently used IBM utilities in LISTDEF format. 

    The behavior is similar for all IBM utilities although the following examples only show IBM REORG.


    The following features are now provided: 

    Feature #1: General Utilities in Parallel
    In this example, we are generating an IBM REORG from CA RC/Query. We used the new profile option Utility Object Count in 1 Step with a value of 5 to include up to 5 objects in each LISTDEF. 

    //SYSIN    DD *                                                      
    
     LISTDEF LIST1 INCLUDE TABLESPACE DSNDB04.DSNR1RHZ                    
    
    
                   INCLUDE TABLESPACE NEWDB45.F75862B1                    
    
    
                   INCLUDE TABLESPACE SRIDB11.CADB2TS                     
    
    
                   INCLUDE TABLESPACE SRIDB11.CADB2TS1                    
    
    
                   INCLUDE TABLESPACE SRIDB12.CADB2TS                     
    
    
    
     TEMPLATE CDATA1 DSN '&USERID..&DB..&TS..P&PA(2)..D&DT(3)..C&TI(1,4).'
    
       UNIT TAPE RETPD 15 VOLCNT(5) DISP(NEW,CATLG,DELETE)                
    


    TEMPLATE CDATA2 DSN '&USERID..&DB..&TS..P&PA(2)..D&DT(3)..C&TI(1,4).'
    
       UNIT(SYSDA) DISP(NEW,CATLG,DELETE)                                 
    
    
       SPACE (4,20)         CYL                                           
    


     TEMPLATE UDATA1 DSN '&USERID..&DB..&TS..P&PA(2)..D&DT(3)..T&TI(1,4).'
    
       UNIT(SYSDA) DISP(NEW,CATLG,DELETE)                                 
    
    
       SPACE (4,20)         CYL                                           
    
    
    
     REORG TABLESPACE LIST LIST1                                         
    
           COPYDDN(CDATA1)                                                
    
    
           UNLDDN(UDATA1)                                                 
    
    
           STATISTICS TABLE(ALL) INDEX(ALL)                               
    
    
           SORTDEVT SYSDA  SORTNUM 03                                     
    
    
           UNLOAD CONTINUE                                                
    
    
           SHRLEVEL CHANGE                                                
    
    
           DEADLINE CURRENT TIMESTAMP + 1 HOUR                            
    
    
           MAXRO 240                                                      
    
    
           LONGLOG                                                        
    
    
            DRAIN DELAY 1200                      
    
    
           DRAIN_WAIT 30 RETRY 4 RETRY_DELAY 10   
    
    
    /*                                            
    


    Feature #2: Change the number of objects per LISTDEF

    Use the new Utility Object Count in 1 Step profile option to change the number of objects to include in each LISTDEF. Enter PROF;2 and set this profile option to a different number. For example, change from 5 to 10 as shown. 

    COMMAND ===>                                                                 

    PRINT PARAMETERS:     (For Printing Query Report being viewed)                
      PRINT SQL PAGE      ==> N   (Y/N; SQL Page N/A if Printline Size < 41)      

    PROCESSING OPTIONS:   (For Query Reports)                                     
    
      VIEW LEVELS         ==> ALL          - For Object Dependency Report.         
    
    
      QUALIFIERS          ==> Y    (Y/N)   - For Obj. Dep. and some Ver. 2.1 cols.
    
    
      MAX LINES           ==> ALL          - For List and Detail reports.          
    
    
      FULL DETAIL         ==> N    (Y/N)   - For reports based on multiple tables. 
    
    
      OMIT ORDER BY       ==> N    (Y/N)   - For List and Detail reports.          
    
    
      CA-UTILITIES        ==> N    (Y/N)   - To use CA-Utilities.                  
    
    
      SAVED REPORTS       ==> Y    (Y/N)   - To use customized reports.            
    
    
      MATCHCASE           ==> CAPS (ON,    - Enables matchcase.                    
    
    
                                    OFF,   - Disables matchcase. ( SQL Intensive ) 
    
    
                                    CAPS)  - Force object names to upper case.     
    
    
      CAPNATS             ==> N    (Y/N)   - Capitalize National Characters.       
    
    
      ALL LOCATIONS       ==> Y    (Y/N)   - For searches in Plan/Package Report.  
    
    
      UNDERSCORE WILDCARD ==> ON   (ON/OFF)- To process underscore as wildcard.    
    
    
      OPTIONS NOTIFY      ==> ON   (ON/OFF)- For options and notify pop-ups.       
    
    
      PREFILL COMMAND     ==> Y    (Y/N)   - Prefill command line for utility.     
    
    
      ISOLATION LEVEL     ==>      (Blank  - Global Level ISOLATION value.         
    
    
                                    CS,    - Cursor Stability.                     
    
    
                                    UR,    - Uncommited Read)                      
    

      UTILITY OBJECT      ==> 10           - Number of objects per step in        
      COUNT IN 1 STEP                        utility processing JCL.
    Then generate the REORG utility again. We now observe LISTDEF has 10 objects included. 
     

    //SYSIN    DD *                                                       
     LISTDEF LIST1 INCLUDE TABLESPACE DSN00027.ABCDEDF                    
                   INCLUDE TABLESPACE DSN03928.ACCOUNT                    
                   INCLUDE TABLESPACE DSN05161.AAA111A                    
                   INCLUDE TABLESPACE DSN06818.ABCLOKES                   
                   INCLUDE TABLESPACE DSN06819.ABCLOKES                    
                   INCLUDE TABLESPACE DSN08195.AAA112                     
                   INCLUDE TABLESPACE DSN08262.AAA111                     
                   INCLUDE TABLESPACE PFG.SACT                            
                   INCLUDE TABLESPACE SRIDB11.LPBFTS1                     
                   INCLUDE TABLESPACE SRIDB11.                             

     TEMPLATE CDATA1 DSN '&USERID..&DB..&TS..P&PA(2)..D&DT(3)..C&TI(1,4).'
       UNIT TAPE RETPD 15 VOLCNT(5) DISP(NEW,CATLG,DELETE)                

     TEMPLATE CDATA2 DSN '&USERID..&DB..&TS..P&PA(2)..D&DT(3)..C&TI(1,4).'
       UNIT(SYSDA) DISP(NEW,CATLG,DELETE)                                 
       SPACE (4,20)         CYL                                           

     TEMPLATE UDATA1 DSN '&USERID..&DB..&TS..P&PA(2)..D&DT(3)..T&TI(1,4).'
       UNIT(SYSDA) DISP(NEW,CATLG,DELETE)                                 
       SPACE (4,20)         CYL                                           

     REORG TABLESPACE LIST LIST1                                           
           COPYDDN(CDATA1)                                                
           UNLDDN(UDATA1)                                                 
           STATISTICS TABLE(ALL) INDEX(ALL)                               
           SORTDEVT SYSDA  SORTNUM 03                      
           UNLOAD CONTINUE                                 
           SHRLEVEL CHANGE                                 
           DEADLINE CURRENT TIMESTAMP + 1 HOUR             
           MAXRO 240                                       
           LONGLOG                                         
            DRAIN DELAY 1200                               
           DRAIN_WAIT 30 RETRY 4 RETRY_DELAY 10            
    /*                                                      

    Feature #3: Convenience/Flexibility
    For the convenience of users, CA RC/Query will generate 2 TEMPLATEs for IMAGECOPY outputs defaulting the image-copy output to TAPE. However, if a user wants to allocate the image-copy output to DASD (as part of COPY or REORG utilities), then the effort needed is a 1 character change. CDATA1 to CDATA2 in generated utility statement. 

    Feature #4: REBUILD included Indexes
    REBUILD of indexes that can be performed individually or at tablespace level. While generating the REBUILD utility statement at the individual index level, LISTDEF approach is used. 

    //SYSIN    DD  *                                
     LISTDEF LIST1 INCLUDE INDEX    ADUSR01.I04011A1
                   INCLUDE INDEX    ADUSR01.I04011BF1
                   INCLUDE INDEX    ADUSR01.I04011BF2
                   INCLUDE INDEX    ADUSR01.I04011BF3
                   INCLUDE INDEX    ADUSR01.I04011BP
                   INCLUDE INDEX    ADUSR01.I04011F1
                   INCLUDE INDEX    ADUSR01.I04011F2
                   INCLUDE INDEX    ADUSR01.I04011F3
                   INCLUDE INDEX    ADUSR01.I04011P 

       REBUILD INDEX LIST LIST1                     
             SORTDEVT SYSDA SORTNUM 03              
             SHRLEVEL CHANGE                        
    /*                                              

    While generating REBUILD utility at the tablespace level, TEMPLATE is not followed. 

    //SYSIN    DD  *                               
     REBUILD INDEX(ALL) TABLESPACE SRIDB01.SP2PBR02
           SORTDEVT SYSDA SORTNUM 03               
           SHRLEVEL CHANGE                         
    /*                                              
     
    Feature #5: UNLOAD generated using DSNUPROC
    The UNLOAD utility now generates the utility using DSNUPROC – 1 step for 1 object (or 1 utility statement). 
    //UTIL0001  EXEC PGM=DSNUTILB,REGION=4096K,PARM='D11A'                 
    -  -  -  -  -  -  -  -  -  -  -  -  -  -  -  - 10 Line(s) not Displayed => lines are excluded
    //SYSIN DD *                                                           
      UNLOAD FROM TABLE ADUSR01.AAA111;                                    
    /*                                                                     

    Feature #6: DSNTIAL line command now unloads using DSNTIAUL utility 
    Generating Unload job in DSNTIAUL format can be achieved by DSNTIAUL command.  

    Feature #7 (Honoring the existing behavior):
    If a user does not want to specify how many utility statements to include in each JCL step, select Y for 2nd value below and keep the (Recommended) option blank or empty. 
    RQPUTCNT                                    

    (Recommended) Enter the maximum number      
    of utility statements you want included     
    in each step (1 to 99)                  :   

    Enter Y to include one step for each        
    object per utility statement or one         
    step for all utility statements (Y|N)   :  N

    PF1 = HELP                                  

    However, to improve system performance when multiple objects are processed, we recommend that you make use of the recommended option.In the following example, the user entered 5 for the recommended option (include up to 5 objects in 1 LISTDEF statement (1 step). 
    RQPUTCNT                                     
                                                 
    (Recommended) Enter the maximum number       
    of utility statements you want included      
    in each step (1 to 99)                  :  5
                                                 
    Enter Y to include one step for each         
    object per utility statement or one          
    step for all utility statements (Y|N)   :                                         

    PF1 = HELP                                   


    ------------------------------
    Srinivas Adupa
    Product Marketing Engineer
    Broadcom
    Lisle
    ------------------------------