Category: News from the Labs

A quick history of EXPLAIN and a new standard Db2 EXPLAIN Query to bring out the best and most underused columns.

EXPLAIN has been with us from nearly the start of Db2 (DB2 V2R1 when the b was B!) and, over the years, the number of Explain tables has mushroomed up to 20 in Db2 12. Not *all* of this data is really useful but there are bits and pieces that are well worth adding to your standard repertoire of explain queries!

PLAN_TABLE

This can be used to see in which release the PLAN_TABLE was created:

V2.1 – 25 column format
V2.2 – 28 column format
V2.3 – 30 column format
V3.1 – 34 column format
V4.1 – 43 column format
V5.1 – 46 column format
V6.1 – 49 column format
V7.1 – 51 column format
V8.1 – 58 column format
V9.1 – 59 column format
10 – 64 column format ending with MERGN
11 – 66 Column format ending with EXPANSION_REASON
12 – 67 column format ending with PER_STMT_ID

What could be interesting here?? Well what about:

PREFETCH                           D, S, L, U, or Blank?

• D for Dynamic
• S for pure Sequential
• L for through a page list
• U for unsorted RID list
• Blank for Unknown or no prefetch

COLUMN_FN_EVAL R, S, X, Y, or Blank?

• R for while data is read
• S for while performing a sort
• X for while data is read but using OFFSET
• Y for while performing a sort but using OFFSET
• Blank for after data retrieval and any sort

PAGE_RANGE Y or Blank.

• Y for yes the table qualifies for page range screening
  so that only the needed partitions are scanned
• Blank for no

PRIMARY_ACCESSTYPE D, P, S, T, Blank. Is direct row access attempted first:

• D it tries to use direct row access with a rowid column. If it cannot do this it uses the access path that is described in the ACCESSTYPE column
• P it uses a DPSI and a part-level operation to access the data
• S it uses sparse index access for a sideways table reference
• T the base table or result file is materialized into a work file, and the work file is accessed via sparse index access. If a base table is involved, then ACCESSTYPE indicates how the base table is accessed
• Blank it does not try to use direct row access by using a rowid column or sparse index access for a work file.

DSN_DETCOST_TABLE

There are a ton of interesting columns here but most are “IBM internal only”, however, these are available for our enjoyment:

DSN_FILTER_TABLE

Sometimes it is really interesting to see when the predicate is applied and whether or not it could be pushed down.

DSN_PREDICAT_TABLE

Here lives the really good stuff that most people do not use! The Bubble Up is normally an SQL coding error and the T is “forgetfulness”…

New Standard Explain SQL

Just adding these to your standard Explain query like this:

SET CURRENT SQLID = 'BOXWELL' ;                                   
 SELECT SUBSTR(P.PROGNAME, 1 , 8 )        AS PROGNAME              
       ,SUBSTR(DIGITS(P.QUERYNO), 6, 5)   AS LINE                  
       ,SUBSTR(DIGITS(P.QBLOCKNO), 4, 2)  AS QNO                   
       ,SUBSTR(DIGITS(P.PLANNO), 4, 2)    AS PNO                   
       ,SUBSTR(DIGITS(P.MIXOPSEQ), 4, 2)  AS SQ                    
       ,SUBSTR(DIGITS(P.METHOD), 5, 1)    AS M                     
       ,SUBSTR(P.TNAME, 1, 18)            AS TABLE_NAME            
       ,P.ACCESSTYPE                      AS A                     
       ,P.PRIMARY_ACCESSTYPE              AS PA                    
       ,SUBSTR(DIGITS(P.MATCHCOLS), 4, 2) AS CS                    
       ,SUBSTR(P.ACCESSNAME, 1, 12)       AS INDEX                 
       ,P.INDEXONLY                       AS IO                    
       ,SUBSTR(CASE SORTN_UNIQ    WHEN 'N' THEN '-' ELSE 'Y' END ||
               CASE SORTN_JOIN    WHEN 'N' THEN '-' ELSE 'Y' END ||
               CASE SORTN_ORDERBY WHEN 'N' THEN '-' ELSE 'Y' END ||
               CASE SORTN_GROUPBY WHEN 'N' THEN '-' ELSE 'Y' END   
                                 , 1 , 4) AS UJOG                  
       ,SUBSTR(CASE SORTC_UNIQ    WHEN 'N' THEN '-' ELSE 'Y' END ||
               CASE SORTC_JOIN    WHEN 'N' THEN '-' ELSE 'Y' END ||
               CASE SORTC_ORDERBY WHEN 'N' THEN '-' ELSE 'Y' END ||
               CASE SORTC_GROUPBY WHEN 'N' THEN '-' ELSE 'Y' END   
                                 , 1 , 4) AS UJOGC                 
       ,P.PREFETCH                        AS P                     
       ,P.COLUMN_FN_EVAL                  AS CE                    
       ,P.PAGE_RANGE                      AS PR                    
       ,P.QBLOCK_TYPE                     AS TYPE                  
       ,P.MERGE_JOIN_COLS                 AS MJC                   
       ,S.PROCMS                          AS MS                    
       ,S.PROCSU                          AS SU                    
       ,D.EARLY_OUT                       AS EO                    
       ,D.BLOCK_FETCH                     AS BF                    
       ,F.ORDERNO                         AS ON                    
       ,F.PREDNO                          AS PN                    
       ,F.STAGE                           AS STAGE                 
       ,F.PUSHDOWN                        AS PD                    
       ,R.TYPE                            AS TYPE                  
       ,R.ADDED_PRED                      AS AP                    
       ,R.CLAUSE                          AS CLAUSE                
       ,R.ORIGIN                          AS OR                    
       ,R.REDUNDANT_PRED                  AS RP                    
       ,R.TEXT                            AS TRANSFORMED_PREDICATE 
 FROM PLAN_TABLE         P                                         
 FULL OUTER JOIN                                                   
      DSN_STATEMNT_TABLE S               
 ON    P.QUERYNO      = S.QUERYNO        
   AND P.APPLNAME     = S.APPLNAME       
   AND P.PROGNAME     = S.PROGNAME       
   AND P.COLLID       = S.COLLID         
   AND P.GROUP_MEMBER = S.GROUP_MEMBER   
   AND P.SECTNOI      = S.SECTNOI        
   AND P.VERSION      = S.VERSION        
   AND P.EXPLAIN_TIME = S.EXPLAIN_TIME   
 FULL OUTER JOIN                         
      DSN_DETCOST_TABLE  D               
 ON    P.QUERYNO      = D.QUERYNO        
   AND P.APPLNAME     = D.APPLNAME       
   AND P.PROGNAME     = D.PROGNAME       
   AND P.COLLID       = D.COLLID         
   AND P.GROUP_MEMBER = D.GROUP_MEMBER   
   AND P.SECTNOI      = D.SECTNOI        
   AND P.VERSION      = D.VERSION        
   AND P.EXPLAIN_TIME = D.EXPLAIN_TIME   
   AND P.QBLOCKNO     = D.QBLOCKNO       
   AND P.PLANNO       = D.PLANNO         
 FULL OUTER JOIN                         
      DSN_FILTER_TABLE   F               
 ON    P.QUERYNO      = F.QUERYNO        
   AND P.APPLNAME     = F.APPLNAME       
   AND P.PROGNAME     = F.PROGNAME       
   AND P.COLLID       = F.COLLID         
   AND P.GROUP_MEMBER = F.GROUP_MEMBER   
   AND P.SECTNOI      = F.SECTNOI        
   AND P.VERSION      = F.VERSION        
   AND P.EXPLAIN_TIME = F.EXPLAIN_TIME   
   AND P.QBLOCKNO     = F.QBLOCKNO       
   AND P.PLANNO       = F.PLANNO         
 FULL OUTER JOIN                         
      DSN_PREDICAT_TABLE R               
 ON    F.QUERYNO      = R.QUERYNO        
   AND F.APPLNAME     = R.APPLNAME       
   AND F.PROGNAME     = R.PROGNAME       
   AND F.COLLID       = R.COLLID         
   AND F.GROUP_MEMBER = R.GROUP_MEMBER   
   AND F.SECTNOI      = R.SECTNOI        
   AND F.VERSION      = R.VERSION        
   AND F.EXPLAIN_TIME = R.EXPLAIN_TIME   
   AND F.QBLOCKNO     = R.QBLOCKNO       
   AND F.PREDNO       = R.PREDNO         
 WHERE 1 = 1                             
   AND P.QUERYNO  IN (1 , 2 )                               
 ORDER BY 1 , 2 , 3 , 4 , 5 , 24 , 25                       
 ;                                      

Here I limit it to just the QUERYNO 1 and 2 as these were the numbers used for the EXPLAIN command:

EXPLAIN ALL SET QUERYNO = 1 FOR              
 SELECT INSTANCE, CLONE                       
 FROM SYSIBM.SYSTABLESPACE A                  
 WHERE ( SELECT B.TSNAME                      
         FROM SYSIBM.SYSTABLES  B             
             ,SYSIBM.SYSINDEXES C             
         WHERE C.CREATOR   = ?                
           AND C.NAME      = ?                
           AND C.TBCREATOR = B.CREATOR        
           AND C.TBNAME    = B.NAME ) = A.NAME
 AND A.DBNAME = ?                             
 ;                                            
 EXPLAIN ALL SET QUERYNO = 2 FOR              
 SELECT A.INSTANCE, A.CLONE                   
 FROM SYSIBM.SYSTABLESPACE A                  
     ,SYSIBM.SYSTABLES     B                  
     ,SYSIBM.SYSINDEXES    C                  
 WHERE C.CREATOR   = ?                        
   AND C.NAME      = ?                        
   AND C.TBCREATOR = B.CREATOR                
   AND C.TBNAME    = B.NAME                   
   AND A.DBNAME    = ?                        
   AND A.NAME      = B.TSNAME                 
   AND A.DBNAME    = B.DBNAME                 
 WITH UR                                      
 ;                         

--------+--------+------+--------+---------+---------+--------+------+---
PROGNAME LINE QNO PNO SQ M  TABLE_NAME    A PA CS INDEX    IO UJOG  UJOGC
--------+--------+------+--------+---------+---------+--------+------+---
DSNESM68 00001 01 01  00 0  SYSTABLESPACE I    02  DSNDSX01 N  ----  ---- 
DSNESM68 00001 01 01  00 0  SYSTABLESPACE I    02  DSNDSX01 N  ----  ---- 
DSNESM68 00001 02 01  00 0  SYSINDEXES    I    02  DSNDXX01 N  ----  ---- 
DSNESM68 00001 02 01  00 0  SYSINDEXES    I    02  DSNDXX01 N  ----  ---- 
DSNESM68 00001 02 02  00 1  SYSTABLES     I    02  DSNDTX01 N  ----  ---- 
DSNESM68 00001 02 02  00 1  SYSTABLES     I    02  DSNDTX01 N  ----  ---- 
DSNESM68 00002 01 01  00 0  SYSINDEXES    I    02  DSNDXX01 N  ----  ---- 
DSNESM68 00002 01 01  00 0  SYSINDEXES    I    02  DSNDXX01 N  ----  ---- 
DSNESM68 00002 01 02  00 1  SYSTABLES     I    02  DSNDTX01 N  ----  ---- 
DSNESM68 00002 01 02  00 1  SYSTABLES     I    02  DSNDTX01 N  ----  ---- 
DSNESM68 00002 01 02  00 1  SYSTABLES     I    02  DSNDTX01 N  ----  ---- 
DSNESM68 00002 01 03  00 1  SYSTABLESPACE I    02  DSNDSX01 N  ----  ---- 
DSNESM68 00002 01 03  00 1  SYSTABLESPACE I    02  DSNDSX01 N  ----  ---- 
DSNE610I NUMBER OF ROWS DISPLAYED IS 13 

--+---------+---------+---------+---------+---------+---------+--------
 P  CE  PR  TYPE      MJC            MS           SU  EO  BF        ON
--+---------+---------+--------+---------+---------+---------+---------
            SELECT  ------            1            2  N   N          1
            SELECT  ------            1            2  N   N          2
            NCOSUB  ------            1            2  N   N          1
            NCOSUB  ------            1            2  N   N          2
            NCOSUB  ------            1            2  N   N          1
            NCOSUB  ------            1            2  N   N          2
            SELECT  ------            1            2  N   N          1
            SELECT  ------            1            2  N   N          2
            SELECT  ------            1            2  N   N          1
            SELECT  ------            1            2  N   N          2
            SELECT  ------            1            2  N   N          3
            SELECT  ------            1            2  N   N          1
            SELECT  ------            1            2  N   N          2

---------+---------+---------+---------+---------+---------+---------+-----
PN  STAGE  PD TYPE  AP  CLAUSE    OR  RP  TRANSFORMED_PREDICATE 
---------+---------+---------+---------+---------+---------+---------+-----
3  MATCHING   EQUAL     WHERE     U   N   "A"."DBNAME"=(EXPR) 
2  MATCHING   EQUAL     WHERE     U   N   "A"."NAME"=(SELECT "B"."TSNAME"
5  MATCHING   EQUAL     WHERE     U   N   "C"."CREATOR"=(EXPR)           
6  MATCHING   EQUAL     WHERE     U   N   "C"."NAME"=(EXPR)              
7  MATCHING   EQUAL     WHERE     U   N   "C"."TBCREATOR"="B"."CREATOR"
8  MATCHING   EQUAL     WHERE     U   N   "C"."TBNAME"="B"."NAME"        
2  MATCHING   EQUAL     WHERE     U   N   "C"."CREATOR"=(EXPR)           
3  MATCH      EQUAL     WHERE     U   N   "C"."NAME"=(EXPR)              
4  MATCH      EQUAL     WHERE     U   N   "C"."TBCREATOR"="B"."CREATOR"  
5  MATCHING   EQUAL     WHERE     U   N   "C"."TBNAME"="B"."NAME"        
9  STAGE1     EQUAL  T  WHERE         N   "B"."DBNAME"=(EXPR)            
6  MATCHING   EQUAL     WHERE     U   N   "A"."DBNAME"=(EXPR)            
7  MATCHING   EQUAL     WHERE     U   N   "A"."NAME"="B"."TSNAME"        

It shows you a lot more data than the normal bare bones Explain PLAN_TABLE data especially here the TYPE, STAGE and ADDED_PREDICATE (AP Column).

Feel free to send me your comments and ask questions.

TTFN,
Roy Boxwell
Senior Architect