Limited supportBMC 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.

Collecting statistics

With DASD MANAGER PLUS, you can manage statistics that the IBM RUNSTATS utility or the BMC Software BMCSTATS utility collects. You can view and compare these statistics. You can also enter statistics from any other source that you choose.

IBM RUNSTATS utility

The RUNSTATS utility places statistical information in the DB2 catalog.

DB2 uses RUNSTATS values to determine the optimal access path to DB2 structures. BMCSTATS collects the same statistics as the RUNSTATS utility, in addition to other statistics. The additional information that BMCSTATS provides can help you plan for future system requirements that increasing production activity causes.

Note

By analyzing the statistics, you can determine the cases in which you require RUNSTATS. For more information, see the IBM DB2 for z/OS SQL Reference.

BMCSTATS utility

The BMCSTATS utility populates the DASD MANAGER PLUS database, which is in DB2 tables.

You can use the additional statistics that BMCSTATS collects to determine when to run maintenance utilities on the physical objects. For example, BMCSTATS calculates the REORGNLEVELS (the number of index levels necessary if you reorganize the object). By comparing REORGNLEVELS with the current levels, you can determine whether reorganizing the index space will reduce the number of levels that the index requires.

The BMCSTATS utility also provides the PAGEGROUP facility, which allows you to review the statistics on a specified grouping of pages to uncover additional information about hot spots in the data. When you generate control statements for the BMCSTATS utility, you determine the number of pages to group. This facility and the graphic displays can help you locate areas of concentrated activity within a table space. You can display and compare statistics values for the first, last, and next-to-last time that you ran BMCSTATS.

The following figure shows how the BMCSTATS utility gathers statistics:

gathering_statistics_BMCSTATS.png 

DASD MANAGER PLUS historical database

The following table shows the information that DASD MANAGER PLUS records in the statistics tables of its historical database. BMCSTATS stores this information, and BMCTRIG scans it.

For complete information about the DASD MANAGER PLUS historical database, see BMCCPRS-options.

DASD MANAGER PLUS historical database

Table name

Contents

BMCATS nn.RS_COLDIST

Distribution statistics on columns

BMCATS nn.RS_COLDISTSTAT

Distribution statistics on columns at the partition level

BMCATS nn.RS_COLSTATS

Statistics on each column in a partition

BMCATS nn.RS_COLUMNS

Table columns

BMCATS nn.RS_INDEXES

Indexes

BMCATS nn.RS_INDEXPART

Index partitions

BMCATS nn.RS_IXPART_DIST

Index partition page group

BMCATS nn.RS_KEYTARGETS

Key targets

BMCATS nn.RS_KEYTARGETSTATS

Key targets partitions

BMCATSnn.RS_KEYTGTDIST

Distribution statistics on key target columns

BMCATS nn.RS_KEYTGTDISTSTATS

Distribution statistics on key target columns at the partition level

BMCATS nn.RS_LOBSTATS

LOB statistics

BMCATS nn.RS_STOGROUP

Storage groups

BMCATS nn.RS_TABLEPART

Table space partitions

BMCATS nn.RS_TABLES

Tables

BMCATS nn.RS_TABLESPACE

Table space statistics

BMCATS nn.RS_TSPART_DIST

Table space partition page group

BMCATS nn.RS_VOLUMES

Volumes

BMCSTATS options for collecting statistics (PTFs BQU2647, BQU2648, and BQU2861 applied)

When you collect statistics, you choose parameters on the first BMCSTATS panel. As the following sample shows, this panel lists the parameters by category.

DEBA               BMCSTATS TABLESPACE XXXXXX.XXXXXX          Row 1 to 37 of 58
Command ===>                                                  Scroll ===> CSR

Service Syntax: BMCSTATS


Type Service Syntax options. Then press End.                       More: +    

--------------------------What to Collect ------------------------------------
TABLE  . . . . . . * N      (Y/N/S Y=ALL Tables, N=No Tables, S=Select Tables)
INDEX  . . . . . . Y        (Y/N Collect column statistics on all indexes)     
SPACEONLY  . . . . N        (Y/N Collect space information only)               

-------------------------- Index Space Options --------------------------------
Distribution Stats:                                                           
  NUMCOLS . . . . 1        (1-64 Max index key columns to concatenate)        
Histogram Stats:                                                               
  IXNUMQUANTILES            (1-100 Number of quantiles to collect)             
  NUMQCOLS . . . .          (1-64 Number of columns for quantiles)             

-------------------------- Table Space Options --------------------------------
FREQVAL  . . . . .          (Y/N Collect frequent value statistics)            

-------------------------- Table and Index Space Options ----------------------
COUNT  . . . . . . 10       (1-300 Max number of frequent values to collect)   
FREQTYPE . . . . . M        (M/L/B M=Most L=Least B=Both)                      


-------------------------- BMC Stats Reporting and Update Options -------------
SAVESTATS  . . . . Y        (Y/N Save statistics in STATS DB)                  
DELETEAGE  . . . . 32767    (0-32767 Days for deleting old STATS DB entries)   
REPORT . . . . . . Y        (Y/N Print statistics report)                      


-------------------------- DB2 Catalog Update Options -------------------------
UPDATEDB2  . . . . N        (N/A/P/S N=None, A=All, P=Accesspath, S=Space)     
HISTORY  . . . . . N        (N/A/P/S N=None, A=All, P=Accesspath, S=Space)     
  DELETEHISTAGE    32767    (0-32767 Days for deleting history table entries)  
RESET ACCESSPATH   N        (N/Y Reset accesspath statistics for all tables)   
   RESETHISTORY . . N       (N/Y Inserts history rows for which access
                             statistics are reset)   
INVALIDATECACHE             (Y/N Invalidate Dynamic Statement Cache)                   
OMITCARD0  . . . . N        (Y/N - Bypass catalog update if cardinality is 0)  

Type Service Syntax options. Then press End.                        More:   -  


-------------------------- Stats Processing Options ---------------------------
BADOBJECTRC  . . . 4       (0-8 Return code when object is bypassed)          
RECALL . . . . . . N       (Y/N Recall archived data datasets)                
FORCEROLLUP  . . . N       (Y/N Produce aggregate stats when missing parts)   
MSGLEVEL . . . . . 0       (0/1 0-Normal msgs, 1-Additional msgs)             
911ACTION  . . . . I       (I/S Ignore or Stop at -911 SQL errors)            

--------------------------stats Tuning Options -------------------------------
TASKS  . . . . . . 5       (1-16 Multitasking level)                          
TSSAMPLEPCT  . . . N       (Y/N/1-50 Random sampling tablespace statistics)   
IXSAMPLEPCT  . . . N       (Y/N/1-50 Random sampling for index statistics)    
OPTIMIZECOMMIT . .         (Y/N Reduce DELETE, INSERT, UPDATE commits)        
QUIESCEINTERVAL            (0/10000-1000000 Partitions processed before
                           storage reorganization)                            

ZIIP . . . . . . .         (E/D/B E=Enable, D=Disable zIIP offloading,
                           B=Enable ZIIP using BMCSTATS enclave)          
NPICACHEACTION . .         (N/A/L N=None, A=ATBCACHE, L=Limit Key Caching)    
NPICACHESTHRESH            (1-500 MEGS before using NPICACHEACTION)           
NPICACHEDSNUM  . .         (2-4096 NumDS before using NPICACHEACTION)         

--------------------------Sort Options ---------------------------------------


SORTNUM  . . . . .         (0-255 Number of SORTWKs for Histogram Stats)      
SORTDEVT . . . . .         (Device type for sort data sets for Histogram Stats)                                             

******************************* Bottom of data ********************************

Note

Because collecting column statistics can be expensive in terms of CPU processing time, carefully consider what value you plan to set for each of the following fields: TABLE, NUMCOLS, and COUNT.

  • TABLE specifies whether to gather statistics on table columns:
    • Y—Collects statistics on all table columns (see BMCSTATS options for tables).
    • N—Does not collect statistics on table columns.

    • S—Collects statistics on specific tables and columns.

      You can also select column groups for collection.

      Tip

      If you specify TABLES, review the values on the Select Tables panel the next time that you specify this action. You might need to change the values from an earlier execution.

  • INDEX indicates whether to run BMCSTATS on the indexes in the table space. If you specify Indexall Y when you are collecting statistics from an image copy, DASD MANAGER PLUS uses the DB2 data sets to obtain index statistics. The default is Y.
  • SPACEONLY specifies whether to gather only space statistics from the ICF catalog. If you need information only about size and extents, specify Y for the SpaceOnly option. The default is N.

  • NUMCOLS is the number of key columns from left to right that is concatenated to evaluate the value (1 through 64) for each frequent value that the product collects. The default value of 1 collects frequent values for the first key column only.

    BMCSTATS returns all combinations up to the NUMCOLS value. For example, if you specify NUMCOLS 5, BMCSTATS returns the following values: column 1; columns 1 and 2; columns 1, 2, and 3; columns 1, 2, 3, and 4; and columns 1, 2, 3, 4, and 5. For this information to be useful to the optimizer, you also must specify UPDATEDB2 Y.

  • IXNUMQUANTILES specifies the number of quantiles to collect for index key column processing. Statistics for the specified set of columns are divided into groups based on the number of quantiles specified. Index columns must be in all ascending or all descending order for DASD MANAGER PLUS to collect this type of statistic. Otherwise, DASD MANAGER PLUS ignores this option.

    Note

    BMCSTATS invokes DSNUTILB to collect histogram data for key columns only if you specified UPDATEDB2 A or UPDATEDB2 P on the BMCSTATS parameters panel (see the above figure). Specifying IXNUMQUANTILES invokes DSNUTILB.

  • NUMQCOLS specifies the number of index columns on which to collect quantiles.
  • FREQVAL indicates whether BMCSTATS should collect frequency statistics for columns when using the TABLE option. If you do not specify a value for this option, the value specified by the FREQVAL keyword in the installation options is used. The product is shipped with an installation default of Y.
  • COUNT is the maximum number of frequent values to collect (for table columns, first key columns, concatenated table columns, and concatenated key columns). Type a value from 1 through 300. The default is 10.

  • FREQTYPE specifies whether to collect the most (M) frequently occurring values in a column, the least (L) frequently occurring values in a column, or both (B) types of values. The default is M. The value that you specify for this field also applies to COLGROUP for column group statistics and to Keycard for concatenated key column statistics.

    Note

    Specifying a value of L or B in this field can be expensive in terms of CPU processing time and should be considered before choosing these values.

  • SAVESTATS specifies whether to save the statistics in the DASD MANAGER PLUS database. The default is Y.

    If you specify SAVESTATS N (to not save BMCSTATS statistics data) and you also specify DELETEAGE to delete statistics rows that are older than a number of days that you specify, DASD MANAGER PLUS does not delete the rows. The SAVESTATS command controls the statistics table handling routines. If you specify SAVESTATS N, no statistics table processing occurs.

  • DELETEAGE specifies whether to automatically delete statistics for an object after a certain amount of time. Specify the number of days from 0 through 32767 to keep statistics.

    The number that you specify is the minimum age of object statistics. For example, to delete all statistics entries on this object that are at least 30 days old, enter 30 for this parameter. The default is 32767, which specifies not to delete any statistics.

    Note

    This command has no affect if you specify SAVESTATS N.

  • REPORT specifies whether to print a report into the job output of the statistics. The default is Y.
  • UPDATEDB2 specifies whether to update the DB2 catalog with the statistics that you gather. If the existing DB2 catalog values provide efficient optimizer choices, type N. The product ignores this field for volumes and storage groups. Valid values are A (All), P (Accesspath), S (Space), or N (None). The default is N.
  • HISTORY specifies whether to update the DB2 Catalog History tables.
  • DELETEHISTAGE specifies how long to keep the DB2 Catalog History table entries before deleting them.
  • RESET ACCESSPATH resets access path statistics in the DB2 Catalog to -1 for all tables in the specified table space and related indexes. Real-time statistics and space statistics in the DB2 Catalog are not reset.
  • RESETHISTORY inserts rows into the following tables, for the objects specified in the RESET ACCESSPATH command:
    • SYSIBM.SYSTABLES_HIST for tables
    • SYSIBM.SYSINDEXES_HIST for indexes

  • (PTFs BQU2647, BQU2648, and BQU2861 applied) INVALIDATECACHE specifies whether to invalidate the dynamic statement cache for an object. 
    The default is YES when you have specified RESET ACCESSPATH or UPDATEDB2 NO REPORT NO SAVESTATS NO with the following exceptions:  

    • For RESET ACCESSPATH, the default is YES.
      When you specify RESET ACCESSPATH, the INVALIDATECACHE NO is not supported.
    • For UPDATEDB2 NO REPORT NO SAVESTATS NO, the default is YES. 
      When you specify UPDATEDB2 NO REPORT NO SAVESTATS NO, the INVALIDATECACHE NO is not supported.

    The values defined are as follows:

    Option

    Meaning and use

    YES

    Invalidate the dynamic statement cache. Y is the default.

    NO

    Do not invalidate the dynamic statement cache.

  • OMITCARD0 specifies whether to bypass updating the DB2 catalog for objects in which BMCSTATS finds a zero cardinality. The default is N.
  • BADOBJECTRC specifies the return code that BMCSTATS will set if it is unable to process a requested object due to object characteristics (unsupported objects), invalid status, object serialization, or object authorization failures. The default is to issue return code 4. Regardless of the setting of this option, BMCSTATS processing continues.

  • RECALL specifies whether to recall archived data sets to collect statistics on them.

    The RECALL option opens the data sets that initiate a recall. N skips the object and returns a code 4, but continues with other objects. The default is N.

  • FORCEROLLUP specifies whether to roll up the partition level statistics to the object level in cases in which not all partition statistics are available. The default is N.
  • MSGLEVEL specifies the level of messaging. The default of 0 provides standard messaging. Specifying messaging level 1 provides some additional informative messages about progress and timestamps.
  • 911ACTION specifies the action BMCSTATS will take if a -911 SQL error occurs during a DELETE, INSERT, or UPDATE operation.
    • S specifies if an SQL -911 error occurs, stop all processing, issue error messages, and issue a return code 8 at termination.
    • I specifies if an SQL -911 error occurs, stop processing the current object, issue warning messages, continue on with the next available object, and issue a return code 4 at termination. I is the default.
  • TASKS is the number of concurrent tasks for gathering statistics. Specify the number of multitasking levels that are used for processing partitioned objects by typing a value from 1 through 16. The default is 5.

  • TSSAMPLEPCT specifies whether to use random sampling for statistics.

    Sampling greatly reduces resource consumption in producing statistics. If you need to use sampling, consider specifying a percentage of pages to sample.

    The following table defines the sample table space parameter options.

    BMCSTATS sample table space parameter options

    Option

    Meaning and use

    N

    Do not sample. Type N to process all pages, including very small tables on a multi-table table space that contains both large and small tables. N is the default.

    Y

    Sample 25 percent of the pages. If you type Y, BMCSTATS does not sample objects with fewer than 1000 pages.1

    1 through 50

    Sample the specified percentage of pages for statistics.1

    1The numPages/numTables must be greater than the minimum pages listed, or BMCSTATS will process all pages.

    For more information about sampling, see Sampling-statistics.

  • IXSAMPLEPCT specifies whether to use random sampling for statistics. Sampling greatly reduces resource consumption in producing statistics. If you need to use sampling, you should consider specifying a percentage of pages to sample.

    While sampling indexes reduces resource consumption, restrictions affect when you can specify this option. In the following instances, DASD MANAGER PLUS suppresses index sampling and processes the entire index:

    • Specifying an index sampling option in addition to UPDATEDB2 Y

      To eliminate this restriction, you can specify the UPDCATIXS=Y installation option during installation to allow the catalog to be updated with index sampled statistics.

      Warning

      Index sampling might produce statistics that cause SQL optimizer access selection problems. BMC recommends that you review the index sampled statistics for your indexes before deciding to use them for updating the catalog.

    • Specifying an index sampling option and an option to collect histogram statistics for indexes

      When collecting histogram statistics, BMCSTATS invokes DSNUTILB (which also does not support index sampling).

    • Index has less than 1000 pages

      The following table defines sample index parameter options.

      BMCSTATS sample index parameter options

      Option

      Meaning and use

      N

      Do not sample. Type N to process all pages, including very small indexes. N is the default.

      Y

      Sample 25 percent of the pages. If you type Y, BMCSTATS does not sample objects with fewer than 1000 pages.

      1–50

      Sample the specified percentage of pages for statistics. BMCSTATS does not sample objects with fewer than 1000 pages.

      For more information about sampling, see Sampling-statistics.

  • ATBWORKAREA specifies if the cardinality and frequency work areas will be placed above or below the 2G bar.

    • Y specifies work areas will be placed above the 2G bar.
    • N specifies work areas will be placed below the 2G bar.

    Above the 2G bar can reduce normal 32 bit addressed storage requirements. Below the bar reduces CPU consumption due to additional dynamic address translation overhead. N is the default.

  • OPTIMIZECOMMIT specifies how SQL COMMIT(s) after SQL DELETE, INSERT, and UPDATE will occur.

    • Y specifies COMMIT strategy for DELETE, INSERT, and UPDATE will be optimized.
    • N specifies COMMIT(s) will occur after every DELETE, INSERT, and UPDATE.

    Optimized COMMIT(s) reduce overall SQL overhead. Unoptimized COMMIT(s) reduce deadlocks and deadlock timeouts (SQL error -911). Y is the default.

  • QUIESCEINTERVAL (100001000000) specifies the number of object partitions processed before releasing and reinitializing O/S storage pools.

    • 0 specifies no QUIESCEINTERVAL.
    • 0–1000000 specifies the number of partitions processed before storage pools will be reorganized.

    QUIESCEINTERVAL can reduce the risk of storage depletion errors due to storage fragmentation. Storage fragmentation can occur after a large number of object partitions are processed. Use QUIESCEINTERVAL with large object sets and wildcards if storage depletion errors occur (Sx78 ABENDs).

  • SORTNUM specifies the number of temporary data sets that DFSORT can use for sorting and is used only with COLGROUP. Specify this field with the SORTDEVT field. You can specify an integer value of 0 through 99.
  • SORTDEVT specifies the device type for dynamic allocation of the sort work files that DFSORT can use for sorting and is only used with COLGROUP. Specify this field with the SORTNUM field.

BMCSTATS options for tables

When you specify TABLE Y on the BMCSTATS parameters panel, the BMCSTATS Select Tables panel is displayed:

BMCSTATS Select Tables panel

------------------------------- SELECT TABLES ----------- Row 1 to 33 of 1,121
COMMAND ===>                                                  Scroll ===>     
                                                                              
Enter S to Select and Edit, * to Select without Edit, blank to Unselect        
Enter Z to Zoom full table name                                                
                                                                              
Cmd   Database Tablespace   Owner      Table                                   
------------------------------------------------------------------------------
 S   QZUD10   QZUS0110     QZU        QZUT01_D10S01                           
     QZUD10   QZUS0210     QZU        QZUT01_D10S02                           
     QZUD11   QZUS0111     QZU        QZUT01_D11S01                           
     QZUD11   QZUS0211     QZU        QZUT01_D11S02                           
     QZUD11   QZUS0311     QZU        QZUT01_D11S03                           
     QZUD12   QZUS0112     QZU        QZUT01_D12S01

From the Select Tables panel, you can specify the tables that you want to include in the column statistics gathering, as follows:

  • To collect statistics for some but not all columns in a table, type S in the Cmd field for the table. The Options for Table tableName is displayed in the following figure.
  • To collect statistics for all columns, type an asterisk (*) in the Cmd field for the table.
  • To unselect a table, blank out any character in the Cmd field.

BMCSTATS Options for Table tableName panel

DEAE           Options for Table TABLE QZU.QZUT01_D10S01      Row 1 to 33 of 33
Command ===>                                                  Scroll ===> CSR  
                                                                              
Service Syntax: BMCSTATS.DEMO                                                  
                                                                              
Enter data, then press end.                                         More:      
                                                                              
-------------------------- What to Collect ------------------------------------
COLUMN . . . . . . * Y      (Y/S Y=ALL Columns, S=Select Columns)              
                                                                              
-------------------------- Column ColGroup Options ----------------------------
To generate column lists or column groups, set Table all to 'S'                
To collect histogram statistics, use UpdateDB2 A or P                          
Select COLGROUP 1  * N      (N/Y Choose columns for distribution stats)        
  COLGROUPFREQVAL           (Y/N Collect frequent value statistics)            
  COLGROUPFREQTYPE          (M/L/B M=Most L=Least B=Both)                      
  COLGROUPCOUNT             (1-300 Number of frequencies to collect)           
  NUMQUANTILES . .          (1-100 Number of quantiles to collect)             
Select COLGROUP 2  * N      (N/Y Choose columns for distribution stats)        
  COLGROUPFREQVAL           (Y/N Collect frequent value statistics)            
  COLGROUPFREQTYPE          (M/L/B M=Most L=Least B=Both)                      
  COLGROUPCOUNT             (1-300 Number of frequencies to collect)           
  NUMQUANTILES . .          (1-100 Number of quantiles to collect)             
Select COLGROUP 3  * N      (N/Y Choose columns for distribution stats)        
  COLGROUPFREQVAL           (Y/N Collect frequent value statistics)            
  COLGROUPFREQTYPE          (M/L/B M=Most L=Least B=Both)                      
  COLGROUPCOUNT             (1-300 Number of frequencies to collect)           
  NUMQUANTILES . .          (1-100 Number of quantiles to collect)             
Select COLGROUP 4  * N      (N/Y Choose columns for distribution stats)        
  COLGROUPFREQVAL           (Y/N Collect frequent value statistics)            
  COLGROUPFREQTYPE          (M/L/B M=Most L=Least B=Both)                      
  COLGROUPCOUNT             (1-300 Number of frequencies to collect)           
  NUMQUANTILES . .          (1-100 Number of quantiles to collect)             
Select COLGROUP 5  * N      (N/Y Choose columns for distribution stats)        
  COLGROUPFREQVAL           (Y/N Collect frequent value statistics)            
  COLGROUPFREQTYPE          (M/L/B M=Most L=Least B=Both)                      
  COLGROUPCOUNT             (1-300 Number of frequencies to collect)           
  NUMQUANTILES . .          (1-100 Number of quantiles to collect)             
******************************* Bottom of data *******************************

The fields on the Options for Table tableName panel are as follows:

  • COLUMN specifies whether to collect statistics on all columns or on specific columns.

  • Select COLGROUP n instructs BMCSTATS to calculate correlated column cardinality and frequency statistics for the specified set of columns. If you need more than five COLGROUP sets for a table, you can manually add them to the syntax.

    Note

    Note the following considerations:

    • BMCSTATS invokes DSNUTILB to collect column histogram statistics only if you specified UPDATEDB2 A or UPDATEDB2 P on the BMCSTATS parameters panel. Otherwise, this option is ignored.
    • Frequency values are collected as a standard part of regular column statistics and also for first key columns when index statistics are collected. For more information about changing the collection of frequency values, see the FREQVAL and FREQTYPE fields listed in the BMCSTATS Select Tables panel. Alternatively, you can specify COLGROUPCOUNT to specify the number of frequent values to collect for the preceding COLGROUP.
  • COLGROUPFREQVAL indicates whether BMCSTATS should collect frequency statistics for a group of columns. If you do not specify a value for this option, the value specified by the FREQVAL on the BMCSTATS panel (see the figure, BMCSTATS Select Tables panel) is used for this COLGROUP.
  • COLGROUPFREQTYPE specifies whether to collect the most (M) frequently occurring values, the least (L) frequently occurring values, or both (B) types of values for the group of columns. If no value is specified, the default is the value you specified for FREQTYPE on the BMCSTATS panel (see the figure, BMCSTATS Select Tables panel). If no value is specified in either location, the default is M. The value that you specify for this field applies to COLGROUP for column group statistics.
  • COLGROUPCOUNT specifies the number of frequencies to collect for the preceding group of columns. If no value is specified, the default is the value you specified for COUNT on the BMCSTATS panel. If no value is specified in either location, the default is 10.

  • NUMQUANTILES specifies the number of quantiles to collect and can be specified when collecting column group statistics. The values are divided into the number of quantiles you specify. If you specify 0, BMCSTATS does not collect histogram statistics.

    If you type S in the Select Columns or Select COLGROUP field, the following figure appears:

    BMCSTATS Select Columns panel

    ------------------------------- SELECT COLUMNS -------------- Row 1 to 21 of 21
    COMMAND ===>                                                  Scroll ===> PAGE
                                                                                  
    Enter S to Select and Edit, * to Select without Edit, blank to Unselect        
                                                                                  
    TABLE: QZU.QZUT01_D10S01                                                      
    Sel  Column Name                    Type      Seq Len Scl Kyseq Nulls Def FProc
    ----v----1----v----2----v----3----v----4----v----5----v----6----v----7----v---
         COLUMN_1                       INTEGER     1   4   0     0   N    Y    N
         COLUMN_10                      INTEGER    10   4   0     0   N    Y    N
         COLUMN_11                      FLOAT      11   4   0     0   N    Y    N
         COLUMN_12                      FLOAT      12   8   0     0   N    Y    N
         COLUMN_13                      DATE       13   4   0     0   N    Y    N
         COLUMN_14                      TIME       14   3   0     0   N    Y    N
         COLUMN_15                      TIMESTMP   15  10   0     1   N    Y    N
         COLUMN_16                      VARCHAR    16  30   0     0   N    Y    N

You can select up to a maximum of 80 column names on which to gather statistics, as follows:

  • To select a column, type S in the Sel field for the column.
  • To unselect a column, blank out any character in the Sel field.

BMCSTATS performance and tuning

The following topics discuss offloading workloads to a zIIP processor and performing non-partition Indexes processing.

zIIP offloading

DASD MANAGER PLUS uses BMCSTATS to offload eligible workloads to an IBM System z Integrated Information Processor (zIIP) on IBM z9 and z10 systems. Offloading workloads provides the following benefits:

  • Frees general computing capacity
  • Reduces the IBM monthly licensing charges
  • Reduces your mainframe's total cost of ownership (TCO)

You can offload the following types of workload:

  • All DB2 page file I/O processing
  • Table space data analysis
  • Index space data analysis

Offloading zIIP-eligible processing only applies to stand alone BMCSTATS jobs. zIIP offload does not apply to BMC Utilities executing BMCSTATS inline. If no zIIP processor is available, BMCSTATS ignores the zIIP request and executes the workload on the general processor.

There are two different ways to set the zIIP processing options. To establish a global or default zIIP processing option, use the ZIIP keyword in the default options module (DOPTs). When the ZIIP option is set globally, it may be overridden for an individual BMCSTATS job via syntax. For example, in the default options ZIIP=E enables zIIP-eligible offload using the XBM enclave for all BMCSTATS jobs. If you want to disable zIIP offload for an individual job, specify ZIIP D in the BMCSTATS syntax. The ZIIP D in the BMCSTATS syntax overrides ZIIP= E in the default options module (DOPTs).

DASD MANAGER PLUS offers the use of two different enclaves for zIIP processing:

  • XBM
  • BMCSTATS 

To use the XBM enclave, you must have installed version 5.6 or later of the EXTENDED BUFFER MANAGER (XBM) product (with PTF BPE0313) or SNAPSHOT UPGRADE FEATURE for DB2 (SUF) technology. To use the XBM enclave, use ZIIP=E in the default options module (DOPTs) or ZIIP E in the BMCSTATS syntax.

To use the BMC enclave, use ZIIP=B in the default options module (DOPTS) or ZIIP B in the BMCSTATS syntax.

To disable automatic zIIP offloading, set the zIIP option to ZIIP=D in the default options module (DOPTS) or ZIIP D in the BMCSTATS syntax.

The primary difference between the two options is the level of dispatch priority assigned to the enclaves. The XBM enclave typically runs or is assigned a higher dispatch priority than typical batch processing receives. The BMCSTATS enclave is typically assigned a standard batch dispatch priority. You can specify ZIIP= E in the default options module for all BMCSTATS jobs and ZIIP B in individual BMCSTATS jobs that need to execute at a lower priority.

If the ZIIP option is not specified in the default option module (DOPTs) or in the BMCSTATS syntax, BMCSTATS will attempt to use the XBM enclave. If either the XBM subsystem or a zIIP process is not available the workload is distributed to the general processor.


Non Partition Indexes (NPIs) processing

BMCSTATS uses a key caching algorithm when it processes indexes. Large multi-datasets (MUDS) Non Partition Indexes (NPIs) can consume large amounts of memory during the caching process. BMCSTATS provides storage relief during key column statistics collection for NPIs. You can use the following three BMCSTATS keywords to tune high storage-consuming objects:

Keyword

Used to:

Parameters

NPICACHEACTION:

Activate or deactivate the key caching processing

<NONE||LIMIT||ATBCACHE>

  • NONE— No storage limit is enforced for multi-dataset non-partitioned index key caching. The values that NPICACHESTHRESH and NPICACHEDSNUM specify are ignored.
  • LIMIT—For multi-dataset non-partitioned indexes, cache storage below the bar is limited to the value that NPICACHESTHRESH sets. When the storage limit is exceeded, key caching stops.
  • ATBCACHE— For multi-dataset non-partitioned indexes, cache storage below the bar is limited to the value that NPICACHESTHRESH sets. When the storage limit is exceeded:
    • The key cache is moved above the 2GB bar.
    • Caching continues.
    • Cache storage held below the bar is released.

NPICACHESTHRESH

Specify the amount of storage in megabytes that can be used below the 2GB bar before the action that NPICACHEACTION specifies takes place.

<1–500>100

NPICACHEDSNUM

Specify the number of datasets required in a multi-dataset non-partitioned index before the values that NPICACHEACTION and NPICACHESTHRESH specify are be observed.

<2–4096> 25

Note

If NPICACHEACTION=NONE, NPICACHEDSNUM and NPICACHESTHRESH are ignored.

To set these keywords globally for all BMCSTATS NPI processing, edit the Default Options module (DOPTs). To override the default settings for processing selected jobs, use the BMCSTATS syntax options.

BMCSTATS options for collecting statistics (PTFs BQU2647, BQU2648, and BQU2861 applied)

When you collect statistics, you choose parameters on the first BMCSTATS panel. As the following sample shows, this panel lists the parameters by category.

DEAE                           BMCSTATS                      Row 1 to 26 of 55
Command ===>                                                 Scroll ===> CSR
                                                                             
Service Syntax: BMCSTATS.I431937S                                             
                                                                             
Type Service Syntax options. Then press End.                        More: +   
                                                                             
-------------------------- What to Collect -----------------------------------
TABLE  . . . . . . * N      (Y/N/S Y=ALL Tables, N=No Tables, S=Select Tables)
INDEX  . . . . . . Y        (Y/N Collect column statistics on all indexes)    
SPACEONLY  . . . . N        (Y/N Collect space information only)              
                                                                             
-------------------------- Index Space Options -------------------------------
Distribution Stats:                                                           
  NUMCOLS  . . . . 1        (1-64 Max index key columns to concatenate)       
Histogram Stats:                                                              
  IXNUMQUANTILES            (1-100 Number of quantiles to collect)            
  NUMQCOLS . . . .          (1-64 Number of columns for quantiles)            
                                                                             
-------------------------- Table Space Options -------------------------------
FREQVAL  . . . . .          (Y/N Collect frequent value statistics)           
                                                                             
-------------------------- Table and Index Space Options ---------------------
COUNT  . . . . . . 10       (1-300 Max number of frequent values to collect)  
FREQTYPE . . . . . M        (M/L/B M=Most L=Least B=Both)                     
                                                                             
-------------------------- BMC Stats Reporting and Update Options ------------
SAVESTATS  . . . . Y        (Y/N Save statistics in STATS DB)                 
DELETEAGE  . . . . 32767    (0-32767 Days for deleting old STATS DB entries)  
REPORT . . . . . . Y        (Y/N Print statistics report)                     
                                                                             
-------------------------- DB2 Catalog Update Options ------------------------
UPDATEDB2  . . . . N        (N/A/P/S N=None, A=All, P=Accesspath, S=Space)    
HISTORY  . . . . . N        (N/A/P/S N=None, A=All, P=Accesspath, S=Space)    
  DELETEHISTAGE    32767    (0-32767 Days for deleting history table entries)
RESET ACCESSPATH   N        (N/Y Reset accesspath statistics for all tables)  
  RESETHISTORY . . N        (N/Y Inserts history rows for which access
                             statistics are reset)
INVALIDATECACHE             (Y/N Invalidate Dynamic Statement Cache)                         
OMITCARD0  . . . . N        (Y/N - Bypass catalog update if cardinality is 0)
                                                                             
-------------------------- Stats Processing Options --------------------------
BADOBJECTRC  . . . 4        (0-8 Return code when object is bypassed)         
RECALL . . . . . . N        (Y/N Recall archived data datasets)               
FORCEROLLUP  . . . N        (Y/N Produce aggregate stats when missing parts)  
MSGLEVEL . . . . . 0        (0/1 0-Normal msgs, 1-Additional msgs)            
911ACTION  . . . . I        (I/S Ignore or Stop at -911 SQL errors)           
-------------------------- Stats Tuning Options ------------------------------
TASKS  . . . . . . 5        (1-16 Multitasking level)                         
TSSAMPLEPCT  . . . N        (Y/N/1-50 Random sampling tablespace statistics)  
IXSAMPLEPCT  . . . N        (Y/N/1-50 Random sampling for index statistics)   
ATBWORKAREA  . . .          (Y/N Freq/card work areas above the 2G bar)       
OPTIMIZECOMMIT . .          (Y/N Reduce DELETE, INSERT, UPDATE commits)       
QUIESCEINTERVAL             (0/10000-1000000 Partitions processed before      
                            storage reorganization)                           
                                                                             
-------------------------- Sort Options --------------------------------------
SORTNUM  . . . . .          (0-255 Number of SORTWKs for Histogram Stats)     
SORTDEVT . . . . .          (Device type for sort data sets for Histogram     
                            Stats)

Note

Because collecting column statistics can be expensive in terms of CPU processing time, carefully consider what value you plan to set for each of the following fields: TABLE, NUMCOLS, and COUNT.

  • TABLE specifies whether to gather statistics on table columns:
    • Y—Collects statistics on all table columns (see BMCSTATS options for tables).
    • N—Does not collect statistics on table columns.

    • S—Collects statistics on specific tables and columns.

      You can also select column groups for collection.

      Tip

      If you specify TABLES, review the values on the Select Tables panel the next time that you specify this action. You might need to change the values from an earlier execution.

  • INDEX indicates whether to run BMCSTATS on the indexes in the table space. If you specify Indexall Y when you are collecting statistics from an image copy, DASD MANAGER PLUS uses the DB2 data sets to obtain index statistics. The default is Y.
  • SPACEONLY specifies whether to gather only space statistics from the ICF catalog. If you need information only about size and extents, specify Y for the SpaceOnly option. The default is N.

  • NUMCOLS is the number of key columns from left to right that is concatenated to evaluate the value (1 through 64) for each frequent value that the product collects. The default value of 1 collects frequent values for the first key column only.

    BMCSTATS returns all combinations up to the NUMCOLS value. For example, if you specify NUMCOLS 5, BMCSTATS returns the following values: column 1; columns 1 and 2; columns 1, 2, and 3; columns 1, 2, 3, and 4; and columns 1, 2, 3, 4, and 5. For this information to be useful to the optimizer, you also must specify UPDATEDB2 Y.

  • IXNUMQUANTILES specifies the number of quantiles to collect for index key column processing. Statistics for the specified set of columns are divided into groups based on the number of quantiles specified. Index columns must be in all ascending or all descending order for DASD MANAGER PLUS to collect this type of statistic. Otherwise, DASD MANAGER PLUS ignores this option.

    Note

    BMCSTATS invokes DSNUTILB to collect histogram data for key columns only if you specified UPDATEDB2 A or UPDATEDB2 P on the BMCSTATS parameters panel (see the above figure). Specifying IXNUMQUANTILES invokes DSNUTILB.

  • NUMQCOLS specifies the number of index columns on which to collect quantiles.
  • FREQVAL indicates whether BMCSTATS should collect frequency statistics for columns when using the TABLE option. If you do not specify a value for this option, the value specified by the FREQVAL keyword in the installation options is used. The product is shipped with an installation default of Y.
  • COUNT is the maximum number of frequent values to collect (for table columns, first key columns, concatenated table columns, and concatenated key columns). Type a value from 1 through 300. The default is 10.

  • FREQTYPE specifies whether to collect the most (M) frequently occurring values in a column, the least (L) frequently occurring values in a column, or both (B) types of values. The default is M. The value that you specify for this field also applies to COLGROUP for column group statistics and to Keycard for concatenated key column statistics.

    Note

    Specifying a value of L or B in this field can be expensive in terms of CPU processing time and should be considered before choosing these values.

  • SAVESTATS specifies whether to save the statistics in the DASD MANAGER PLUS database. The default is Y.

    If you specify SAVESTATS N (to not save BMCSTATS statistics data) and you also specify DELETEAGE to delete statistics rows that are older than a number of days that you specify, DASD MANAGER PLUS does not delete the rows. The SAVESTATS command controls the statistics table handling routines. If you specify SAVESTATS N, no statistics table processing occurs.

  • DELETEAGE specifies whether to automatically delete statistics for an object after a certain amount of time. Specify the number of days from 0 through 32767 to keep statistics.

    The number that you specify is the minimum age of object statistics. For example, to delete all statistics entries on this object that are at least 30 days old, enter 30 for this parameter. The default is 32767, which specifies not to delete any statistics.

    Note

    This command has no affect if you specify SAVESTATS N.

  • REPORT specifies whether to print a report into the job output of the statistics. The default is Y.
  • UPDATEDB2 specifies whether to update the DB2 catalog with the statistics that you gather. If the existing DB2 catalog values provide efficient optimizer choices, type N. The product ignores this field for volumes and storage groups. Valid values are A (All), P (Accesspath), S (Space), or N (None). The default is N.
  • HISTORY specifies whether to update the DB2 Catalog History tables.
  • DELETEHISTAGE specifies how long to keep the DB2 Catalog History table entries before deleting them.
  • RESET ACCESSPATH resets access path statistics in the DB2 Catalog to -1 for all tables in the specified table space and related indexes. Real-time statistics and space statistics in the DB2 Catalog are not reset.
  • RESETHISTORY inserts rows into the following tables, for the objects specified in the RESET ACCESSPATH command:
    • SYSIBM.SYSTABLES_HIST for tables
    • SYSIBM.SYSINDEXES_HIST for indexes

  • (PTFs BQU2647, BQU2648, and BQU2861 applied) INVALIDATECACHE specifies whether to invalidate the dynamic statement cache for an object. 
    The default is YES when you have specified RESET ACCESSPATH or UPDATEDB2 NO REPORT NO SAVESTATS NO with the following exceptions:  

    • For RESET ACCESSPATH, the default is YES.
      When you specify RESET ACCESSPATH, the INVALIDATECACHE NO is not supported.
    • For UPDATEDB2 NO REPORT NO SAVESTATS NO, the default is YES. 
      When you specify UPDATEDB2 NO REPORT NO SAVESTATS NO, the INVALIDATECACHE NO is not supported.

    The values defined are as follows:

    Option

    Meaning and use

    YES

    Invalidate the dynamic statement cache. Y is the default.

    NO

    Do not invalidate the dynamic statement cache.

  • OMITCARD0 specifies whether to bypass updating the DB2 catalog for objects in which BMCSTATS finds a zero cardinality. The default is N.
  • BADOBJECTRC specifies the return code that BMCSTATS will set if it is unable to process a requested object due to object characteristics (unsupported objects), invalid status, object serialization, or object authorization failures. The default is to issue return code 4. Regardless of the setting of this option, BMCSTATS processing continues.

  • RECALL specifies whether to recall archived data sets to collect statistics on them.

    The RECALL option opens the data sets that initiate a recall. N skips the object and returns a code 4, but continues with other objects. The default is N.

  • FORCEROLLUP specifies whether to roll up the partition level statistics to the object level in cases in which not all partition statistics are available. The default is N.
  • MSGLEVEL specifies the level of messaging. The default of 0 provides standard messaging. Specifying messaging level 1 provides some additional informative messages about progress and timestamps.
  • 911ACTION specifies the action BMCSTATS will take if a -911 SQL error occurs during a DELETE, INSERT, or UPDATE operation.
    • S specifies if an SQL -911 error occurs, stop all processing, issue error messages, and issue a return code 8 at termination.
    • I specifies if an SQL -911 error occurs, stop processing the current object, issue warning messages, continue on with the next available object, and issue a return code 4 at termination. I is the default.
  • TASKS is the number of concurrent tasks for gathering statistics. Specify the number of multitasking levels that are used for processing partitioned objects by typing a value from 1 through 16. The default is 5.

  • TSSAMPLEPCT specifies whether to use random sampling for statistics.

    Sampling greatly reduces resource consumption in producing statistics. If you need to use sampling, consider specifying a percentage of pages to sample.

    The following table defines the sample table space parameter options.

    BMCSTATS sample table space parameter options

    Option

    Meaning and use

    N

    Do not sample. Type N to process all pages, including very small tables on a multi-table table space that contains both large and small tables. N is the default.

    Y

    Sample 25 percent of the pages. If you type Y, BMCSTATS does not sample objects with fewer than 1000 pages. 1

    1 through 50

    Sample the specified percentage of pages for statistics. 1

    1The numPages/numTables must be greater than the minimum pages listed, or BMCSTATS will process all pages.

    For more information about sampling, see Sampling-statistics.

  • IXSAMPLEPCT specifies whether to use random sampling for statistics. Sampling greatly reduces resource consumption in producing statistics. If you need to use sampling, you should consider specifying a percentage of pages to sample.

    While sampling indexes reduces resource consumption, restrictions affect when you can specify this option. In the following instances, DASD MANAGER PLUS suppresses index sampling and processes the entire index:

    • Specifying an index sampling option in addition to UPDATEDB2 Y

      To eliminate this restriction, you can specify the UPDCATIXS=Y installation option during installation to allow the catalog to be updated with index sampled statistics.

      Warning

      Index sampling might produce statistics that cause SQL optimizer access selection problems. BMC recommends that you review the index sampled statistics for your indexes before deciding to use them for updating the catalog.

    • Specifying an index sampling option and an option to collect histogram statistics for indexes

      When collecting histogram statistics, BMCSTATS invokes DSNUTILB (which also does not support index sampling).

    • Index has less than 1000 pages

      The following table defines sample index parameter options.

      BMCSTATS sample index parameter options

      Option

      Meaning and use

      N

      Do not sample. Type N to process all pages, including very small indexes. N is the default.

      Y

      Sample 25 percent of the pages. If you type Y, BMCSTATS does not sample objects with fewer than 1000 pages.

      1–50

      Sample the specified percentage of pages for statistics. BMCSTATS does not sample objects with fewer than 1000 pages.

      For more information about sampling, see Sampling-statistics.

  • ATBWORKAREA specifies if the cardinality and frequency work areas will be placed above or below the 2G bar.

    • Y specifies work areas will be placed above the 2G bar.
    • N specifies work areas will be placed below the 2G bar.

    Above the 2G bar can reduce normal 32 bit addressed storage requirements. Below the bar reduces CPU consumption due to additional dynamic address translation overhead. N is the default.

  • OPTIMIZECOMMIT specifies how SQL COMMIT(s) after SQL DELETE, INSERT, and UPDATE will occur.

    • Y specifies COMMIT strategy for DELETE, INSERT, and UPDATE will be optimized.
    • N specifies COMMIT(s) will occur after every DELETE, INSERT, and UPDATE.

    Optimized COMMIT(s) reduce overall SQL overhead. Unoptimized COMMIT(s) reduce deadlocks and deadlock timeouts (SQL error -911). Y is the default.

  • QUIESCEINTERVAL (100001000000) specifies the number of object partitions processed before releasing and reinitializing O/S storage pools.

    • 0 specifies no QUIESCEINTERVAL.
    • 0–1000000 specifies the number of partitions processed before storage pools will be reorganized.

    QUIESCEINTERVAL can reduce the risk of storage depletion errors due to storage fragmentation. Storage fragmentation can occur after a large number of object partitions are processed. Use QUIESCEINTERVAL with large object sets and wildcards if storage depletion errors occur (Sx78 ABENDs).

  • SORTNUM specifies the number of temporary data sets that DFSORT can use for sorting and is used only with COLGROUP. Specify this field with the SORTDEVT field. You can specify an integer value of 0 through 99.
  • SORTDEVT specifies the device type for dynamic allocation of the sort work files that DFSORT can use for sorting and is only used with COLGROUP. Specify this field with the SORTNUM field.

BMCSTATS options for tables

When you specify TABLE Y on the BMCSTATS parameters panel, the BMCSTATS Select Tables panel is displayed:

BMCSTATS Select Tables panel

------------------------------- SELECT TABLES ----------- Row 1 to 33 of 1,121
COMMAND ===>                                                  Scroll ===>     
                                                                              
Enter S to Select and Edit, * to Select without Edit, blank to Unselect        
Enter Z to Zoom full table name                                                
                                                                              
Cmd   Database Tablespace   Owner      Table                                   
------------------------------------------------------------------------------
 S   QZUD10   QZUS0110     QZU        QZUT01_D10S01                           
     QZUD10   QZUS0210     QZU        QZUT01_D10S02                           
     QZUD11   QZUS0111     QZU        QZUT01_D11S01                           
     QZUD11   QZUS0211     QZU        QZUT01_D11S02                           
     QZUD11   QZUS0311     QZU        QZUT01_D11S03                           
     QZUD12   QZUS0112     QZU        QZUT01_D12S01

From the Select Tables panel, you can specify the tables that you want to include in the column statistics gathering, as follows:

  • To collect statistics for some but not all columns in a table, type S in the Cmd field for the table. The Options for Table tableName is displayed in the following figure.
  • To collect statistics for all columns, type an asterisk (*) in the Cmd field for the table.
  • To unselect a table, blank out any character in the Cmd field.

BMCSTATS Options for Table tableName panel

DEAE           Options for Table TABLE QZU.QZUT01_D10S01      Row 1 to 33 of 33
Command ===>                                                  Scroll ===> CSR  
                                                                              
Service Syntax: BMCSTATS.DEMO                                                  
                                                                              
Enter data, then press end.                                         More:      
                                                                              
-------------------------- What to Collect ------------------------------------
COLUMN . . . . . . * Y      (Y/S Y=ALL Columns, S=Select Columns)              
                                                                              
-------------------------- Column ColGroup Options ----------------------------
To generate column lists or column groups, set Table all to 'S'                
To collect histogram statistics, use UpdateDB2 A or P                          
Select COLGROUP 1  * N      (N/Y Choose columns for distribution stats)        
  COLGROUPFREQVAL           (Y/N Collect frequent value statistics)            
  COLGROUPFREQTYPE          (M/L/B M=Most L=Least B=Both)                      
  COLGROUPCOUNT             (1-300 Number of frequencies to collect)           
  NUMQUANTILES . .          (1-100 Number of quantiles to collect)             
Select COLGROUP 2  * N      (N/Y Choose columns for distribution stats)        
  COLGROUPFREQVAL           (Y/N Collect frequent value statistics)            
  COLGROUPFREQTYPE          (M/L/B M=Most L=Least B=Both)                      
  COLGROUPCOUNT             (1-300 Number of frequencies to collect)           
  NUMQUANTILES . .          (1-100 Number of quantiles to collect)             
Select COLGROUP 3  * N      (N/Y Choose columns for distribution stats)        
  COLGROUPFREQVAL           (Y/N Collect frequent value statistics)            
  COLGROUPFREQTYPE          (M/L/B M=Most L=Least B=Both)                      
  COLGROUPCOUNT             (1-300 Number of frequencies to collect)           
  NUMQUANTILES . .          (1-100 Number of quantiles to collect)             
Select COLGROUP 4  * N      (N/Y Choose columns for distribution stats)        
  COLGROUPFREQVAL           (Y/N Collect frequent value statistics)            
  COLGROUPFREQTYPE          (M/L/B M=Most L=Least B=Both)                      
  COLGROUPCOUNT             (1-300 Number of frequencies to collect)           
  NUMQUANTILES . .          (1-100 Number of quantiles to collect)             
Select COLGROUP 5  * N      (N/Y Choose columns for distribution stats)        
  COLGROUPFREQVAL           (Y/N Collect frequent value statistics)            
  COLGROUPFREQTYPE          (M/L/B M=Most L=Least B=Both)                      
  COLGROUPCOUNT             (1-300 Number of frequencies to collect)           
  NUMQUANTILES . .          (1-100 Number of quantiles to collect)             
******************************* Bottom of data *******************************

The fields on the Options for Table tableName panel are as follows:

  • COLUMN specifies whether to collect statistics on all columns or on specific columns.

  • Select COLGROUP n instructs BMCSTATS to calculate correlated column cardinality and frequency statistics for the specified set of columns. If you need more than five COLGROUP sets for a table, you can manually add them to the syntax.

    Note

    Note the following considerations:

    • BMCSTATS invokes DSNUTILB to collect column histogram statistics only if you specified UPDATEDB2 A or UPDATEDB2 P on the BMCSTATS parameters panel. Otherwise, this option is ignored.
    • Frequency values are collected as a standard part of regular column statistics and also for first key columns when index statistics are collected. For more information about changing the collection of frequency values, see the FREQVAL and FREQTYPE fields listed in the BMCSTATS Select Tables panel. Alternatively, you can specify COLGROUPCOUNT to specify the number of frequent values to collect for the preceding COLGROUP.
  • COLGROUPFREQVAL indicates whether BMCSTATS should collect frequency statistics for a group of columns. If you do not specify a value for this option, the value specified by the FREQVAL on the BMCSTATS panel (see the figure, BMCSTATS Select Tables panel) is used for this COLGROUP.
  • COLGROUPFREQTYPE specifies whether to collect the most (M) frequently occurring values, the least (L) frequently occurring values, or both (B) types of values for the group of columns. If no value is specified, the default is the value you specified for FREQTYPE on the BMCSTATS panel (see the figure, BMCSTATS Select Tables panel). If no value is specified in either location, the default is M. The value that you specify for this field applies to COLGROUP for column group statistics.
  • COLGROUPCOUNT specifies the number of frequencies to collect for the preceding group of columns. If no value is specified, the default is the value you specified for COUNT on the BMCSTATS panel. If no value is specified in either location, the default is 10.

  • NUMQUANTILES specifies the number of quantiles to collect and can be specified when collecting column group statistics. The values are divided into the number of quantiles you specify. If you specify 0, BMCSTATS does not collect histogram statistics.

    If you type S in the Select Columns or Select COLGROUP field, the following figure appears:

    BMCSTATS Select Columns panel

    ------------------------------- SELECT COLUMNS -------------- Row 1 to 21 of 21
    COMMAND ===>                                                  Scroll ===> PAGE
                                                                                  
    Enter S to Select and Edit, * to Select without Edit, blank to Unselect        
                                                                                  
    TABLE: QZU.QZUT01_D10S01                                                      
    Sel  Column Name                    Type      Seq Len Scl Kyseq Nulls Def FProc
    ----v----1----v----2----v----3----v----4----v----5----v----6----v----7----v---
         COLUMN_1                       INTEGER     1   4   0     0   N    Y    N
         COLUMN_10                      INTEGER    10   4   0     0   N    Y    N
         COLUMN_11                      FLOAT      11   4   0     0   N    Y    N
         COLUMN_12                      FLOAT      12   8   0     0   N    Y    N
         COLUMN_13                      DATE       13   4   0     0   N    Y    N
         COLUMN_14                      TIME       14   3   0     0   N    Y    N
         COLUMN_15                      TIMESTMP   15  10   0     1   N    Y    N
         COLUMN_16                      VARCHAR    16  30   0     0   N    Y    N

You can select up to a maximum of 80 column names on which to gather statistics, as follows:

  • To select a column, type S in the Sel field for the column.
  • To unselect a column, blank out any character in the Sel field.

BMCSTATS performance and tuning

The following topics discuss offloading workloads to a zIIP processor and performing non-partition Indexes processing.

zIIP offloading

DASD MANAGER PLUS can use BMCSTATS to offload eligible workloads to an IBM System z Integrated Information Processor (zIIP) on IBM z9 and z10 systems. Offloading workloads provides the following benefits:

  • Frees general computing capacity
  • Reduces the IBM monthly licensing charges
  • Reduces your mainframe's total cost of ownership (TCO)

You can offload the following types of workload:

  • All DB2 page file I/O processing
  • Table space data analysis
  • Index space data analysis

To use zIIP processing, you must have installed version 5.6 or later of the EXTENDED BUFFER MANAGER (XBM) product (with PTF BPE0313) or SNAPSHOT UPGRADE FEATURE for DB2 (SUF) technology. This zIIP offload does not apply to BMC Utilities executing BMCSTATS inline.

You can set the zIIP option for BMCSTATS as the default by setting the zIIP option to zIIP=E in the default options module (DOPTs). When the zIIP option is the default option, all BMCSTATS jobs that use the DOPTs module offload all zIIP-eligible processing. If no zIIP is available, BMCSTATS executes the workload on the general processor.

To disable automatic zIIP offloading, set the zIIP option to zIIP=D.

Use the BMCSTATS ZIIP syntax options to override the DOPTs setting for individual BMCSTATS jobs. In the BMCSTATS syntax, ZIIP E enables BMCSTATS to zIIP offloading; and ZIIP D disables zIIP offloading.

Since XBM handles zIIP usage for BMCSTATS, BMCSTATS and XBM use the same dispatching priority. To lower the BMCSTATS dispatching priority, create another XBM with a lower priority and use the lower priority XBM in the BMCSTATS job. Use the BMCSTATS syntax option XMBID to cause BMCSTATS to execute using the XBMID with the desired priority dispatching.

Non Partition Indexes (NPIs) processing

BMCSTATS uses a key caching algorithm when it processes indexes. Large multi-datasets (MUDS) Non Partition Indexes (NPIs) can consume large amounts of memory during the caching process. BMCSTATS provides storage relief during key column statistics collection for NPIs. You can use the following three BMCSTATS keywords to tune high storage-consuming objects:

Keyword

Used to:

Parameters

NPICACHEACTION:

Activate or deactivate the key caching processing

<NONE||LIMIT||ATBCACHE>

  • NONE— No storage limit is enforced for multi-dataset non-partitioned index key caching. The values that NPICACHESTHRESH and NPICACHEDSNUM specify are ignored.
  • LIMIT—For multi-dataset non-partitioned indexes, cache storage below the bar is limited to the value that NPICACHESTHRESH sets. When the storage limit is exceeded, key caching stops.
  • ATBCACHE— For multi-dataset non-partitioned indexes, cache storage below the bar is limited to the value that NPICACHESTHRESH sets. When the storage limit is exceeded:
    • The key cache is moved above the 2GB bar.
    • Caching continues.
    • Cache storage held below the bar is released.

NPICACHESTHRESH

Specify the amount of storage in megabytes that can be used below the 2GB bar before the action that NPICACHEACTION specifies takes place.

<1–500>100

NPICACHEDSNUM

Specify the number of datasets required in a multi-dataset non-partitioned index before the values that NPICACHEACTION and NPICACHESTHRESH specify are be observed.

<2–4096> 25

Note

If NPICACHEACTION=NONE, NPICACHEDSNUM and NPICACHESTHRESH are ignored.

To set these keywords globally for all BMCSTATS NPI processing, edit the Default Options module (DOPTs). To override the default settings for processing selected jobs, use the override the default settings.

 

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