Hi Chad
Many thanks for your questions.
ISPF user panel and dialogues you can set it up in your environment. Here I have given a sample example.
For details of DSNACCOX, see the Stored Procedure section in the Db2 V12 SQL Reference Guide. Also Please refer my below PART1 thread hope it helps.
https://community.ibm.com/community/user/datamanagement/discussion/dsnaccox-and-exceptions-part-1#bm2fcd9b55-3988-42b9-9c88-d6ef7bdd6862
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.
Please find sample REXX DSNACCOX we used locally.
MEMBER NAME DSNACCOX
/* REXX -------------------------------------------------------------*/
/* */
/* REXX to call stored procedure DSNACCOX */
/* */
/* Must supply the DB2 ssid and test environment code e.g. D1 or R3 */
/* Crieria ply the DB2 ssid, using a default criteria for testing. */
/* Call using a batch job only. */
/* */
/*-------------------------------------------------------------------*/
say 'Start of DSNACCOX'
parse upper arg ssid env qtype /* ssid, criteria and query type */
call initialise
call DB2_Connect
if ret_code = 0 then
do
call Main_Step
/* if ret_code < 8 then call Write_Output */
call DB2_Disconnect
end
say
say 'End of processing rc' maxrc
exit maxrc
/*-------------------------------------------------------------------*/
/* Main step */
/*-------------------------------------------------------------------*/
Main_Step:
say 'Started main step'
/*-------------------------------------------------------------------*/
/* call DB2 stored procedure */
/*-------------------------------------------------------------------*/
say ' Calling stored procedure'
SQL1 = "CALL SYSPROC.DSNACCOX",
"(:QUERYTYPE :QUERYTYPE_IND, " ,
" :OBJECTTYPE :OBJECTTYPE_IND, " ,
" :ICTYPE :ICTYPE_IND, " ,
" :CATLGSCHEMA :CATLGSCHEMA_IND, " ,
" :LOCALSCHEMA :LOCALSCHEMA_IND, " ,
" :CHKLVL :CHKLVL_IND, " ,
" :CRITERIA :CRITERIA_IND, " ,
" :UNUSED :UNUSED_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 :RRTINSERTSABS_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, " ,
" :SRIINSDELUPDPCT :SRIINSDELUPDPCT_IND, " ,
" :SRIINSDELUPDABS :SRIINSDELUPDABS_IND, " ,
" :SRIMASSDELLIMIT :SRIMASSDELLIMIT_IND, " ,
" :EXTENTLIMIT :EXTENTLIMIT_IND, " ,
" :LASTSTATEMENT :LASTSTATEMENT_IND, " ,
" :RETURNCODE :RETURNCODE_IND, " ,
" :ERRORMSG :ERRORMSG_IND, " ,
" :IFCARETCODE :IFCARETCODE_IND, " ,
" :IFCARESCODE :IFCARESCODE_IND, " ,
" :XSBYTES :XSBYTES_IND)";
if MSGon then say SQL1
Stmt = SQL1
address dsnrexx "execsql" Stmt
if MSGon then say Stmt 'SQLCODE' SQLcode
if SQLcode <> 0 then call Display_SQLcode
if ret_code > 4 then return
/*-------------------------------------------------------------------*/
/* Combine two locators with the stored procedure */
/*-------------------------------------------------------------------*/
say ' Associating LOC1 and LOC2'
LOC1 = d2x(0)
LOC2 = d2x(0)
Stmt = "ASSOCIATE LOCATORS (:LOC1, :LOC2)",
"WITH PROCEDURE SYSPROC.DSNACCOX"
if MSGon then say Stmt
address DSNREXX "EXECSQL" Stmt
if SQLcode <> 0 then call Display_SQLcode
if ret_code > 4 then return
/*-------------------------------------------------------------------*/
/* Allocate and process cursor-driven */
/*-------------------------------------------------------------------*/
say ' Allocating C101 cursor to :LOC1'
Stmt = "ALLOCATE C101 CURSOR FOR RESULT SET :LOC1"
if MSGon then say Stmt
address DSNREXX "EXECSQL" Stmt
if SQLcode <> 0 then call Display_SQLcode
if ret_code > 4 then return
say ' Fetching C101...'
call Fetch_C101
call Close_C101
if ret_code > 4 then return
/*-------------------------------------------------------------------*/
/* Allocate and process cursor-driven */
/*-------------------------------------------------------------------*/
say ' Allocating C102 cursor to :LOC2'
Stmt = "ALLOCATE C102 CURSOR FOR RESULT SET :LOC2"
if MSGon then say Stmt
address DSNREXX "EXECSQL" Stmt;
if ret_code > 4 then return
say ' Fetching C102...'
call Fetch_C102
call Close_C102
if ret_code > 4 then return
if ret_code > maxrc then maxrc = ret_code
return
/*-------------------------------------------------------------------*/
/* process line output, hide if value contains no information */
/*-------------------------------------------------------------------*/
line: arg lval ltext
lval = trunc(lval)
if lval > 0
then say right(lval,20)" "ltext
Return
/*-------------------------------------------------------------------*/
/* Fetch using cursor C101 */
/*-------------------------------------------------------------------*/
Fetch_C101:
RS_SEQ = ''
RS_DATA = ''
Stmt = "FETCH C101 INTO :RS_SEQ, :RS_DATA"
if MSGon then say Stmt
Address DSNREXX "EXECSQL" Stmt
if SQLcode <> 0 then call Display_SQLcode
if ret_code > 4 then return
say ' - RS_SEQ:' RS_SEQ 'RS_DATA:' RS_DATA
if ret_code > maxrc then maxrc = ret_code
return
/*-------------------------------------------------------------------*/
/* Fetch using cursor C102 */
/*-------------------------------------------------------------------*/
Fetch_C102:
Stmt = "FETCH C102 INTO ",
":DBNAME :DBNAME_IND ,",
":NAME :NAME_IND ,",
":PARTITION :PARTITION_IND ,",
":INSTANCE :INSTANCE_IND ,",
":CLONE :CLONE_IND ,",
":OBJECTTYPE :OBJECTTYPE_IND ,",
":INDEXSPACE :INDEXSPACE_IND ,",
":CREATOR :CREATOR_IND ,",
":OBJECTSTATUS :OBJECTSTATUS_IND ,",
":IMAGECOPY :IMAGECOPY_IND ,",
":RUNSTATS :RUNSTATS_IND ,",
":EXTENTS :EXTENTS_IND ,",
":REORG :REORG_IND ,",
":INEXCEPTTABLE :INEXCEPTTABLE_IND ,",
":ASSOCIATEDTS :ASSOCIATEDTS_IND ,",
":COPYLASTTIME :COPYLASTTIME_IND ,",
":LOADRLASTTIME :LOADRLASTTIME_IND ,",
":REBUILDLASTTIME :REBUILDLASTTIME_IND ,",
":CRUPDPGSPCT :CRUPDPGSPCT_IND ,",
":CRUPDPGSABS :CRUPDPGSABS_IND ,",
":CRCPYCHGPCT :CRCPYCHGPCT_IND ,",
":CRDAYSCELSTCPY :CRDAYSCELSTCPY_IND ,",
":CRINDEXSIZE :CRINDEXSIZE_IND ,",
":REORGLASTTIME :REORGLASTTIME_IND ,",
":RRTINSERTSPCT :RRTINSERTSPCT_IND ,",
":RRTINSERTSABS :RRTINSERTSABS_IND ,",
":RRTDELETESPCT :RRTDELETESPCT_IND ,",
":RRTDELETESABS :RRTDELETESABS_IND ,",
":RRTUNCINSPCT :RRTUNCINSPCT_IND ,",
":RRTDISORGLOBPCT :RRTDISORGLOBPCT_IND ,",
":RRTDATASPACERAT :RRTDATASPACERAT_IND ,",
":RRTMASSDELETE :RRTMASSDELETE_IND ,",
":RRTINDREF :RRTINDREF_IND ,",
":RRIINSERTPCT :RRIINSERTPCT_IND ,",
":RRIINSERTABS :RRIINSERTABS_IND ,",
":RRIDELETEPCT :RRIDELETEPCT_IND ,",
":RRIDELETABS :RRIDELETABS_IND ,",
":RRIAPPINSPCT :RRIAPPINSPCT_IND ,",
":RRIPSDDELPCT :RRIPSDDELPCT_IND ,",
":RRIMASSDELETE :RRIMASSDELETE_IND ,",
":RRILEAF :RRILEAF_IND ,",
":RRINUMLEVELS :RRINUMLEVELS_IND ,",
":STATSLASTTIME :STATSLASTTIME_IND ,",
":SRTINSDELUPDPCT :SRTINSDELUPDPCT_IND ,",
":SRTINSDELUPDABS :SRTINSDELUPDABS_IND ,",
":SRTMASSDELETE :SRTMASSDELETE_IND ,",
":SRIINSDELPCT :SRIINSDELPCT_IND ,",
":SRIINSDELABS :SRIINSDELABS_IND ,",
":SRIMASSDELETE :SRIMASSDELETE_IND ,",
":TOTALEXTENTS :TOTALEXTENTS_IND ";
if MSGon then say Stmt
SQLcode = 0
do while SQLcode = 0
Address DSNREXX "EXECSQL" Stmt
if MSGon then say ' - Fetch ended SQLcode' SQLcode
if SQLcode = 0 then
do
if OBJECTTYPE <> "ALL" then
Say "DATABASE: "DBNAME" SPACENAME: "NAME "("OBJECTTYPE")"
if IMAGECOPY <> "NO" then
do
if COPYLASTTIME_IND = -1 then COPYLASTTIME = "NEVER"
say "- Imagecopy ...: "IMAGECOPY", last done "COPYLASTTIME
call Line(CRUPDPGSPCT "% updated pages")
call Line(CRCPYCHGPCT "% rows INS/DEL/UPD")
call Line(CRDAYSCELSTCPY "day(s) since last COPY")
end
if RUNSTATS <> "NO" then
do
if STATSLASTTIME_IND = -1 then STATSLASTTIME = "NEVER"
say "- Runstats ....: "RUNSTATS", last done "STATSLASTTIME
call Line(SRTMASSDELETE "mass deleted")
call Line(SRTINSDELUPDPCT "% rows INS/DEL/UPD")
end
if REORG <> "NO" then
do
if REORGLASTTIME_IND = -1 then REORGLASTTIME = "NEVER"
say "- Reorg .......: "REORG", last done "REORGLASTTIME
call Line(RRTMASSDELETE "mass deleted")
call Line(RRTINSERTSPCT "% rows INS/DEL/UPD")
call line(RRTUNCINSPCT "% rows unclustered INSERT's")
call line(RRTINDREF "% overflowed records")
end
if EXTENTS <> "NO" then
do
say "- Extents .....: "EXTENTS", "TOTALEXTENTS" extent(s)"
end
end
else
call Display_SQLcode
end
if ret_code > maxrc then maxrc = ret_code
return
/*-------------------------------------------------------------------*/
/* Close cursor C101 */
/*-------------------------------------------------------------------*/
Close_C101:
address dsnrexx
Stmt = "CLOSE C101"
"execsql" stmt
if MSGon then say Stmt 'SQLCODE' SQLcode
if SQLcode = 0 then call Display_SQLcode
if ret_code > maxrc then maxrc = ret_code
return
/*-------------------------------------------------------------------*/
/* Close cursor C102 */
/*-------------------------------------------------------------------*/
Close_C102:
address dsnrexx
Stmt = "CLOSE C102"
"execsql" stmt
if MSGon then say Stmt 'SQLCODE' SQLcode
if SQLcode = 0 then call Display_SQLcode
if ret_code > maxrc then maxrc = ret_code
return
/*-------------------------------------------------------------------*/
/* Display SQLcode */
/*-------------------------------------------------------------------*/
Display_SQLcode:
select
when SQLcode = 0 then ret_code = 0
when SQLcode = +100 then ret_code = 0
when SQLcode = +466 then ret_code = 0
when SQLcode = -803 then ret_code = 4
otherwise ret_code = 8
end
if ret_code > 4 then
do
say copies('*',72)
say Stmt
say copies('*',72)
say 'DSNT400I SQLCODE =' SQLcode
say 'DSNT418I SQLSTATE =' SQLstate 'SQLSTATE RETURN CODE'
say 'DSNT416I' SQLerrml SQLerrmc SQLerrp,
'SQL DIAGNOSTIC INFORMATION'
say 'DSNT416I SQLERRD =' SQLerrd.1 SQLerrd.2 SQLerrd.3,
SQLerrd.4 SQLerrd.5 SQLerrd.6
say "DSNT416I X'"d2x(SQLerrd.1,8)"'",
"X'"d2x(SQLerrd.2,8)"'",
"X'"d2x(SQLerrd.3,8)"'",
"X'"d2x(SQLerrd.4,8)"'",
"X'"d2x(SQLerrd.5,8)"'",
"X'"d2x(SQLerrd.6,8)"'"
end
if ret_code > maxrc then maxrc = ret_code
return
/*-------------------------------------------------------------------*/
/* DB2 Connect */
/*-------------------------------------------------------------------*/
DB2_Connect:
say 'Connecting to DB2 ssid:' ssid
"subcom dsnrexx"
ret_code = rc
if ret_code < 8 then
ret_code = rxsubcom('add','dsnrexx','dsnrexx')
if ret_code > 4 then
do
say copies('*',72)
say 'ERROR - Unable to start dsnrexx rc:' ret_code
say copies('*',72)
ret_code = 8
end
else
ret_code = 0
if ret_code = 0 then
do
address dsnrexx "connect" ssid
ret_code = rc
if (ret_code < 0) | (ret_code > 2) then
do
say copies('*',72)
say 'ERROR - Unable to connect to DB2 rc:' ret_code
say copies('*',72)
ret_code = 8
end
else
ret_code = 0
end
if ret_code > maxrc then maxrc = ret_code
return
/*-------------------------------------------------------------------*/
/* DB2 disconnect */
/*-------------------------------------------------------------------*/
DB2_Disconnect:
say 'Disconnecting from DB2'
address dsnrexx "DISCONNECT"
ret_code = rxsubcom('DELETE','DSNREXX','DSNREXX')
if ret_code > maxrc then maxrc = ret_code
return
/*-------------------------------------------------------------------*/
/* Write output */
/*-------------------------------------------------------------------*/
Write_Output:
address tso
say 'Writing output'
say ' Output to' dsn2
"alloc f(OUT01) ds('"dsn2"') shr"
ret_code = rc
if (ret_code < 0) | (ret_code > 2) then
do
say copies('*',72)
say 'ERROR - Unable to allocate OUT01 rc:' ret_code
say copies('*',72)
ret_code = 8
end
else
ret_code = 0
if ret_code = 0 then
do
say ' No. of records:' outrec.0
"execio * diskw OUT01 (stem outrec. finis )"
ret_code = rc
if (ret_code < 0) | (ret_code > 2) then
do
say copies('*',72)
say 'ERROR - Unable to write OUT01 rc:' ret_code
say copies('*',72)
ret_code = 8
end
else
ret_code = 0
end
"free f(OUT01)"
if ret_code > maxrc then maxrc = ret_code
return
/*-------------------------------------------------------------------*/
/* Initialise */
/*-------------------------------------------------------------------*/
Initialise:
MSGon = 0
ret_code = 0
maxrc = 0
inrec.0 = 0
linein = 0
outrec.0 = 0
lineout = 0
if ssid = "" then
do
say copies('*',72)
say "ERROR - Must supply a DB2 ssid"
say copies('*',72)
ret_code = 8
end
if env <> '' then
do
CRITERIA = "DBNAME LIKE 'DBCS" || env || "%'",
"AND NAME LIKE '%'"
CRITERIA_IND = 0
end
else
do
say copies('*',72)
say "ERROR - Must supply an environment, e.g. D1 or R3 etc."
say copies('*',72)
CRITERIA = ''
CRITERIA_IND = -1
ret_code = 8
end
if (Qtype = 'ALL') |,
(Qtype = 'COPY') |,
(Qtype = 'EXTENTS') |,
(Qtype = 'RESTRICT') |,
(Qtype = 'RUNSTATS') |,
(Qtype = 'REORG') then
do
QUERYTYPE = Qtype
QUERYTYPE_IND = 0
end
else
do
say " WARNING - QUERYTYPE not specified defaulting to 'ALL'"
say " QUERYTYPE maybe: ALL, COPY, EXTENTS,",
"RESTRICT, RUNSTATS or REORG"
QUERYTYPE = 'ALL'
QUERYTYPE_IND = 0
ret_code = 8
end
if ret_code > maxrc then maxrc = ret_code
if ret_code > 4 then return
OBJECTTYPE = "ALL"; OBJECTTYPE_IND = 0
ICTYPE = "B"; ICTYPE_IND = 0
CATLGSCHEMA = "SYSIBM"; CATLGSCHEMA_IND = 0
LOCALSCHEMA = "DSNACC"; LOCALSCHEMA_IND = 0
CHKLVL = 64; CHKLVL_IND = 0
UNUSED = ""; UNUSED_IND = -1
CRUPDATEDPAGESPCT = 0; CRUPDATEDPAGESPCT_IND = -1
CRUPDATEDPAGESABS = 0; CRUPDATEDPAGESABS_IND = -1
CRCHANGESPCT = 0; CRCHANGESPCT_IND = -1
CRDAYSNCLASTCOPY = 0; CRDAYSNCLASTCOPY_IND = -1
ICRUPDATEDPAGESPCT = 0; ICRUPDATEDPAGESPCT_IND = -1
ICRUPDATEDPAGESABS = 0; ICRUPDATEDPAGESABS_IND = -1
ICRCHANGESPCT = 0; ICRCHANGESPCT_IND = -1
CRINDEXSIZE = 0; CRINDEXSIZE_IND = -1
RRTINSERTSPCT = 0; RRTINSERTSPCT_IND = -1
RRTINSERTSABS = 0; RRTINSERTSABS_IND = -1
RRTDELETESPCT = 0; RRTDELETESPCT_IND = -1
RRTDELETESABS = 0; RRTDELETESABS_IND = -1
RRTUNCLUSTINSPCT = 0; RRTUNCLUSTINSPCT_IND = -1
RRTDISORGLOBPCT = 0; RRTDISORGLOBPCT_IND = -1
RRTDATASPACERAT = 0; RRTDATASPACERAT_IND = -1
RRTMASSDELLIMIT = 0; RRTMASSDELLIMIT_IND = -1
RRTINDREFLIMIT = 0; RRTINDREFLIMIT_IND = -1
RRIINSERTSPCT = 0; RRIINSERTSPCT_IND = -1
RRIINSERTSABS = 0; RRIINSERTSABS_IND = -1
RRIDELETESPCT = 0; RRIDELETESPCT_IND = -1
RRIDELETESABS = 0; RRIDELETESABS_IND = -1
RRIAPPENDINSERTPCT = 0; RRIAPPENDINSERTPCT_IND = -1
RRIPSEUDODELETEPCT = 0; RRIPSEUDODELETEPCT_IND = -1
RRIMASSDELLIMIT = 0; RRIMASSDELLIMIT_IND = -1
RRILEAFLIMIT = 0; RRILEAFLIMIT_IND = -1
RRINUMLEVELSLIMIT = 0; RRINUMLEVELSLIMIT_IND = -1
SRTINSDELUPDPCT = 0; SRTINSDELUPDPCT_IND = -1
SRTINSDELUPDABS = 0; SRTINSDELUPDABS_IND = -1
SRTMASSDELLIMIT = 0; SRTMASSDELLIMIT_IND = -1
SRIINSDELUPDPCT = 0; SRIINSDELUPDPCT_IND = -1
SRIINSDELUPDABS = 0; SRIINSDELUPDABS_IND = -1
SRIMASSDELLIMIT = 0; SRIMASSDELLIMIT_IND = -1
EXTENTLIMIT = 0; EXTENTLIMIT_IND = -1
LASTSTATEMENT = 0; LASTSTATEMENT_IND = -1
RETURNCODE = 0; RETURNCODE_IND = -1
ERRORMSG = 0; ERRORMSG_IND = -1
IFCARETCODE = 0; IFCARETCODE_IND = -1
IFCARESCODE = 0; IFCARESCODE_IND = -1
XSBYTES = 0; XSBYTES_IND = -1
CRUPDATEDPAGESPCT = 4; CRUPDATEDPAGESPCT_IND = 0
CRCHANGESPCT = 2; CRCHANGESPCT_IND = 0
RRTINSDELUPDPCT = 2; RRTINSDELUPDPCT_IND = 0
RRTUNCLUSTINSPCT = 5; RRTUNCLUSTINSPCT_IND = 0
RRTDISORGLOBPCT = 5; RRTDISORGLOBPCT_IND = 0
RRIAPPENDINSERTPCT = 5; RRIAPPENDINSERTPCT_IND = 0
SRTINSDELUPDPCT = 5; SRTINSDELUPDPCT_IND = 0
SRIINSDELPCT = 5; SRIINSDELPCT_IND = 0
EXTENTLIMIT = 3; EXTENTLIMIT_IND = 0
RRTMASSDELLIMIT = 0; RRTMASSDELLIMIT_IND = -1
RRIMASSDELLIMIT = 0; RRIMASSDELLIMIT_IND = -1
LASTSTATEMENT = COPIES("",8012);
LASTSTATEMENT_IND = 0;
RETURNCODE = 0; RETURNCODE_IND = 0;
ERRORMSG = COPIES("",1331);
ERRORMSG_IND = 0;
IFCARETCODE = 0; IFCARETCODE_IND = 0;
IFCARESCODE = 0; IFCARESCODE_IND = 0;
XSBYTES = 0; XSBYTES_IND = 0;
say "The following values have been set for DSNACCOX"
say "DB2 SSID ...:" ssid
say "CHKLVL .....: Use the chklvl parameter to cause DSNACCOX to"
say " check for related table spaces when processing"
say " index spaces, and delete recommendation for"
say " indexspaces when an action (such as reorg) on the"
say " table space will also cause the action to be done"
say " on the index space. (CHKLVL=64)"
say "CRITERIA ...:" CRITERIA
say "QUERYTYPE...:" QUERYTYPE
say " Use the criteria parameter to cause DSNACCOX to"
say " make recommendations only for objects in database"
say " you want to."
say "Other parms : Following parameter values have been set, which are"
say " lower than the defaults:"
say " CRUPDATEDPAGESPCT 4 RRIAPPENDINSERTPCT"
say " CRCHANGESPCT 2 SRTINSDELUPDPCT"
say " RRTINSERTPCT 2 SRIINSDELPCT"
say " RRTUNCLUSTINSPCT 5 EXTENTLIMIT"
say " RRTDISORGLOBPCT 5"
say " Excluded the checking for these criteria by set the"
say " following values to a negative value:"
say " RRTMASSDELLIMIT -1 RRIMASSDELLIMIT -1"
say ""
if ret_code > maxrc then maxrc = ret_code
return
------------------------------
Sandeep Jain
------------------------------
Original Message:
Sent: Wed May 15, 2024 06:39 PM
From: Chad Breiner
Subject: DSNACCOX and Exceptions Part 2
Hello Sandeep, where can I find the ISPF dialog members for this as well as the REXX DSNACCOX program you referenced?
------------------------------
Chad Breiner
Original Message:
Sent: Wed March 27, 2024 04:44 AM
From: Sandeep Jain
Subject: DSNACCOX and Exceptions Part 2
DSNACCOX and Exceptions Part 2
Exceptions
DSNACCOX allows use of an exceptions table called EXCEPT_TBL with a creator name (schema) matching the id used by DSNACCOX and the results table, DB2DBA is being used throughout.
Mechanism
The exception table must contain the first three columns, DBNAME, NAME and QUERYTYPE and have a primary index on DBNAME and NAME. Additional columns may and have been added and additional indexing may be used. These additions are for our use and ignored by DSNACCOX.
When DSNACCOX is running, where DBNAME and NAME match a row in EXCEPT_TBL the contents of column QUERYTYPE are copied into the results table RESULTS column INEXCEPTTABLE. The contents of QUERYTYPE (and therefore INEXCEPTTABLE) are free format and for our use only.
Columns
As stated above, the first three columns are required, and all other columns are to suit our requirements.
COLUMN | TYPE | LENGTH | REMARKS |
DBNAME | CHAR | 8 | Required, first column of primary key |
NAME | CHAR | 8 | Required, second column of primary key |
QUERYTYPE | CHAR | 40 | Required, contents are free format data for our use |
ENVIRONMENT | CHAR | 2 | Two characters, e.g. P1, D1, D2, R1 etc. |
OBJECTTYPE | CHAR | 2 | TS or IX |
TSTYPE | CHAR | 1 | TYPE taken from SYSIBM.SYSTABLESPACE |
TBTYPE | CHAR | 1 | TYPE taken from SYSIBM.SYSTABLES |
IXTYPE | CHAR | 1 | TYPE taken from SYSIBM.SYSINDEXES |
CSOBJECT | CHAR | 1 | Y = CS, N = Not CS object |
PARTS | SMALLINT | | 0 if not partitioned or number of partitions |
HOWPART | CHAR | 8 | Indication of partitioning type |
CPYFLAG | CHAR | 7 | Copy types (I, F or N) for days of week MTWTFSS |
RUNFLAG | CHAR | 7 | Runstats (Y or N) for days of week MTWTFSS |
EXTFLAG | CHAR | 7 | Alter extents (Y or N) for days of week MTWTFSS |
REOFLAG | CHAR | 7 | Reorg (Y or N) for days of week MTWTFSS |
NOSTATS | CHAR | 1 | Y = Not to collect statistics, else N |
NOEXTENT | CHAR | 1 | Y = Not to alter PRIQTY/SECQTY, else N |
NOREORG | CHAR | 1 | Y = Not to reorganise, else N |
ACTIVE | CHAR | 1 | Y = Between start and end dates (below), else N |
STARTDATE | DATE | | Start date of exception |
ENDDATE | DATE | | End date of exception |
UPDATEDBY | CHAR | 8 | Last updated by user-id (SQLID) |
LASTUPDATED | TIMESTAMP | | Timestamp when last updated |
REMARKS | VARCHAR | 64 | Remarks generated by ISPF dialogue |
The above columns are used and maintained by an ISPF dialogue, so changes to the table layout will require changes to the dialogue.
Dialogue
Setting up
An ISPF dialogue is available to select, insert, update and delete exceptions. Before first use, modify your USER panel adding a suitably named option, for example,
- Add to body: + £X% - $DSNACCOX exception table dialogue
- Add to &ZSEL: X,'CMD(%DSNXCEPT) NEWAPPL(XCPT) PASSLIB'
Option X has been used here, however any letter(s) or number(s) may be used but must match the &ZSEL entry.
Primary Menu
To start dialogue use option U (your user panel) and select the exceptions (e.g. U;X). The first screen presented looks like this:
-------------------------- DSNACCOX Exceptions - Main-------------- SQLcode: 0
Command ===> Scroll ===> CSR
Cmd: TIDY - Tidy up errors or COPY - Copy current entries to new environment
Row: S - Select details, I - Insert object, U - Update or D - Delete a row
DB2 ssid ===> DBxz
Environment ===> XX Use ZZ if not CS
MTWTFSS MTWTFSS MTWTFSS MTWTFSS
C DBname ObjName Ob A Querytype ImgCopy Runstat Extents Reorg
- -------- -------- -- - ---------------------- ------- ------- ------- -------
_ CSXXDC3 EXPLAIN8 TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN
_ CSXXDC3 FLHS0049 TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN
_ CSXXDC3 FLHS0059 TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN
_ CSXXDC3 FLHS0068 TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN
_ DBCSXXNA SNAADDR TS Y NOREORG STATS NOEXTENT IIIIIIF NNNNYNN NNNNNNN NNNNNNN
_ DBCSXXNA SNACACT TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN
_ DBCSXXNA SNACOLC TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN
_ DBCSXXNA SNACOLD TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN
_ DBCSXXNA SNACSMH TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN
_ DBCSXXNA SNACUST TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN
_ DBCSXXNA SNADCEV TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN
_ DBCSXXNA SNADCON TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN
_ DBCSXXNA SNADSRV TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN
_ DBCSXXNA SNAEACO TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN
_ DBCSXXNA SNAHZDN TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN
_ DBCSXXNA SNALYSC TS Y NOREORG NOSTATS NOEXTE IIIIIIF NNNNNNN NNNNNNN NNNNNNN
It shows at the top the DB2 group name and environment which may be changed by overtyping. The main body shows the database and name of the object and type (TS or IX) followed by the availability, start of the QUERYTYE column and then the image copy, runstats, extents and reorg flags.
The dialogue uses row command S, I, U and D, allowing rows to be selected, inserted, updated and deleted.
Insert
Insert row command, is a two-step process. Initially enter details shown in red (object type, database, name, with status (CS?) and environment, and if required overtype the active start and end dates. By default, exceptions are active from date of setting up until 31/12/9999.
On pressing enter a row is inserted into the exceptions table and an update screen is displayed to complete the new object processing.
------------------------- DSNACCOX Exceptions - Inser Step 1 Basic details
Command ===>
Insert Basic Details - Supply required details in red then press enter
Database Tablespace or index
Object TS DBCSXX44 S44COLG___________
Query Type ERROR___________________________________
CS EN TS TB IX
Status Y XX __ __ __
Parts 32___ INDEX___
M T W T F S S Stop housekeeping?
Image Copy I I I I I I F Image copies always run
Runstats _ _ _ _ _ _ _ _ Runstats not to be run!
Extents _ _ _ _ _ _ _ _ Alter space not to run!
Reorg N N N N N Y N N Reorgs are not to run!
Active from 28 02 2024 to 31 12 9999 Y
Updated By ABC1234_
Updated On 2024-02-28-16.15.35.973844
Remarks...
INSERT INCOMPLETE_____________________________________________
make any changes to the days of week when to or not to run utilities etc. and press enter to see the effect of the changes. When ready use PF3 to exit and save the details.
------------------------- DSNACCOX Exceptions - Update -------- Change details
Command ===>
Update details - Press enter to see updates then PF3 to apply changes
Database Tablespace or index
Object TS DBCSXX44 S44COLG___________
Query Type NOSTATS_________________________________
CS EN TS TB IX
Status Y XX L_ T_ __
Parts 32___ INDEX___
M T W T F S S Stop housekeeping?
Image Copy I I I I I I F Image copies always run
Runstats N Y N N N N N N Runstats are to be run
Extents N N N N N N N Y Alter space not to run!
Reorg N N N N N Y N N Reorgs are to be run
Active from 28 02 2020 to 31 12 9999 Y
Updated By ABC1234_
Updated On 2020-02-28-16.15.35.973844
Remarks...
INSERTED NEW OBJECT___________________________________________
Press enter to refresh then PF3 to save or CAN to cancel
Select
Row command S selects rows and display the full detail of the exception.
------------------------- DSNACCOX Exceptions - Select ------------ SQLcode: 0
Command ===>
Details of selected row - Press enter to see potential updates then PF3 to exit
Object TS DBCSXX44 S44COLG___________
Query Type STATS NOEXTENT__________________________
CS EN TS TB IX
Status Y XX L_ T_ __
Parts 32___ INDEX___
MTWTFSS Stop housekeeping?
Image Copy IIIIIIF Image copies always run
Runstats NYNNNNN N Runstats are to be run
Extents NNNNNNN Y Alter space not to run!
Reorg NNNNNYN N Reorgs are to be run
Active from 28.02.2020 to 31.12.9999 Y
Updated By ABC1234_
Updated On 2020-02-28-16.34.21.146539
Remarks...
AUTO update by TIDY command
Update
U row command allows rows to be updated, see fields using red font on the screen (not highlighted below).
------------------------- DSNACCOX Exceptions - Update -------- Change details
Command ===>
Update details - Press enter to see updates then PF3 to apply changes
Database Tablespace or index
Object TS DBCSXX44 S44COLG___________
Query Type STATS NOEXTENT__________________________
CS EN TS TB IX
Status Y XX L_ T_ __
Parts 32___ INDEX___
M T W T F S S Stop housekeeping?
Image Copy I I I I I I F Image copies always run
Runstats N Y N N N N N N Runstats are to be run
Extents N N N N N N N Y Alter space not to run!
Reorg N N N N N Y N N Reorgs are to be run
Active from 28 02 2024 to 31 12 9999 Y
Updated By ABC1234_
Updated On 2024-02-28-16.34.21.146539
Remarks...
UPDATED OBJECT________________________________________________
Press enter to refresh then PF3 to save or CAN to cancel
Delete
Typing D next to an exception allows a row to be deleted. On pressing enter a confirmation screen is displayed, press enter again to delete or PF3 to cancel and keep the row. Confirmation screen:
------------------------- DSNACCOX Exceptions - Delet ENTER or PF3 to keep
Command ===>
Delete Object
Object TS DBCSXX44 S44COLG
Query Type STATS NOEXTENT
Environment XX
No. of rows 1
=======================================================
= =
= WARNING - Deleting row(s) from exceptions table =
= =
= =
= Press ENTER key to confirm deletion =
= =
= or =
= =
= Use PF3 or type CAN to cancel the request =
= =
=======================================================
After pressing enter to confirm deletion, the dialogue return to the main screen with a confirmatory message (highlighted).
-------------------------- DSNACCOX Exceptions - Main --- Row has been deleted
Command ===> Scroll ===> CSR
Cmd: TIDY - Tidy up errors or COPY - Copy current entries to new environment
Row: S - Select details, I - Insert object, U - Update or D - Delete a row
DB2 ssid ===> DBXZ
Environment ===> XX Use ZZ if not CS
MTWTFSS MTWTFSS MTWTFSS MTWTFSS
C DBname ObjName Ob A Querytype ImgCopy Runstat Extents Reorg
- -------- -------- -- - ---------------------- ------- ------- ------- -------
Page commands TIDY and COPY are available.
- Tidy command displays exceptions where there is an error or mismatched parameters with a facility to automatically or manually correct the problems.
- Copy command allows exceptions in one environment to be copied into another, with any pre-existing entries are ignored.
Tidy
The Tidy command checks for inconsistencies (object does not exist, flags and availability) and reports them on the following screen. Since there are no problems nothing is shown.
-------------------------- DSNACCOX Exceptions - Tidy -------- Empty list, PF3
Command ===> Scroll ===> CSR
The following exceptions are incorrectly setup, take action to correct.
Use command AUTO to automatically try and correct errors or use row command
S Select details, U Update and correct or D Delete object from list.
DB2 ssid ===> DBXZ
Environment ===> XX
Error MTWTFSS MTWTFSS MTWTFSS MTWTFSS Available dates
C DBname ObjName Status ImgCopy Runstat Extents Reorg A Start End
- -------- -------- ------- ------- ------- ------- ------- - -------- --------
******************************* Bottom of data ********************************
Auto
If object were listed, they may be corrected manually (update and delete available) or automatically using the AUTO command. Auto rebuilds the flags and updates rows in the exceptions table, normally this resolves issues.
Copy
Use the copy command to copy exceptions in one environment into another. It should be noted that any pre-existing entries in the target environment are not update or replaced. Example XX to D1:
-------------------------- DSNACCOX Exceptions - Copy -------------------------
Command ===>
Copy an exception list
Supply new target environment and press enter key to copy all exceptions used
by the source environment into the target environment. Any pre-existing target
exceptions will be unchanged.
SOURCE
Environment XX
TARGET
Environment XY
After the target environment name has been supplied, press enter and upon completion the target exception will be displayed. When copying, the database name of CS objects will be renamed to reflect the new environment.
To change the source environment, return to the main list and overtype the environment name which will redisplay the exceptions list, then repeat copy command.
Housekeeping
DSNACCOX produces recommendation for housekeeping where objects are in too many extents, in a restricted state, where image copies, statistics and reorganisation are required. Further jobs are required to generate suitable housekeeping.
Recommendations
If for example housekeeping for objects in R1 database DBCSR180, where are in a restricted state or an image copy or reorg are required, then a job like this may be 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=R1 <-- ENVIRONMENT XY, XX ETC. (## BELOW)
// SET QTYPE='REORG COPY RESTRICT'
// SET SCHEMA=DB2DBA <-- OVERRIDE SCHEMA, DEFAULT DSNACC
// SET DBNAME='DBCSXY80' <-- 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
After processing the results header table will contain a row for this run. The query needs to specify the environment (R1) and use a QTYPE matching that used in the job above.
Note – The keywords must be in the same sequence with one space between keywords.
SELECT MAX(LASTUPDATED) LASTUPDATED
FROM DB2DBA.RESULTS_HEADER
WHERE ENVIRONMENT = 'XY'
AND QTYPE = 'REORG COPY RESTRICT'
AND RUNOK = 'Y'
WITH UR;
---------+---------+---------+---------+----
LASTUPDATED
---------+---------+---------+---------+----
2020-03-04-17.03.20.000000
The query above returns the associated timestamp (2020-03-04-17.03.20.000000) which matches the LASTUPDATED timestamp in the results table.
Generation
This query above may then be used as a subquery when selecting rows from the results table, see below.
, CHAR(DBNAME,8) DBNAME
, CHAR(NAME,18) NAME
, PARTITION PART
, OBJECTTYPE TYP
, IMAGECOPY COPY
, RUNSTATS RUN
, EXTENTS EXT
, REORG REO
, OBJECTSTATUS STATUS
, INEXCEPTTABLE
FROM DB2DBA.RESULTS R
WHERE DB2GROUP = 'DBXZ'
AND ENVIRONMENT = 'XY'
AND LASTUPDATED = (SELECT MAX(LASTUPDATED)
FROM DB2DBA.RESULTS_HEADER
WHERE ENVIRONMENT = R.ENVIRONMENT
AND QTYPE = 'REORG COPY RESTRICT'
AND RUNOK = 'Y')
Notes on generating jobs:
- QTYPE of "ALL" may be used so DSNACCOX is only run once per day and followed by separate generate jobs, producing image copies, reorgs and runstats jobs.
- A better way may be to run separate DSNACCOX and generate HK jobs for QTYPE "COPY" and then then "REORG EXTENTS RESTRICT" and finally "RUNSTATS" so that recommendations are more current. This is because typically image copies would be run early evening and any reorgs later with Runstats later still.
The process of generating housekeeping jobs can be complicated due to the variety of object types and sizes and the impact due to job elapse times and load on the system.
In the past I check recommendations to run Runstats with the days since statistics last collected (age) and size of objects, with results in descending age order so that objects are not forgotten. When processing the list, a running total of object sizes is used to limit the number of jobs.
Flowchart
Here is a high-level flowchart showing the running of DSNACCOX and generation of housekeeping jobs.
The starting point is DSNACCOX run to generate a list of recommended housekeeping which may be one or many types (COPY, EXTENTS, RESTRICT, RUNSTATS, REORG or ALL). The end point is reading the results table and checking for the housekeeping you are interested in. The exceptions keywords will already be in the results but of the day of the week flags are required, will need to select the object from the exceptions table. The flags CPYFLAG, RUNFLAG, EXTFLAG and REOFLAG are CHAR (7) columns representing days of the week from Monday (byte 1) to Sunday (byte 7).
To prevent an excessive number of objects being processed (not image copies), keep a track of the size of objects (partitions) and a running total, ending the housekeeping generation after exceeding the limits (e.g. assuming object size is proportional to elapsed time).
+-----------------------------+
| Setup and run job DSNACCOX. |
+--------------+--------------+
|
+--------------+--------------+
| DSNACCOX generates |<--- Db2 Catalog
| recommendations and check |<--- EXCEPT_TBL
| for exceptions, writing |
| RESULTS and updates the |\
| RESULTS_HEADER | \
+--------------+--------------+ \
| > RESULTS and
+--------------+--------------+ / RESULTS_HEADER
| Query results (1 row per | /
| object/partition) |/
+--------------+--------------+
|
+--------------+--------------+
| Check column INEXCEPTTABLE |
| for NO or keyword list |
+--------------+--------------+
|
+--------------+--------------+
| If required check columns |
| CPYFLAG, RUNFLAG, EXTFLAG |<--- EXCEPT_TBL
| or REOFLAG in EXCEPT_TBL |
+--------------+--------------+
|
|---------<--------+
| |
+--------------+--------------+ |
| Generate housekeeping | |
| parameters where appropriate| |
+--------------+--------------+ |
| |
+--------------+--------------+ |
| Keep track of object sizes. | |
+--------------+--------------+ |
| |
+--------------+--------------+ |
| Check if still within limits| |
+---------+----------+--------+ |
No| |Yes |
Or end| +------>-----+
|
+---------+-------------------+
| Schedule housekeeping jobs. |
+-----------------------------+
------------------------------
Sandeep Jain
------------------------------