DSNACCOX stored procedure
The DB2® real-time statistics stored procedure (DSNACCOX) is a sample stored procedure that makes recommendations to help you maintain your DB2 databases.
- 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 real-time 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 real-time statistics tables. However, you can override this default through input parameters.
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.
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. If the table space is a LOB table space, and CHKLVL=1, the formula does not include EXTENTS>ExtentLimit.
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.
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, 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'
Example
The following figure is a COBOL example that shows variable declarations and an SQL CALL for obtaining recommendations for objects in databases DSN8D10A and DSN8D10L. 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
WORKING-STORAGE SECTION.
***********************
* DSNACCOX PARAMETERS *
***********************
01 QUERYTYPE.
49 QUERYTYPE-LN PICTURE S9(4) COMP VALUE 40.
49 QUERYTYPE-DTA PICTURE X(40) VALUE 'ALL'.
01 OBJECTTYPE.
49 OBJECTTYPE-LN PICTURE S9(4) COMP VALUE 3.
49 OBJECTTYPE-DTA PICTURE X(3) VALUE 'ALL'.
01 ICTYPE.
49 ICTYPE-LN PICTURE S9(4) COMP VALUE 1.
49 ICTYPE-DTA PICTURE X(1) VALUE 'B'.
01 CATLGSCHEMA.
49 CATLGSCHEMA-LN PICTURE S9(4) COMP VALUE 128.
49 CATLGSCHEMA-DTA PICTURE X(128) VALUE 'SYSIBM'.
01 LOCALSCHEMA.
49 LOCALSCHEMA-LN PICTURE S9(4) COMP VALUE 128.
49 LOCALSCHEMA-DTA PICTURE X(128) VALUE 'DSNACC'.
01 CHKLVL PICTURE S9(9) COMP VALUE +3.
01 CRITERIA.
49 CRITERIA-LN PICTURE S9(4) COMP VALUE 4096.
49 CRITERIA-DTA PICTURE X(4096) VALUE SPACES.
01 SPECIALPARM.
49 SPECIALPARM-LN PICTURE S9(4) COMP VALUE 80.
49 SPECIALPARM-DTA PICTURE X(80) VALUE SPACES.
01 CRUPDATEDPAGESPCT USAGE COMP-2 VALUE +0.
01 CRUPDATEDPAGESABS PICTURE S9(9) COMP VALUE +0.
01 CRCHANGESPCT USAGE COMP-2 VALUE +0.
01 CRDAYSNCLASTCOPY PICTURE S9(9) COMP VALUE +0.
01 ICRUPDATEDPAGESPCT USAGE COMP-2 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 COMP-2 VALUE +0.
01 RRTINSERTSABS PICTURE S9(9) COMP VALUE +0.
01 RRTDELETESPCT USAGE COMP-2 VALUE +0.
01 RRTDELETESABS PICTURE S9(9) COMP VALUE +0.
01 RRTUNCLUSTINSPCT USAGE COMP-2 VALUE +0.
01 RRTDISORGLOBPCT USAGE COMP-2 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 COMP-2 VALUE +0.
01 RRIINSERTSABS PICTURE S9(9) COMP VALUE +0.
01 RRIDELETESPCT USAGE COMP-2 VALUE +0.
01 RRIDELETESABS PICTURE S9(9) COMP VALUE +0.
01 RRIAPPENDINSERTPCT USAGE COMP-2 VALUE +0.
01 RRIPSEUDODELETEPCT USAGE COMP-2 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 COMP-2 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 LASTSTATEMENT-LN PICTURE S9(4) COMP VALUE 8012.
49 LASTSTATEMENT-DTA PICTURE X(8012) VALUE SPACES.
01 RETURNCODE PICTURE S9(9) COMP VALUE +0.
01 ERRORMSG.
49 ERRORMSG-LN PICTURE S9(4) COMP VALUE 1331.
49 ERRORMSG-DTA 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 NON-ESSENTIAL INPUT *
* VARIABLES TO -1, TO INDICATE THAT THE *
* INPUT VALUE IS NULL. *
*****************************************
01 QUERYTYPE-IND PICTURE S9(4) COMP-4 VALUE +0.
01 OBJECTTYPE-IND PICTURE S9(4) COMP-4 VALUE +0.
01 ICTYPE-IND PICTURE S9(4) COMP-4 VALUE +0.
01 CATLGSCHEMA-IND PICTURE S9(4) COMP-4 VALUE -1.
01 LOCALSCHEMA-IND PICTURE S9(4) COMP-4 VALUE -1.
01 CHKLVL-IND PICTURE S9(4) COMP-4 VALUE -1.
01 CRITERIA-IND PICTURE S9(4) COMP-4 VALUE -1.
01 SPECIALPARM-IND PICTURE S9(4) COMP-4 VALUE -1.
01 CRUPDATEDPAGESPCT-IND PICTURE S9(4) COMP-4 VALUE -1.
01 CRUPDATEDPAGESABS-IND PICTURE S9(4) COMP-4 VALUE -1.
01 CRCHANGESPCT-IND PICTURE S9(4) COMP-4 VALUE -1.
01 CRDAYSNCLASTCOPY-IND PICTURE S9(4) COMP-4 VALUE -1.
01 ICRUPDATEDPAGESPCT-IND PICTURE S9(4) COMP-4 VALUE -1.
01 ICRUPDATEDPAGESABS-IND PICTURE S9(4) COMP-4 VALUE -1.
01 ICRCHANGESPCT-IND PICTURE S9(4) COMP-4 VALUE -1.
01 CRINDEXSIZE-IND PICTURE S9(4) COMP-4 VALUE -1.
01 RRTINSERTSPCT-IND PICTURE S9(4) COMP-4 VALUE -1.
01 RRTINSERTSABS-IND PICTURE S9(4) COMP-4 VALUE -1.
01 RRTDELETESPCT-IND PICTURE S9(4) COMP-4 VALUE -1.
01 RRTDELETESABS-IND PICTURE S9(4) COMP-4 VALUE -1.
01 RRTUNCLUSTINSPCT-IND PICTURE S9(4) COMP-4 VALUE -1.
01 RRTDISORGLOBPCT-IND PICTURE S9(4) COMP-4 VALUE -1.
01 RRTDATASPACERAT-IND PICTURE S9(4) COMP-4 VALUE -1.
01 RRTMASSDELLIMIT-IND PICTURE S9(4) COMP-4 VALUE -1.
01 RRTINDREFLIMIT-IND PICTURE S9(4) COMP-4 VALUE -1.
01 RRIINSERTSPCT-IND PICTURE S9(4) COMP-4 VALUE -1.
01 RRIINSERTSABS-IND PICTURE S9(4) COMP-4 VALUE -1.
01 RRIDELETESPCT-IND PICTURE S9(4) COMP-4 VALUE -1.
01 RRIDELETESABS-IND PICTURE S9(4) COMP-4 VALUE -1.
01 RRIAPPENDINSERTPCT-IND PICTURE S9(4) COMP-4 VALUE -1.
01 RRIPSEUDODELETEPCT-IND PICTURE S9(4) COMP-4 VALUE -1.
01 RRIMASSDELLIMIT-IND PICTURE S9(4) COMP-4 VALUE -1.
01 RRILEAFLIMIT-IND PICTURE S9(4) COMP-4 VALUE -1.
01 RRINUMLEVELSLIMIT-IND PICTURE S9(4) COMP-4 VALUE -1.
01 SRTINSDELUPDPCT-IND PICTURE S9(4) COMP-4 VALUE -1.
01 SRTINSDELUPDABS-IND PICTURE S9(4) COMP-4 VALUE -1.
01 SRTMASSDELLIMIT-IND PICTURE S9(4) COMP-4 VALUE -1.
01 SRIINSDELPCT-IND PICTURE S9(4) COMP-4 VALUE -1.
01 SRIINSDELABS-IND PICTURE S9(4) COMP-4 VALUE -1.
01 SRIMASSDELLIMIT-IND PICTURE S9(4) COMP-4 VALUE -1.
01 EXTENTLIMIT-IND PICTURE S9(4) COMP-4 VALUE -1.
01 LASTSTATEMENT-IND PICTURE S9(4) COMP-4 VALUE +0.
01 RETURNCODE-IND PICTURE S9(4) COMP-4 VALUE +0.
01 ERRORMSG-IND PICTURE S9(4) COMP-4 VALUE +0.
01 IFCARETCODE-IND PICTURE S9(4) COMP-4 VALUE +0.
01 IFCARESCODE-IND PICTURE S9(4) COMP-4 VALUE +0.
01 XSBYTES-IND PICTURE S9(4) COMP-4 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 CRITERIA-DTA.
MOVE 'DBNAME = ''DSN8D91A'' OR DBNAME = ''DSN8D91L'''
TO CRITERIA-DTA.
MOVE 46 TO CRITERIA-LN.
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 LASTSTATEMENT-DTA.
MOVE 1 TO LASTSTATEMENT-LN.
MOVE 0 TO RETURNCODE-O2.
MOVE SPACES TO ERRORMSG-DTA.
MOVE 1 TO ERRORMSG-LN.
MOVE 0 TO IFCARETCODE.
MOVE 0 TO IFCARESCODE.
MOVE 0 TO XSBYTES.
*******************************************************
* SET THE INDICATOR VARIABLES TO 0 FOR NON-NULL INPUT *
* PARAMETERS (PARAMETERS FOR WHICH YOU DO NOT WANT *
* DSNACCOX TO USE DEFAULT VALUES) AND FOR OUTPUT *
* PARAMETERS. *
*******************************************************
MOVE 0 TO CHKLVL-IND.
MOVE 0 TO CRITERIA-IND.
MOVE 0 TO CRUPDATEDPAGESPCT-IND.
MOVE 0 TO CRCHANGESPCT-IND.
MOVE 0 TO RRTINSERTSPCT-IND.
MOVE 0 TO RRTUNCLUSTINSPCT-IND.
MOVE 0 TO RRTDISORGLOBPCT-IND.
MOVE 0 TO RRIAPPENDINSERTPCT-IND.
MOVE 0 TO SRTINSDELUPDPCT-IND.
MOVE 0 TO SRIINSDELPCT-IND.
MOVE 0 TO EXTENTLIMIT-IND.
MOVE 0 TO LASTSTATEMENT-IND.
MOVE 0 TO RETURNCODE-IND.
MOVE 0 TO ERRORMSG-IND.
MOVE 0 TO IFCARETCODE-IND.
MOVE 0 TO IFCARESCODE-IND.
MOVE 0 TO XSBYTES-IND.
MOVE 0 TO RRTMASSDELLIMIT-IND.
MOVE 0 TO RRIMASSDELLIMIT-IND.
*****************
* CALL DSNACCOX *
*****************
EXEC SQL
CALL SYSPROC.DSNACCOX
(:QUERYTYPE :QUERYTYPE-IND,
:OBJECTTYPE :OBJECTTYPE-IND,
:ICTYPE :ICTYPE-IND,
:CATLGSCHEMA :CATLGSCHEMA-IND,
:LOCALSCHEMA :LOCALSCHEMA-IND,
:CHKLVL :CHKLVL-IND,
:CRITERIA :CRITERIA-IND,
:SPECIALPARM :SPECIALPARM-IND,
:CRUPDATEDPAGESPCT :CRUPDATEDPAGESPCT-IND,
:CRUPDATEDPAGESABS :CRUPDATEDPAGESABS-IND,
:CRCHANGESPCT :CRCHANGESPCT-IND,
:CRDAYSNCLASTCOPY :CRDAYSNCLASTCOPY-IND,
:ICRUPDATEDPAGESPCT :ICRUPDATEDPAGESPCT-IND,
:ICRUPDATEDPAGESABS :ICRUPDATEDPAGESABS-IND,
:ICRCHANGESPCT :ICRCHANGESPCT-IND,
:CRINDEXSIZE :CRINDEXSIZE-IND,
:RRTINSERTSPCT :RRTINSERTSPCT-IND,
:RRTINSERTSABS :RRTINSERSTSABS-IND,
:RRTDELETESPCT :RRTDELETESPCT-IND,
:RRTDELETESABS :RRTDELETESABS-IND,
:RRTUNCLUSTINSPCT :RRTUNCLUSTINSPCT-IND,
:RRTDISORGLOBPCT :RRTDISORGLOBPCT-IND,
:RRTDATASPACERAT :RRTDATASPACERAT-IND,
:RRTMASSDELLIMIT :RRTMASSDELLIMIT-IND,
:RRTINDREFLIMIT :RRTINDREFLIMIT-IND,
:RRIINSERTSPCT :RRIINSERTSPCT-IND,
:RRIINSERTSABS :RRIINSERTSABS-IND,
:RRIDELETESPCT :RRIDELETESPCT-IND,
:RRIDELETESABS :RRIDELETESABS-IND,
:RRIAPPENDINSERTPCT :RRIAPPENDINSERTPCT-IND,
:RRIPSEUDODELETEPCT :RRIPSEUDODELETEPCT-IND,
:RRIMASSDELLIMIT :RRIMASSDELLIMIT-IND,
:RRILEAFLIMIT :RRILEAFLIMIT-IND,
:RRINUMLEVELSLIMIT :RRINUMLEVELSLIMIT-IND,
:SRTINSDELUPDPCT :SRTINSDELUPDPCT-IND,
:SRTINSDELUPDABS :SRTINSDELUPDABS-IND,
:SRTMASSDELLIMIT :SRTMASSDELLIMIT-IND,
:SRIINSDELPCT :SRIINSDELPCT-IND,
:SRIINSDELABS :SRIINSDELABS-IND,
:SRIMASSDELLIMIT :SRIMASSDELLIMIT-IND,
:EXTENTLIMIT :EXTENTLIMIT-IND,
:LASTSTATEMENT :LASTSTATEMENT-IND,
:RETURNCODE :RETURNCODE-IND,
:ERRORMSG :ERRORMSG-IND,e
:IFCARETCODE :IFCARETCODE-IND,
:IFCARESCODE :IFCARESCODE-IND,
:XSBYTES :XSBYTES-IND)
END-EXEC.
*************************************************************
* 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
END-EXEC.
* LINK A CURSOR TO EACH RESULT SET
EXEC SQL ALLOCATE C1 CURSOR FOR RESULT SET :LOC1
END-EXEC.
EXEC SQL ALLOCATE C2 CURSOR FOR RESULT SET :LOC2
END-EXEC.
* 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. 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.
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:
|
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 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:
|
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. |
- Columns that are not marked as NOT NULL can contain null values.