Display catalog Statistics on a Table/Index
Article ID: 368899
Updated On:
Products
RC/Query for DB2 for z/OS
Database Analyzer for DB2 for z/OS
DATABASE MANAGEMENT SOLUTIONS FOR DB2 FOR Z/OS
Issue/Introduction
Querying the catalog for object statistics is a task that is part of performance tuning.
This can be time consuming as SQL normally is written to obtain the data from the catalog.
Environment
DB2 for Z/OS
Release :R20
Cause
Resolution
In this example the statistic of interest is the CARDF of the table. As we look at the reports there are other statistics of interest seen in particular the STATSTIME.
1. RC/Query for Db2 for Z/OS
RC/Query has the "HS" line command(Table Stats. History) on a table that can display the CARDF value.
RQTL --------------- RC/Q Table List --------------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> T Option ===> L Where => N
Table Name ===> TBUSERID > Creator ===> AUTHID1 >
Qualifier ===> * > N/A ===> * >
Loc: LOCAL ---------- SSID: ssid ----------AUTHID1 - LINE 1 OF 1 >
CMD TABLE NAME CREATOR DATABASE TBLSPACE COL_CNT NUMBER OF ROWS
HS______ TBUSERID AUTHID1 DBCORP TSUSER 3 6
******************************* BOTTOM OF DATA ********************************
and this report is generated......
RQTHS ---------- RC/Q Table Stats. History ---------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> T Option ===> HS Where => N
Table Name ===> TBUSERID > Creator ===> AUTHID1 >
Qualifier ===> * > N/A ===> * >
Loc: LOCAL ---------- SSID: ssid LVL: 01 -AUTHID1 - FRAME 1 OF 2
********************************* TOP OF DATA ********************************
CMD: ________
NAME : TBUSERID
OWNER : AUTHID1
DBNAME : DBCORP STATSTIME: yyyy-mm-dd-hh.mm.ss.mmmmmm
TSNAME : TSUSER CARDF : 6 NPAGES : 1
PARTITION : 1 IBMREQD : N
--------------- END OF DATA ITEM ---------------
2. RC/Query for Db2 for Z/OS
RC/Query has the "HD" line command(Table Detail History) on a table that can display the CARDF value.
RQOD ----------- RC/Q Object Dependency ------------ yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> T Option ===> O Where => N
Table Name ===> TBUSERID > Creator ===> AUTHID1 >
Qualifier ===> * > N/A ===> * >
Loc: LOCAL ---------- SSID: ssid ----------AUTHID1 - LINE 1 OF 2
CMD TABLENAME TBCREATR INDEXNAME IXCREATR
HD______ TBUSERID AUTHID1
________ TBUSERIDX AUTHID1
******************************* BOTTOM OF DATA ********************************
and this report is generated......
RQTHD ---------- RC/Q Table Detail History ---------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> T Option ===> HD Where => N
Table Name ===> TBUSERID > Creator ===> AUTHID1 >
Qualifier ===> * > N/A ===> * >
Loc: LOCAL ---------- SSID: ssid LVL: 01 -AUTHID1 - FRAME 1 OF 2
********************************* TOP OF DATA *********************************
CMD: ________
NAME : TBUSERID
CREATOR : AUTHID1
DBNAME : DBCORP STATSTIME: yyyy-mm-dd-hh.mm.ss.mmmmmm
TSNAME : TSUSER COLCOUNT : 3 PCTPAGES: 33
PCTROWCOMP: 0 CARDF : 6 NPAGESF : 1
AVGROWLEN : 27 SPACEF : 48 IBMREQD : H
--------------- END OF DATA ITEM ---------------
3. RC/Query for Db2 for Z/OS
RC/Query has the "D" line command(Table Detail) on a table that can display the .
RQOD ----------- RC/Q Object Dependency ------------ yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> T Option ===> O Where => N
Table Name ===> TBUSERID > Creator ===> AUTHID1 >
Qualifier ===> * > N/A ===> * >
Loc: LOCAL ---------- SSID: ssid ----------AUTHID1 - LINE 1 OF 2
CMD TABLENAME TBCREATR INDEXNAME IXCREATR
D_______ TBUSERID AUTHID1
________ TBUSERIDX AUTHID1
******************************* BOTTOM OF DATA ********************************
and this report is generated......the "NUMBER OF ROWS" below is CARDF
RQTD -------------- RC/Q Table Detail -------------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
PT029 PT029I - USE PF10/11 FOR UP/DOWN WITHIN DATA ITEM
DB2 Object ===> T Option ===> D Where => N
Table Name ===> TBUSERID > Creator ===> AUTHID1 >
Qualifier ===> * > N/A ===> * >
Loc: LOCAL ---------- SSID: ssid LVL: 01 -AUTHID1 - FRAME 1 OF 1
********************************* TOP OF DATA *********************************
CMD: ________
TABLE : TBUSERID
CREATOR : AUTHID1
OWNER : AUTHID1 OWNER TYPE: AUTHID
CREATEDBY: AUTHID1
CREATED : yyyy/mm/dd / hh:mm:ss IN DATABASE: DBCORP
ALTERED : yyyy/mm/dd / hh:mm:ss TABLESPACE: TSUSER
CREATED RBA: X'00DCC8486C8EFED97600' ALTERED RBA: X'00DCC8486C8EFED97600'
NUMBER OF COLUMNS: 3 OBID: 55 EDIT PROCEDURE :
ENCODING SCHEME : E DBID: 12,405 VALIDATION PROC:
NUMBER OF ROWS : 6 TABLE TYPE: TABLE
NUMBER OF CHECKS : 0 REL CREATED: DB2v12
% ROWS COMPRESSED: 0 TABLE STATUS: COMPLETE
TOTAL NUMBER PAGES: 1 PERCENT OF TABLE SPACE: 33
COLUMNS IN KEY : 1 MAXIMUM RECORD LENGTH : 29
RESTRICT ON DROP :
COLUMNS IN HASHKEY : 0 ACCESS CONTROL :
STATS FEEDBACK: Y
REGENERATETS: yyyy/mm/dd / hh:mm:ss
STATUS : X CHECKFLAG: CHECKRID: ANY AUDITING:
DATA CAPTURE : NONE CLUSTERRID: 0 KEYOBID : 81
LINKS AS CHILD: 0 AS PARENT: 0 NUMBER INDEXES: 1
TABLE LABEL : NUMBER PLANS : 0
4. General Facilities : Space Calculator
Find the table by using an index name and use the "F" line command : Fetch catalog object statistics
ROPSCSS 20.0.11 ------- SpaceCalc Strategy Select ------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
Option ==> C (S,C,*) Object => * Database => DBCORP
Item Name ==> TBUSERID% > Creator => AUTHID1 > Where => N
SSID: ssid ----------------------------------------------------- AUTHID1 >
Op Name S Database Creator Ty SpaceNam Index Name
__ ________ AUTHID1 SS ________ __________________
F_ TBUSERID U DBCORP AUTHID1 IX TBUSERID TBUSERIDX
******************************* BOTTOM OF DATA ********************************
and this report is generated......The Estimated Data Rows below starts with
a current value of CARDF.
ROPIALC ------------- Index Space Calculation ------------ yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
Index ==> TBUSERIDX > Partition ==> 0 Key Length ==> 7
Control Parameters:
Device Type => 3390 (eg: 3390) Freepage => 0 (0-255)
Index Type => 2 (1,2) Pctfree => 10 (0-99)
Unique Index => YES (Yes,No) Avg Duplicate => 0.0 (0,1-max)
Est. Data Rows => 000000006 Secqty % => 10 (0-99)
Volume => N SYSDE > (N,V,S) SSTRAT => N TBUSERID
Base TS Large => NO
Summary Space Statistics -------------------------- Cylinder Rounding: OFF
Number of Levels ===> 1 Number of Index Pages ===> 12
Implicit PRIQTY ===> 48 Number of Tracks ===> 1
Implicit SECQTY ===> 48 Number of Cylinders ===> 1
Detailed Space Statistics:
Leaf Pages at Index Level 1................. 1
Total Index Pages Needed.................... 1
Total Pages with Header and Space Map Pages. 3
Adjusted Total 1K Blocks (PRIQTY)........... 12
Index Entry Length (Leaf)................... 14
Index Entry Length (Non-Leaf)............... 14
Non-Freespace Percentage of Page............ 90%
Entries per Leaf Page....................... 259
Total Leaf Pages Needed..................... 1
FREEPAGE Adjustment......................... 0
Adjusted Total Leaf Pages................... 1
Entries per Non-Leaf Page................... 260
******************************* BOTTOM OF DATA ********************************
5. Database Analyzer for DB2 for Z/OS : CU Catalog Update
Enter the Database and Tablespace to get a list of the structure.
RDA.CUMN ---- CATALOG UPDATE SELECTION ---- yyyy/mm/dd hh:mm
COMMAND ==>
------------------------------------------------------------ User ID: AUTHID1
DB2 Version: 121M502
OBJECT SELECTION LIST PARAMETERS
Database ==> DBCORP
Table Space ==> TSUSER
(or)
Index Name ==>
Creator ==> *
Subsystem Connection ID ==> ssid
Location ==> LOCAL
Enter Database/Table Space or Index/Creator and press ENTER.
The list below is the structure under the Database.....enter a "U" line command next to the Table....
RDA.CUBD ------- EXTRACT DATA SELECTION ------ yyyy/mm/dd hh:mm
Command ==> SCROLL ==> CSR
Loc: LOCAL --------------------- DB2 ID: ssid -------------- User ID: AUTHID1
Database: DBCORP Table Space: TSUSER
O Sta Database TSpace Table Creator Index
_ DBCORP TSUSER 0001 AUTHID1
U TBUSERID AUTHID1
_ 0001 TBUSERID AUTHID1
_ AUTHID1 TBUSERIDX
******************************* BOTTOM OF DATA ********************************
The Catalog Update report shows the CARDF of the table.
RDA.CUTB ---- TABLE CATALOG UPDATE ---- yyyy/mm/dd hh:mm
COMMAND ==>
Loc: LOCAL --------------------- DB2 ID: ssid -------------- User ID: AUTHID1
Creator: AUTHID1 Table Name: TBUSERID
-- Log Entry -
- Column -- -- New Value - -- Catalog --- -- PDA Stats - UpdBy:
Name yy/mm/dd hh:mm
-------------------------------------------------------------------------------
CARDF 6 6 NO LOG DATA
NPAGESF 1 1 NO LOG DATA
PCTPAGES 33 33 NO LOG DATA
PCTROWCOMP 0 NO PDA DATA NO LOG DATA
Enter data for NEW VALUE. Enter SAVE on COMMAND line to save. PF3 to exit.
Additional Information
Feedback
Yes
No
Powered by
