DSNACCOR stored procedure (deprecated)

Environment

DSNACCOR must run in a WLM-established stored procedure address space. The DSNWLM_GENERAL core WLM environment is a suitable environment for this stored procedure.

DSNACCOR is installed and configured by installation job DSNTIJRT, which binds the package for DSNACCOR with isolation UR to avoid lock contention.

Authorization required

To execute the CALL DSNACCOR 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 DSNACCOR
• 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 the real-time statistics tables
• Select authority on catalog tables
• The DISPLAY system privilege

Syntax diagram

The following syntax diagram shows the CALL statement for invoking DSNACCOR. Because the linkage convention for DSNACCOR 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--DSNACCOR--(--+-QueryType-+-,--+-ObjectType-+-,--------->
                      '-NULL------'    '-NULL-------'     

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

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

>--+-Restricted-+-,--+-CRUpdatedPagesPct-+-,-------------------->
   '-NULL-------'    '-NULL--------------'     

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

>--+-ICRUpdatedPagesPct-+-,--+-ICRChangesPct-+-,---------------->
   '-NULL---------------'    '-NULL----------'     

>--+-CRIndexSize-+-,--+-RRTInsDelUpdPct-+-,--------------------->
   '-NULL--------'    '-NULL------------'     

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

>--+-RRTMassDelLimit-+-,--+-RRTIndRefLimit-+-,------------------>
   '-NULL------------'    '-NULL-----------'     

>--+-RRIInsertDeletePct-+-,--+-RRIAppendInsertPct-+-,----------->
   '-NULL---------------'    '-NULL---------------'     

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

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

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

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

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

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

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

Option descriptions

In the following option descriptions, the default value for an input parameter is the value that DSNACCOR uses if you specify a null value.

QueryType

Specifies the types of actions that DSNACCOR 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 to perform an image copy.

RUNSTATS

Makes a recommendation on whether to perform RUNSTATS.

REORG

Makes a recommendation on whether to perform REORG. Choosing this value causes DSNACCOR 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.

QueryType is an input parameter of type VARCHAR(40). The default is ALL.

ObjectType

Specifies the types of objects for which DSNACCOR 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 is ALL.

ICType

Specifies the types of image copies for which DSNACCOR 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.

StatsSchema

Specifies the qualifier for the real-time statistics table names. StatsSchema is an input parameter of type VARCHAR(128). The default is SYSIBM.

CatlgSchema

Specifies the qualifier for DB2 catalog table names. CatlgSchema is an input parameter of type VARCHAR(128). The default is SYSIBM.

LocalSchema

Specifies the qualifier for the names of tables that DSNACCOR creates. LocalSchema is an input parameter of type VARCHAR(128). The default is DSNACC.

ChkLvl

Specifies the types of checking that DSNACCOR performs, and indicates whether to include objects that fail those checks in the DSNACCOR recommendations result set. This value is the sum of any combination of the following values:

0

DSNACCOR performs none of the following actions.

1

For objects that are listed in the recommendations result set, check the SYSTABLESPACE or SYSINDEXES catalog tables to ensure that those objects have not been deleted. If value 16 is not also chosen, exclude rows for the deleted objects from the recommendations result set.

DSNACCOR excludes objects from the recommendations result set if those objects are not in the SYSTABLESPACE or SYSINDEXES catalog tables.

When this setting is specified, DSNACCOR does not use EXTENTS>ExtentLimit to determine whether a LOB table space should be reorganized.

2

For index spaces that are listed in the recommendations result set, check the SYSTABLES, SYSTABLESPACE, and SYSINDEXES catalog tables to determine the name of the table space that is associated with each index space.

Choosing this value causes DSNACCOR to also check for rows in the recommendations result set for objects that have been deleted but have entries in the real-time statistics tables (value 1). This means that if value 16 is not also chosen, rows for deleted objects are excluded from the recommendations result set.

4

Check whether rows that are in the DSNACCOR 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 whether objects that have rows in the recommendations result set are restricted. Indicate the restricted status in the OBJECTSTATUS column of the result set. 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

For objects that are listed in the recommendations result set, check the SYSTABLESPACE or SYSINDEXES catalog tables to ensure that those objects have not been deleted (value 1). In result set rows for deleted objects, specify the word ORPHANED in the OBJECTSTATUS column.

32

Exclude rows from the DSNACCOR recommendations result set for index spaces for which the related table spaces have been recommended for REORG. Choosing this value causes DSNACCOR to perform the actions for values 1 and 2.

64

For index spaces that are listed in the DSNACCOR 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.

ChkLvl is an input parameter of type INTEGER. The default is 7 (values 1+2+4).

Criteria

Narrows the set of objects for which DSNACCOR 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 DSNACCOR 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.

Restricted

A parameter that is reserved for future use. Specify the null value for this parameter. Restricted is an input parameter of type VARCHAR(80).

CRUpdatedPagesPct

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, DSNACCOR 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.

See item 2 in Figure 1. If both of the following conditions are true for an index space, DSNACCOR 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 number of active pages in the index space or partition is greater than CRIndexSize. See items 2 and 3 in Figure 2.

CRUpdatedPagesPct is an input parameter of type INTEGER. The default is 20.

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, DSNACCOR 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.

See item 3 in Figure 1. If both of the following conditions are true for an index table space, DSNACCOR 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.

See items 2 and 4 in Figure 2. CRChangesPct is an input parameter of type INTEGER. The default is 10.

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, DSNACCOR recommends an image copy. (See item 1 in Figure 1 and item 1 in Figure 2.) CRDaySncLastCopy is an input parameter of type INTEGER. The default is 7.

ICRUpdatedPagesPct

Specifies a criterion for recommending an incremental image copy on a table space. If the following condition is true, DSNACCOR 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 CRUpdatedPagesPct.

(See item 1 in Figure 3.) ICRUpdatedPagesPct is an input parameter of type INTEGER. The default is 1.

ICRChangesPct

Specifies a criterion for recommending an incremental image copy on a table space. If the following condition is true, DSNACCOR 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.

(See item 2 in Figure 3.) ICRChangesPct is an input parameter of type INTEGER. The default is 1.

CRIndexSize

Specifies, when combined with CRUpdatedPagesPct or CRChangesPct, a criterion for recommending a full image copy on an index space. (See items 2, 3, and 4 in Figure 2.) CRIndexSize is an input parameter of type INTEGER. The default is 50.

RRTInsDelUpdPct

Specifies a criterion for recommending that the REORG utility is to be run on a table space. If the following condition is true, DSNACCOR 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 RRTInsDelUpdPct

(See item 1 in Figure 4.) RRTInsDelUpdPct is an input parameter of type INTEGER. The default is 20.

RRTUnclustInsPct

Specifies a criterion for recommending that the REORG utility is to be run on a table space. If the following condition is true, DSNACCOR 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.

(See item 2 in Figure 4.) RRTUnclustInsPct is an input parameter of type INTEGER. The default is 10.

RRTDisorgLOBPct

Specifies a criterion for recommending that the REORG utility is to be run on a table space. If the following condition is true, DSNACCOR 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.

(See item 3 in Figure 4.) RRTDisorgLOBPct is an input parameter of type INTEGER. The default is 10.

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, DSNACCOR recommends running REORG:

• The number 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

(See item 5 in Figure 4.) 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, DSNACCOR 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)

(See item 4 in Figure 4.) RRTIndRefLimit is an input parameter of type INTEGER. The default is 10.

RRIInsertDeletePct

Specifies a criterion for recommending that the REORG utility is to be run on an index space. If the following value is greater than RRIInsertDeletePct, DSNACCOR recommends running REORG:

• The sum of the number of index entries that were inserted and deleted since the last REORG, divided by the total number of index entries in the index space or partition (expressed as a percentage)

(See item 1 in Figure 5.) This is an input parameter of type INTEGER. The default is 20.

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, DSNACCOR 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)

(See item 2 in Figure 5.) RRIInsertDeletePct is an input parameter of type INTEGER. The default is 10.

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, DSNACCOR 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)

(See item 3 in Figure 5.) RRIPseudoDeletePct is an input parameter of type INTEGER. The default is 10.

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, DSNACCOR recommends running REORG.

(See item 4 in Figure 5.) RRIMassDelLimit is an input parameter of type INTEGER. The default is 0.

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, DSNACCOR recommends running REORG:

• The number of index page splits that occurred since the last REORG, REBUILD INDEX, or LOAD REPLACE that resulted in a large separation between the parts of the original page, divided by the total number of active pages in the index space or partition (expressed as a percentage)

(See item 5 in Figure 5.) RRILeafLimit is an input parameter of type INTEGER. The default is 10.

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, DSNACCOR 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

(See item 6 in Figure 5.) RRINumLevelsLimit is an input parameter of type INTEGER. The default is 0.

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, DSNACCOR 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.

(See items 1 and 2 in Figure 6.) SRTInsDelUpdPct is an input parameter of type INTEGER. The default is 20.

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, DSNACCOR 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.

(See items 1 and 2 in Figure 6.) 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, DSNACCOR 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.

(See item 3 in Figure 6.) SRTMassDelLimit is an input parameter of type INTEGER. The default is 0.

SRIInsDelUpdPct

Specifies, when combined with SRIInsDelUpdAbs, a criterion for recommending that the RUNSTATS utility is to be run on an index space. If both of the following conditions are true, DSNACCOR 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 SRIInsDelUpdPct.
• The sum of the number of inserted and deleted index entries since the last RUNSTATS on an index space or partition is greater than SRIInsDelUpdAbs.

(See items 1 and 2 in Figure 7.) SRIInsDelUpdPct is an input parameter of type INTEGER. The default is 20.

SRIInsDelUpdAbs

Specifies, when combined with SRIInsDelUpdPct, a criterion for recommending that the RUNSTATS utility is to be run on an index space. If the following condition is true, DSNACCOR 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 SRIInsDelUpdPct.
• The sum of the number of inserted and deleted index entries since the last RUNSTATS on an index space or partition is greater than SRIInsDelUpdAbs,

(See items 1 and 2 in Figure 7.) SRIInsDelUpdAbs 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, DSNACCOR recommends running RUNSTATS.

(See item 3 in Figure 7.) SRIMassDelLimit is an input parameter of type INTEGER. The default is 0.

ExtentLimit

Specifies a criterion for recommending that the REORG utility is to be run on a table space or index space. Also specifies that DSNACCOR is to warn the user that the table space or index space has used too many extents. DSNACCOR 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.

(See Figure 8.) ExtentLimit is an input parameter of type INTEGER. The default is 50.

LastStatement

When DSNACCOR 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 DSNACCOR execution. Possible values are:

0

DSNACCOR executed successfully. The ErrorMsg parameter contains the approximate percentage of the total number of objects in the subsystem that have information in the real-time statistics tables.

4

DSNACCOR completed, but one or more input parameters might be incompatible. The ErrorMsg parameter contains the input parameters that might be incompatible.

8

DSNACCOR terminated with errors. The ErrorMsg parameter contains a message that describes the error.

12

DSNACCOR 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

DSNACCOR terminated because it could not access one or more of the real-time statistics tables. The ErrorMsg parameter contains the names of the tables that DSNACCOR could not access.

15

DSNACCOR terminated because it encountered a problem with one of the declared temporary tables that it defines and uses.

16

DSNACCOR terminated because it could not define a declared temporary table. No table spaces were defined in the TEMP database.

NULL

DSNACCOR terminated but could not set a return code.

ReturnCode is an output parameter of type INTEGER.

ErrorMsg

Contains information about DSNACCOR execution. If DSNACCOR runs successfully (ReturnCode=0), this field contains the approximate percentage of objects in the subsystem that are in the real-time statistics tables. Otherwise, this field contains error messages. ErrorMsg is an output parameter of type VARCHAR(1331).

IFCARetCode

Contains the return code from an IFI COMMAND call. DSNACCOR 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.

ExcessBytes

Contains the number of bytes of information that did not fit in the IFI return area after an IFI COMMAND call. ExcessBytes is an output parameter of type INTEGER.

DSNACCOR formulas for recommending actions

The following formulas specify the criteria that DSNACCOR uses for its recommendations and warnings. The variables in italics are DSNACCOR input parameters. The capitalized variables are columns of the SYSIBM.SYSTABLESPACESTATS or SYSIBM.SYSINDEXSPACESTATS tables. The numbers to the right of selected items are reference numbers for the option descriptions in Option descriptions.

The figure below shows the formula that DSNACCOR uses to recommend a full image copy on a table space.

((QueryType='COPY' OR QueryType='ALL') AND
 (ObjectType='TS' OR ObjectType='ALL') AND
 ICType='F') AND
 (COPYLASTTIME IS NULL OR
 REORGLASTTIME>COPYLASTTIME OR
 LOADRLASTTIME>COPYLASTTIME OR
 (CURRENT DATE-COPYLASTTIME)>CRDaySncLastCopy OR      1 
 (COPYUPDATEDPAGES*100)/NACTIVE>CRUpdatedPagesPct OR  2 
 (COPYCHANGES*100)/TOTALROWS>CRChangesPct)            3 

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

((QueryType='COPY' OR QueryType='ALL') AND
 (ObjectType='IX' OR ObjectType='ALL') AND
 (ICType='F'  OR ICType='B')) AND
 (COPYLASTTIME IS NULL OR
 REORGLASTTIME>COPYLASTTIME OR
 LOADRLASTTIME>COPYLASTTIME OR
 REBUILDLASTTIME>COPYLASTTIME OR
 (CURRENT DATE-COPYLASTTIME)>CRDaySncLastCopy OR       1 
 (NACTIVE>CRIndexSize AND                              2 
 ((COPYUPDATEDPAGES*100)/NACTIVE>CRUpdatedPagesPct OR  3 
 (COPYCHANGES*100)/TOTALENTRIES>CRChangesPct)))        4 

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

((QueryType='COPY' OR QueryType='ALL') AND
 (ObjectType='TS' OR ObjectType='ALL') AND
 ICType='I' AND
 COPYLASTTIME IS NOT NULL) AND
 (LOADRLASTTIME>COPYLASTTIME OR
 REORGLASTTIME>COPYLASTTIME OR
 (COPYUPDATEDPAGES*100)/NACTIVE>ICRUpdatedPagesPct OR  1 
 (COPYCHANGES*100)/TOTALROWS>ICRChangesPct))           2 

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

((QueryType='REORG' OR QueryType='ALL') AND
 (ObjectType='TS' OR ObjectType='ALL')) AND
 (REORGLASTTIME IS NULL OR
 ((REORGINSERTS+REORGDELETES+REORGUPDATES)*100)/TOTALROWS>RRTInsDelUpdPct OR  1 
 (REORGUNCLUSTINS*100)/TOTALROWS>RRTUnclustInsPct OR                          2 
 (REORGDISORGLOB*100)/TOTALROWS>RRTDisorgLOBPct OR                            3 
 ((REORGNEARINDREF+REORGFARINDREF)*100)/TOTALROWS>RRTIndRefLimit OR           4 
 REORGMASSDELETE>RRTMassDelLimit OR                                           5 
 EXTENTS>ExtentLimit)                                                         6 

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

((QueryType='REORG' OR QueryType='ALL') AND
 (ObjectType='IX' OR ObjectType='ALL')) AND
 (REORGLASTTIME IS NULL OR
 ((REORGINSERTS+REORGDELETES)*100)/TOTALENTRIES>RRIInsertDeletePct OR        1 
 (REORGAPPENDINSERT*100)/TOTALENTRIES>RRIAppendInsertPct OR                  2 
 (REORGPSEUDODELETES*100)/TOTALENTRIES>RRIPseudoDeletePct OR                 3 
 REORGMASSDELETE>RRIMassDeleteLimit OR                                       4 
 (REORGLEAFFAR*100)/NACTIVE>RRILeafLimit OR                                  5 
 REORGNUMLEVELS>RRINumLevelsLimit OR                                         6 
 EXTENTS>ExtentLimit)                                                        7 

The figure below shows the formula that DSNACCOR uses to recommend RUNSTATS on a table space.

((QueryType='RUNSTATS' OR QueryType='ALL') AND
 (ObjectType='TS' OR ObjectType='ALL')) AND
 (STATSLASTTIME IS NULL OR
 (((STATSINSERTS+STATSDELETES+STATSUPDATES)*100)/TOTALROWS>SRTInsDelUpdPct AND  1 
 (STATSINSERTS+STATSDELETES+STATSUPDATES)>SRTInsDelUpdAbs) OR                   2 
 STATSMASSDELETE>SRTMassDeleteLimit)                                            3  

The figure below shows the formula that DSNACCOR uses to recommend RUNSTATS on an index space.

((QueryType='RUNSTATS' OR QueryType='ALL') AND
 (ObjectType='IX' OR ObjectType='ALL')) AND
 (STATSLASTTIME IS NULL OR
 (((STATSINSERTS+STATSDELETES)*100)/TOTALENTRIES>SRIInsDelUpdPct AND   1 
 (STATSINSERTS+STATSDELETES)>SRIInsDelUpdPct) OR                       2 
 STATSMASSDELETE>SRIInsDelUpdAbs)                                      3 

The figure below shows the formula that DSNACCOR uses to that too many index space or table space extents have been used.

EXTENTS>ExtentLimit                                                     

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, execute 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.

 CREATE TABLE DSNACC.EXCEPT_TBL
  (DBNAME CHAR(8) NOT NULL,
   NAME CHAR(8) NOT NULL,
   QUERYTYPE CHAR(40))
  CCSID EBCDIC;

The meanings of the columns are:

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, DSNACCOR puts the value YES in the INEXCEPTTABLE column of the recommendations result set row for the object that matches the DBNAME and NAME values.

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'

Output

If DSNACCOR executes successfully, in addition to the output parameters described in Option descriptions, DSNACCOR returns two result sets.

The first result set contains the results from IFI COMMAND calls that DSNACCOR makes. The following table shows the format of the first result set.

The 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.
• DSNACCOR recommends at least one action for the table space, index space, or partition.

The 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 DSNACCOR result set

Column name	Data type	Description
DBNAME	CHAR(8)	Name of the database that contains the object.
NAME	CHAR(8)	Table space or index space name.
PARTITION	INTEGER	Data set number or partition number.
OBJECTTYPE	CHAR(2)	DB2 object type: TS for a table space IX for an index space
OBJECTSTATUS	CHAR(36)	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 restricted-state is one of the status codes that appear in DISPLAY DATABASE output. 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(3)	COPY recommendation: If OBJECTTYPE is TS: FUL (full image copy), INC (incremental image copy), or NO If OBJECTTYPE is IX: YES or NO
RUNSTATS	CHAR(3)	RUNSTATS recommendation: YES or NO.
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	CHAR(8)	If OBJECTTYPE is IX and the ChkLvl input parameter includes the value 2, 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 was terminated.
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 was terminated.
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 was terminated.
CRUPDPGSPCT	INTEGER	If OBJECTTYPE is TS or IX and IMAGECOPY is YES, the ratio of distinct updated pages to preformatted pages, expressed as a percentage. Otherwise null.
CRCPYCHGPCT	INTEGER	If OBJECTTYPE is TS and IMAGECOPY is YES, 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 OBJECTTYPE is IX 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.
CRDAYSCELSTCPY	INTEGER	If OBJECTTYPE is TS or IX and IMAGECOPY is YES, the number of days since the last image copy. Otherwise null.
CRINDEXSIZE	INTEGER	If OBJECTTYPE is IX and IMAGECOPY is YES, the number of active pages in the index space or partition. Otherwise 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.
RRTINSDELUPDPCT	INTEGER	If OBJECTTYPE is TS and REORG is YES, the ratio of the sum of insert, update, and delete 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.
RRTUNCINSPCT	INTEGER	If OBJECTTYPE is TS and 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.
RRTDISORGLOBPCT	INTEGER	If OBJECTTYPE is TS and 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.
RRTMASSDELETE	INTEGER	If OBJECTTYPE is TS, 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 OBJECTTYPE is TS, REORG is YES, and the table space is nonsegmented, the number of dropped tables since the last REORG or LOAD REPLACE. Otherwise null.
RRTINDREF	INTEGER	If OBJECTTYPE is TS, 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.
RRIINSDELPCT	INTEGER	If OBJECTTYPE is IX and REORG is YES, the ratio of the total number of insert and 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.
RRIAPPINSPCT	INTEGER	If OBJECTTYPE is IX and 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.
RRIPSDDELPCT	INTEGER	If OBJECTTYPE is IX and 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.
RRIMASSDELETE	INTEGER	If OBJECTTYPE is IX and REORG is YES, the number of mass deletes from the index space or partition since the last REORG, REBUILD, or LOAD REPLACE. Otherwise null.
RRILEAF	INTEGER	If OBJECTTYPE is IX and 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.
RRINUMLEVELS	INTEGER	If OBJECTTYPE is IX and 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.
STATSLASTTIME	TIMESTAMP	Timestamp of the last RUNSTATS on the object. Null if RUNSTATS was never run, or if the last RUNSTATS execution was terminated.
SRTINSDELUPDPCT	INTEGER	If OBJECTTYPE is TS and 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.
SRTINSDELUPDABS	INTEGER	If OBJECTTYPE is TS and RUNSTATS is YES, the total number of insert, update, and delete operations since the last RUNSTATS on a table space or partition. Otherwise null.
SRTMASSDELETE	INTEGER	If OBJECTTYPE is TS and RUNSTATS is YES, the number of mass deletes from the table space or partition since the last REORG or LOAD REPLACE. Otherwise null.
SRIINSDELPCT	INTEGER	If OBJECTTYPE is IX and 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.
SRIINSDELABS	INTEGER	If OBJECTTYPE is IX and RUNSTATS is YES, the number insert and delete operations since the last RUNSTATS on the index space or partition. Otherwise null.
SRIMASSDELETE	INTEGER	If OBJECTTYPE is IX and 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.
TOTALEXTENTS	SMALLINT	If EXTENTS is YES, the number of physical extents in the table space, index space, or partition. Otherwise, this value is null.