DSNACCOX stored procedure

About DSNACCOX recommendations

• 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 real-time 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 WLM-established 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 system 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.

>>-CALL--DSNACCOX--(--+-QueryType-+-,--+-ObjectType-+-,--------->
                      '-NULL------'    '-NULL-------'     

>--+-ICType-+-,--+-CatlgSchema-+-,--+-LocalSchema-+-,----------->
   '-NULL---'    '-NULL--------'    '-NULL--------'     

>--+-ChkLvl-+-,--+-Criteria-+-,--+-SpecialParm-+-,-------------->
   '-NULL---'    '-NULL-----'    '---NULL------'     

>--+-CRUpdatedPagesPct-+-,--+-CRUpdatedPagesAbs-+-,------------->
   +-NULL--------------+    '-NULL--------------'     
   '- -1---------------'                              

>--+-CRChangesPct-+-,--+-CRDaySncLastCopy-+-,------------------->
   +-NULL---------+    +-NULL-------------+     
   '- -1----------'    '- -1--------------'     

>--+-ICRUpdatedPagesPct-+-,--+-ICRUpdatedPagesAbs-+-,----------->
   +-NULL---------------+    '-NULL---------------'     
   '- -1----------------'                               

>--+-ICRChangesPct-+-,--+-CRIndexSize-+-,----------------------->
   +-NULL----------+    +-NULL--------+     
   '- -1-----------'    '- -1---------'     

>--+-RRTInsertsPct-+-,--+-RRTInsertsAbs-+-,--------------------->
   +-NULL----------+    '-NULL----------'     
   '- -1-----------'                          

>--+-RRTDeletesPct-+-,--+-RRTDeletesAbs-+-,--------------------->
   +-NULL----------+    '-NULL----------'     
   '- -1-----------'                          

>--+-RRTUnclustInsPct-+-,--+-RRTDisorgLOBPct-+-,---------------->
   +-NULL-------------+    +-NULL------------+     
   '- -1--------------'    '- -1-------------'     

>--+-RRTDataSpaceRat-+-,--+-RRTMassDelLimit-+-,----------------->
   +-NULL------------+    +-NULL------------+     
   '- -1-------------'    '- -1-------------'     

>--+-RRTIndRefLimit-+-,--+-RRIInsertsPct-+-,-------------------->
   +-NULL-----------+    +-NULL----------+     
   '- -1------------'    '- -1-----------'     

>--+-RRIInsertsAbs-+-,--+-RRIDeletesPct-+-,--------------------->
   +-NULL----------+    +-NULL----------+     
   '- -1-----------'    '- -1-----------'     

>--+-RRIDeletesAbs-+-,--+-RRIAppendInsertPct-+-,---------------->
   '-NULL----------'    +-NULL---------------+     
                        '- -1----------------'     

>--+-RRIPseudoDeletePct-+-,--+-RRIMassDelLimit-+-,-------------->
   +-NULL---------------+    +-NULL------------+     
   '- -1----------------'    '- -1-------------'     

>--+-RRILeafLimit-+-,--+-RRINumLevelsLimit-+-,------------------>
   +-NULL---------+    +-NULL--------------+     
   '- -1----------'    '- -1---------------'     

>--+-SRTInsDelUpdPct-+-,--+-SRTInsDelUpdAbs-+-,----------------->
   +-NULL------------+    '-NULL------------'     
   '- -1-------------'                            

>--+-SRTMassDelLimit-+-,--+-SRIInsDelUpdPct-+-,----------------->
   +-NULL------------+    +-NULL------------+     
   '- -1-------------'    '- -1-------------'     

>--+-SRIInsDelUpdAbs-+-,--+-SRIMassDelLimit-+-,----------------->
   '-NULL------------'    +-NULL------------+     
                          '- -1-------------'     

>--+-ExtentLimit-+-,--LastStatement,--ReturnCode,--ErrorMsg,---->
   +-NULL--------+                                             
   '- -1---------'                                             

>--IFCARetCode,--IFCAResCode,--ExcessBytes--)------------------><

SpecialParm

|--+-'    '--------+-+-'    '-------------+---------------------|
   +-RRIEmptyLimit-+ +-RRTHashOvrFlwRatio-+   
   '- -1-----------' '- -1----------------'   

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 user-specified 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 REORG-pending status.
• The table space is in advisory REORG-pending 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 REORG-pending status.
• The index is in advisory REORG-pending 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 COPY-pending status.
• The table space is in informational COPY-pending 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 COPY-pending status.
• The index is in informational COPY-pending 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 over-ride the default SSDMultiplier when making a REORG recommendation for a table space or table space partition. The default value is 2 times RRTUnclustInsPct. If CHKLVL 2 is specified RRTUnclustInsPct * 5 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. A row is added to the result set for each object that has a restricted state, even if a row for the same object is already included in the result set because utility operations are recommended. So, the result set might contain duplicate rows for the same object when you specify this option.

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.

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

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 pseudo-empty 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 ' 10'. 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 pseudo-empty pages to the total number of leaf pages is returned in column RRIEMPTYLIMIT of the result set.

RRTHashOvrFlwRatio

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

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

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

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

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

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

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

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

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

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 25.0. A negative value turns off this criterion and RRTInsertsAbs.

RRTInsertsAbs

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

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

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

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. The default is 10.0. A negative value will turn off this criterion.

RRTDisorgLOBPct

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

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 2.0. A negative value turns off this criterion.

RRTMassDelLimit

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

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 non-data sharing environment.

RRIInsertsPct

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 30.0. A negative value turns off this criterion.

RRIInsertsAbs

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. A negative value turns off this criterion.

RRIDeletesPct

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.

RRIDeletesAbs

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. A negative value turns off this criterion.

RRIAppendInsertPct

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)

RRIInsertDeletePct is an input parameter of type DOUBLE. The default is 20.0. A negative value turns off this criterion.

RRIPseudoDeletePct

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 pseudo-deleted 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 in data sharing and 10.0 in non-data sharing environments. A negative value turns off this criterion.

RRIMassDelLimit

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

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

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

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

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

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

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

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

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

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. The ErrorMsg parameter contains the input parameters that might be incompatible.

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 real-time 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 non-zero 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.

(((QueryType='COPY' OR QueryType='ALL') AND
 (ObjectType='TS' OR ObjectType='ALL') AND
 (Object is not in Persistent Read Only (PRO) status) AND
(ICType='F' OR ICType='B')) AND
 (COPYLASTTIME IS NULL OR
  REORGLASTTIME>COPYLASTTIME OR
  LOADRLASTTIME>COPYLASTTIME OR
 (CURRENT DATE-COPYLASTTIME)>CRDaySncLastCopy OR     
 ((COPYUPDATEDPAGES×100)/NACTIVE>CRUpdatedPagesPct AND
  (COPYUPDATEDPAGES>CRupdatedPagesAbs)) OR
 (COPYCHANGES×100)/TOTALROWS>ICRChangesPct) OR 
 ((QueryType='RESTRICT' OR QueryType='ALL' ) AND
  (ObjectType='TS' OR ObjectType='ALL') AND 
The table space is in COPY-pending status or informational COPY-pending status))

The following figure shows the formula that DSNACCOX uses to recommend a full image copy on an index space.

(((QueryType='COPY' OR QueryType='ALL') AND
  (ObjectType='IX' OR ObjectType='ALL') AND
  
  (SYSINDEXES.COPY = 'Y')) AND
 (COPYLASTTIME IS NULL OR
  REORGLASTTIME>COPYLASTTIME OR
  LOADRLASTTIME>COPYLASTTIME OR
  REBUILDLASTTIME>COPYLASTTIME OR
  (CURRENT DATE-COPYLASTTIME)>CRDaySncLastCopy OR
  (NACTIVE>CRIndexSize AND
  (((COPYUPDATEDPAGES×100)/NACTIVE>CRUpdatedPagesPct) AND
   (COPYUPDATEDPAGES>CRUpdatedPagesAbs)) OR
  (COPYCHANGES×100)/TOTALENTRIES>CRChangesPct)) OR
 ((QueryType='RESTRICT' OR QueryType='ALL' ) AND
(ObjectType='IX' OR ObjectType='ALL') AND
The index space is in COPY-pending status or informational COPY-pending status))

The following figure shows the formula that DSNACCOX uses to recommend an incremental image copy on a table space.

((QueryType='COPY' OR QueryType='ALL') AND
 (ObjectType='TS' OR ObjectType='ALL') AND
 (Object is not in Persistent Read Only (PRO) status) AND
(ICType='F' OR ICType='B')) AND
(ICType='I') AND
 COPYLASTTIME IS NOT NULL) AND
(LOADRLASTTIME>COPYLASTTIME OR
 REORGLASTTIME>COPYLASTTIME OR
  ((COPYUPDATEDPAGES×100)/NACTIVE>ICRUpdatedPagesPct) AND
   (COPYUPDATEDPAGES>ICRUpdatedPagesAbs)) OR
  (COPYCHANGES×100)/TOTALROWS>ICRChangesPct)

The following figure shows the formula that DSNACCOX uses to recommend a REORG on a table space. If the table space is a LOB table space, and CHKLVL=1, the formula does not include EXTENTS>ExtentLimit.

(((QueryType='REORG' OR QueryType='ALL') AND
  (ObjectType='TS' OR ObjectType='ALL')) AND
 (Object is not in Persistent Read Only (PRO) status) AND
( REORGLASTTIME IS NULL AND LOADRLASTTIME IS NULL) OR
 (NACTIVE IS NULL OR NACTIVE > 5) AND
 ((((REORGINSERTS×100)/TOTALROWS>RRTInsertsPct) AND
   REORGINSERTS>RRTInsertsAbs) OR
  (((REORGDELETE×100)/TOTALROWS>RRTDeletesPct) AND
   REORGDELETE>RRTDeletesAbs) OR
  (REORGCLUSTERSENS > 0 AND  (REORGUNCLUSTINS×100)/TOTALROWS>RRTUnclustInsPct) OR
  (REORGDISORGLOB×100)/TOTALROWS>RRTDisorgLOBPct OR
  (SPACE×1024)/DATASIZE>RRTDataSpaceRat OR
  ((REORGNEARINDREF+REORGFARINDREF)×100)/TOTALROWS>RRTIndRefLimit OR
  REORGMASSDELETE>RRTMassDelLimit OR
  EXTENTS>ExtentLimit)) OR
((QueryType='REORG' OR QueryType='ALL') AND 
  ObjectType='ALL'1 AND
  overflow index for hash access is used2, and 
  (overflow index TOTALENTRY x 100) / TOTALROWS > RRTHashOvrFlwRatio)) OR 
 ((QueryType='RESTRICT' OR QueryType='ALL' ) AND
  (ObjectType='TS' OR ObjectType='ALL') AND
  The table space is in advisory or informational reorg pending status) 

Notes:

1. Both IX and TS must be selected, thus ObjectType=ALL must be specified to use this criterion. If only TS or IX is specified, and the value of the special parameter contains a plus sign (+) to indicate that the RRTHASHOVRFLWRAT column is to be included in the result set, an error message is issued. Otherwise, this criterion does not apply when only TS or IX is specified.
2. The overflow index is used when SYSINDEXES.HASH = 'Y' AND SYSINDEXSPACESTATS.REORGINDEXACCESS > 0.

The following figure shows the formula that DSNACCOX uses to recommend a REORG on an index space.

(((QueryType='REORG' OR QueryType='ALL') AND
  (ObjectType='IX' OR ObjectType='ALL') AND
  (REORGLASTTIME IS NULL AND REBUILDLASTTIME IS NULL) OR
  (NACTIVE IS NULL OR NACTIVE > 5) AND
  ((((REORGINSERTS×100)/TOTALENTRIES>RRIInsertsPct) AND
    REORGINSERTS>RRIInsertsAbs) OR
   (((REORGDELETE×100)/TOTALENTRIES>RRIDeletesPct) AND
    REORGDELETE>RRIDeletesAbs) OR
   (REORGAPPENDINSERT×100)/TOTALENTRIES>RRIAppendInsertPct OR
   (REORGPSEUDODELETES×100)/TOTALENTRIES>RRIPseudoDeletePct OR
   REORGMASSDELETE>RRIMassDeleteLimit OR
   (REORGLEAFFAR×100)/NACTIVE>RRILeafLimit OR
   REORGNUMLEVELS>RRINumLevelsLimit OR
(NPAGES>5 AND 
(NPAGES*100)/NLEAF>RRIEmptyLimit) OR 
   EXTENTS>ExtentLimit)) OR
 ((QueryType='RESTRICT' OR QueryType='ALL' ) AND
  (ObjectType='IX' OR ObjectType='ALL') AND
  An index is in advisory-REBUILD-pending stats (ARBDP)))

The following figure shows the formula that DSNACCOX uses to recommend RUNSTATS on a table space.

((QueryType='RUNSTATS' OR QueryType='ALL') AND
 (ObjectType='TS' OR ObjectType='ALL') AND
 Table Space is not cloned) AND
 (STATSLASTTIME IS NULL OR
  STATSLASTTIME<LOADRLASTTIME OR
  STATSLASTTIME<REORGLASTTIME OR
  (((STATSINSERTS+STATSDELETES+STATSUPDATES)×100)/TOTALROWS>SRTInsDelUpdPct AND
    (STATSINSERTS+STATSDELETES+STATSUPDATES)>SRTInsDelUpdAbs) OR
  STATSMASSDELETE>SRTMassDeleteLimit)))

The following figure shows the formula that DSNACCOX uses to recommend RUNSTATS on an index space.

((QueryType='RUNSTATS' OR QueryType='ALL') AND
 (ObjectType='IX' OR ObjectType='ALL')
 Table Space for the index is not cloned ) AND
 (STATSLASTTIME IS NULL OR
  STATSLASTTIME<LOADRLASTTIME OR
  STATSLASTTIME<REORGLASTTIME OR
  (((STATSINSERTS+STATSDELETES)×100)/TOTALENTRIES>SRIInsDelPct AND
    (STATSINSERTS+STATSDELETES)>SRIInsDelAbs) OR
  STATSMASSDELETE>SRIInsDelAbs)))

Using an exception table

An exception table is an optional, user-created 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.

After you create the exception table, insert a row for each object for which you want to include information in the INEXCEPTTABLE column.

INSERT INTO DSNACC.EXCEPT_TBL VALUES('DSNDB04 ', 'STAFF ', 'IRRELEVANT'); 
INSERT INTO DSNACC.EXCEPT_TBL VALUES('DSN8D10A', 'DSN8S10D', 'CURRENT');

To use the contents of INEXCEPTTABLE for filtering, include a condition that involves the INEXCEPTTABLE column in the search condition that you specify in your Criteria input parameter.

DBNAME='DSNDB04' AND INEXCEPTTABLE<>'IRRELEVANT'

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.

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. If ChkLvl 8 is specified, the result set might contain additional rows, including duplicate rows for the same object.

The following table shows the columns of a result set row.

Table 2. Result set row for second DSNACCOX result set

Column name	Data type1	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: 'TS' for a table space 'IX' for an index space
INDEXSPACE	VARCHAR(24)	Index space name.
CREATOR	VARCHAR(128)	Index creator name.
OBJECTSTATUS	CHAR(40)	Status of the object: ORPHANED, if the object is an index space with no corresponding table space, or if the object does not exist If the object is in a restricted state, one of the following values: TS=restricted-state, if OBJECTTYPE is TS IX=restricted-state, if OBJECTTYPE is IX LS=restricted-state, if object is LOB TS. LX=restricted-state, if object is XML TS. restricted-state is one of the status codes that appear in the output of the DISPLAY DATABASE command. Related information: DSNT362I -DISPLAY DATABASE (DB2) A, if the object is in an advisory state. L, if the object is a logical partition, but not in an advisory state. AL, if the object is a logical partition and in an advisory state.
IMAGECOPY	CHAR(4)	COPY recommendation: If the object is a table space, one of the following values: FULL Full image copy is recommended INC Incremental image copy is recommended NO Image copy is not recommended. If the object is an index, whether image copy is recommended: YES or NO
RUNSTATS	CHAR(3)	RUNSTATS recommendation: YES, NO, or Y**. Y** indicates that the table space contains volatile and non-volatile 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: Text that you specify in the QUERYTYPE column of the exception table. YES, if you put a row in the exception table for the object that this result set row represents, but you specify NULL in the QUERYTYPE column. NO, if the exception table exists but does not have a row for the object that this result set row represents. Null, if the exception table does not exist, or if the ChkLvl input parameter does not include the value 4.
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 pre-formatted pages, expressed as a percentage. Otherwise null. If the ratio of distinct updated pages to pre-formatted 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 pre-formatted pages. Otherwise null. If the ratio of distinct updated pages to pre-formatted 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 pseudo-deleted (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 pseudo-deleted 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 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 pseudo-empty 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 pseudo-empty pages does not exceed the value specified for the RRIEmptyLimit input parameter, this value is null.
RRTHASHOVRFLWRAT	DOUBLE	This is column is returned only when the value of RRTHashOvrFlwRatio 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.

1. Columns that are not marked as NOT NULL can contain null values.