PH66545: ZIIP USAGE OF RUNSTATS INCREASES WHEN IT COLLECTS KEYCARD CARDINALITY FROM MULTI-KEY-COLUMN PARTITIONING INDEX.

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• PH45533 fixes an incorrect keycard cardinality issue for a
  Partitioning Index (PI).  It changed RUNSTATS/inline-stats to
  use a different algorithm to collect keycard cardinality for a
  multi-column PI. However, it causes ziip CPU usage increases
  when RUNSTATS scans PI and collects keycard cardinality from
  multi-key-column PI.
  NPI/DPSI is not affected by PH45533.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All Db2 12 for z/OS and Db2 13 for z/OS      *
  *                 utility users of RUNSTATS or inline          *
  *                 STATISTICS gathering statistics on a         *
  *                 multi-column partitioning index              *
  ****************************************************************
  * PROBLEM DESCRIPTION: Excessive zIIP time usage was observed  *
  *                      during RUNSTATS or inline STATISTICS    *
  *                      gathering statistics on a multi-column  *
  *                      partitioning index that is not a Data   *
  *                      Partitioned Secondary Index (DPSI)      *
  ****************************************************************
  * RECOMMENDATION: Apply corrective PTF when available          *
  ****************************************************************
  When the user executed RUNSTATS INDEX on a partitioning index
  with more than two key columns, the user noticed an increase
  in zIIP time usage.
  
  The increase in zIIP time usage was due to the use of a Hash
  algorithm that was added to determine the intermediate key
  cardinalities correctly.
  
  The change to use the hash algorithm for multi-column
  partitioning indexes was added by PH45533 / UI80643 (v12)
  and PH46351 / UI80817 (v13) and was necessary to correctly
  calculate the keycard cardinality for a partitioning
  index (PI) with duplicate key-column values spanning
  partition boundaries. However the existing algorithm could
  be more efficient for a multi-column partitioning index
  and PH66545 improves upon the algorithm for this
  calculation.
  
  ADDITIONAL KEYWORDS / SYMPTOMS:
  RC00E70111 SYSIBM.SYSSTATSFEEDBACK
  

Problem conclusion

• Code was modified to use a more efficient algorithm to
  calculate the intermediate key cardinalities for the
  partitioning indexes.
  
  As a result of the code changes made in this apar,
  it necessitates the doc change for the description of the
  LOWVALUE and HIGHVALUE columns in the SYSIBM.SYSCOLDISTSTATS
  catalog table.
  
  Here is the modified description text for LOWVALUE and
  HIGHVALUE columns in the SYSCOLDISTSTATS catalog table.
  
  LOWVALUE  VARCHAR(2000) For TYPE='H', this is the lower bound
            NOT NULL WITH for the quantile indicated by
  |         DEFAULT FOR   QUANTILENO. For TYPE='C', this is the
  |         BIT DATA      lowest intermediate key value for the
  |                       index partition. Not used if TYPE is
                          not 'H' or 'C'. This column is not
                          updatable.
  
  HIGHVALUE VARCHAR(2000) For TYPE='H', this is the higher bound
            NOT NULL WITH for the quantile indicated by
  |         DEFAULT FOR   QUANTILENO. For TYPE='C', this is the
  |         BIT DATA      highest intermediate key value for the
  |                       index partition. Not used if TYPE is
                          not 'H' or 'C'. This column is not
                          updatable.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UO05166 UO05167

Modules/Macros

• DSNUGPRD DSNURWIR DSNUSAC1 DSNUSAC2 DSNUSACD DSNUSACO DSNUSACR
  DSNUSAF1 DSNUSAF2 DSNUSAFK DSNUSAFQ DSNUSAFV DSNUSAH1 DSNUSAH2
  DSNUSAIX DSNUSAKT DSNUSATS DSNUSDST DSNUSEF2 DSNUSEOF DSNUSIDX
  DSNUSIIX DSNUSILB DSNUSINI DSNUSINK DSNUSITS DSNUSLOB DSNUSLTS
  DSNUSPKD DSNUSRST DSNUSSFB DSNUSTBL DSNUSUCD DSNUSUCO DSNUSUIP
  DSNUSUIX DSNUSUKD DSNUSUKT DSNUSUPC DSNUSUPD DSNUSUPI DSNUSUPK
  DSNUSUPT DSNUSUTB DSNUSUTP DSNUSUTS
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UO05166

     UP25/10/08 P F510

• RD10 PSY UO05167

     UP25/10/08 P F510

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.