Skip to Content
Information
Limited support BMC provides limited support for this version of the product. As a result, BMC no longer accepts comments in this space. If you encounter problems with the product version or the space, contact BMC Support.BMC recommends upgrading to the latest version of the product. To see documentation for that version, see DASD MANAGER PLUS for DB2 13.1.

Index statistics

The Index Statistics panel displays the last, previous, and first statistics from the DASD MANAGER PLUS database for the specified index. You access this panel through the Table Statistics panel or through the Object List panel (by typing D in the Act field beside the object name).

This panel displays statistics entries generated by BMCSTATS as well as any included in the DASD MANAGER PLUS database by BMCCPRS.

(BMC.DB2.SPE2110) Index Statistics panel

DEJM -------------------- 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     |                            
 |             2021-07-12-04.35 |  2021-07-13-06.48 |                            
 ----------------------------------------------------                            
                                                                                
 Select additional panels to display and press Enter.                            
   Display Partition Statistics?    Display Keytargets?                          
Warning

Note

If BMCSTATS encounters negative data points, it will not display them in the cardinality statistics, cluster ratio, or pages graphs. Negative data points occur when BMCSTATS gathers selected partition statistics on a table space in which the Force rollup field is set to N (so the statistics are not rolled up for the entire table space).

To display index statistics

You can perform any of the following tasks:

  • In the Display Partition info? field, type S to display Index Partition Statistics (or type P at the COMMAND line) and press Enter. The Index Partition Selection panel appears.

   (BMC.DB2.SPE2110) Index Partition Selection panel 

DEJM ------------------- INDEX PARTITION SELECTION ----------- Row 1 to 4 of 4  
COMMAND ===>                                                  SCROLL ===> CSR   
                                                                                
Index Name. . : QZU.QZUX01_DEFS01T01                      DB Name   : QZUDEF    
Table Name. . : QZU.QZUT01_DEFS01                                               
1stKeyCol . . : PARTITION_PERIOD                   Last Run  : 2021-07-13-06.4  
Type S in Act field to select more detail for object.                           
                                                                                
Act Part  Nactive  Cluster    Free     Full     Space    Ext  Volume  Stogroup  
         /Dssize                                                                
-------------------------------------------------------------------------------
      1      16209  100          9    16201      4170      1  PLU050  QZUSGEA   
          8388608                                                               
                                                                                
      2      14291  100          6    14284      4170      1  PLU017  QZUSGEA   
          8388608                                                               
                                                                                
      3       5693  100          7     5687      4170      1  PLU084  QZUSGEA   
          8388608                                                               
                                                                                
      4       2880    0          3        0      4170      1  PLU030  QZUSGEA   
          8388608                                                               
                                                                                
******************************* Bottom of data ******************************** 


  • In the Graph Cardinality? field, type S to display a graph of the cardinality statistics (or type GC at the COMMAND line) and press Enter.
  • In the Graph Clusterratio? field, type S to display a graph of the cluster ratio statistics (or type GR at the COMMAND line) and press Enter.
  • In the Graph Pages? field, type S to display a graph of the page statistics (or type GP at the COMMAND line) and press Enter.
  • In the Display Entry List? field, type S to display the Index Statistics List (or type L at the COMMAND line) and press Enter.
  • In the Display Keytargets? field, type S to display the Keytargets List.

Tips for index statistics analysis

The following tips can help you analyze index statistics:

  • BMCSTATS reports the number of index levels and the percent clustered value and calculates the number of levels for a reorganized index. For very large indexes, this indicates that you can improve performance of SQL statements that use the index if you reorganize the index. Guidelines are difficult, but you should review this statistic when performance degrades to determine an appropriate threshold for your system. Both LevelMin and PctCluster are thresholds that can be monitored using BMCTRIG.
  • To see the relationship between Cardinality and Faroffpos and Nearoffpos, display the Cardinality Graph. If Faroffpos and Nearoffpos increase relatively to Cardinality, the table is becoming more disorganized. A reorganization should be considered.
  • To see the relationship between the number of allocated pages and the number of active pages over time, display the Pages Graph. The IF REORGED line indicates how many pages will be active if the index is reorganized. The number shown can be more or less than the current number of active pages. If the value is more than the number of allocated pages, a reorganization will cause allocation of secondary extents. If the value approaches the active value, you should consider allocating more space for new rows.
  • To analyze the clustering ratio of the index over time, display the Clusterratio Graph.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

DASD MANAGER PLUS for DB2 12.1