Sysview for DB2: Display DB2 Catalog Statistics for Tables/Indexes

Products

• SYSVIEW Performance Management Option for DB2 for z/OS

Issue/Introduction

Is there a way to retrieve DB2 Explain Statistics for Tables/Indexes from existing explain data stored on the Catalog?

Environment

Db2 for Z/OS

Release: R20

Resolution

If the Explain data is already on the Plan_table and the Catalog,
 
go into menu item 7  Explain,

then 1   Display EXPLAIN data for packages,

enter your Plan/Package Selection Criteria to find the one you want,
                        Plan/Package Selection Criteria


   Enter the selection criteria below.  Then press Enter.

     Name of Plan . . . . . . . . . . . . . . *
     Name of Collection ID  . . . . . . . . . *
     Name of Package  . . . . . . . . . . . . *
     Location name associated with package  . *
     Plan or collection owner ID  . . . . . . *

when the list of Plans and packages and Collections is displayed, select the package you want with an "S" on the left hand selection area on the package. 

                          Qualifying List of Programs
                                                                    Row 63-68/68
 Actions: S=View EXPLAIN data, P=View PATH, L=View long names

   Plan     Collection ID      Program  EXP BindDate BindTime Owner    Creator
 S          TSTPRG2K           TSTPRG2   Y  20/05/17 20:28:45 authid   authid   
 . TESTPLNA                    *******   N  19/11/26 21:16:50 authid   authid   
 .          DSN_DEFAULT_COLLID TESTPRGA  N  19/11/26 21:16:50 authid   authid   
 . TESTPRGA                    *******   Y  19/11/26 21:11:55 authid   authid   
 .          TESTPRGA           TESTPRGA  Y  19/11/26 21:11:54 authid   authid   
 . TSTPRG2P                    *******   Y  20/05/17 20:28:47 authid   authid   

This displays the Explain Data for the Plan....On the "EXPLAIN Data for......." screen, there is a Actions: C=Catalog stats option. Enter "C" in the input field next to the table and it will get the "DB2 Catalog Statistics for Tables/Indexes" screen that is required. 

   1 Expanded explanation   2 Summary list
                                             Level set (-) . 0
                      EXPLAIN Data for plan MXB1TS
                                                                     Row 4-27/27
 Actions: C=Catalog stats, T=SQL Text, O=Update OPTHINT (DB2 V6).  F6=Delete.
            QB JN   Mtch                           Index N-Sort-C TS Pre C Dgree
      Stmt#  # MT AC Cl Table/Index Name            Only UJOGUJOG LCK Tp E Ac Jn
 C      135  1    I   1 authid.CARS                   N --------  IS
                        authid.CARS_IX
               NL I   1 authid.CARS1                  N --------  IS
                        authid.CARS_IX1
 -------------------------------------------------------------------------------
  Line 01         Data accessed from the table
   SQL operation: SELECT statement
   Cost estimate: Milliseconds=1         Service units=1
                : Cost made with default values due to TABLE CARDINALITY
   In......Table: authid.CARS
           Index: authid.CARS_IX
   Tbl corr name: A
   Access Method: Matching index scan with data access using  1 column. Forward
            Lock: Intent share

  Line 02         Data accessed from the table
   In......Table: authid.CARS1
           Index: authid.CARS_IX1
   Tbl corr name: B
   Access Method: Matching index scan with data access using  1 column. Forward
            Lock: Intent share
   Join...Method: Nested loop

 -------------------------------------------------------------------------------

The DB2 catalog Statistics for the table and indexes are displayed. 

                   DB2 Catalog Statistics for Tables/Indexes

 ***************** Table stats for authid.CARS
 Num pages: -1       Pct pages: -1    Num columns: 4     Editproc: N/A
 Num rows : -1       Seg Size : 4     Max rec len: 107   Valiproc: N/A
 ----------------- Index stats for authid.CARS_IX
 %Clustered: 0%     1st key card : -1        URule: PRIMARY    Clustered : Y
 Page size : 4      Full key card: -1        Space: -1         Clustering: Y
 Num levels: -1     Num leaf pgs : -1        Type : 2
 Seq# <- Column name --> Match Column-type      Colcard  Low2key  High2key Colum
 1    CARCOLOR             Y   CHAR(30)         -1       @@@@@@@@ @@@@@@@@ CARCO
 *******************************************************************************
 ***************** Table stats for authid.CARS1
 Num pages: -1       Pct pages: -1    Num columns: 4     Editproc: N/A
 Num rows : -1       Seg Size : 4     Max rec len: 107   Valiproc: N/A
 ----------------- Index stats for authid.CARS_IX1
 %Clustered: 0%     1st key card : -1        URule: PRIMARY    Clustered : Y
 Page size : 4      Full key card: -1        Space: -1         Clustering: Y
 Num levels: -1     Num leaf pgs : -1        Type : 2
 Seq# <- Column name --> Match Column-type      Colcard  Low2key  High2key Colum
 1    CARCOLOR             Y   CHAR(30)         -1       @@@@@@@@ @@@@@@@@ CARCO

Additional Information

EXPLAIN Data for an Existing Program