Wednesday, December 14, 2011

Access path of a SQL before coding it in program using File-AID

The PLAN ANALYSIS function of File-AID for DB2 enables you to examine the performance efficiency of your SQL statements.  File-AID for DB2 invokes the EXPLAIN function of DB2 to analyze SQL.  Plan Analysis then produces a formatted analysis report that you can view online. 


Follow the below steps to generate "plain english" access path for your SQL query.

Select “4” from the File-aid/Db2 main menu

File-AID for DB2 - 6.2 ------ Primary Option Menu --------------- Enter option
 OPTION  ===> 4                                                                
                                                                    More:     +
    0  DEFAULTS and USER PARMS                          USERID   -    TXXBXXX  
    1  BROWSE                                           TIME     -    20:02    
    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                                    


First we need to create the Explain tables under under our TSO id. So, for that select “0” in the below screen.


File-AID for DB2 ----------- SQL Analysis and Development Menu ---- SSID: DBXT
 OPTION  ===> 0                                                                
                                                                               
                                                                               
    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 “1” in the below screen.
                                                                               

File-AID for DB2 ----- Create or Maintain Explain Table(s) --------------------
 OPTION  ===> 1                                                Scroll ===> CSR 
                                                                     SSID: DBXT
                                                                                
 Plan_Table AUTHID ===> TXXBXXX   Authorization ID for all EXPLAIN tables      
                                                                               
    1  Create Explain Table(s)                                                  
                                                                               
    2  Alter or Upgrade Explain Table(s)                                       
                                                                                
    3  Delete rows from Explain Table(s)                                       
                                                                               
                                                                                
                                                                               
      (Explain Tables: PLAN_TABLE, F2PLAN_TABLE, DSN_STATEMNT_TABLE,           
                       DSN_FUNCTION_TABLE)                                     
                                                                                
                                                                               

Key in the below highlighted fields


File-AID for DB2 ----------- Create Explain Tables ---------------------------
 COMMAND ===>                                                  Scroll ===> CSR 
                                                                     SSID: DBXT
 Select the tables below to be created.                                         
                                                                               
 Plan_Table AUTHID ===> TXXBXXX   Authorization ID for all EXPLAIN tables      
 Required Tables:                                                              
   Specify the Database and Tablespace for PLAN_TABLE                          
     Database   ===> MYTESTDB                                                   
     Tablespace ===>               (blank for implicit Tablespace)             
   Specify the Database and Tablespace for F2PLAN_TABLE                        
     Database   ===> MYTESTDB                                                  
     Tablespace ===>               (blank for implicit Tablespace)             
                                                                                
 Optional Tables:                                                              
 _ Specify the Database and Tablespace for DSN_STATEMNT_TABLE                  
     Database   ===>                                                            
     Tablespace ===>               (blank for implicit Tablespace)             
 _ Specify the Database and Tablespace for DSN_FUNCTION_TABLE                  
     Database   ===>               (This table requires at least a 8K buffer   
     Tablespace ===>                pool to be created in DB2 V8 & above)      
 Instructions:                                                                 
    Press ENTER to continue or END to cancel.                                   

                                                                               
Once we press ENTER in the above screen, the explain tables will be created.


File-AID for DB2 ----- Create or Maintain Explain Tab   Explain Tables Created
 OPTION  ===>                                                  Scroll ===> CSR 
                                                                     SSID: DBXT
                                                                               
 Plan_Table AUTHID ===> TXXBXXX   Authorization ID for all EXPLAIN tables      
                                                                               
    1  Create Explain Table(s)                                                 
                                                                                
    2  Alter or Upgrade Explain Table(s)                                       
                                                                               
    3  Delete rows from Explain Table(s)                                        
                                                                               
                                                                               
                                                                                
      (Explain Tables: PLAN_TABLE, F2PLAN_TABLE, DSN_STATEMNT_TABLE,           
                       DSN_FUNCTION_TABLE)                                     
                                                                                

Press F3 in the above screen to reach the below screen and select option “3”
                                                                               
File-AID for DB2 ----------- SQL Analysis and Development Menu ---- SSID: DBXT
 OPTION  ===> 3                                                                
                                                                               
                                                                                
    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                           
                                                                               
                                                                                
Type the dataset which has the SQL to be tested
                                                                               
File-AID for DB2 -------------- SQL ANALYSIS EDIT ENTRY ----------- SSID: DBXT
 COMMAND ===>                                                                   
                                                                               
 ISPF LIBRARY:                                                                 
    PROJECT ===>                                                                
    GROUP   ===>                                                               
    TYPE    ===>                                                               
    MEMBER  ===>                  (Blank or pattern for member selection list) 
                                                                               
                                                                               
                                                                                
 OTHER PARTITIONED OR SEQUENTIAL DATASET:                                      
    DATASET NAME     ===> 'TXXBXXX.TSTS.TEST(MYSQL)’                              
    VOLUME SERIAL    ===>            (If not cataloged)                        
                                                                                
    DATASET PASSWORD ===>            (If password protected)                   
    INITIAL MACRO    ===>                                                      
                                                                                
Once we press ENTER, We will get the below screen.


EDIT ---- TXXBXXX.TSTS.TEST(MYSQL) - 01.00 --------------------- Columns 001 072
 COMMAND ===>                                                  SCROLL ===> CSR 
 ****** ***************************** Top of Data ******************************
 =NOTE=  IN ADDITION TO ISPF COMMANDS, FOUR LINE COMMANDS ARE                  
 =NOTE=  PROVIDED THAT ENABLE YOU TO ANALYZE SQL. THESE COMMANDS                
 =NOTE=  ARE:  EX  EXECUTE, XP  EXPLAIN, BR  BROWSE, ED  EDIT.                 
 =NOTE=  TO INITIATE THE ACTION AFTER ENTERING THE LINE COMMAND ON             
 =NOTE=  AN SQL STATEMENT, TYPE FADB2 ON THE COMMAND LINE OR PRESS             
 =NOTE=  THE PFK THAT IS ASSIGNED TO FADB2.                                    
 =NOTE=                                                                        
 000001     SELECT *                                                           
 000002       FROM MYTESTDB.MYTAB_DATA                                          
 000003      WHERE MYTAB_TBL_ID = 'XX918'                                       
 000004        AND MYTAB_KEY_DATA IN (                                          
 000005        'XX2070301',                                                    
 000006        'XX2080301',                                                    
 000007        'XX6000801',                                                    
 000008        'XX6010801',                                                     
 000009        'XX6020601',                                                    
 000010        'XX6031001',                                                    
 000011        'XX6090201',                                                     
 000012        'XX6110901',                                                    
 000013        'XX6270301',                                                    


Type “.XP” against the first line of the SQL query and type “FADB2” in the command line

EDIT ---- TXXBXXX.TSTS.SOS(SQL6) - 01.00 --------------------- Columns 001 072
 COMMAND ===> FADB2                                            SCROLL ===> CSR 
 ****** ***************************** Top of Data ******************************
 =NOTE=  IN ADDITION TO ISPF COMMANDS, FOUR LINE COMMANDS ARE                  
 =NOTE=  PROVIDED THAT ENABLE YOU TO ANALYZE SQL. THESE COMMANDS               
 =NOTE=  ARE:  EX  EXECUTE, XP  EXPLAIN, BR  BROWSE, ED  EDIT.                  
 =NOTE=  TO INITIATE THE ACTION AFTER ENTERING THE LINE COMMAND ON             
 =NOTE=  AN SQL STATEMENT, TYPE FADB2 ON THE COMMAND LINE OR PRESS             
 =NOTE=  THE PFK THAT IS ASSIGNED TO FADB2.                                    
 =NOTE=                                                                         
 .XP        SELECT *                                                           
 000002       FROM MYTESTDB.MYTAB_DATA                                          
 000003      WHERE MYTAB_TBL_ID = 'XX918'                                       
 000004        AND MYTAB_KEY_DATA IN (                                          
 000005        'XX2070301',                                                    
 000006        'XX2080301',                                                     
 000007        'XX6000801',                                                    
 000008        'XX6010801',                                                    
 000009        'XX6020601',                                                     
 000010        'XX6031001',                                                    
 000011        'XX6090201',                                                    
 000012        'XX6110901',                                                     
 000013        'XX6270301',                                                    

Select “1” in the below screen for access path analysis


File-AID for DB2 - Explain - SQL Development and Analysis -- Row 1 to 11 of 13
 OPTION ===> 1                                                 SCROLL ===> PAGE
                                                                     SSID: DBXT
     1 - Access Path Analysis         4 - Object Information                   
     2 - Explain Detail               5 - Cost Detail                          
     3 - Predicate Analysis           6 - Function Detail                      
                                                                               
   DBRM/Package QUERYNO   Type    DB2 Cost  * PROCSU    Access Type            
   ------------ --------- ------  --------- - --------- ------------------------
   <SOURCE>     1         SELECT  5           <N/A>     IN-list Index Scan     
                                                                                
 SQL Statement: 1 OF 1                                                         
                                                                               
     SELECT *                                                                   
     FROM MYTESTDB.MYTAB_DATA                                                   
     WHERE MYTAB_TBL_ID = 'XX918'                                               
     AND MYTAB_KEY_DATA IN ( 'XX2070301', 'XX2080301', 'XX6000801', 'XX6010801',
     'XX6020601', 'XX6031001', 'XX6090201', 'XX6110901', 'XX6270301',          


You can the see the access path explained in plain English in the below screen


File-AID for DB2 ------- PLAN_TABLE Analysis Report for: <SOURCE>             
 COMMAND ===>                                                   SCROLL ===> CSR
                                                                               
 Use SQL to View/Save/Re-explain the SQL Source                                
                                                                                
                                                                               
 DB2 VERSION:  9.1.5                                                           
 QUERY NO   : 1                                                                 
 CURR SCHEMA: TXXBXXX                                                          
                                                                               
                                                                                
 Conditions                                                                    
   Query Block No:        1                                                    
   Plan No:               1                                                     
                                                                               
   Query Block Type:      SELECT                                               
                                                                                
   01 - The following table will be the first table accessed in this           
        query block.                                                           
          TB Creator: MYTESTDB                                                 
               Table: MYTAB_DATA                                                
   10 - The following table                                                    
        will be accessed through the following index                           
           TB Creator: MYTESTDB                                                


No comments:

Post a Comment

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