Find if program is doing table scan or index scan

When a program selects table scan for a DB2 table, it processes all the records from that table and this will ok if the table is small, but if the table is big it will cause serious performance issue. DB2 resorts to table scan, when the WHERE condition in SQL query does not use any index defined on the table.

If the WHERE condition in the query uses all the fields from an index or few fields from an index, it will perform index scan and index scan is more efficient from performance standpoint. When the WHERE condition uses few fields from an index, it must refer to top order fields of the index, so DB2 can select index scan. For example, if an index has 5 fields, then WHERE condition should refer to 1^st field, or 1^st &2^nd, or 1^st & 2^nd & 3^rd fields and so on.

The output of EXPLAIN is stored in the PLAN_TABLE. We can find if a program is doing table scan or index scan by analyzing the records from this table.

The below query retrieves all data related to table space scan for latest package of a program. ACCESSTYPE column from the PLAN_TABLE tells if program is doing table scan or index scan.

    SELECT                                                             

      A.QUERYNO                                                        

     ,SUBSTR(A.PROGNAME,1,8) AS PROGNAME                               

     ,SUBSTR(A.COLLID,1,8)   AS COLLID                                 

     ,SUBSTR(A.TNAME,1,20)   AS TAB_NAME                               

     ,A.ACCESSTYPE                                                     

     ,A.TIMESTAMP                                                      

   FROM MYPRODDB.PLAN_TABLE AS A                                         

    WHERE (A.COLLID = 'MYCOLLID')  AND

          (A.PROGNAME = ‘TESTPGM’) AND          

          (A.ACCESSTYPE  = 'R') AND                

           A.BIND_TIME =                                               

           (SELECT MAX(B.BIND_TIME)                                    

              FROM MYPRODDB.PLAN_TABLE AS B                              

             WHERE B.PROGNAME = A.PROGNAME AND                         

                   B.COLLID   = A.COLLID);                              

The various possible values of ACCESSTYPE column is given below.

Column Name       Description

ACCESSTYPE       The method of accessing the table

I                By an index  

I1               By a one-fetch index scan

N                By an index scan when the matching predicate contains the IN keyword

R                By a table space scan

M                By a multiple index scan (followed by MX, MI, or MU)

MX               By an index scan on the index named in ACCESSNAME

MI               By an intersection of multiple indexes

MU               By a union of multiple indexes

blank            Not applicable to the current row

The below query retrieves all data related to index space scan for latest package of a program. COLCOUNT column has number of columns present in an index.  MATCHCOLS tells how many columns of the index are used in matching

    SELECT                                                             

      A.QUERYNO                                                        

     ,SUBSTR(A.PROGNAME,1,8) AS PROGNAME                               

     ,SUBSTR(A.COLLID,1,8)   AS COLLID                                 

     ,SUBSTR(A.TNAME,1,20)   AS TAB_NAME                               

     ,A.ACCESSTYPE                                                     

     ,SUBSTR(A.ACCESSNAME,1,15) AS INDEX_NAME                          

     ,X.COLCOUNT                                                       

     ,A.MATCHCOLS                                                      

   FROM  MYPRODDB.PLAN_TABLE AS A,                                        

        SYSIBM.SYSINDEXES AS X                                         

    WHERE (A.COLLID = 'MYCOLLID') AND

          (A.PROGNAME = ‘TESTPGM’) AND          

          ((X.TBCREATOR = 'MYPRODDB'      AND                          

            X.TBNAME    =  A.TNAME        AND                          

            X.CREATOR   = 'MYPRODDB'      AND                          

            X.NAME      =  A.ACCESSNAME   AND                          

            A.ACCESSTYPE IN('I','I1','N','M','MX','MI','MU'))) AND     

           A.BIND_TIME =                        

           (SELECT MAX(B.BIND_TIME)             

              FROM  MYPRODDB.PLAN_TABLE AS B       

             WHERE B.PROGNAME = A.PROGNAME AND  

                   B.COLLID   = A.COLLID)       

      WITH UR;