REXX procedure to execute DSNACCOX DB2-supplied stored procedure (V9)
DSNACCOX, the successor of DSNACCOR, is a IBM DB2-supplied stored procedure to provide information to maintain DB2 objects. It includes proposals for creating image copyies (COPY), reorganization (REORG) and renewal of statistics (RUNSTATS).
This version covers DB2 for z/OS Version 9.
/*REXX****************************************************************/
/* XMPACCOX ..: Retrieve recommendations to help you maintain */
/* DB2 V9 z/OS databases using DSNACCOX stored procedure*/
/*-------------------------------------------------------------------*/
/* CALLED BY..: IKJEFT01 - TSO Batch oder Online */
/* ARGUMENTS..: SSID - Local/Home Subsystem */
/* CRITERIA - Criteria for DSNACCOX's recommendations */
/* CALLS......: DSNREXX M - DB2/REXX Interface */
/* XMPABND M - Abend U0001 (Non-ISPF) */
/*-------------------------------------------------------------------*/
/* COMMENTS...: Verify that SDSNLOAD ist part of LINKLIST or already */
/* allocated to TSO session. If not, you need to */
/* dynamically allocate. (See STEPLIBX in code below!) */
/*-------------------------------------------------------------------*/
/* FUNCTION...: Calls DSNACOXX stored procedure to determine objects */
/* which need maintenance (copy, runstats, reorg, all). */
/*-------------------------------------------------------------------*/
/* HISTORY....: 19.08.10/G.Ruban - V2R0 adapted from DSNACCOR->COX */
/*********************************************************************/
x = MSG("OFF")
/*********************************************************************/
/* process arguments passed to procedure */
/*********************************************************************/
parse upper arg arglist
ver = "2.0"
Numeric Digits 12
if pos("DEBUG",arglist)>0
then do
debug = "DEBUG"
TRACE ?I
SQLTRACE = "TRACE"
end
/*********************************************************************/
/* Some environment information and initialzations */
/*********************************************************************/
PARSE SOURCE,
source_host_env,
source_call_type,
source_uc_exec_name,
source_hostdd,
source_hostdsn,
source_nf_exec_name,
source_initial_cmd_env,
source_asid_name
jobnamestep = Get_Jobname()
sysvar_sysuid = SYSVAR("SYSUID")
sysvar_sysenv = SYSVAR("SYSENV")
/*********************************************************************/
/* Header */
/*********************************************************************/
"CLRSCRN"
say copies("#",79)
say "# XMPACCOX V"ver" - Recommendations to maintain DB2 objects ",
" "date("O")" "time()" #";
say copies("#",79)
say ""
/*********************************************************************/
/* Cechk required environment */
/*********************************************************************/
if (source_host_env ^= "TSO") then do
Say "Wrong host environment" source_host_env
signal error
end
parse var arglist ssid CRITERIA
if debug = "DEBUG"
then do
i = pos("DEBUG",ssid)
if i>0 then ssid = delstr(ssid,i,5)
i = wordpos("DEBUG",CIRTERIA)
if i>9 then CRITERIA = delword(CRITERIA,i,1)
end
if ssid = "" ! ssid = "DEBUG"
then do
say "Executing without arguments,",
"setting default DB2 subsystem id and criteria."
ssid = "DB2T"
CRITERIA = "DBNAME LIKE 'DSN%'", /* WHERE CLAUSE 4096 */
"AND NAME LIKE '%'"
end
CRITERIA_IND = 0
say "XMPACCOX ...: 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 " Use the criteria parameter to cause DSNACCOX to "
say " make recommendations only for objects in databases"
say " you want to."
say "Other parms : Following parameter values have been set, which are"
say " lower than the defaults:"
say " CRUPDATEDPAGESPCT 4 RRIAPPENDINSERTPCT 5"
say " CRCHANGESPCT 2 SRTINSDELUPDPCT 5"
say " RRTINSERTPCT 2 SRIINSDELPCT 5"
say " RRTUNCLUSTINSPCT 5 EXTENTLIMIT 3"
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 DB2 Load Library is not part of z/OS LINKLIST: */
/* Allocate SDSNLOAD using CA TSOPLUS (or any other which adds the */
/* DB2 load libray dynamically to the STEPLIB). */
/*********************************************************************/
address TSO
"FREE FI(STEPLIBX) DA('SAMPLE."ssid".SDSNLOAD')"
"ALLOC FI(STEPLIBX) DA('SAMPLE."ssid".SDSNLOAD') SHR REUSE"
if rc <> 0
then do
say "DB2 SDSNLOAD library for SSID="ssid" is not available!"
say "Check z/OS LINKLIST or allocate to STEPLIB in advance!"
signal error
end
ADDRESS TSO "SUBCOM DSNREXX"
if rc <> 0
then do
cc = RXSUBCOM("ADD","DSNREXX","DSNREXX")
if cc
then do
say "DB2 SDSNLOAD library not available!"
say "Check z/OS LINKLIST or allocate to STEPLIB in advance!"
signal error
end
end
/*********************************************************************/
/* Setup utility name and utility statement variables: */
/*********************************************************************/
QUERYTYPE = "ALL"; QUERYTYPE_IND = 0 ;
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;
/*********************************************************************/
/* Set the indicator variables to 0 for non-null input parameters */
/* (parameters for which you do not want DSNACCOX to use default */
/* values) and for output parameters. */
/*********************************************************************/
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;
/*********************************************************************/
/* call DB2 stored procedure */
/*********************************************************************/
sqlstmt = "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)";
ADDRESS DSNREXX "CONNECT" ssid
call Code 0
address DSNREXX "EXECSQL" sqlstmt
call Code 0 466 /* +466 = more rows to fetch */
if returncode = 0
then do
/* say copies("=",79) */
/* say "Approximate "space(delword(errormsg,1,1)), */
/* "of the total number of objects in the subsystem that", */
/* "have information in the real-time statistics tables." */
/* say copies("=",79) */
/* say "" */
say "Recommendations: You should watch ..."
say copies("-",79)
end
else do
if returncode = 4
then Say "DSNACCOX completed, but with incompatible parms."
if returncode = 8
then say "DSNACCOX terminated with errors."
if returncode = 12
then say "DSNACCOX terminated with severe errors."
if returncode = 14
then say "You need to create the RTS tables or grant access."
if returncode = 16
then say "You need to create a TEMP database and tablespaces."
say "Error Message......: "errormsg
say "IFCA Return Code...: "IFCARetCode
say "IFCA Reseas Code...: "IFCAResCode
say "Last Statement.....: "laststatement
signal error
end
/*********************************************************************/
/* Combine two locators with the stored procedure */
/*********************************************************************/
LOC1 = d2x(0)
LOC2 = d2x(0)
sqlstmt = "ASSOCIATE LOCATORS (:LOC1, :LOC2)",
"WITH PROCEDURE SYSPROC.DSNACCOX"
address DSNREXX "EXECSQL" sqlstmt;
call code 0
/*********************************************************************/
/* Allocate and process cursor-driven */
/*********************************************************************/
sqlstmt = "ALLOCATE C101 CURSOR FOR RESULT SET :LOC1"
address DSNREXX "EXECSQL" sqlstmt;
do while sqlcode = 0
call code 0
sqlstmt = "FETCH C101 INTO :RS_SEQ, :RS_DATA"
Address DSNREXX "EXECSQL" sqlstmt;
/*say RS_SEQ RS_DATA*/
end
call code 0 100
sqlstmt = "CLOSE C101"
ADDRESS DSNREXX "EXECSQL "sqlstmt;
call code 0 100
/*********************************************************************/
/* Allocate and process cursor-driven */
/*********************************************************************/
sqlstmt = "ALLOCATE C102 CURSOR FOR RESULT SET :LOC2"
address DSNREXX "EXECSQL" sqlstmt;
call Code 0
/*********************************************************************/
/* Fetch all messages via the cursor */
/*********************************************************************/
sqlstmt = "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 ";
do while sqlcode = 0
if OBJECTTYPE <> "ALL"
then do
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
Address DSNREXX "EXECSQL" SQLSTMT
end
call code 0 100
sqlstmt = "CLOSE C102"
address DSNREXX "EXECSQL "sqlstmt;
call code 0 100
say "*** end ***"
/*********************************************************************/
/* Disconnect vom Subsystem */
/*********************************************************************/
Disconnect:
ADDRESS DSNREXX "DISCONNECT"
cc = RXSUBCOM("DELETE","DSNREXX","DSNREXX")
return
/*********************************************************************/
/* Error processing and termination */
/*********************************************************************/
SYNTAX:
say "XMPACCOX: Syntax error."
say " [TSO] XMPACCOX ssid options"
say " ssid .......: DB2 Subsystem ID"
say " criteria ...: criteria for DSNACCOX"
ERROR:
say ""
say "Current enviroment:" jobnamestep source_host_env,
source_call_type source_uc_exec_name source_hostdd,
source_hostdsn source_nf_exec_name source_initial_cmd_env
If Sysvar(SYSISPF) = "NOT ACTIVE"
then do
say "REXX procedure XMPACCOX ends with U0001."
address ATTACH "XMPABND"
exit 0
end
say "REXX procedure XMPACCOX ends with RC=20."
exit 20
/*********************************************************************/
/* Check SQLCODE's allowed */
/*********************************************************************/
Code:
arg codes
allowed = "NO"
do ci=1 to words(codes)
if sqlcode = word(codes,ci)
then allowed = "YES"
end
if allowed = "NO"
then do
say "CONNECT: SQL Error with SQLCODE="sqlcode
call TIAR sqlcode
say " "sqlstmt
call Disconnect
signal error
end
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
/*********************************************************************/
/* Retrieve some internal information from z/OS control blocks */
/*********************************************************************/
GET_JOBNAME: Procedure
/* all fields are part of general use prog'ing interface */
PSATOLD = '21C'X
TCB = C2D(STORAGE(C2X(PSATOLD),4))
TIOT = C2D(STORAGE(D2X(TCB+12),4))
TIOCNJOB = STRIP(STORAGE(D2X(TIOT),8))
TIOCSTEP = STRIP(STORAGE(D2X(TIOT+8),8))
Return TIOCNJOB TIOCSTEP
/*********************************************************************/
/* Display SQL Commmunication area using DSNTIAR */
/*********************************************************************/
TIAR: arg sqlcode
sqlc = d2x(sqlcode,8)
sqlc = x2c(sqlc)
sqlca = 'sqlca '
sqlca = sqlca !! x2c(00000088)
sqlca = sqlca !! sqlc
sqlca = sqlca !! x2c(0000)
sqlca = sqlca !! copies(' ',78)
sqlca = sqlca !! copies(x2c(00),24)
sqlca = sqlca !! copies(' ',16)
tiar_msg = x2c(0190)copies(' ',400)
text_len = x2c(00000050) ;
/***************************************************************/
/* Extract Message from Return Area */
/***************************************************************/
ADDRESS ATTCHPGM 'DSNTIAR SQLCA TIAR_MSG TEXT_LEN'
say substr(tiar_msg,4,400);
/***************************************************************/
/* Extract Message from Return Area */
/***************************************************************/
len = c2d(substr(rtrnarea,5,2))
/***************************************************************/
/* Extract Message from Return Area */
/***************************************************************/
say " SQLCODE="sqlcode
say " SQLERRMC="sqlerrmc
say " SQLWARN5="sqlwarn.5
say " SQLERRP ="sqlerrp
say " SQLSTATE="sqlstate
return
A possible result looks like …
###############################################################################
# XMPACCOX V2.0 - Recommendations to maintain DB2 objects 11/02/17 19:25:30 #
###############################################################################
Executing without arguments, setting default DB2 subsystem id and criteria.
XMPACCOX …: Following values have been set for DSNACCOX
DB2 SSID …: DB2T
CHKLVL …..: Use the chklvl parameter to cause dsnaccox to
check for related table spaces when processing
index spaces, and delete recommendation for
indexspaces when an action (such as reorg) on the
table space will also cause the action to be done
on the index space. (CHKLVL=64)
CRITERIA …: DBNAME LIKE ‚DSN%‘ AND NAME LIKE ‚%‘
Use the criteria parameter to cause DSNACCOX to
make recommendations only for objects in databases
you want to.
Other parms : Following parameter values have been set, which are
lower than the defaults:
CRUPDATEDPAGESPCT 4 RRIAPPENDINSERTPCT 5
CRCHANGESPCT 2 SRTINSDELUPDPCT 5
RRTINSERTPCT 2 SRIINSDELPCT 5
RRTUNCLUSTINSPCT 5 EXTENTLIMIT 3
RRTDISORGLOBPCT 5
Excluded the checking for these criteria by set the
following values to a negative value:
RRTMASSDELLIMIT -1 RRIMASSDELLIMIT -1
Recommendations: You should watch …
——————————————————————————-
DATABASE: DSN8D81A SPACENAME: DSN8S81D (TS)
– Imagecopy …: FULL, last done 0001-01-01-00.00.00.000000
8 % UPDATED PAGES
28 % ROWS INS/DEL/UPD
734549 DAY(S) SINCE LAST COPY
DATABASE: DSN8D81A SPACENAME: DSN8S81E (TS)
– Imagecopy …: FULL, last done NEVER
DATABASE: DSN8D81A SPACENAME: DSN8S81E (TS)
– Imagecopy …: FULL, last done NEVER
DATABASE: DSN8D81A SPACENAME: DSN8S81E (TS)
– Imagecopy …: FULL, last done NEVER
DATABASE: DSN8D81A SPACENAME: DSN8S81E (TS)
– Imagecopy …: FULL, last done NEVER
DATABASE: DSN8D81A SPACENAME: DSN8S81E (TS)
– Imagecopy …: FULL, last done NEVER
DATABASE: DSN8D81A SPACENAME: DSN8S81P (TS)
– Imagecopy …: FULL, last done 0001-01-01-00.00.00.000000
734549 DAY(S) SINCE LAST COPY
DATABASE: DSN8D81A SPACENAME: DSN8S81R (TS)
– Imagecopy …: FULL, last done 0001-01-01-00.00.00.000000
734549 DAY(S) SINCE LAST COPY
– Reorg …….: YES, last done 0001-01-01-00.00.00.000000
DATABASE: DSN8D81A SPACENAME: DSN8S81S (TS)
– Imagecopy …: FULL, last done 0001-01-01-00.00.00.000000
734549 DAY(S) SINCE LAST COPY
– Reorg …….: YES, last done 0001-01-01-00.00.00.000000
DATABASE: DSN8D81E SPACENAME: DSN8S81U (TS)
– Imagecopy …: FULL, last done 0001-01-01-00.00.00.000000
734549 DAY(S) SINCE LAST COPY
DATABASE: DSN8D81P SPACENAME: DSN8S81C (TS)
– Imagecopy …: FULL, last done 0001-01-01-00.00.00.000000
734549 DAY(S) SINCE LAST COPY
DATABASE: DSN8D81P SPACENAME: DSN8S81Q (TS)
– Imagecopy …: FULL, last done 0001-01-01-00.00.00.000000
734549 DAY(S) SINCE LAST COPY
DATABASE: DSN8D81U SPACENAME: NEWDEPT (TS)
– Imagecopy …: FULL, last done NEVER
– Reorg …….: YES, last done 2008-12-16-15.00.33.305895
100 % ROWS INS/DEL/UPD
DATABASE: DSN8D81U SPACENAME: NEWPHONE (TS)
– Imagecopy …: FULL, last done NEVER
– Reorg …….: YES, last done 2008-12-16-15.00.33.712209
100 % ROWS INS/DEL/UPD
DATABASE: DSNADMDB SPACENAME: DSNADMTH (TS)
– Runstats ….: YES, last done NEVER
DATABASE: DSNATPDB SPACENAME: DSNASDMA (TS)
– Reorg …….: YES, last done NEVER
DATABASE: DSNATPDB SPACENAME: DSNASDME (TS)
– Reorg …….: YES, last done NEVER
DATABASE: DSNATPDB SPACENAME: DSNASDMU (TS)
– Reorg …….: YES, last done NEVER
DATABASE: DSNATPDB SPACENAME: DSNATPTS (TS)
– Reorg …….: YES, last done NEVER
DATABASE: DSNDB04 SPACENAME: TSQ18925 (TS)
– Imagecopy …: FULL, last done NEVER
– Runstats ….: YES, last done NEVER
DATABASE: DSNDB04 SPACENAME: TSQ41683 (TS)
– Imagecopy …: FULL, last done NEVER
– Runstats ….: YES, last done NEVER
DATABASE: DSNDB04 SPACENAME: TSQ89405 (TS)
– Imagecopy …: FULL, last done NEVER
– Runstats ….: YES, last done NEVER
DATABASE: DSNDB06 SPACENAME: SYSALTER (TS)
– Runstats ….: YES, last done 2010-08-23-11.54.16.724754
586 % ROWS INS/DEL/UPD
– Reorg …….: YES, last done 2011-10-29-14.11.27.622702
43 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSCONTX (TS)
– Runstats ….: YES, last done 2010-08-23-11.54.17.032202
300 % ROWS INS/DEL/UPD
– Reorg …….: YES, last done 2011-10-29-14.16.32.605912
DATABASE: DSNDB06 SPACENAME: SYSCOPY (TS)
– Imagecopy …: INC , last done 2012-02-17-06.07.18.872718
– Runstats ….: YES, last done 2010-08-23-11.54.18.580229
1029 % ROWS INS/DEL/UPD
– Reorg …….: YES, last done 2011-10-29-14.11.34.498504
114 % ROWS INS/DEL/UPD
7 % ROWS UNCLUSTERED INSERT’S
DATABASE: DSNDB06 SPACENAME: SYSDBASE (TS)
– Runstats ….: YES, last done 2010-08-23-11.54.19.636099
274 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSDBAUT (TS)
– Runstats ….: YES, last done 2010-08-23-11.54.27.024685
65 % ROWS INS/DEL/UPD
– Reorg …….: YES, last done 2011-10-29-14.12.27.742294
DATABASE: DSNDB06 SPACENAME: SYSDDF (TS)
– Runstats ….: YES, last done 2010-08-23-11.54.27.699100
13 % ROWS INS/DEL/UPD
– Reorg …….: YES, last done 2011-10-29-14.12.44.538830
DATABASE: DSNDB06 SPACENAME: SYSEBCDC (TS)
– Reorg …….: YES, last done NEVER
DATABASE: DSNDB06 SPACENAME: SYSGPAUT (TS)
– Runstats ….: YES, last done 2010-08-23-11.54.28.917269
29 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSGROUP (TS)
– Runstats ….: YES, last done 2010-08-23-11.54.29.166183
76 % ROWS INS/DEL/UPD
– Reorg …….: YES, last done 2011-10-29-14.12.56.197333
DATABASE: DSNDB06 SPACENAME: SYSGRTNS (TS)
– Runstats ….: YES, last done 2010-08-23-11.54.29.747266
DATABASE: DSNDB06 SPACENAME: SYSHIST (TS)
– Runstats ….: YES, last done 2010-08-23-11.54.30.205863
111 % ROWS INS/DEL/UPD
– Reorg …….: YES, last done 2011-10-29-14.13.12.652053
42 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSJAUXA (TS)
– Reorg …….: YES, last done NEVER
DATABASE: DSNDB06 SPACENAME: SYSJAUXB (TS)
– Reorg …….: YES, last done NEVER
DATABASE: DSNDB06 SPACENAME: SYSJAVA (TS)
– Runstats ….: YES, last done 2010-08-23-11.54.31.545214
DATABASE: DSNDB06 SPACENAME: SYSOBJ (TS)
– Runstats ….: YES, last done 2010-08-23-11.54.32.159832
161 % ROWS INS/DEL/UPD
– Reorg …….: YES, last done 2011-10-29-14.13.44.983536
31 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSPKAGE (TS)
– Runstats ….: YES, last done 2010-08-23-11.54.36.461244
335 % ROWS INS/DEL/UPD
– Reorg …….: YES, last done 2011-10-29-14.14.05.817685
34 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSPLAN (TS)
– Runstats ….: YES, last done 2010-08-23-11.54.38.156912
243 % ROWS INS/DEL/UPD
– Reorg …….: YES, last done 2011-10-29-14.14.13.682557
32 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSPLUXA (TS)
– Runstats ….: YES, last done 2010-08-23-11.54.38.515056
15700 % ROWS INS/DEL/UPD
– Reorg …….: YES, last done 2011-10-29-14.16.00.877993
3000 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSROLES (TS)
– Runstats ….: YES, last done 2010-08-23-11.54.38.630576
DATABASE: DSNDB06 SPACENAME: SYSSEQ (TS)
– Runstats ….: YES, last done 2010-08-23-11.54.40.091097
96 % ROWS INS/DEL/UPD
– Reorg …….: YES, last done 2011-10-29-14.14.20.156097
DATABASE: DSNDB06 SPACENAME: SYSSEQ2 (TS)
– Runstats ….: YES, last done 2010-08-23-11.54.40.556231
94 % ROWS INS/DEL/UPD
– Reorg …….: YES, last done 2011-10-29-14.14.26.592014
DATABASE: DSNDB06 SPACENAME: SYSSTATS (TS)
– Imagecopy …: INC , last done 2012-02-17-06.07.07.620002
– Runstats ….: YES, last done 2010-08-23-11.54.40.996292
7221 % ROWS INS/DEL/UPD
– Reorg …….: YES, last done 2011-10-29-14.14.58.985117
2006 % ROWS INS/DEL/UPD
1451 % ROWS UNCLUSTERED INSERT’S
DATABASE: DSNDB06 SPACENAME: SYSSTR (TS)
– Runstats ….: YES, last done 2010-08-23-11.54.41.821443
94 % ROWS INS/DEL/UPD
– Reorg …….: YES, last done 2011-10-29-14.15.07.509454
49 % ROWS INS/DEL/UPD
6 % ROWS UNCLUSTERED INSERT’S
DATABASE: DSNDB06 SPACENAME: SYSTARG (TS)
– Runstats ….: YES, last done 2010-08-23-11.54.42.414679
1900 % ROWS INS/DEL/UPD
– Reorg …….: YES, last done 2011-10-29-14.15.49.328602
900 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSUSER (TS)
– Runstats ….: YES, last done 2010-08-23-11.54.42.699836
8 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSVIEWS (TS)
– Runstats ….: YES, last done 2010-08-23-11.54.44.840788
84 % ROWS INS/DEL/UPD
DATABASE: DSNDB06 SPACENAME: SYSXML (TS)
– Runstats ….: YES, last done 2010-08-23-11.54.46.074462
212 % ROWS INS/DEL/UPD
– Reorg …….: YES, last done 2011-10-29-14.15.43.445283
150 % ROWS INS/DEL/UPD
DATABASE: DSNOSCDB SPACENAME: DSNMQLTS (TS)
– Runstats ….: YES, last done NEVER
DATABASE: DSNOSCDB SPACENAME: DSNOSCL1 (TS)
– Runstats ….: YES, last done NEVER
DATABASE: DSNOSCDB SPACENAME: DSNOSCTS (TS)
– Reorg …….: YES, last done 2010-07-20-09.52.42.282010
520 % ROWS INS/DEL/UPD
DATABASE: DSNRLST SPACENAME: DSNRLS01 (TS)
– Reorg …….: YES, last done NEVER
DATABASE: DSN8D81A SPACENAME: XEMP1 (IX)
– Reorg …….: YES, last done NEVER
DATABASE: DSN8D81A SPACENAME: XEMP1 (IX)
– Reorg …….: YES, last done NEVER
DATABASE: DSN8D81A SPACENAME: XEMP1 (IX)
– Reorg …….: YES, last done NEVER
DATABASE: DSN8D81A SPACENAME: XEMP2 (IX)
– Reorg …….: YES, last done NEVER
DATABASE: DSN8D81A SPACENAME: XEMP1 (IX)
– Reorg …….: YES, last done NEVER
DATABASE: DSN8D81A SPACENAME: XEMP1 (IX)
– Reorg …….: YES, last done NEVER
DATABASE: DSNADMDB SPACENAME: ADMIN_TASKS_IX (IX)
– Imagecopy …: YES , last done NEVER
DATABASE: DSNADMDB SPACENAME: ADMIN_TASKS_HIST_IX (IX)
– Imagecopy …: YES , last done NEVER
DATABASE: DSNDB06 SPACENAME: DSNACX01 (IX)
– Reorg …….: YES, last done 2011-10-29-14.12.17.780825
DATABASE: DSNDB06 SPACENAME: DSNDXX01 (IX)
– Reorg …….: YES, last done 2011-10-29-14.12.17.780470
DATABASE: DSNDB06 SPACENAME: DSNDRX01 (IX)
– Reorg …….: YES, last done 2011-10-29-14.12.17.780618
DATABASE: DSNDB06 SPACENAME: DSNDKX01 (IX)
– Reorg …….: YES, last done 2011-10-29-14.12.17.780677
DATABASE: DSNDB06 SPACENAME: DSNDYX01 (IX)
– Reorg …….: YES, last done 2011-10-29-14.12.17.780854
DATABASE: DSNDB06 SPACENAME: DSNATX03 (IX)
– Reorg …….: YES, last done 2011-10-29-14.12.17.780766
DATABASE: DSNDB06 SPACENAME: DSNATX02 (IX)
– Reorg …….: YES, last done 2011-10-29-14.12.17.780737
DATABASE: DSNDB06 SPACENAME: DSNDPX01 (IX)
– Reorg …….: YES, last done 2011-10-29-14.12.17.780040
DATABASE: DSNDB06 SPACENAME: DSNDPX03 (IX)
– Reorg …….: YES, last done 2011-10-29-14.12.17.780108
DATABASE: DSNDB06 SPACENAME: DSNDTX02 (IX)
– Reorg …….: YES, last done 2011-10-29-14.12.17.780253
*** end ***
Comments
Comments are closed.