DSNACCOX and Exceptions Part 1
Introduction
A stored procedure DSNACCOX produces a list of recommended housekeeping. To run the stored procedure, execute REXX program also called DSNACCOX, see details in the next chapter. An exceptions table may be used, this is described in its own (NEXT) chapter.
DSNACCOX only produces recommendations based upon the run parameters and filtering supplied, and the state of the tablespaces and indexes it processes. Additional processing is required to generate suitable housekeeping jobs. The final chapter outlines how housekeeping may be generated.
For full details on the DSNACCOX stored procedure see the Db2 V13 SQL Reference Guide.
DSNACCOX
The Db2 real-time statistics stored procedure (DSNACCOX) is a sample stored procedure that makes recommendations to help you maintain your Db2 databases.
The DSNACCOX stored procedure replaced the DSNACCOR stored procedure, which is not supported in Db2 13. DSNACCOX provides the following improvements over DSNACCOR:
· Improved recommendations
· New fields
· New formulas
· The option to choose the formula for making recommendations.
You can call the DSNACCOX stored procedure to accomplish the following actions:
· Get recommendations for when to reorganize, image copy, or update statistics for table spaces or index spaces.
· Identify when a data set has exceeded a specified threshold for the number of extents that it occupies.
· Identify whether objects are in restricted states
DSNACCOX uses data from catalog tables, including 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.
The stored procedure DSNACCOX produces a list of housekeeping recommendations, controlled by the run parameters and filtering being used. A REXX program called DSNACCOX has been written to execute the stored procedure and writes the recommendations (or results) to a results table DB2DBA.RESULTS.
The REXX program also maintains a row in a results header table DB2DBA.RESULTS_HEADER, which will assist in interpolation of the results. When the stored procedure runs it looks for any matching objects in an exceptions table called DB2DBA.EXCEPT_TBL, details of which are in the next chapter.
JCL
Here is an example job used to run DSNACCOX. The SET commands setup the parameters passed into the REXX program and optionally DD name IN01 is used to override the default criteria which set the reporting thresholds.
//ABC1234X JOB (DBA),'DSNACCOX',CLASS=1,MSGCLASS=X,MSGLEVEL=(1,1),
// NOTIFY=&SYSUID,REGION=0M,SCHENV=ANYSYSTEM,TIME=1440
//*
// SET SSID=DBxz <-- DB2 SSID
// SET ENV=XX <-- ENVIRONMENT XX, R3 ETC. (## BELOW)
// SET QTYPE=ALL <-- QUERYTYPE E.G. ALL, REORG, ...
// SET SCHEMA=DB2DBA <-- OVERRIDE SCHEMA, DEFAULT DSNACC
// SET DBNAME='' <-- DBNAME, USING DEFAULT OF 'DBCS##%'
// SET OBJNAME='' <-- TS/IX NAMED, USING DEFAULT '%'
//*
//*--------------------------------------------------------------------
//* EXECUTE STORED PROCEDURE DSNACCOX
//*--------------------------------------------------------------------
//*
//JSTEP010 EXEC PGM=IKJEFT01,REGION=0M,
// PARM=('%DSNACCOX SSID(&SSID) ENV(&ENV) QTYPE(&QTYPE)',
// 'SCHEMA(&SCHEMA),DB(&DBNAME),OBJ(&OBJNAME)')
//STEPLIB DD DSN=DBS.&SSID..RUNLIB.LOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSPROC DD DSN=DBA.UTIL.EXEC,DISP=SHR
//IN01 DD DSN=DBA.UTIL.CNTLCARD(&ENV.ACCOX),DISP=SHR
//SYSTSIN DD DUMMY
Run Parameters
The parameters are indicated above in the example JCL above, passed to the REXX executing the stored procedure.
SSID: For production use DBxy and pre-production use DBxz.
ENV: For production use P1 and pre-production XX, D2 and XX – 3.
QTYPE1: May be COPY, EXTENTS, RESTRICT, RUNSTATS, REORG or ALL (for all options).
SCHEMA2: Use DB2DBA unless using your own results and exceptions tables.
DBNAME3: Optional, defaults to DBCS##% (where ## = environment above).
OBJNAME3: TS or IX name or like string, defaults to '%'
Notes:
1. Multiple values for QTYPE may be used, e.g. QTYPE='REORG COPY RESTRICT' noting that ALL may not be used in a list.
2. The schema name is the creator name of the results, results header and exceptions tables.
3. The DBNAME and OBJNAME values are used to build the criteria value in the REXX, which as the predicates for the stored procedure. Examples:
Defaults used become: "WHERE (DBNAME LIKE 'DBCSXX%' AND NAME LIKE '%')"
DB and object specified: "WHERE (DBNAME LIKE 'DBCSXX%' AND NAME LIKE 'S80%')"
Filters or Limits
An optional DD IN01 may be used to pass the filtering limits, see members in Lib i.e. DBA.UTIL.CNTLCARD named DSNACCOX and ##ACCOX (where ## = environment). Member DSNACCOX should be maintained as a template containing defaults and members ##ACCOX tailored for each environment. Records are used as follows:
- Records starting with * in column 1 are comments.
- Only the first two words a of each record are read, all other text is treated as comments.
Example:
* ---------------------------------------------------------------------
* EXAMPLE - FOR USE IN XX
*
* ANY LINE STARTING WITH * IS A COMMENT
* ONLY PARAMETER AND VALUE ARE READ, ANYTHING ELSE IS A COMMENT
*
* FOR MEANING SEE DSNACCOX IN SQL REFERENCE GUIDE
*
* OVERRIDES FORMATTED:
* PARAMETER VALUE |OPTIONAL COMMENTS (DEFAULTS AND REMARKS)
* -------------------------|-------------------------------------------
CRUPDATEDPAGESPCT 20.0 | 20.0 RATIO OF TOTAL UPDATED TO PREFORMAT
CRUPDATEDPAGESABS 0 | 0 ABSOLUTE UPDATED TO PREFORMATED PAGES
CRCHANGESPCT 10.0 | 10.0 RATIO OF INS, UPD AND DEL TO TOTALROWS
CRDAYSNCLASTCOPY 7 | 7 DAYS SINCE LAST FULL COPY
ICRUPDATEDPAGESPCT 1.0 | 1.0 IMAGE COPY RATIO OF UPDATED PAGES
ICRUPDATEDPAGESABS 0 | 0 IMAGE COPY ABSOLUTE VALUE
ICRCHANGESPCT 1.0 | 1.0 RATIO OF INS, UPD AND DEL TO TOTALROWS
CRINDEXSIZE 50 | 50 SEE CRUPDATEDPAGESPCT & CRCHANGESPCT
RRTINSERTSPCT 25.0 | 25.0 REORG TS PER CENT INSERTS
RRTINSERTSABS 0 | 0 REORG TS ABSOLUTE NUMBER OF INSERTS
The first 11 records are comments and ignored. Then only the parameter and value are processed (words 1 and 2), e.g. CRUPDATEDPAGESPC is set to 20.0 where the remaining comments show the defaults and brief description.
Notes:
- The parameter name (word 1) may start in column 1, a later column has been used above so that it may be commented out if required without upsetting the layout.
- The value (word 2) has been decimal point aligned for appearance.
- Decimal values have been supplied with a decimal point and integers without, so that it is each to see what is required.
For details of DSNACCOX, see the Stored Procedure section in the Db2 V12 SQL Reference Guide.
REXX
The stored procedure is executed using REXX program also called DSNACCOX.
Several modifications have been made:
- Recommendations are inserted into a results table with a summary in a results header table.
- Customised to supports different pre-production environments.
- More parameters may be passed giving greater control.
- Filtering limits may be updated to suit different environments, see input DD IN01.
- Presentation changes have been applied.
The basic process is:
- Validate supplied parameters
- Check for IN01 and if used, read and apply new runtime criteria
- Insert a row into RESULTS_HEADER table to show start of processing
- Execute stored procedure DSNACCOX
- Insert results in RESULTS table
- Update row in RESULTS _HEADER showing completion.
Results and Header
The recommendations produced by DSNACCOX are inserted into a table DB2DBA.RESULTS which has 54 columns and has two indexes. Where rows are clustered on DBNAME, NAME, DB2GROUP, ENVIRONMENT and LASTUPDATED.
The extra columns in the results table include ENVIRONMENT, HKFLAG and LASTUPDATED. HKFLAG holds a short hand version of the housekeeping requirements which may assist in the generation of housekeeping jobs (last chapter).
|
POS
|
PURPOSE
|
VALUES
|
MEANING
|
|
1
|
RESTRICTED STATE?
|
Y or N
|
Yes or No
|
|
2
|
IMAGECOPY (in need of copy)
|
F, I or N
|
Full, Incremental or Not required
|
|
3
|
RUNSTATS
|
T, I, L or X
|
TS, IX, LOB or XML
|
|
4
|
EXTENTS (alter PRI/SECQTY required)
|
Y or N
|
Yes or No
|
|
5
|
REORG is required
|
T, I, L or X
|
TS, IX, LOB or XML
|
The stored procedure outputs a lot of information and all of this is inserted into the results table, with with some other columns used to identify the appropriate rows when querying. With experience, the number columns may be reduced with corresponding changes to the REXX (see Insert_Results sub routine).
The results header table contains a row for each run of DSNACCOX and is used to identify rows in the results table for a given run. This is required because the results table is likely to be holding rows relating to different runs (day and type), where rows are distinguished by timestamps.
When the REXX starts a row is inserted into the results header table so that there is a record of the job starting. Layout of results header table:
|
COLUMN
|
TYPE
|
LENGTH
|
REMARKS
|
|
RUNDATE
|
DATE
|
|
Start date
|
|
RUNTIME
|
TIME
|
|
Start time
|
|
DB2GROUP
|
CHAR
|
4
|
Db2 data sharing group name
|
|
ENVIRONMENT
|
CHAR
|
5
|
Environment: P1, D1, D2, XX etc.
|
|
QTYPE
|
VARCHAR
|
48
|
Run type
|
|
LASTUPDATED
|
TIMESTAMP
|
|
Timestamp of results rows = date and time above
|
|
RUNOK
|
CHAR
|
1
|
Y = Ended OK, default is N
|
|
PROCESSED
|
CHAR
|
5
|
For use by housekeeping generator
|
When the stored procedure completed OK, the row was updated changing the LASTUPDATED from 0001-01-01-00.00.00.000000 to the generated timestamp and RUNOK to Y.
RUNDATE RUNTIME DB2GROUP ENVIRONMENT QTYPE LASTUPDATED RUNOK
---------+---------+---------+---------+---------+--------+---------+---------+---------
04.03.2020 10.26.57 DB0P XX REORG 2024-02-04 -10.26.57.000000 Y
You will see that at start of processing the date and time read and a timestamp generated with zero microseconds which is used when inserting rows into the results table and recorded in the header table at end of processing. This ensures the correct timestamp is used to identify rows in the results table.
, DBNAME
, NAME
, PARTITION
, OBJECTTYPE
, HKFLAG
, IMAGECOPY
, RUNSTATS
, EXTENTS
, REORG
, OBJECTSTATUS
FROM DB2DBA.RESULTS R
WHERE DB2GROUP = 'DBxz'
AND ENVIRONMENT = 'XX'
AND LASTUPDATED = (SELECT MAX(LASTUPDATED)
FROM DB2DBA.RESULTS_HEADER
WHERE ENVIRONMENT = R.ENVIRONMENT
AND QTYPE = 'REORG' -- RUN TYPE
AND RUNOK = 'Y')
ORDER BY 2, 3, 4
Results Housekeeping
Each time DSNACCOX is run, many new rows are inserted into the results table, identified by a timestamp in column LASTUPDATED. Likewise, the results header table will have one row inserted on each run. By not emptying the results table before each run, allows many different types of DSNACCOX to be run and to share a single results table.
Periodic housekeeping is required (not setup) to delete old rows. For DB2DBA.RESULTS a reorg with delete where LASTUPDATED is less than current timestamp minus N days would be the best method. Alternatively, the table could be dummy loaded daily before the first of the DSNACCOX runs.
In the cause of DB2DBA.RESULTS_HEADER an SQL delete would be the best method, deleting where LASTUPDATED older than a date or PROCESSED <> ' '.
Example Job
The following demonstration job was run.
//ABC1234X JOB (DBA),'DSNACCOX',CLASS=1,MSGCLASS=X,MSGLEVEL=(1,1),
// NOTIFY=&SYSUID,REGION=0M,SCHENV=ANYSYSTEM,TIME=1440
//*
// SET SSID=DBxz <-- DB2 SSID
// SET ENV=XX <-- ENVIRONMENT D1, R3 ETC. (## BELOW)
// SET QTYPE=REORG <-- QUERYTYPE E.G. ALL, REORG, ...
// SET SCHEMA=DB2DBA <-- OVERRIDE SCHEMA, DEFAULT DSNACC
// SET DBNAME='' <-- DBNAME, USING DEFAULT OF 'DBCS##%'
// SET OBJNAME='' <-- TS/IX NAMED, USING DEFAULT '%'
//*
//*--------------------------------------------------------------------
//* EXECUTE STORED PROCEDURE DSNACCOX
//*
//* USING DEFAULT PREDICATES:
//* WHERE (DBNAME LIKE 'DBCSD1%' AND NAME LIKE '%')
//*--------------------------------------------------------------------
//*
//JSTEP010 EXEC PGM=IKJEFT01,REGION=0M,
// PARM=('%DSNACCOX SSID(&SSID) ENV(&ENV) QTYPE(&QTYPE)',
// 'SCHEMA(&SCHEMA),DB(&DBNAME),OBJ(&OBJNAME)')
//STEPLIB DD DSN=DBS.&SSID..RUNLIB.LOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//SYSTSPRT DD SYSOUT=*
//SYSPROC DD DSN=DBA.UTIL.EXEC,DISP=SHR
//IN01 DD DSN=DBA.UTIL.CNTLCARD(&ENV.ACCOX),DISP=SHR
The job checked for reorgs need in XX using default run parameters and filtering. This run parameters produced predicates WHERE (DBNAME LIKE 'DBCSXX%' AND NAME LIKE '%'). The most significant items have been highlighted in the output below.
Initialising
Supplied parameters...
DB2 ssid : DBxz
Environment : XX
Query type : REORG
Schema name : DB2DBA
DBname :
OBject name :
Generated parameters...
Criteria : DBNAME LIKE 'DBCSXX%' AND NAME LIKE '%'
Local Schema: DB2DBA
Timestamp : 2024-02-04 -10.26.57.000000
Setting default values
Default overrides being applied
-------------------------------------------------------------------------------
The following values have been set for DSNACCOX
DB2 SSID ...: 'DBxz'
CHKLVL .....: '64'
: Use the CHKLVL parameter to cause DSNACCOX to
: check for related tablespaces 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)
ICTYPE .....: 'B'
CATLGSCHEMA : 'SYSIBM'
LOCALSCHEMA : 'DB2DBA'
: Use LOCALSCHEMA to set exceptions table schema
: (default DSNACC) of table EXCEPT_TBL.
CRITERIA ...: 'DBNAME LIKE 'DBCSXX%' AND NAME LIKE '%''
: Use the criteria parameter to cause DSNACCOX to
: make recommendations only for objects in database
: you want to.
QUERYTYPE ..: 'REORG'
: QUERYTYPE may be 'ALL' for all queries or 'COPY',
: 'EXTENTS', 'RESTRICT', 'RUNSTATS' or 'REORG'.
Checking for override parameters
No. of override records read: 44
Applying supplied override values
REC PARAMETER NAME VALUE COMMENTS
--- -------------------- ------ ------------------------------------------------
12 CRUPDATEDPAGESPCT 20.0 | 20.0 RATIO OF TOTAL UPDATED TO PREFORMAT
13 CRUPDATEDPAGESABS 0 | 0 ABSOLUTE UPDATED TO PREFORMATED PAGES
14 CRCHANGESPCT 10.0 | 10.0 RATIO OF INS, UPD AND DEL TO TOTALROWS
15 CRDAYSNCLASTCOPY 7 | 7 DAYS SINCE LAST FULL COPY
16 ICRUPDATEDPAGESPCT 1.0 | 1.0 IMAGE COPY RATIO OF UPDATED PAGES
17 ICRUPDATEDPAGESABS 0 | 0 IMAGE COPY ABSOLUTE VALUE
18 ICRCHANGESPCT 1.0 | 1.0 RATIO OF INS, UPD AND DEL TO TOTALROWS
19 CRINDEXSIZE 50 | 50 SEE CRUPDATEDPAGESPCT & CRCHANGESPCT
20 RRTINSERTSPCT 25.0 | 25.0 REORG TS PER CENT INSERTS
21 RRTINSERTSABS 0 | 0 REORG TS ABSOLUTE NUMBER OF INSERTS
22 RRTDELETESPCT 25.0 | 25.0 REORG TS PER CENT DELETES
23 RRTDELETESABS 0 | 0 REORG TS ABSOLOTE NUMBER OF DELETE
24 RRTUNCLUSTINSPCT 10.0 | 10.0 REORG TS UNCLUSTERED INSERT PER CENT
25 RRTDISORGLOBPCT 50.0 | 50.0 REORG LOB TS
26 RRTDATASPACERAT -1 | -1 REORG TS
27 RRTMASSDELLIMIT 0 | 0 REORG TS MASS DELET ELIMIT
28 RRTINDREFLIMIT 5.0 | 5.0 REORG TS
29 RRIINSERTSPCT 30.0 | 30.0 REORG IX PER CENT DELETES
30 RRIINSERTSABS 0 | 0 REORG IX ABSOLOTE NUMBER OF DELETE
31 RRIDELETESPCT 30.0 | 30.0 REORG IX
32 RRIDELETESABS 0 | 0 REORG IX
33 RRIAPPENDINSERTPCT 20.0 | 20.0 REORG IX
34 RRIPSEUDODELETEPCT 0 | 0 REORG IX
35 RRIMASSDELLIMIT 0 | 0 REORG IX
36 RRILEAFLIMIT 10.0 | 10.0 REORG IX
37 RRINUMLEVELSLIMIT 0 | 0 REORG IX
38 SRTINSDELUPDPCT 20.0 | 20.0
39 SRTINSDELUPDABS 0 | 0
40 SRTMASSDELLIMIT 0 | 0
41 SRIINSDELUPDPCT 20.0 | 20.0
42 SRIINSDELUPDABS 0 | 0
43 SRIMASSDELLIMIT 0 | 0
44 EXTENTLIMIT 25 |254 EXTENTS LIMIT
-------------------------------------------------------------------------------
Connecting to DB2 ssid: DBxz
Started main step
Inserting into RESULTS_HEADER
- Date : 2024-02-04
- Time : 10.26.57
- SSID : DBxz
- ENV : XX
- Qtype: REORG
- Row inserted OK
Calling stored procedure
Associating LOC1 and LOC2
Allocating C101 cursor to :LOC1
Fetching C101...
- RS_SEQ: RS_DATA:
Allocating C102 cursor to :LOC2
Fetching C102...
OBJ DBNAME NAME PART ICPY RUN REO EXT FLAG INEXCEPTTABLE
--- -------- ------------------ ---- ---- --- --- --- ----- -----------------
IX DBCSXXSE XSE20FT2 0 - - YES NO NNNNI NO
TS DBCSXXPT EXPLU4K 0 - - YES - YNNNT NO
LS DBCSXXPT EXPLA32A 0 - - YES NO NNNNL NO
Etc.
TS DBCSXX99 S99XZRT 11 - - YES NO NNNNT NO
TS DBCSXX99 S99XZRT 2 - - YES NO NNNNT NO
------------------------------------------------ ----- ----------------------
Updating RESULTS_HEADER
- Timestamp: 2024-02-04 -10.26.57.000000 RUNOK: Y
- Updated OK
Summary of results
No. rows fetched : 5234
No. rows inserted : 5234
Restricted state : 4
Imagecopy full : 0
incremental : 0
Runstats tablespace : 0
index : 0
LOB : 0
XML : 0
Total object: 0
Extents (alter) : 694
Reorg tablespace : 5226
index : 2
LOB : 6
XML : 0
Total : 5234
Disconnecting from DB2
------------------------------
Sandeep Jain
------------------------------