CA Sysview for DB2 for Z/OS: Display DB2 Catalog Statistics for Tables/Indexes
book
Article ID: 191106
calendar_today
Updated On:
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
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 is displayed 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 you will get the "DB2 Catalog Statistics for Tables/Indexes" screen that you are after.
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