DSNACCOX stored procedure
The Db2 realtime statistics stored procedure (DSNACCOX) is a sample stored procedure that makes recommendations to help you maintain your Db2 databases.
The DSNACCOX stored procedure replaced the DSNACCOR stored procedure, which is not supported in Db2 12. DSNACCOX provides the following improvements over DSNACCOR:
 Improved recommendations
 New fields
 New formulas
 The option to choose the formula for making recommendations
You can call the DSNACCOX stored procedure to accomplish the following actions:
 Get recommendations for when to reorganize, image copy, or update statistics for table spaces or index spaces
 Identify when a data set has exceeded a specified threshold for the number of extents that it occupies.
 Identify whether objects are in restricted states
DSNACCOX uses data from catalog tables, including realtime statistics tables, to make its recommendations. DSNACCOX provides its recommendations in a result set.
DSNACCOX uses the set of criteria that are shown in DSNACCOX formulas for recommending actions to evaluate table spaces and index spaces. By default, DSNACCOX evaluates all table spaces and index spaces in the subsystem that have entries in the realtime statistics tables. However, you can override this default through input parameters.
About DSNACCOX recommendations
 You can improve the quality of DSNACCOX recommendations, especially for frequently changed objects, by externalizing inmemory statistics to the realtime statistics tables immediately before calling the stored procedure. For more information, see:
 DSNACCOX makes recommendations based on general formulas that require input from the user about the maintenance policies for a subsystem. These recommendations might not be accurate for every installation.
 If the realtime statistics tables contain information for only a small percentage of your Db2 subsystem, the recommendations that DSNACCOX makes might not be accurate for the entire subsystem.
 Before you perform any action that DSNACCOX recommends, ensure that the object for which DSNACCOX makes the recommendation is available, and that the recommended action can be performed on that object. For example, REORG might be recommended for an object, but the object might be stopped.
Environment
DSNACCOX must run in a WLMestablished stored procedure address space.The DSNWLM_GENERAL core WLM environment is a suitable environment for this stored procedure.
DSNACCOX is installed and configured by installation job DSNTIJRT, which binds the package for DSNACCOX with isolation UR to avoid lock contention.
Authorization required
To execute the CALL DSNACCOX statement, the owner of the package or plan that contains the CALL statement must have one or more of the following privileges on each package that the stored procedure uses:
 The EXECUTE privilege on the package for DSNACCOX
 Ownership of the package
 PACKADM authority for the package collection
 SYSADM authority
The owner of the package or plan that contains the CALL statement must also have:
 SELECT authority on catalog tables
 The DISPLAY privilege
Syntax diagram
The following syntax diagram shows the CALL statement for invoking DSNACCOX. Because the linkage convention for DSNACCOX is GENERAL WITH NULLS, if you pass parameters in host variables, you need to include a null indicator with every host variable. Null indicators for input host variables must be initialized before you execute the CALL statement.
For more information, see:Option descriptions
In the following option descriptions, the default value for an input parameter is the value that DSNACCOX uses if you specify a null value.
 QueryType
 Specifies the types of actions that DSNACCOX recommends. This field contains one or more of the
following values. Each value is enclosed in single quotation marks and separated from other values
by a space.
 ALL
 Makes recommendations for all of the following actions.
 COPY
 Makes a recommendation on whether run an image copy.
 RUNSTATS
 Makes a recommendation on whether to run RUNSTATS.
 REORG
 Makes a recommendation on whether to run REORG. Choosing this value causes DSNACCOX to process the EXTENTS value also.
 EXTENTS
 Indicates when data sets have exceeded a userspecified extents limit.
 RESTRICT
 Indicates which objects are in a restricted state.
DSNACCOX recommends REORG on the table space when one of the following conditions is true, and REORG (or ALL) is also specified for the value of QUERYTYPE:
 The table space is in REORGpending status.
 The table space is in advisory REORGpending status as the result of an ALTER TABLE statement.
DSNACCOX recommends REORG on the index when on the following conditions is true and REORG (or ALL) is also specified for the value of QUERYTYPE:
 The index is in REORGpending status.
 The index is in advisory REORGpending as the result of an ALTER TABLE statement.
DSNACCOX recommends FULL COPY on the table space when on the following conditions is true and COPY (or ALL) is also specified for the value of QUERYTYPE:
 The table space is in COPYpending status.
 The table space is in informational COPYpending status.
DSNACCOX recommends FULL COPY on the index when on the following conditions is true and COPY (or ALL) is also specified for the value of QUERYTYPE: and SYSINDEX.COPY='Y':
 The index is in COPYpending status.
 The index is in informational COPYpending status.
QueryType is an input parameter of type VARCHAR(40). The default value is ALL.
 ObjectType
 Specifies the types of objects for which DSNACCOX recommends actions:
 ALL
 Table spaces and index spaces.
 TS
 Table spaces only.
 IX
 Index spaces only.
ObjectType is an input parameter of type VARCHAR(3). The default value is ALL.
 ICType
 Specifies the types of image copies for which DSNACCOX is to make recommendations:
 F
 Full image copy.
 I
 Incremental image copy. This value is valid for table spaces only.
 B
 Full image copy or incremental image copy.
ICType is an input parameter of type VARCHAR(1). The default is B.
 CatlgSchema
 Specifies the qualifier for Db2 catalog table names. CatlgSchema is an input parameter of type VARCHAR(128). The default value is SYSIBM.
 LocalSchema
 Specifies the qualifier for the names of local tables that DSNACCOX references. LocalSchema is an input parameter of type VARCHAR(128). The default value is DSNACC.
 ChkLvl
 Specifies the types of checking that DSNACCOX performs, and indicates whether to include objects
that fail those checks in the DSNACCOX recommendations result set. This value is the sum of any
combination of the following values:
 0
 DSNACCOX performs none of the following actions.
 1
 Exclude rows from the DSNACCOX recommendations result set for RUNSTATS on:
 Index spaces that are related to tables that are defined as VOLATILE.
 Table spaces for which all of the tables are defined as VOLATILE.
 2
 Choosing this value causes DSNACCOX to override the default SSDMultiplier value when it makes a REORG recommendation for a table space or table space partition. SSDMultiplier is the value by which RRTUnclustInsPct is multiplied when table space data sets are on solid state drives (DRIVETYPE='SSD' in catalog table SYSIBM.SYSTABLESPACESTATS). The default value of SSDMultiplier is 2. If ChkLvl is set to 2, SSDMultiplier is 5. See Figure 4 for details on how SSDMultiplier is used.
 4
 Check whether rows that are in the DSNACCOX recommendations result set refer to objects that are in the exception table. For recommendations result set rows that have corresponding exception table rows, copy the contents of the QUERYTYPE column of the exception table to the INEXCEPTTABLE column of the recommendations result set.
 8
 Check for objects that have restricted states. The value of the QueryType option must be ALL or contain RESTRICTED when this value is specified. The OBJECTSTATUS column of the result set indicates the restricted state of the object. A row is added to the result set for each object that has a restricted state.
 16
 Reserved for future use.
 32
 Exclude rows from the DSNACCOX recommendations result set for index spaces for which the related table spaces have been recommended for REORG or RUNSTATS.
 64
 For index spaces that are listed in the DSNACCOX recommendations result set, check whether the related table spaces are listed in the exception table. For recommendations result set rows that have corresponding exception table rows, copy the contents of the QUERYTYPE column of the exception table to the INEXCEPTTABLE column of the recommendations result set. Selecting CHKLVL64 also activates CHKLVLs 32 and 4.
ChkLvl is an input parameter of type INTEGER. The default is 5 (values 1+4).
 Criteria
 Narrows the set of objects for which DSNACCOX makes recommendations. This value is the search
condition of an SQL WHERE clause.Criteria is an input parameter of type
VARCHAR(4096). The default is that DSNACCOX makes recommendations for all table spaces and index
spaces in the subsystem. The search condition can use any column in the result set and wildcards are
allowed.
DSNACCOX can optimize the retrieval of recommendations if the criteria references only the following columns in the realtime statistics tables:
 DBNAME
 NAME
 PARTITION
 DBID
 PSID
 SpecialParm
 SpecialParm is an input of type CHAR(160), broken into 4 byte sections
to accommodate new options. An empty 4 bytes of EBCDIC blanks indicates that the default is used for
the option. An EBCDIC character string of '1', indicates that this option is not used.
 RRIEmptyLimit
 Is the ratio of pseudoempty pages to the total number of leaf pages. Specifies a criterion for recommending that the REORG utility is to be run on an index space. If the following value is greater than RRIEmptyLimit, DSNACCOX recommends running REORG: The number of pseudoempty leaf pages that were created since the last CREATE, REORG, REBUILD INDEX, or LOAD REPLACE, divided by the total number of leaf pages in the index space or partition, expressed as a percentage.
RRIEmptyLimit is an input parameter of type CHAR(4). The default value is ' 5'. A plus sign (+) preceding the value indicates that the DSNACCOX stored procedure returns the value in the result set. A negative value turns off this criterion.
The ratio of pseudoempty pages to the total number of leaf pages is returned in column RRIEMPTYLIMIT of the result set.
 RRTHashOvrFlwRatio
 The ratio of hash access overflow index entries to the total number of rows. Specifies a criterion for recommending that the REORG utility is to be run on a table space. If the following condition is true, DSNACCOX recommends running REORG: The hash access overflow index is being used for access, and the ratio of hash access overflow index entries divided by the total number of rows (expressed as a percentage) is greater than RRTHashOvrFlwRatio.
RRTHashOvrFlwRatio is an input parameter of type CHAR(4). The default value is ' 15'. A plus sign (+) preceding or after the value or by itself indicates that the DSNACCOX stored procedure returns the calculated ratio value in the result set. The value of the ObjectType parameter must be ALL, or contain both TS and IX, for this criterion to be used. A negative value turns off this criterion.
The ratio of Hash Access overflow index entries to the total number of rows is returned in the RRTHASHOVRFLWRAT column of the result set.
 CRUpdatedPagesPct
 Is the ratio of the total number of distinct updated pages to
the total number of preformatted pages. Specifies, when combined with
CRUpdatedPagesAbs, a criterion for recommending a full image copy on a
table space or index space. If both of the following conditions are true for a table space, DSNACCOX
recommends an image copy:
 The total number of distinct updated pages, divided by the total number of preformatted pages (expressed as a percentage) is greater than CRUpdatedPagesPct.
 The total number of distinct updates pages is greater than CRUpdatedPagesABS.
If all of the following conditions are true for an index space, DSNACCOX recommends an image copy:
 The total number of distinct updated pages, divided by the total number of preformatted pages (expressed as a percentage) is greater than CRUpdatedPagesPct.
 The total number of distinct updates pages is greater than CRUpdatedPagesABS.
 The number of active pages in the index space or partition is greater than CRIndexSize.
CRUpdatedPagesPct is an input parameter of type DOUBLE. The default is 20.0. A negative value turns off both this criterion and CRUpdatedPagesABS.
 CRUpdatedPagesABS
 Is the total number of distinct updated pages. Specifies,
when combined with CRUpdatedPagesPct, a criterion for recommending a full
image copy on a table space or index space. If both of the following conditions are true for a table
space, DSNACCOX recommends an image copy:
 The total number of distinct updated pages, divided by the total number of preformatted pages (expressed as a percentage) is greater than CRUpdatedPagesPct.
 The total number of distinct updated pages is greater than CRUpdatedPagesAbs.
If all of the following conditions are true for an index space, DSNACCOX recommends an image copy:
 The total number of distinct updated pages, divided by the total number of preformatted pages (expressed as a percentage) is greater than CRUpdatedPagesPct.
 The total number of distinct updates pages is greater than CRUpdatedPagesAbs.
 The number of active pages in the index space or partition is greater than CRIndexSize.
CRUpdatedPagesAbs is an input parameter of type INTEGER. The default value is 0.
 CRChangesPct
 Is the ratio of the total number of insert, update, and delete
operations to the total number of rows. Specifies a criterion for recommending a full image
copy on a table space or index space. If the following condition is true for a table space, DSNACCOX
recommends an image copy:
 The total number of insert, update, and delete operations since the last image copy, divided by the total number of rows or LOBs in a table space or partition (expressed as a percentage) is greater than CRChangesPct.
If both of the following conditions are true for an index table space, DSNACCOX recommends an image copy:
 The total number of insert and delete operations since the last image copy, divided by the total number of entries in the index space or partition (expressed as a percentage) is greater than CRChangesPct.
 The number of active pages in the index space or partition is greater than CRIndexSize.
CRChangesPct is an input parameter of type DOUBLE. The default is 10.0. A negative value turns off this criterion.
 CRDaySncLastCopy
 Is the number of days since the last image copy.
Specifies a criterion for recommending a full image copy on a table space or index space. If the
number of days since the last image copy is greater than this value, DSNACCOX recommends an image
copy.
CRDaySncLastCopy is an input parameter of type INTEGER. The default is 7. A negative value turns off this criterion.
 ICRUpdatedPagesPct
 Is the ratio of the total number of distinct updated pages to
the total number of preformatted pages. Specifies a criterion for recommending an incremental
image copy on a table space. If both of the following conditions are true, DSNACCOX recommends an
incremental image copy:
 The number of distinct pages that were updated since the last image copy, divided by the total number of active pages in the table space or partition (expressed as a percentage) is greater than ICRUpdatedPagesPct..
 The number of distinct pages that were updated since last image copy is greater than ICRUpdatedPagesAbs.
ICRUpdatedPagesPct is an input parameter of type DOUBLE. The default value is 1.0. A negative value turns off this criterion and ICRUpdatedPagesAbs.
 ICRUpdatedPagesAbs
 Is the total number of distinct updated pages. Specifies,
when combined with ICRUpdatedPagesPct, a criterion for recommending an
incremental image copy on a table space. If both of the following conditions are true, DSNACCOX
recommends an incremental image copy:
 The number of distinct pages that were updated since the last image copy, divided by the total number of active pages in the table space or partition (expressed as a percentage) is greater than ICRUpdatedPagesPct.
 The number of distinct pages that were updated since last image copy is greater than ICRUpdatedPagesAbs.
ICRUpdatedPagesAbs is an input parameter of type INTEGER. The default is 0.
 ICRChangesPct
 Is the ratio of the total number of insert, update, or delete
operations to the total number of rows. Specifies a criterion for recommending an incremental
image copy on a table space. If the following condition is true, DSNACCOX recommends an incremental
image copy:
 The ratio of the number of insert, update, or delete operations since the last image copy, to the total number of rows or LOBs in a table space or partition (expressed as a percentage) is greater than ICRChangesPct.
ICRChangesPct is an input parameter of type DOUBLE. The default is 1.0. A negative value turns off this criterion.
 CRIndexSize
 Is the minimum index size. Specifies the minimum index size before checking the
CRUpdatedPagesPct or CRChangesPct criteria for
recommending a full image copy on an index space.
CRIndexSize is an input parameter of type INTEGER. The default is 50. A negative value turns of this criterion and ICRChangesPct.
 RRTInsertsPct
 Is the ratio of total number of insert operations to the total
number of rows. Specifies, when combined with RRTInsertsAbs, a criterion for recommending that the REORG utility is to be run on a
table space. If both of the following condition are true, DSNACCOX recommends running REORG:
 The sum of insert, update, and delete operations since the last REORG, divided by the total number of rows or LOBs in the table space or partition (expressed as a percentage) is greater than RRTInsertsPct
 The sum of insert operations since the last REORG is greater than RRTInsertsAbs.
RRTInsertsPct is an input parameter of type DOUBLE. The default value is 1. A negative value turns off this criterion and RRTInsertsAbs.
 RRTInsertsAbs
 Is the total number of insert operations. Specifies, when
combined with RRTInsertsPct, a criterion for recommending
that the REORG utility is to be run on a table space. If both of the following condition are true,
DSNACCOX recommends running REORG:
 The sum of insert operations since the last REORG, divided by the total number of rows or in the table space or partition (expressed as a percentage) is greater than RRTInsertsPct
 The sum of insert operations since the last REORG is greater than RRTInsertsAbs.
RRTInsertsAbs is an input parameter of type INTEGER. The default value is 0.
 RRTDeletesPct
 Is the ratio of the total number of delete operations to the
total number of rows. Specifies, when combined with RRTDeletesAbs, a criterion for recommending that the REORG utility is to be run on a
table space. If both of the following condition are true, DSNACCOX recommends running REORG:
 The sum of delete operations since the last REORG, divided by the total number of rows or in the table space or partition (expressed as a percentage) is greater than RRTDeletesPct
 The sum of delete operations since the last REORG is greater than RRTDeletesAbs.
RRTDeletesPct is an input parameter of type DOUBLE. The default value is 25.0. A negative value turns off this criterion and RRTDeletesAbs.
 RRTDeletesAbs
 Is the total number of delete operations. Specifies, when
combined with RRTDeletesPct, a criterion for recommending
that the REORG utility is to be run on a table space. If both of the following condition are true,
DSNACCOX recommends running REORG:
 The sum of delete operations since the last REORG, divided by the total number of rows or in the table space or partition (expressed as a percentage) is greater than RRTDeletesPct
 The sum of delete operations since the last REORG is greater than RRTDeletesAbs.
RRTDeletesAbs is an input parameter of type INTEGER. The default value is 0.
 RRTUnclustInsPct
 Is the ratio of the total number of unclustered insert operations to the total number of rows. Specifies a criterion for recommending that the REORG utility is to be run on a table space. If the following condition is true, DSNACCOX recommends running REORG:
 The number of unclustered insert operations, divided by the total number of rows or LOBs in the table space or partition (expressed as a percentage) is greater than RRTUnclustInsPct.
RRTUnclustInsPct is an input parameter of type DOUBLE. A negative value will turn off this criterion.
For solidstate drives (SSD), the value that you specify is multiplied by 2 unless you specify CHKLVL 2. If you specify CHKLVL 2, the value that you specify is multiplied by 5.
For hard disk drives (HDD), the default value is 10.0. For SSDs, the default value is 20.0 unless you specify CHKLVL 2; in this case, the default is 50.0
 RRTDisorgLOBPct
 Is the ratio of the number of imperfectly chunked LOBs to the
total number of rows. Specifies a criterion for recommending that the REORG utility is to be
run on a table space. If the following condition is true, DSNACCOX recommends running REORG:
 The number of imperfectly chunked LOBs, divided by the total number of rows or LOBs in the table space or partition (expressed as a percentage) is greater than RRTDisorgLOBPct.
RRTDisorgLOBPct is an input parameter of type DOUBLE. The default is 50.0. A negative value will turn off this criterion.
 RRTDataSpaceRat
 Is the ratio of the space allocated to the actual space
used. Specifies a criterion for recommending that the REORG utility is to be run on table space
for space reclamation. If the following condition is true, DSNACCOX recommends running REORG:
 The object is not using hash organization.
 The SPACE allocated is greater than RRTDataSpaceRat multiplied by the actual space used. (SPACE > RRTDataSpaceRat × (DATASIZE/1024))
RRTDataSpaceRat is an input parameter of type DOUBLE. The default value is 1. A negative value turns off this criterion.
 RRTMassDelLimit
 Is the sum of the number of mass deletes. Specifies a
criterion for recommending that the REORG utility is to be run on a table space. If one of the
following values is greater than RRTMassDelLimit, DSNACCOX recommends
running REORG:
 The sum of mass deletes from a segmented or LOB table space since the last REORG or LOAD REPLACE
 The number of dropped tables from a nonsegmented table space since the last REORG or LOAD REPLACE
RRTMassDelLimit is an input parameter of type INTEGER. The default is 0.
 RRTIndRefLimit
 Is the ratio of the total number of overflow records that were
created to the total number of rows. Specifies a criterion for recommending that the REORG
utility is to be run on a table space. If the following value is greater than
RRTIndRefLimit, DSNACCOX recommends running REORG:
 The total number of overflow records that were created since the last REORG or LOAD REPLACE, divided by the total number of rows or LOBs in the table space or partition (expressed as a percentage)
RRTIndRefLimit is an input parameter of type DOUBLE. The default is 5.0 in data sharing environment and 10.0 in a nondata sharing environment.
 RRIInsertsPct
 Is the ratio of the total number of index entries that were
inserted to the total number of index entries. Specifies a criterion for recommending that the
REORG utility is to be run on an index space. If the both of the following conditions are true,
DSNACCOX recommends running REORG:
 The sum of the number of index entries that were inserted since the last REORG, divided by the total number of index entries in the index space or partition (expressed as a percentage) is greater than RRIInsertsPct.
 The sum of the number of index entries that were inserted since the last REORG is greater than RRIInsertsAbs.
RRIInsertsPct is an input parameter of type DOUBLE. The default is 1. A negative value turns off this criterion and RRIInsertsAbs.
 RRIInsertsAbs
 Is the sum of the number of index entries that were inserted. Specifies a criterion for recommending that the REORG utility is to be run on an index space. If both of the following conditions are true, DSNACCOX recommends running REORG:
 The sum of the number of index entries that were inserted since the last REORG, divided by the total number of index entries in the index space or partition (expressed as a percentage) is greater than RRIInsertsPct.
 The sum of the number of index entries that were inserted since the last REORG is greater than RRIInsertsAbs.
RRIInsertsAbs is an input parameter of type INTEGER. The default is 0.
 RRIDeletesPct
 Is the ratio of the sum of the number of index entries that were deleted to the total number of index entries. Specifies a criterion for recommending that the REORG utility is to be run on an index space. If the following value is greater than RRIDeletesPct, DSNACCOX recommends running REORG:
 The sum of the number of index entries that were deleted since the last REORG, divided by the total number of index entries in the index space or partition (expressed as a percentage) is greater than RRIDeletesPct.
 The sum of the number of index entries that were deleted since the last REORG is greater than RRIDeletesAbs.
RRIDeletesPct is an input parameter of type DOUBLE. The default is 30.0. A negative value turns off this criterion and RRIDeletesAbs.
 RRIDeletesAbs
 Is the sum of the number of index entries that were deleted. Specifies a criterion for recommending that the REORG utility is to be run on an index space. If the following value is greater than RRIDeletesPct, DSNACCOX recommends running REORG:
 The sum of the number of index entries that were deleted since the last REORG, divided by the total number of index entries in the index space or partition (expressed as a percentage) is greater than RRIDeletesPct.
 The sum of the number of index entries that were deleted since the last REORG is greater than RRIDeletesAbs.
RRIDeletesAbs is an input parameter of type INTEGER. The default is 0.
 RRIAppendInsertPct
 Is the ratio of the number of index entries that were inserted with a key value greater than the maximum key value in the index space or partition to the number of index entries. Specifies a criterion for recommending that the REORG utility is to be run on an index space. If the following value is greater than RRIAppendInsertPct, DSNACCOX recommends running REORG:
 The number of index entries that were inserted since the last REORG, REBUILD INDEX, or LOAD REPLACE with a key value greater than the maximum key value in the index space or partition, divided by the number of index entries in the index space or partition (expressed as a percentage)
RRIAppendInsertPct is an input parameter of type DOUBLE. The default is 20.0. A negative value turns off this criterion.
 RRIPseudoDeletePct
 Is the ratio of the number of index entries that were pseudodeleted to the number of index entries. Specifies a criterion for recommending that the REORG utility is to be run on an index space. If the following value is greater than RRIPseudoDeletePct, DSNACCOX recommends running REORG:
 The number of index entries that were pseudodeleted since the last REORG, REBUILD INDEX, or LOAD REPLACE, divided by the number of index entries in the index space or partition (expressed as a percentage)
RRIPseudoDeletePct is an input parameter of type DOUBLE. The default is 5.0. A negative value turns off this criterion.
 RRIMassDelLimit
 Is the sum of the number of mass deletes of index
entries. Specifies a criterion for recommending that the REORG utility is to be run on an index
space. If the number of mass deletes from an index space or partition since the last REORG, REBUILD,
or LOAD REPLACE is greater than this value, DSNACCOX recommends running
REORG.
RRIMassDelLimit is an input parameter of type INTEGER. The default is 0. A negative value turns off this criterion.
 RRILeafLimit
 Is the ratio of the number of index page splits in which the
higher part of the split page was far from the location of the original page to the total number of
active pages. Specifies a criterion for recommending that the REORG utility is to be run on an
index space. If the following value is greater than RRILeafLimit, DSNACCOX
recommends running REORG:
 The number of index page splits that occurred since the last REORG, REBUILD INDEX, or LOAD REPLACE in which the higher part of the split page was far from the location of the original page, divided by the total number of active pages in the index space or partition (expressed as a percentage)
RRILeafLimit is an input parameter of type DOUBLE. The default is 10.0. A negative value turns off this criterion.
 RRINumLevelsLimit
 Is the number of levels in the index tree that were added or
removed. Specifies a criterion for recommending that the REORG utility is to be run on an index
space. If the following value is greater than RRINumLevelsLimit, DSNACCOX
recommends running REORG:
 The number of levels in the index tree that were added or removed since the last REORG, REBUILD INDEX, or LOAD REPLACE
RRINumLevelsLimit is an input parameter of type INTEGER. The default is 0. A negative value turns off this criterion.
 SRTInsDelUpdPct
 Is the ratio of the total number of insert, update, or delete
operations to the total number of rows. Specifies, when combined with
SRTInsDelUpdAbs, a criterion for recommending that the RUNSTATS utility is
to be run on a table space. If both of the following conditions are true, DSNACCOX recommends
running RUNSTATS:
 The number of insert, update, or delete operations since the last RUNSTATS on a table space or partition, divided by the total number of rows or LOBs in table space or partition (expressed as a percentage) is greater than SRTInsDelUpdPct.
 The sum of the number of inserted and deleted index entries since the last RUNSTATS on an index space or partition is greater than SRTInsDelUpdAbs.
SRTInsDelUpdPct is an input parameter of type DOUBLE. The default is 20.0. A negative value turns off this criterion.
 SRTInsDelUpdAbs
 Is the number of insert, update, and delete operations.
Specifies, when combined with SRTInsDelUpdPct, a criterion for
recommending that the RUNSTATS utility is to be run on a table space. If both of the following
conditions are true, DSNACCOX recommends running RUNSTATS:
 The number of insert, update, and delete operations since the last RUNSTATS on a table space or partition, divided by the total number of rows or LOBs in table space or partition (expressed as a percentage) is greater than SRTInsDelUpdPct.
 The sum of the number of inserted and deleted index entries since the last RUNSTATS on an index space or partition is greater than SRTInsDelUpdAbs.
SRTInsDelUpdAbs is an input parameter of type INTEGER. The default is 0.
 SRTMassDelLimit
 Is the sum of the number of mass deletes. Specifies a
criterion for recommending that the RUNSTATS utility is to be run on a table space. If the following
condition is true, DSNACCOX recommends running RUNSTATS:
 The number of mass deletes from a table space or partition since the last REORG or LOAD REPLACE is greater than SRTMassDelLimit.
SRTMassDelLimit is an input parameter of type INTEGER. The default is 0.0. A negative value turns off this criterion.
 SRIInsDelPct
 Is the ratio of the total number of inserted and deleted index
entries to the total number of index entries. Specifies, when combined with SRIInsDelAbs, a criterion for recommending that the RUNSTATS utility is
to be run on an index space. If both of the following conditions are true, DSNACCOX recommends
running RUNSTATS:
 The number of inserted and deleted index entries since the last RUNSTATS on an index space or partition, divided by the total number of index entries in the index space or partition (expressed as a percentage) is greater than SRIInsDelPct
 The sum of the number of inserted and deleted index entries since the last RUNSTATS on an index space or partition is greater than SRIInsDelAbs
SRIInsDelPct is an input parameter of type DOUBLE. The default is 20.0. A negative value turns off this criterion.
 SRIInsDelAbs
 Is the total number of inserted and deleted index
entries. Specifies, when combined with SRIInsDelPct, a
criterion for recommending that the RUNSTATS utility is to be run on an index space. If the
following condition is true, DSNACCOX recommends running RUNSTATS:
 The number of inserted and deleted index entries since the last RUNSTATS on an index space or partition, divided by the total number of index entries in the index space or partition (expressed as a percentage) is greater than SRIInsDelPct
 The sum of the number of inserted and deleted index entries since the last RUNSTATS on an index space or partition is greater than SRIInsDelAbs,
SRIInsDelAbs is an input parameter of type INTEGER. The default is 0.
 SRIMassDelLimit
 Is the sum of the number of mass deletes. Specifies a
criterion for recommending that the RUNSTATS utility is to be run on an index space. If the number
of mass deletes from an index space or partition since the last REORG, REBUILD INDEX, or LOAD
REPLACE is greater than this value, DSNACCOX recommends running
RUNSTATS.
SRIMassDelLimit is an input parameter of type INTEGER. The default value is 0. A negative value turns off this criterion.
 ExtentLimit
 Is the number of physical extents. Specifies a criterion for
recommending that the REORG utility is to be run on a table space or index space. Also specifies
that DSNACCOX is to warn the user that the table space or index space has used too many extents.
DSNACCOX recommends running REORG, and altering data set allocations if the following condition is true:
 The number of physical extents in the index space, table space, or partition is greater than ExtentLimit.
ExtentLimit is an input parameter of type INTEGER. The default value is 254. A negative value turns off this criterion.
 LastStatement
 When DSNACCOX returns a severe error (return code 12), this field contains the SQL statement that was executing when the error occurred. LastStatement is an output parameter of type VARCHAR(8012).
 ReturnCode
 The return code from DSNACCOX execution. Possible values are:
 0
 DSNACCOX executed successfully.
 4
 DSNACCOX completed with a warning. For more information about the problem, check the output parameters, such as the ErrorMsg, IFCAResCode, and IFCARetCode parameters.
 8
 DSNACCOX terminated with errors. The ErrorMsg parameter contains a message that describes the error.
 12
 DSNACCOX terminated with severe errors. The ErrorMsg parameter contains a message that describes the error. The LastStatement parameter contains the SQL statement that was executing when the error occurred.
 14
 DSNACCOX terminated because the realtime statistics table were not yet migrated to the catalog.
 15
 DSNACCOX terminated because it encountered a problem with one of the declared temporary tables that it defines and uses.
 16
 DSNACCOX terminated because it could not define a declared temporary table.
 NULL
 DSNACCOX terminated but could not set a return code.
ReturnCode is an output parameter of type INTEGER.
 ErrorMsg
 Contains information about DSNACCOX execution when DSNACCOX terminates with a nonzero value for ReturnCode.
 IFCARetCode
 Contains the return code from an IFI COMMAND call. DSNACCOX issues commands through the IFI interface to determine the status of objects. IFCARetCode is an output parameter of type INTEGER.
 IFCAResCode
 Contains the reason code from an IFI COMMAND call. IFCAResCode is an output parameter of type INTEGER.
 XsBytes
 Contains the number of bytes of information that did not fit in the IFI return area after an IFI COMMAND call. XsBytes is an output parameter of type INTEGER.
DSNACCOX formulas for recommending actions
The following formulas specify the criteria that DSNACCOX uses for its recommendations and warnings. The variables in italics are DSNACCOX input parameters. The capitalized variables are columns of the SYSIBM.SYSTABLESPACESTATS or SYSIBM.SYSINDEXSPACESTATS catalog tables.
The following figure shows the formula that DSNACCOX uses to recommend a full image copy on a table space.
The following figure shows the formula that DSNACCOX uses to recommend a full image copy on an index space.
The following figure shows the formula that DSNACCOX uses to recommend an incremental image copy on a table space.
The following figure shows the formula that DSNACCOX uses to recommend a REORG on a table space.
The following figure shows the formula that DSNACCOX uses to recommend a REORG on an index space.
The following figure shows the formula that DSNACCOX uses to recommend RUNSTATS on a table space.
 PROFILE_UPDATE is a tablelevel timestamp column in the SYSIBM.SYSTABLES_PROFILES catalog table. It is updated by RUNSTATS SET or UPDATE. The PROFILE_UPDATE value is not returned as a column in the DSNACCOX result set.
The following figure shows the formula that DSNACCOX uses to recommend RUNSTATS on an index space.
Using an exception table
An exception table is an optional, usercreated Db2 table that you can use to place information in the INEXCEPTTABLE column of the recommendations result set. You can put any information in the INEXCEPTTABLE column, but the most common use of this column is to filter the recommendations result set. Each row in the exception table represents an object for which you want to provide information for the recommendations result set.
To create the exception table, issue a CREATE TABLE statement similar to the following one. You can include other columns in the exception table, but you must include at least the columns that are shown. It is best to create a primary key on the exception table to prevent the duplication of rows in the exception table.
CREATE TABLE DSNACC.EXCEPT_TBL
(DBNAME CHAR(8) NOT NULL,
NAME CHAR(8) NOT NULL,
QUERYTYPE CHAR(40),
PRIMARY KEY (DBNAME,NAME))
CCSID EBCDIC;
The exception table columns have the following meanings:
 DBNAME
 The database name for an object in the exception table.
 NAME
 The table space name or index space name for an object in the exception table.
 QUERYTYPE
 The information that you want to place in the INEXCEPTTABLE column of the recommendations result
set.
If you put a null value in this column, DSNACCOX puts the value YES in the INEXCEPTTABLE column of the recommendations result set row for the object that matches the DBNAME and NAME values.
If you plan to put many rows in the exception table, create a nonunique index on DBNAME, NAME, and QUERYTYPE.
INSERT INTO DSNACC.EXCEPT_TBL VALUES('DSNDB04 ', 'STAFF ', 'IRRELEVANT');
INSERT INTO DSNACC.EXCEPT_TBL VALUES('DSN8D12A', 'DSN8S12D', 'CURRENT');
DBNAME='DSNDB04' AND INEXCEPTTABLE<>'IRRELEVANT'
Example of calling DSNACCOX
The following figure is a COBOL example that shows variable declarations and an SQL CALL for obtaining recommendations for objects in databases DSN8D12A and DSN8D12L. This example also outlines the steps that you need to perform to retrieve the two result sets that DSNACCOX returns. These result sets are described in DSNACCOX output
WORKINGSTORAGE SECTION.
***********************
* DSNACCOX PARAMETERS *
***********************
01 QUERYTYPE.
49 QUERYTYPELN PICTURE S9(4) COMP VALUE 40.
49 QUERYTYPEDTA PICTURE X(40) VALUE 'ALL'.
01 OBJECTTYPE.
49 OBJECTTYPELN PICTURE S9(4) COMP VALUE 3.
49 OBJECTTYPEDTA PICTURE X(3) VALUE 'ALL'.
01 ICTYPE.
49 ICTYPELN PICTURE S9(4) COMP VALUE 1.
49 ICTYPEDTA PICTURE X(1) VALUE 'B'.
01 CATLGSCHEMA.
49 CATLGSCHEMALN PICTURE S9(4) COMP VALUE 128.
49 CATLGSCHEMADTA PICTURE X(128) VALUE 'SYSIBM'.
01 LOCALSCHEMA.
49 LOCALSCHEMALN PICTURE S9(4) COMP VALUE 128.
49 LOCALSCHEMADTA PICTURE X(128) VALUE 'DSNACC'.
01 CHKLVL PICTURE S9(9) COMP VALUE +3.
01 CRITERIA.
49 CRITERIALN PICTURE S9(4) COMP VALUE 4096.
49 CRITERIADTA PICTURE X(4096) VALUE SPACES.
01 SPECIALPARM.
49 SPECIALPARMLN PICTURE S9(4) COMP VALUE 80.
49 SPECIALPARMDTA PICTURE X(80) VALUE SPACES.
01 CRUPDATEDPAGESPCT USAGE COMP2 VALUE +0.
01 CRUPDATEDPAGESABS PICTURE S9(9) COMP VALUE +0.
01 CRCHANGESPCT USAGE COMP2 VALUE +0.
01 CRDAYSNCLASTCOPY PICTURE S9(9) COMP VALUE +0.
01 ICRUPDATEDPAGESPCT USAGE COMP2 VALUE +0.
01 ICRUPDATEDPAGESABS PICTURE S9(9) COMP VALUE +0.
01 ICRCHANGESPCT PICTURE S9(9) COMP VALUE +0.
01 CRINDEXSIZE PICTURE S9(9) COMP VALUE +0.
01 RRTINSERTSPCT USAGE COMP2 VALUE +0.
01 RRTINSERTSABS PICTURE S9(9) COMP VALUE +0.
01 RRTDELETESPCT USAGE COMP2 VALUE +0.
01 RRTDELETESABS PICTURE S9(9) COMP VALUE +0.
01 RRTUNCLUSTINSPCT USAGE COMP2 VALUE +0.
01 RRTDISORGLOBPCT USAGE COMP2 VALUE +0.
01 RRTDATASPACERAT PICTURE S9(9) COMP VALUE +0.
01 RRTMASSDELLIMIT PICTURE S9(9) COMP VALUE +0.
01 RRTINDREFLIMIT PICTURE S9(9) COMP VALUE +0.
01 RRIINSERTSPCT USAGE COMP2 VALUE +0.
01 RRIINSERTSABS PICTURE S9(9) COMP VALUE +0.
01 RRIDELETESPCT USAGE COMP2 VALUE +0.
01 RRIDELETESABS PICTURE S9(9) COMP VALUE +0.
01 RRIAPPENDINSERTPCT USAGE COMP2 VALUE +0.
01 RRIPSEUDODELETEPCT USAGE COMP2 VALUE +0.
01 RRIMASSDELLIMIT PICTURE S9(9) COMP VALUE +0.
01 RRILEAFLIMIT PICTURE S9(9) COMP VALUE +0.
01 RRINUMLEVELSLIMIT PICTURE S9(9) COMP VALUE +0.
01 SRTINSDELUPDPCT PICTURE S9(9) COMP VALUE +0.
01 SRTINSDELUPDABS PICTURE S9(9) COMP VALUE +0.
01 SRTMASSDELLIMIT PICTURE S9(9) COMP VALUE +0.
01 SRIINSDELPCT USAGE COMP2 VALUE +0.
01 SRIINSDELABS PICTURE S9(9) COMP VALUE +0.
01 SRIMASSDELLIMIT PICTURE S9(9) COMP VALUE +0.
01 EXTENTLIMIT PICTURE S9(9) COMP VALUE +0.
01 LASTSTATEMENT.
49 LASTSTATEMENTLN PICTURE S9(4) COMP VALUE 8012.
49 LASTSTATEMENTDTA PICTURE X(8012) VALUE SPACES.
01 RETURNCODE PICTURE S9(9) COMP VALUE +0.
01 ERRORMSG.
49 ERRORMSGLN PICTURE S9(4) COMP VALUE 1331.
49 ERRORMSGDTA PICTURE X(1331) VALUE SPACES.
01 IFCARETCODE PICTURE S9(9) COMP VALUE +0.
01 IFCARESCODE PICTURE S9(9) COMP VALUE +0.
01 XSBYTES PICTURE S9(9) COMP VALUE +0.
*****************************************
* INDICATOR VARIABLES. *
* INITIALIZE ALL NONESSENTIAL INPUT *
* VARIABLES TO 1, TO INDICATE THAT THE *
* INPUT VALUE IS NULL. *
*****************************************
01 QUERYTYPEIND PICTURE S9(4) COMP4 VALUE +0.
01 OBJECTTYPEIND PICTURE S9(4) COMP4 VALUE +0.
01 ICTYPEIND PICTURE S9(4) COMP4 VALUE +0.
01 CATLGSCHEMAIND PICTURE S9(4) COMP4 VALUE 1.
01 LOCALSCHEMAIND PICTURE S9(4) COMP4 VALUE 1.
01 CHKLVLIND PICTURE S9(4) COMP4 VALUE 1.
01 CRITERIAIND PICTURE S9(4) COMP4 VALUE 1.
01 SPECIALPARMIND PICTURE S9(4) COMP4 VALUE 1.
01 CRUPDATEDPAGESPCTIND PICTURE S9(4) COMP4 VALUE 1.
01 CRUPDATEDPAGESABSIND PICTURE S9(4) COMP4 VALUE 1.
01 CRCHANGESPCTIND PICTURE S9(4) COMP4 VALUE 1.
01 CRDAYSNCLASTCOPYIND PICTURE S9(4) COMP4 VALUE 1.
01 ICRUPDATEDPAGESPCTIND PICTURE S9(4) COMP4 VALUE 1.
01 ICRUPDATEDPAGESABSIND PICTURE S9(4) COMP4 VALUE 1.
01 ICRCHANGESPCTIND PICTURE S9(4) COMP4 VALUE 1.
01 CRINDEXSIZEIND PICTURE S9(4) COMP4 VALUE 1.
01 RRTINSERTSPCTIND PICTURE S9(4) COMP4 VALUE 1.
01 RRTINSERTSABSIND PICTURE S9(4) COMP4 VALUE 1.
01 RRTDELETESPCTIND PICTURE S9(4) COMP4 VALUE 1.
01 RRTDELETESABSIND PICTURE S9(4) COMP4 VALUE 1.
01 RRTUNCLUSTINSPCTIND PICTURE S9(4) COMP4 VALUE 1.
01 RRTDISORGLOBPCTIND PICTURE S9(4) COMP4 VALUE 1.
01 RRTDATASPACERATIND PICTURE S9(4) COMP4 VALUE 1.
01 RRTMASSDELLIMITIND PICTURE S9(4) COMP4 VALUE 1.
01 RRTINDREFLIMITIND PICTURE S9(4) COMP4 VALUE 1.
01 RRIINSERTSPCTIND PICTURE S9(4) COMP4 VALUE 1.
01 RRIINSERTSABSIND PICTURE S9(4) COMP4 VALUE 1.
01 RRIDELETESPCTIND PICTURE S9(4) COMP4 VALUE 1.
01 RRIDELETESABSIND PICTURE S9(4) COMP4 VALUE 1.
01 RRIAPPENDINSERTPCTIND PICTURE S9(4) COMP4 VALUE 1.
01 RRIPSEUDODELETEPCTIND PICTURE S9(4) COMP4 VALUE 1.
01 RRIMASSDELLIMITIND PICTURE S9(4) COMP4 VALUE 1.
01 RRILEAFLIMITIND PICTURE S9(4) COMP4 VALUE 1.
01 RRINUMLEVELSLIMITIND PICTURE S9(4) COMP4 VALUE 1.
01 SRTINSDELUPDPCTIND PICTURE S9(4) COMP4 VALUE 1.
01 SRTINSDELUPDABSIND PICTURE S9(4) COMP4 VALUE 1.
01 SRTMASSDELLIMITIND PICTURE S9(4) COMP4 VALUE 1.
01 SRIINSDELPCTIND PICTURE S9(4) COMP4 VALUE 1.
01 SRIINSDELABSIND PICTURE S9(4) COMP4 VALUE 1.
01 SRIMASSDELLIMITIND PICTURE S9(4) COMP4 VALUE 1.
01 EXTENTLIMITIND PICTURE S9(4) COMP4 VALUE 1.
01 LASTSTATEMENTIND PICTURE S9(4) COMP4 VALUE +0.
01 RETURNCODEIND PICTURE S9(4) COMP4 VALUE +0.
01 ERRORMSGIND PICTURE S9(4) COMP4 VALUE +0.
01 IFCARETCODEIND PICTURE S9(4) COMP4 VALUE +0.
01 IFCARESCODEIND PICTURE S9(4) COMP4 VALUE +0.
01 XSBYTESIND PICTURE S9(4) COMP4 VALUE +0
PROCEDURE DIVISION.
*********************************************************
* SET VALUES FOR DSNACCOX INPUT PARAMETERS: *
*  USE THE CHKLVL PARAMETER TO CAUSE DSNACCOX TO CHECK *
* FOR RELATED TABLE SPACES WHEN PROCESSING INDEX *
* SPACES, AND DELETE RECOMMENDATION FOR INDEXSPACES *
* WHEN AN ACTION (SUCH AS REORG) ON THE TABLE SPACE *
* WILL ALSO CAUSE THE ACTION TO BE DONE ON THE INDEX *
* SPACE. (CHKLVL=64) *
*  USE THE CRITERIA PARAMETER TO CAUSE DSNACCOX TO *
* MAKE RECOMMENDATIONS ONLY FOR OBJECTS IN DATABASES *
* DSN8D91A AND DSN8D91L. *
*  FOR THE FOLLOWING PARAMETERS, SET THESE VALUES, *
* WHICH ARE LOWER THAN THE DEFAULTS: *
* CRUPDATEDPAGESPCT 4 *
* CRCHANGESPCT 2 *
* RRTINSDELUPDPCT 2 *
* RRTUNCLUSTINSPCT 5 *
* RRTDISORGLOBPCT 5 *
* RRIAPPENDINSERTPCT 5 *
* SRTINSDELUPDPCT 5 *
* SRIINSDELPCT 5 *
* EXTENTLIMIT 3 *
*  EXCLUDE CHECKING FOR THESE CRITERIA BY SET THE *
* FOLLOWING VALUES TO A NEGATIVE VALUE. *
* RRTMASSDELLIMIT 1 *
* RRIMASSDELLIMIT 1 *
*********************************************************
MOVE 64 TO CHKLVL.
MOVE SPACES TO CRITERIADTA.
MOVE 'DBNAME = ''DSN8D91A'' OR DBNAME = ''DSN8D91L'''
TO CRITERIADTA.
MOVE 46 TO CRITERIALN.
MOVE 4 TO CRUPDATEDPAGESPCT.
MOVE 2 TO CRCHANGESPCT.
MOVE 2 TO RRTINSERTSPCT.
MOVE 5 TO RRTUNCLUSTINSPCT.
MOVE 5 TO RRTDISORGLOBPCT.
MOVE 5 TO RRIAPPENDINSERTPCT.
MOVE 5 TO SRTINSDELUPDPCT.
MOVE 5 TO SRIINSDELPCT
MOVE 3 TO EXTENTLIMIT.
MOVE 1 TO RRTMASSDELLIMIT.
MOVE 1 TO RRIMASSDELLIMIT.
********************************
* INITIALIZE OUTPUT PARAMETERS *
********************************
MOVE SPACES TO LASTSTATEMENTDTA.
MOVE 1 TO LASTSTATEMENTLN.
MOVE 0 TO RETURNCODEO2.
MOVE SPACES TO ERRORMSGDTA.
MOVE 1 TO ERRORMSGLN.
MOVE 0 TO IFCARETCODE.
MOVE 0 TO IFCARESCODE.
MOVE 0 TO XSBYTES.
*******************************************************
* SET THE INDICATOR VARIABLES TO 0 FOR NONNULL INPUT *
* PARAMETERS (PARAMETERS FOR WHICH YOU DO NOT WANT *
* DSNACCOX TO USE DEFAULT VALUES) AND FOR OUTPUT *
* PARAMETERS. *
*******************************************************
MOVE 0 TO CHKLVLIND.
MOVE 0 TO CRITERIAIND.
MOVE 0 TO CRUPDATEDPAGESPCTIND.
MOVE 0 TO CRCHANGESPCTIND.
MOVE 0 TO RRTINSERTSPCTIND.
MOVE 0 TO RRTUNCLUSTINSPCTIND.
MOVE 0 TO RRTDISORGLOBPCTIND.
MOVE 0 TO RRIAPPENDINSERTPCTIND.
MOVE 0 TO SRTINSDELUPDPCTIND.
MOVE 0 TO SRIINSDELPCTIND.
MOVE 0 TO EXTENTLIMITIND.
MOVE 0 TO LASTSTATEMENTIND.
MOVE 0 TO RETURNCODEIND.
MOVE 0 TO ERRORMSGIND.
MOVE 0 TO IFCARETCODEIND.
MOVE 0 TO IFCARESCODEIND.
MOVE 0 TO XSBYTESIND.
MOVE 0 TO RRTMASSDELLIMITIND.
MOVE 0 TO RRIMASSDELLIMITIND.
*****************
* CALL DSNACCOX *
*****************
EXEC SQL
CALL SYSPROC.DSNACCOX
(:QUERYTYPE :QUERYTYPEIND,
:OBJECTTYPE :OBJECTTYPEIND,
:ICTYPE :ICTYPEIND,
:CATLGSCHEMA :CATLGSCHEMAIND,
:LOCALSCHEMA :LOCALSCHEMAIND,
:CHKLVL :CHKLVLIND,
:CRITERIA :CRITERIAIND,
:SPECIALPARM :SPECIALPARMIND,
:CRUPDATEDPAGESPCT :CRUPDATEDPAGESPCTIND,
:CRUPDATEDPAGESABS :CRUPDATEDPAGESABSIND,
:CRCHANGESPCT :CRCHANGESPCTIND,
:CRDAYSNCLASTCOPY :CRDAYSNCLASTCOPYIND,
:ICRUPDATEDPAGESPCT :ICRUPDATEDPAGESPCTIND,
:ICRUPDATEDPAGESABS :ICRUPDATEDPAGESABSIND,
:ICRCHANGESPCT :ICRCHANGESPCTIND,
:CRINDEXSIZE :CRINDEXSIZEIND,
:RRTINSERTSPCT :RRTINSERTSPCTIND,
:RRTINSERTSABS :RRTINSERSTSABSIND,
:RRTDELETESPCT :RRTDELETESPCTIND,
:RRTDELETESABS :RRTDELETESABSIND,
:RRTUNCLUSTINSPCT :RRTUNCLUSTINSPCTIND,
:RRTDISORGLOBPCT :RRTDISORGLOBPCTIND,
:RRTDATASPACERAT :RRTDATASPACERATIND,
:RRTMASSDELLIMIT :RRTMASSDELLIMITIND,
:RRTINDREFLIMIT :RRTINDREFLIMITIND,
:RRIINSERTSPCT :RRIINSERTSPCTIND,
:RRIINSERTSABS :RRIINSERTSABSIND,
:RRIDELETESPCT :RRIDELETESPCTIND,
:RRIDELETESABS :RRIDELETESABSIND,
:RRIAPPENDINSERTPCT :RRIAPPENDINSERTPCTIND,
:RRIPSEUDODELETEPCT :RRIPSEUDODELETEPCTIND,
:RRIMASSDELLIMIT :RRIMASSDELLIMITIND,
:RRILEAFLIMIT :RRILEAFLIMITIND,
:RRINUMLEVELSLIMIT :RRINUMLEVELSLIMITIND,
:SRTINSDELUPDPCT :SRTINSDELUPDPCTIND,
:SRTINSDELUPDABS :SRTINSDELUPDABSIND,
:SRTMASSDELLIMIT :SRTMASSDELLIMITIND,
:SRIINSDELPCT :SRIINSDELPCTIND,
:SRIINSDELABS :SRIINSDELABSIND,
:SRIMASSDELLIMIT :SRIMASSDELLIMITIND,
:EXTENTLIMIT :EXTENTLIMITIND,
:LASTSTATEMENT :LASTSTATEMENTIND,
:RETURNCODE :RETURNCODEIND,
:ERRORMSG :ERRORMSGIND,e
:IFCARETCODE :IFCARETCODEIND,
:IFCARESCODE :IFCARESCODEIND,
:XSBYTES :XSBYTESIND)
ENDEXEC.
*************************************************************
* ASSUME THAT THE SQL CALL RETURNED +466, WHICH MEANS THAT *
* RESULT SETS WERE RETURNED. RETRIEVE RESULT SETS. *
*************************************************************
* LINK EACH RESULT SET TO A LOCATOR VARIABLE
EXEC SQL ASSOCIATE LOCATORS (:LOC1, :LOC2)
WITH PROCEDURE SYSPROC.DSNACCOX
ENDEXEC.
* LINK A CURSOR TO EACH RESULT SET
EXEC SQL ALLOCATE C1 CURSOR FOR RESULT SET :LOC1
ENDEXEC.
EXEC SQL ALLOCATE C2 CURSOR FOR RESULT SET :LOC2
ENDEXEC.
* PERFORM FETCHES USING C1 TO RETRIEVE ALL ROWS FROM FIRST RESULT SET
* PERFORM FETCHES USING C2 TO RETRIEVE ALL ROWS FROM SECOND RESULT SET
DSNACCOX output
If DSNACCOX executes successfully, in addition to the output parameters described in Option descriptions, DSNACCOX returns two result sets.
The first result set contains the results from IFI COMMAND calls that DSNACCOX makes. The following table shows the format of the first result set.
Column name  Data type  Contents 

RS_SEQUENCE  INTEGER  Sequence number of the output line 
RS_DATA  CHAR(80)  A line of command output 
The second result set contains rows for table spaces, index spaces, or partitions, if both of the following conditions are true for the object:
 If the Criteria input parameter contains a search condition, and the search condition is true for the table space, index space, or partition.
 DSNACCOX recommends at least one action for the table space, index space, or partition.
The second result set contains one row for each nonpartitioned table space or nonpartitioning index space. For partitioned table spaces or partitioning indexes, the result set contains one row for each partition.
The following table shows the columns of a result set row.
Column name  Data type^{1}  Description 

DBNAME  VARCHAR(24) NOT NULL  Name of the database that contains the object. 
NAME  VARCHAR(128) NOT NULL  Table space name, index name, or index space name. Index space name is used if the row is added as a result of checking a restricted state and the index name is not available at the time. 
PARTITION  INTEGER NOT NULL  Data set number or partition number. 
INSTANCE  SMALLINT NOT NULL  Indicates whether the object is associated with a data set instance. 
CLONE  CHAR(1)  'Y' or 'N', 'Y' indicates a cloned object. 
OBJECTTYPE  CHAR(2) NOT NULL  Db2 object type:

INDEXSPACE  VARCHAR(24)  Index space name. 
CREATOR  VARCHAR(128)  Index creator name. 
OBJECTSTATUS  CHAR(40)  Status of the object:

IMAGECOPY  CHAR(4)  COPY
recommendation:

RUNSTATS  CHAR(3)  RUNSTATS recommendation: YES, NO, or Y**. Y** indicates that the table space contains volatile and nonvolatile tables. 
EXTENTS  CHAR(3)  Indicates whether the data sets for the object have exceeded ExtentLimit: YES or NO. 
REORG  CHAR(3)  REORG recommendation: YES or NO. 
INEXCEPTTABLE  CHAR(40)  A string that contains one of the following values:

ASSOCIATEDTS  VARCHAR(128)  If OBJECTTYPE is IX this value is the name of the table space that is associated with the index space. Otherwise null. 
COPYLASTTIME  TIMESTAMP  Timestamp of the last full or incremental image copy on the object. Null if COPY was never run, or if the last COPY execution is unknown. 
LOADRLASTTIME  TIMESTAMP  Timestamp of the last LOAD REPLACE on the object. Null if LOAD REPLACE was never run, or if the last LOAD REPLACE execution is unknown. 
REBUILDLASTTIME  TIMESTAMP  Timestamp of the last REBUILD INDEX on the object. Null if REBUILD INDEX was never run, or if the last REBUILD INDEX execution is unknown. 
CRUPDPGSPCT  DOUBLE  If
IMAGECOPY contains a value other than NO, the ratio of distinct updated pages to preformatted
pages, expressed as a percentage. Otherwise
null. If the ratio of distinct updated pages to preformatted pages, does not exceed the CRUpdatedPagesPct or ICRUpdatedPagesPct (for tables spaces only, when incremental copy is recommended), this value is null. 
CRUPDPGSABS  INTEGER  If
IMAGECOPY contains a value other than NO, the ratio of distinct updated pages to preformatted
pages. Otherwise
null. If the ratio of distinct updated pages to preformatted pages, does not exceed the value specified forCRUpdatedPagesAbs or ICRUpdatedPagesAbs (for tables spaces only, when incremental copy is recommended), this value is null. 
CRCPYCHGPCT  DOUBLE 
If the object is a table space and the value of IMAGECOPY is any value other than NO, the ratio
of the total number insert, update, and delete operations since the last image copy to the total
number of rows or LOBs in the table space or partition, expressed as a percentage.
If the object is an index and IMAGECOPY is YES, the ratio of the total number of insert and delete operations since the last image copy to the total number of entries in the index space or partition, expressed as a percentage. Otherwise null. If the ratio of the total number insert, update, and delete operations since the last image copy to the total number of rows or LOBs in the table space or partition does not exceed the value specified forCRChangesPct or ICRChangesPct (incremental copy is recommended), this value is null. 
CRDAYSCELSTCPY  INTEGER  If IMAGECOPY is YES,
the number of days since the last image copy. Otherwise null. If the number of days since the last image copy does not exceed the value specified for CrDaySncLastCopy, this value is null. 
CRINDEXSIZE  INTEGER  If IMAGECOPY is YES,
the number of active pages in the index space or partition. Otherwise null. If the number of active pages in the index space or partition does not exceed the value specified for CRIndexSize, this value is null. 
REORGLASTTIME  TIMESTAMP  Timestamp of the last REORG on the object. Null if REORG was never run, or if the last REORG execution was terminated. 
RRTINSERTSPCT  DOUBLE  If REORG is YES, the
ratio of the sum of insert
operations since
the last REORG to the total number of rows or LOBs in the table space or partition, expressed as a
percentage. Otherwise null. If the ratio of the sum of insert operations since the last REORG to the total number of rows or LOBs in the table space or partition does not exceed the value specified for RRTInsertsPct, this value is null. 
RRTINSERTSABS  INTEGER  If
REORG is YES, the sum of insert operations
since the last REORG to the total number of rows in the table space or partition. Otherwise
null. If the sum of insert operations since the last REORG to the total number of rows in the table space or partition does not exceed the value specified for RRTInsertsAbs, this value is null. 
RRTDELETESPCT  DOUBLE  If
REORG is YES, the ratio of the sum of delete operations since the last REORG to the total number of
rows in the table space or partition, expressed as a percentage. Otherwise null. If the ratio of the sum of delete operations since the last REORG to the total number of rows in the table space or partition does not exceed the value specified for RRTDeletesPct, this value is null. 
RRTDELETESABS  INTEGER  If
REORG is YES, the total number of delete operations since the last REORG on a table space or
partition. Otherwise null. If the total number of delete operations since the last REORG does not exceed the value specified for RRTDeletesAbs, this value is null. 
RRTUNCINSPCT  DOUBLE  If REORG is YES, the
ratio of the number of unclustered insert operations to the total number of rows or LOBs in the
table space or partition, expressed as a percentage. Otherwise null. If the ratio of the number of unclustered insert operations to the total number of rows or LOBs does not exceed the value specified for RRTUnclustInsPct, this value is null. 
RRTDISORGLOBPCT  DOUBLE  If REORG is YES, the
ratio of the number of imperfectly chunked LOBs to the total number of rows or LOBs in the table
space or partition, expressed as a percentage. Otherwise null. If the ratio of the number of imperfectly chunked LOBs to the total number of rows or LOBs in the table space or partition does not exceed the value of RRTDisorgLOBPct, this value is null 
RRTDATSPRAT  DOUBLE  If REORG is YES, the ratio of the number of SPACE allocated and the space used, exceed the value specified by the RRTDataSpaceRat threshold. Otherwise null. 
RRTMASSDELETE  INTEGER  If REORG is YES,
and the table space is a segmented table space or LOB table space, the number of mass deletes since
the last REORG or LOAD REPLACE. If REORG is YES, and the table space is nonsegmented, the number of
dropped tables since the last REORG or LOAD REPLACE. Otherwise null. If the number of dropped tables since the last REORG or LOAD REPLACE does not exceed the value specified for RRTMassDelLimit, this value is null. 
RRTINDREF  DOUBLE  If REORG is YES, the
ratio of the total number of overflow records that were created since the last REORG or LOAD REPLACE
to the total number of rows or LOBs in the table space or partition, expressed as a percentage.
Otherwise null. If the ratio of the total number of overflow records that were created since the last REORG or LOAD REPLACE to the total number of rows or LOBs does not exceed the value specified for RRTIndRef, this value is null. 
RRIINSERTSPCT  DOUBLE  If REORG is YES, the
ratio of the total number of insert operations since the last REORG to the total number of index
entries in the index space or partition, expressed as a percentage. Otherwise null. If the ratio of the total number of insert operations since the last REORG to the total number of index entries does not exceed the value specified for RRIInsertsPct, this value is null. 
RRIINSERTSABS  INTEGER  If
REORG is YES, the total number of insert operations since the last REORG. Otherwise null. If the total number of insert operations since the last REORG does not exceed the value specified for RRTInsertsAbs, this value is null. 
RRIDELETESPCT  DOUBLE  If
REORG is YES, the ratio of the total number of delete operations since the last REORG to the total
number of index entries in the index space or partition, expressed as a percentage. Otherwise
null. If the ratio of the total number of delete operations since the last REORG to the total number of index entries does not exceed the value specified for RRIDeletesPct, this value is null. 
RRIDELETABS  INTEGER  If
REORG is YES, the total number of delete operations since the last REORG. Otherwise null. If the total number of delete operations since the last REORG does not exceed the value specified for RRTDeletesAbs, this value is null. 
RRIAPPINSPCT  DOUBLE  If REORG is YES, the
ratio of the number of index entries that were inserted since the last REORG, REBUILD INDEX, or LOAD
REPLACE that had a key value greater than the maximum key value in the index space or partition, to
the number of index entries in the index space or partition, expressed as a percentage. Otherwise
null. If the ratio of the number of index entries that were inserted, which had a key value greater than the maximum key value, to the number of index entries does not exceed the value specified for RRIAppendInsertPct, this value is null. 
RRIPSDDELPCT  DOUBLE  If REORG is YES, the
ratio of the number of index entries that were pseudodeleted (the RID entry was marked as deleted)
since the last REORG, REBUILD INDEX, or LOAD REPLACE to the number of index entries in the index
space or partition, expressed as a percentage. Otherwise null. If the ratio of the number of index entries that were pseudodeleted since the last REORG, REBUILD INDEX, or LOAD REPLACE to the number of index entries does not exceed the value specified for RRIPseudoDeletePct, this value is null. 
RRIMASSDELETE  INTEGER  If REORG is YES, the
number of mass deletes from the index space or partition since the last REORG, REBUILD, or LOAD
REPLACE. Otherwise null. If the number of mass deletes from the index space or partition since the last REORG, REBUILD, or LOAD REPLACE does not exceed the value specified for RRIMassDelLimit, this value is null. 
RRILEAF  DOUBLE  If REORG is YES, the
ratio of the number of index page splits that occurred since the last REORG, REBUILD INDEX, or LOAD
REPLACE in which the higher part of the split page was far from the location of the original page,
to the total number of active pages in the index space or partition, expressed as a percentage.
Otherwise null. If the ratio of the number of index page splits that occurred since the last REORG, REBUILD INDEX, or LOAD REPLACE to the total number of active pages does not exceed the value specified for RRILeafLimit, this value is null. 
RRINUMLEVELS  INTEGER  If REORG is YES, the
number of levels in the index tree that were added or removed since the last REORG, REBUILD INDEX,
or LOAD REPLACE. Otherwise null. If the number of levels in the index tree that were added or removed does not exceed the value specified for RRINumLevelsLimit, this value is null. 
STATSLASTTIME  TIMESTAMP  Timestamp of the last RUNSTATS on the object. Null if RUNSTATS was never run, or if the last RUNSTATS execution was unknown. 
SRTINSDELUPDPCT  DOUBLE  If
RUNSTATS is YES, the ratio of the total number of insert, update, and delete operations since the
last RUNSTATS on a table space or partition, to the total number of rows or LOBs in the table space
or partition, expressed as a percentage. Otherwise null. If the ratio of the total number of insert, update, and delete operations since the last RUNSTATS to the total number of rows or LOBs does not exceed the value specified for SRTInsDelUpdPct, this value is null. 
SRTINSDELUPDABS  INTEGER  If RUNSTATS is YES, the
total number of insert, update, and delete operations since the last RUNSTATS on a table space or
partition. Otherwise null. If the total number of insert, update, and delete operations since the last RUNSTATS does not exceed the value specified for SRTInsDelUpdAbs, this value is null. 
SRTMASSDELETE  INTEGER  If RUNSTATS is YES, the
number of mass deletes from the table space or partition since the last REORG or LOAD REPLACE.
Otherwise null. If the number of mass deletes from the table space or partition since the last REORG or LOAD REPLACE does not exceed the value specified for SRTMassDelLimit, this value is null. 
SRIINSDELPCT  DOUBLE  If RUNSTATS is YES, the
ratio of the total number of insert and delete operations since the last RUNSTATS on the index space
or partition, to the total number of index entries in the index space or partition, expressed as a
percentage. Otherwise null. If the ratio of the total number of insert and delete operations since the last RUNSTATS, to the total number of index entries does not exceed the value specified for SRIInsDelPct, this value is null. 
SRIINSDELABS  INTEGER  If
RUNSTATS is YES, the number insert and delete operations since the last RUNSTATS on the index space
or partition. Otherwise null. If the total number of insert, update, and delete operations since the last RUNSTATS does not exceed the value specified for , this value is null. 
SRIMASSDELETE  INTEGER  If RUNSTATS is YES, the
number of mass deletes from the index space or partition since the last REORG, REBUILD INDEX, or
LOAD REPLACE. Otherwise, this value is null. If the number of mass deletes does not exceed the value specified for SRIMassDelete, this value is null. 
TOTALEXTENTS  SMALLINT  If EXTENTS is YES, the number of physical extents in the table
space, index space, or partition. Otherwise, this value is null. If the number of physical extents does not exceed the value specified for ExtentLimit, this value is null. 
RRIEMPTYLIMIT  DOUBLE 
This
column is returned when the function level is function level 500 or higher. Otherwise, this
column is returned only when the value of RRIEmptyLimit contains a plus
(+ ) sign.
If ObjectType is IX and REORG is YES, the ratio of the total number of leaf pages since the last REORG to the total number of pseudoempty pages in the index space or partition, expressed as a percentage. Otherwise null. If the ratio of the total number leaf pages since the last REORG to the total number of pseudoempty pages does not exceed the value specified for the RRIEmptyLimit input parameter, this value is null. 
RRTHASHOVRFLWRAT  DOUBLE 
This
column is returned when the function level is function level 500 or higher. Otherwise, this
column is returned only when the value of RRIEmptyLimit contains a plus
(+ ) sign.
If REORG is YES, the ratio of Hash Access overflow index entries to the total number of rows, expressed as a percentage. Otherwise null. If the ratio of Hash Access overflow index entries to the total number of rows does not exceed the value specified for RRTHashOvrFlwRatio or meet the criteria requirement, this value is null. 
RRTPBGSPACEPCT  DOUBLE  Reserved for future use. 
 Columns that are not marked as NOT NULL can contain null values.