Wednesday, December 14, 2011

File-aid : Create An Explain Using File-Aid For DB2


You can create EXPLAIN report for an existing package thru File-aid. The Demo is below.

Select Option 4 from the file-aid main menu

File-AID for DB2 - 6.2 ------ Primary Option Menu -----------------------------
 OPTION  ===> 4                                                                
                                                                    More:     +
    0  DEFAULTS and USER PARMS                          USERID   -    TXUXXXX  
    1  BROWSE                                           TIME     -    20:28    
    2  EDIT                                             TERMINAL -    3278     
    3  UTILITIES                                        PF KEYS  -    24       
       3.1 - Object Administration                      CCSID    -    00037    
       3.2 - Create, Model, Drop, Alter DB2 Objects     DB2 SSID ===> DBXT     
       3.3 - Copy Rows Between Tables                                          
       3.4 - Object List Processing                                            
       3.5 - SQL Command Manager                                               
       3.6 - Display, Grant or Revoke DB2 Privileges                           
       3.7 - Extract and Load Tables                                           
       3.8 - DBA-XPERT Column Impact Analysis                                  
       3.9 - Batch Execute SQL with Auto-COMMIT Processing                     
    4  SQL ANALYSIS  - SQL Development and Analysis                            
    5  PRINT         - Print table Data or Audit Trail                         
    6  RELATIONSHIPS - Relationship Facilitator                                 
    7  DATA DISGUISE - Define Disguise Criteria and Disguise Extract files     
    R  File-AID/RDX  - Transfer to File-AID/Related Data XPERT                 
    XT XPEDITER      - Transfer to XPEDITER                                     


Select Option 1


File-AID for DB2 ----------- SQL Analysis and Development Menu ---- SSID: DBXT
 OPTION  ===> 1                                                                
                                                                               
                                                                               
    0  Create or Maintain Explain Table(s)                                     
                                                                               
    1  SQL Plan Analysis                                                       
                                                                                
    2  Review or Print DB2 Explain Reports                                     
                                                                               
    3  ISPF/PDF  SQL Source Development and Analysis                           
                                                                               
                                                                               
Select package and fill in the highlighted fields. That is the fill in program name, select batch processing mode and qualifier of the package
                                                                                

File-AID for DB2 ------- SQL Plan Analysis Specification ---------------------
 COMMAND ===>                                                  Scroll ===> CSR 
                                                                     SSID: DBXT
 Select one Object Type to be Analyzed:                                        
   _ Plan                                                                      
   _ DBRM                                                                      
   _ Collection ID                                                             
   S Package                                                                   
                                                                               
 Specify Selection Criteria:                                                    
   Object Name  ===> TESTPGM            (* or pattern for selection list)      
   Use Filters  ===> YES                (Yes or No, Not valid for Collection ID)
                                                                                
 Specify Processing Option:                                                    
   Processing Mode             ===> 2     (1 = Online, 2 = Batch)              
   Host variable Substitution  ===> 1     (1 = Literal, 2 = Parameter Marker)  
                                                                               
 Specify Object Qualifier Options:                                             
   Qualify by            ===> 2     (1 = CURRENT SQLID, 2 = Bind Qualifier     
                                     3 = Default Qualifier , 4 = CURRENT SCHEMA)
   Default Qualifier     ===> MYDBID                                           
                                                                               
                                                                                
In the below screen, put “*” to see all the packages of the selected program


File-AID for DB2 ------- SQL Plan Analysis Specification ---------------------
 COMMAND ===>                                                  Scroll ===> CSR 
                                                                     SSID: DBXT
 Select one Object Type to be  Esssssss Filters Used to Select PACKAGE sssssssN
   _ Plan                      e COMMAND ===>               Scroll ===> CSR   e
   _ DBRM                      e                                              e
   _ Collection ID             e Object Name: TESTPGM                         e
   S Package                   e Creator ===> *            (* or pattern)     e
                               e                                              e
 Specify Selection Criteria:   e                                              e
   Object Name  ===> TESTPGM   e                                              e
   Use Filters  ===> YES       e                                              e
                               DssssssssssssssssssssssssssssssssssssssssssssssM
 Specify Processing Option:                                                     
   Processing Mode             ===> 2     (1 = Online, 2 = Batch)              
   Host variable Substitution  ===> 1     (1 = Literal, 2 = Parameter Marker)  
                                                                               
 Specify Object Qualifier Options:                                             
   Qualify by            ===> 2     (1 = CURRENT SQLID, 2 = Bind Qualifier     
                                     3 = Default Qualifier , 4 = CURRENT SCHEMA)
   Default Qualifier     ===> MYDBID                                           
                                                                               
                                                                               
                                                                                
Type “X” against the desired package


File-AID for DB2 ------------ Package Selection ----------- Row 61 to 63 of 65
 COMMAND ===>                                                  SCROLL ===> CSR 
                                                                     SSID: DBXT
                                                                               
 Line Commands:                                                                
   S - Select Package for more detail                                          
   X - Explain entire Package                                                  
                                                                               
 Cmd                                                                            
  X   Package Name    : TESTPGM                                                
      Bind Timestamp  : 2011-12-14-00.18.57.728515                             
      Collection ID   : CTESTOTD                                                
      Version         : 2011-09-02-13.52.39.549462                             
                                                                               
  _   Package Name    : TESTPGM                                                 
      Bind Timestamp  : 2011-12-14-00.17.30.499010                             
      Collection ID   : CTESTOTD                                               
      Version         : 2011-11-23-03.14.50.057038                              
                                                                               
  _   Package Name    : TESTPGM                                                
      Bind Timestamp  : 2011-10-15-15.15.36.566333                             
      Collection ID   : CTESTOUD                                               
      Version         : 2011-09-02-13.52.39.549462                             
                                                                               

Select “S” for access path analysis in the below screen.


File-AID for DB2 ------------ Package Selection ----------- Row 61 to 63 of 65
 C Essssssssssssssssssss Batch Plan Analysis Reports ssssssssssssssssssssN CSR 
   e                                                                     e DBXT
   e Batch SQL Analysis will always produce the following reports:       e     
 L e   -  Batch SQL Analysis Summary                                     e     
   e   -  Explain Detail Summary (Includes Index Information)            e     
   e                                                                     e     
   e Use 'S' to select additional reports                                e     
 C e   S Access Path Analysis                                            e     
   e   _ Predicate Analysis Summary                                      e     
   e   _ Predicate Analysis Detail (Rule descriptions)                   e     
   e                                                                     e     
   DsssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssM     
                                                                               
  _   Package Name    : TESTPGM                                                
      Bind Timestamp  : 2011-12-14-00.17.30.499010                             
      Collection ID   : CTESTOTD                                               
      Version         : 2011-11-23-03.14.50.057038                             
                                                                                
  _   Package Name    : TESTPGM                                                
      Bind Timestamp  : 2011-10-15-15.15.36.566333                             
      Collection ID   : CTESTOUD                                                
      Version         : 2011-09-02-13.52.39.549462                             
                                                                               

Type a new dataset name in the below screen, so the report can be stored in that dataset


File-AID for DB2 ----- SQL Analysis Batch Output Specification ----------------
 COMMAND ===>                                                                  
                                                                                
 Select Printer Destination or Dataset then press ENTER                        
                                                                               
   _  TO PRINTER                                                               
                                                                                
   Printer Destination  ===>                                                   
   Sysout Class         ===>                                                   
                                                                                
                                                                               
   S  TO DATASET                                                               
                                                                                
   Report Dataset Name  ===> 'TXUXXXX.TSTS.XRPT'                               
   Volume Serial        ===>                                                   
                                                                                
                                                                               
                                                                               
Submit the below job. Once the job is completed, you can see the report for access path.


EDIT ---- TXUXXXX.ISP01743.SPFTEMP1.CNTL --------------------- Columns 001 072
 COMMAND ===>                                                  SCROLL ===> CSR 
 ****** ***************************** Top of Data ******************************
 000001 //SS301 JOB (1013DBDC),'I',                                            
 000002 //  MSGCLASS=X,CLASS=3,                                                
 000003 //  NOTIFY=SS301,                                                       
 000004 //  MSGLEVEL=(1,1)                                                     
 000005 //*------------------------------------------------------------------- 
 000006 //COPYTL   EXEC PGM=IEBCOPY                                            
 000007 //SYSPRINT DD SYSOUT=*                                                 
 000008 //SYSOUT   DD SYSOUT=*                                                 
 000009 //INDD1    DD DSN= FADB2.DBXT.V6R2M0.ISPTLIB,                   
 000010 //         DISP=SHR                                                    
 000011 //INDD2    DD DSN=SYS1.SISPTENU,                                       
 000012 //         DISP=SHR                                                    
 000013 //*                                                                     
 000014 //OUTDD    DD DSN=&TBLIBS,                                             
 000015 //            DISP=(NEW,PASS,DELETE),                                  
 000016 //            SPACE=(TRK,(30,15,15),RLSE),                             
 000017 //            DSNTYPE=LIBRARY,LRECL=80,                                
 000018 //            UNIT=SYSDA                                               
 000019 //OUTDD2   DD DSN=&CEELIB,                                              
 000020 //            DISP=(NEW,PASS,DELETE),                                  
 000021 //            SPACE=(TRK,(30,15,15),RLSE),                             

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.