PK44133: NEW STORED PROCEDURE, DSNACCOX

Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• New stored procedure, dsnaccox
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 for z/OS Version 9 users             *
  ****************************************************************
  * PROBLEM DESCRIPTION: This new function APAR adds a new       *
  *                      stored procedure, DSNACCOX, to DB2 for  *
  *                      z/OS Version 9.                         *
  ****************************************************************
  * RECOMMENDATION: Install the corrective PTF when available.   *
  ****************************************************************
  A new stored procedure, DSNACCOX, which is similar to DSNACCOR
  that uses a set of criteria to evaluate table spaces and index
  spaces in the subsystem that have entries in the real-time
  statistics tables.
  

Problem conclusion

Temporary fix

Comments

• A new stored procedure, DSNACCOX.
  
  DSNACCOX performs these actions in addition to DSNACCOR's
  actions:
  o Exploits new columns (SPACE, DATASIZE, INSTANCE, and CREATOR)
    and data types (TOTALROWS and TOTALENTRIES in BIGINT) in the
    new cataloged RTS tables.
  o Returns only the criteria that exceed the threshold.
  o Returns IMAGECOPY column as CHAR(4), so FULL can fit into it.
  o Recommends indexes for COPY only when the indexes are defined
    with SYSIBM.SYSINDEXES(COPY)='YES'
  o Can exclude recommending REORG or RUNSTATS on indexes when the
    related TABLESPACE of the index had been recommended for
    REORG or RUNSTATS
  o Recommends RUNSTATS when the last RUNSTATS was taken before
    the last REORG.
  o Returns the result of formula calculation in DOUBLE, so user
    can get more granularity (recommends a FULL image copy when
    only one page had been changed on a large object)
  o Excludes directory and workfile objects from the
    recommendation result set
  o Can pick and choose different criteria to determine
    recommendation.
  o Identifies clone objects
  o Can exclude VOLATILE objects
  o Uses new default thresholds
  o Exploits new formula on REORG recommendation
  
  
  DSNACCOX uses data from the real-time statistics (RTS) tables,
  SYSIBM.SYSTABLESPACESTATS and SYSIBM.SYSINDEXSPACESTATS,
  SYSIBM.SYSTABLESPACE, SYSIBM.SYSINDEXES, SYSIBM.SYSTABLES,
  SYSIBM.SYSTABLEPART, SYSIBM.SYSINDEXPART, and SYSIBM.SYSDATABASE
  to make its recommendations.
  DSNACCOX only works after the RTS tables are migrated to the
  catalog and IBMREQD field in SYSIBM.SYSTABLESPACE for SYSRTSTS
  contains 'M'. Otherwise, it will return a return code of 14.
  
  Do not install (create and bind) DSNACCOX if running DB2 in
  V9 compatibility mode, DSNACCOX will be created and bound by
  your updated DSNTIJNX job which you will run after DB2 enters
  V9 new-function mode.
  
  Please refer to flat file DSNDACOX for documentation on
  DSNACCOX stored procedure.  The DB2 Administration Guide and
  Utility Guide and Reference manuals will be updated with the
  information contained in that file.
  
  DSNDACOX contains the following information:
  -Syntax for the CALL statement along with option descriptions.
  -Description of the result sets returned by DSNACCOX.
  -The formula used by DSNACCOX.
  -A description of the optional exception table.
  -Sample CREATE PROCEDURE and BIND to install DSNACCOX.
  

APAR Information

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

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

  UK32795

Modules/Macros

• DSN@CCOX DSNACCOX DSNCIM   DSNDACOX DSNTIJNX
  DSNTIJSG DSNTINS1 DSNTINS3 HDB9910J
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• R910 PSY UK32795

     UP08/01/23 P F801

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.