CA Sysview for DB2 for Z/OS: Display DB2 Catalog Statistics for Tables/Indexes
Article ID: 191106
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
-------------------------------------------------------------------------------
The DB2 catalog Statistics for the table and indexes are diaplayed.
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
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
-------------------------------------------------------------------------------
The DB2 catalog Statistics for the table and indexes are diaplayed.
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
Feedback
Yes
No
Powered by
