Displaying statistics
This topic describes the following tasks:
- To display Db2 catalog and BMCSTATS statistics for Db2 objects
- To display table space statistics
- To display table statistics
- To display index statistics
- To display index partition statistics
- To display table space partition statistics
- To display table partition statistics
- To display column statistics
Before you begin
You must run the BMCSTATS utility at least once to populate the DASD MANAGER PLUS database with statistics before you can view any BMC Software statistics.
To display Db2 catalog and BMCSTATS statistics for Db2 objects
Perform this procedure to display Db2 catalog and BMCSTATS statistics for Db2 objects on the same panel:
Create a Db2 object list.
For Db2 catalog statistics, the following object types are valid:
- TB (table)
- TS (table space)
- IX (index)
For more information, see Creating-a-Db2-object-list.
The Display Db2 Object List panel shows the objects that match the information that you specified on the Db2 Object Selection panel.
Type B in the Act field beside the object for which you want to display statistics and press Enter.Objects for which both BMCSTATS and Db2 catalog statistics are available have a blank in the Source column.
The Statistics Browse panel for the specified object appears.
- (optional) Press END to exit the panels.
You can compare the statistics. You can also display BMCSTATS statistics for the first, last, and next-to-last time that you ran BMCSTATS. For more information, see Analyzing-statistical-trends.
To display table space statistics
- Navigate to the Tablespace Statistics Browse panel.
The Tablespace Statistics Browse panel displays the Db2 catalog and BMC Software statistics for the specified table space. The product obtains the Db2 catalog value from the SYSIBM.SYSTABLESPACE catalog table. The BMCSTATS value comes from the corresponding statistics table in the DASD MANAGER PLUS database and shows the value from the last run.
Tablespace Statistics Browse panel
COMMAND ===>
Tablespace Name: QZUDEF.QZUS01EF Type : L Partitions: 4
Clone : N Tables. . : 1
MaxParts: 0 Segsize : 0
Member Cluster: N Implicit : N
PageNum : A Dssize : 8388608
----------------------------------------------------
| DB2 Catalog | BMCSTATS |
| Nactive: 246015 | 246015 |
| yyyy-mm-dd-04.35 | yyyy-mm-dd-06.48 |
----------------------------------------------------
Select additional panels to display and press Enter.
Display Table statistics? Display Partition Statistics?
Where to go from here
To display statistics for a particular table, see To display table statistics.
To display table statistics
- Open the Table Selection panel using one of the following methods:
- On the Tablespace Statistics Browse panel, in the Display Table Statistics? field, type S, and press Enter. Use this method to display updatable table statistics.
- On the COMMAND line, type T and press Enter.
- From the Object Selection List panel, type B in the Act field next to the table name.
If the table space contains more than one table, the Table Selection panel is displayed:
Table Selection panel
DEMO ------------------------ Table Selection ---------------- Row 1 to 2 of 2
COMMAND ===> SCROLL ===> PAGE
Type S to select an entry and press Enter.
Act Owner Table Name Card Npages Pages Type
-------------------------------------------------------------------------------
QZU QZUT01_DA1S02 2036 0 100 T
QZU QZUT02_DA1S02 0 0 0 T
******************************* Bottom of data ********************************Select a table from the list in the Table Selection panel. Type S in the Act field beside the table name and press Enter.
The Db2 catalog statistics for the table appear in the Table Statistics Browse panel:
Table Statistics Browse panel
ASUCTBPB -------------------- Table Statistics Browse ------------------------
COMMAND ===>
Type data and press Enter.
Table Name . . : QZU.QZUT01_DACS01
Tablespace Name: QZUDAC.QZUS01AC
Type . . . . . : T
------------------------------------------------
| DB2 Catalog | BMCSTATS |
| Card . . . 65000 | 65000 |
| Npages . . 21669 | 21669 |
| | |
| PctPages 93 | 93 |
| PctRowComp 0 | 0 |
| yyyy-mm-dd-10.47 | yyyy-mm-dd-12.54 |
------------------------------------------------
Select additional panels to display and press Enter.
Display Column statistics? Display Index statistics?- (optional) Press END to return to the previous panel.
Where to go from here
To display index statistics, see To display index statistics.
To display column statistics, see To display column statistics.
To display index statistics
- Perform To display table statistics.
Open the Index Statistics Browse panel using one of the following methods:
- In the Display Index Statistics? field, type S, and press Enter.
- Type I at the COMMAND line.
The Db2 catalog and BMC Software statistics for a specified index appear in the Index Statistics Browse panel:
Index Statistics Browse panel
DEMO -------------------- Index Statistics Browse -----------------------------
COMMAND ===>
Index Name: QZU.QZUX01_DEFS01T01 DB Name : QZUDEF
Table Name: QZU.QZUT01_DEFS01
1stKeyCol : PARTITION_PERIOD
Compress : N IX Extension Type:
Hash . . .: N Sparse . . . . . :
Dssize : 8388608 Pagenum . . . . .: A
----------------------------------------------------
| DB2 Catalog | BMCSTATS |
| FirstKeyCard: 10 | 10 |
| FullKeyCard : 10 | 10 |
| Nleaf . . . : 36051 | 36051 |
| Nlevels . . : 3 | 3 |
| ClusterRatio: 99 | 100 |
| DataRepeat : 245730 | 245744 |
| High2key . : 09 | 09 |
| FF444444 | FF000000 |
| 09000000 | 09000000 |
| Low2key . . : 02 | 02 |
| FF444444 | FF000000 |
| 02000000 | 02000000 |
| yyyy-mm-dd-04.35 | yyyy-mm-dd-06.48 |
----------------------------------------------------
Select additional panels to display and press Enter.
Display Partition Statistics? Display Keytargets?The product obtains the Db2 catalog values from the SYSIBM.SYSINDEXES catalog table. The BMCSTATS values come from the corresponding statistics table in the DASD MANAGER PLUS database.
Where to go from here
To display index partition statistics, see To display index partition statistics.
To display index partition statistics
- Perform To display index statistics.
On the Index Statistics Browse panel, select Display Partition Statistics?. If the index space has more than one partition, the Index Partition Selection panel is displayed:
Index Partition Selection panel
DEMO ------------------- INDEX PARTITION SELECTION ----------- Row 1 to 4 of 4
COMMAND ===> SCROLL ===> CSR
Index Name: QZU.QZUX01_DPBS01T01 DB Name : QZUDPBR2
Table Name: QZU.QZUT01_DPBS01
1stKeyCol : COLUMN_1
Type S in Act field to select the partition.
Act
Part Dssize Keycount Cr FirstKey FullKey NLeaf Nlvls
-------------------------------------------------------------------------------
1 4194304 2036 100 2036 2036 5 2
2 4194304 0 0 0 0 0 2
3 4194304 0 0 0 0 0 2
4 4194304 0 0 0 0 0 2
******************************* Bottom of data ********************************Type S in the Act field beside the index partition name to select that index partition and press Enter.
The DB2 catalog statistics for the partition appear in the Index Partition Statistics Browse panel:
Index Partition Statistics Browse panel
ASUCIPPB --------------- Index Partition Statistics Browse-------------------
COMMAND ===>
Index Name: QZU.QZUX02_D09S01T01
Partition : 1
-------------------------------------------------------
| DB2 Catalog | BMCSTATS |
| FirstKeyCard : 0 | 0 |
| FullKeyCard : 0 | 0 |
| Nleaf . . . . : 0 | 0 |
| Nlevels . . . : 0 | 0 |
| ClusterRatio : 0 | 0 |
| DataRepeat : 0 | 0 |
| Keycount . . : 0 | 0 |
| yyyy-mm-dd-09.19 | yyyy-mm-dd-01.16 |
-------------------------------------------------------The product obtains the Db2 catalog statistics from the SYSIBM. SYSINDEXSTATS catalog table. The BMCSTATS statistics come from the corresponding statistics table in the DASD MANAGER PLUS database (value from the last run).
To display table space partition statistics
- Navigate to the Tablespace Statistics Browse panel.
Open the Tablespace Partition Selection panel using one of the following methods:
- In the Display Partition Statistics? field, type S, and press Enter.
- On the COMMAND line, type P, and press Enter.
If more than one partition exists in the table space, the Tablespace Partition Selection panel is displayed.
Tablespace Partition Selection panel
DEMO ---------------- TABLESPACE PARTITION SELECTION --------- Row 1 to 4 of 4
COMMAND ===> SCROLL ===> CSR
Tablespace Name: QZUDPBR2.QZUS01PB
Type S in Act field to select for more detail.
Act Pct Pct
Part Dssize Nactive Card Npages Pages Rowcomp
-------------------------------------------------------------------------------
1 4194304 68 2036 66 97 0
2 4194304 2 0 0 0 0
3 4194304 2 0 0 0 0
4 4194304 2 0 0 0 0
******************************* Bottom of data ********************************- Select the partition you need to scan for statistics.
To display table partition statistics
- Perform To display table space partition statistics.
To select a table from the list, type S in the Act field beside the table space partition name, and press Enter.
The Db2 catalog statistics for the partition appears in the Tablespace Partition Table Statistics Browse panel:
Tablespace Partition Table Statistics Browse panel
DEMO --Tablespace Partition Table Statistics Browse --
COMMAND ===>
Type data and press Enter.
Table Name . . : ZZU.ZZUT01_D11S01
Tablespace Name: ZZUD11.ZZUS0111
Partition . . : 4
------------------------------------------------------
| DB2 Catalog | BMCSTATS |
| Cardinality : 1402 | 1350 |
| Pages . . . : 86 | 85 |
| PctPages . . : 79 | 78 |
| Nactive : 108 | 108 |
| PctRowComp : 0 | 0 |
| yyyy-mm-dd-16.07 | yyyy-mm-dd-13.48 |
------------------------------------------------------
Select additional panels to display and press Enter.
Display Column statistics?The product obtains the Db2 catalog statistics from the SYSIBM.SYSTABSTATS catalog table. The BMCSTATS statistics come from the corresponding statistics table in the DASD MANAGER PLUS database (value from the last run).
To display column statistics
- Perform To display table partition statistics.
Open the Tablespace Partition Table Statistics Browse panel using one of the following methods:
- In the Display Column statistics? field, type S, and press Enter.
- Type C at the COMMAND line.
The Column Selection panel lists the columns in the specified table:
Column Selection panel
ASUCTCSE ------------------- Column Selection -------------- Row 1 to 13 of 21
COMMAND ===> SCROLL ===> PAGE
Type S and press Enter to select object.
Table Name : QZU.QZUT01_DA1S01
Act Column Name Card High2key Low2key
-------------------------------------------------------------------------------
COLUMN_1 2036 Ø.ó Ø.7
COLUMN_10 1952 Ø.?î Ø IÍ
COLUMN_11 1 Ø Ø
COLUMN_12 1 Ø Ø
COLUMN_13 1 .... ....
COLUMN_14 2 .. ..
COLUMN_15 2036 .... ... .... ..îType S in the Act field beside the column name and press Enter.
Catalog statistics for the column appear in the Column Statistics Browse panel:
Column Statistics Browse panel
DEMO ------------------------ Column Statistics Browse ------------------------
COMMAND ===>
Column Name: CITYSTATE
Table Name : ZZU.ZZUT12_D34S01
-------------------------------------------------------------
| DB2 Catalog | BMCSTATS |
| Cardinality : 91 | 91 |
| | |
| High2key . . : WEST ORA | WEST ORA |
| ECEE4DDC | ECEE4DDC |
| 65230691 | 65230691 |
| | |
| Low2key . . : ALLENTOW | ALLENTOW |
| CDDCDEDE | CDDCDEDE |
| 13355366 | 13355366 |
-------------------------------------------------------------The product obtains the Db2 catalog values from the SYSIBM.SYSCOLUMNS catalog table. The BMCSTATS values come from the corresponding statistics table in the DASD MANAGER PLUS database and are the values from the last run.
- (optional) Press END to return to the previous panel.