REXX procedure for DPropR Apply V5 Information and Control
This TSO/E REXX procedures reads information from Data Propagator tables, which can be used to control dependent process flow.
The procedure is quite old but still a good sample for querying Data Propagator Control Tables.
/*REXX****************************************************************/
/* APPLYIS....: DPropR Apply V5 Information and Control */
/*-------------------------------------------------------------------*/
/* CALLED BY..: IKJEFT01 - TSO online or batch */
/* ARGUMENTS..: SSID I - DB2 Subsystem ID */
/* ASNQUAL I - Apply Qualifier and opt. SET name */
/* OPTION I - SUBS list subscriptions */
/* I - TRAIL list log (APPLYTRAIL) */
/* I - CHECK check sub's success */
/* I - WATCH watch sub's processing for n secs */
/* I - MAINT deletes from APPLYTRAIL */
/* I - ACT activate subscriptions */
/* I - DEACT deactivate subscriptions */
/* I - EVENT add an event timestamp */
/* CALLS......: RXSQL M - REXX to SQL Interface */
/* D2PABND M - batch only: abend job step */
/* anyWAIT M - platform standard WAIT program */
/* SEND C - TSO SEND command */
/* FILES......: SYSTSPRT O - Information */
/*-------------------------------------------------------------------*/
/* FUNCTION...: This TSO/E REXX procedures reads information from */
/* Data Propagator tables, which can be used to */
/* control dependent process flow. */
/*-------------------------------------------------------------------*/
/* COMMENTS...: Remind, when comparing LASTRUN and LASTSUCCESS, that */
/* Apply does not set microseconds in LASTSUCCESS. */
/*-------------------------------------------------------------------*/
/* HISTORY....: 29.09.98/GR - V1R0 Gernot Ruban */
/* 98-10-09/GR: IWWTO replaced by TSO SEND to CN(00), V1.1 */
/* anyWAIT instead of timer function, */
/* tolerance for WATCH functions, new functions ACT, */
/* DEACT and EVENT. MAINT expanded for events. */
/* 98-10-19/GR: WAIT-Select corrected V1.1 */
/* 98-12-02/GR: SYNCHRONIZE and opt. parm setname added V1.2 */
/* 99-01-12/GR: STOP added, new parms for MAINT and EVENT V1.3 */
/* 99-01-20/GR: problem with event time fixed, new parameter V1.4 */
/* EVENT RESTART added */
/*********************************************************************/
x = MSG("OFF")
/********************************************************************/
/* Initial values */
/********************************************************************/
version = "V1.4"; /* Current version */
tolerance = "2 HOURS"; /* Set watching time tolerance default value */
/********************************************************************/
/* Check arguments passed */
/********************************************************************/
parse upper arg ssid "," asnqual "," option "," debug
If ssid = "",
! ( option <> "SUBS" ,
& option <> "TRAIL" ,
& option <> "CHECK" ,
& pos("WATCH",option) = 0,
& pos("MAINT",option) = 0,
& pos("EVENT",option) = 0,
& option <> "ACT" ,
& option <> "DEACT" ,
& option <> "SYNCH" ,
& option <> "STOP" ,
& option <> "" )
then signal syntax
if pos("WATCH",option) > 0
then do
if words(option) > 1
then do
wtime = word(option,2)
if words(option) > 2
then tolerance = subword(option,3)
option = "WATCH"
if verify(wtime,"0123456789") > 0
then signal syntax
end
else wtime = 60 /* dflt watch time in minutes */
end
if pos("MAINT",option) > 0
then do
if words(option) > 1
then mtime = subword(option,2)
else mtime = ""
option = "MAINT"
end
if pos("EVENT",option) > 0
then do
if words(option) > 1
then do
etime = word(option,2)
option = "EVENT"
if etime <> "RESTART",
& verify(etime,"0123456789") > 0
then signal syntax
end
else etime = "" /* dflt event time */
end
if debug = "DEBUG"
then trace ?I
/********************************************************************/
/* Write header to report file */
/********************************************************************/
say copies("#",79)
say "# APPLYIS - DPropR Apply V5 Info System "version" "left(ssid,4),
" "date("O")" "time()" #";
say copies("#",79)
/********************************************************************/
/* Set RXSQL's thread type: NO EXPLICIT SQL END (single thread) */
/********************************************************************/
rxsql_db2cleanup = "NOSQLEND"
/********************************************************************/
/* Apply qualifier containing search pattern results in SQL LIKE */
/********************************************************************/
parse value asnqual with asnqual"."setname
if pos("%",asnqual) > 0 ,
! pos("_",asnqual) > 0
then pred_qual = "APPLY_QUAL LIKE '"asnqual"%'"
else pred_qual = "APPLY_QUAL = '"asnqual"'"
/********************************************************************/
/* Check SET_NAME */
/********************************************************************/
if setname <> ""
then do
if pos("%",setname) > 0 ,
! pos("_",setname) > 0
then set_qual = "SET_NAME LIKE '"setname"%'"
else set_qual = "SET_NAME = '"setname"'"
end
else set_qual = "SET_NAME >= ''"
/********************************************************************/
/* Distinguish between options */
/********************************************************************/
select
when option = "SUBS" then CALL LIST_SUBS
when option = "TRAIL" then CALL LIST_TRAIL
when option = "CHECK" then CALL CHECK_SUBS
when option = "WATCH" then CALL WATCH_SUBS
when option = "MAINT" then CALL MAINT_TRAIL
when option = "ACT" then CALL MODIFY_SUBS "ACT"
when option = "DEACT" then CALL MODIFY_SUBS "DEACT"
when option = "EVENT" then CALL ADD_EVENT
when option = "SYNCH" then CALL SYNCHRONIZE
when option = "STOP" then CALL STOP_TASK
otherwise CALL LIST_SUBS
end
say ""
say "APPLYIS: ends with CC=0"
exit 0
/********************************************************************/
/* */
/* Internal subprocedures and functions */
/* */
/********************************************************************/
/********************************************************************/
/* SYNCHRONIZE: Move ASN.IBMSNAP_REGISTER.CD_OLD_SYNCHPOINT */
/* to ASN.IBMSNAP_SUBS_SET.SYNCHPOINT */
/* */
/* This is to avoid gaps for noncomplete target tables */
/********************************************************************/
SYNCHRONIZE:
CALL LIST_SUBS
stmt = "SELECT DISTINCT SOURCE_SERVER, ",
"SOURCE_OWNER, SOURCE_TABLE",
"FROM ASN.IBMSNAP_SUBS_SET S,",
" ASN.IBMSNAP_SUBS_MEMBR M",
"WHERE S."pred_qual,
"AND S."set_qual,
"AND S.APPLY_QUAL = M.APPLY_QUAL",
"AND S.SET_NAME = M.SET_NAME";
cc = DOSQL(stmt)
say " "
say "LIST OF SYNCHRONIZED SUBS SET MEMBERS: "rxsql_0" list entries"
say "======================================"
do i=1 to rxsql_0
stmt = "SELECT MAX(CD_OLD_SYNCHPOINT)",
"FROM "space(source_server.i)".ASN.IBMSNAP_REGISTER R,",
space(source_server.i)".ASN.IBMSNAP_PRUNCNTL P",
"WHERE P."pred_qual,
"AND P."set_qual,
"AND P.SOURCE_OWNER = '"source_owner.i"'",
"AND P.SOURCE_TABLE = '"source_table.i"'",
"AND P.SOURCE_OWNER = R.SOURCE_OWNER",
"AND P.SOURCE_TABLE = R.SOURCE_TABLE";
cc = DOSQL(stmt)
if cc = 0
then do
stmt = "UPDATE ASN.IBMSNAP_SUBS_SET",
"SET SYNCHPOINT = '"temp.1.1"'",
"WHERE "pred_qual,
"AND "set_qual;
cc = DOSQL(stmt)
if cc = 0
then say left(source_owner.1,18)" ! "left(source_table.i,18),
"synchronization successful.";
else say left(source_owner.1,18)" ! "left(source_table.i,18),
"synchronization failed.";
end
end
return
/********************************************************************/
/* WATCH_SUBS: Watch DPropR Apply Subscription processing for */
/* specified number of seconds, abend if elapsed or */
/* unsuccessful subscriptions found. */
/* List Success of Subscriptions, */
/* Abend Procedure if unsuccessful subscriptions */
/********************************************************************/
WATCH_SUBS:
CALL LIST_SUBS
say " "
say copies("=",79)
say "= START WATCHING EXECUTING SUBSCRIPTIONS:"copies(' ',37)"="
say copies("=",79)
say "Apply Qualifier .............: "asnqual
say "Wait for Apply to finish work: "wtime" minutes"
stmt = "SELECT CURRENT TIMESTAMP, CURRENT TIMESTAMP - " tolerance,
"FROM SYSIBM.SYSDUMMY1";
cc = DOSQL(stmt)
ctim = temp.1.1
ftim = temp.2.1
say "Current DB2 Timestamp .......: "ctim
say "Tolerance ...................: "tolerance
say "Watch Time Frame ............: "ftim
drop temp.1.1 temp.2.1
/*****************************************************************/
/* Check subscriptions sets until successful or wait time elapsed*/
/*****************************************************************/
wexec = 0
wwait = time("R") /* timer reset */
do while wexec = 0 & wwait <= wtime*60
stmt = "SELECT VALUE(COUNT(*),0)",
"FROM ASN.IBMSNAP_SUBS_SET",
"WHERE "pred_qual,
"AND LASTRUN > '"ftim"'",
"AND ACTIVATE > 0",
"AND STATUS = 0",
"HAVING COUNT(*) = (",
"SELECT COUNT(*)",
"FROM ASN.IBMSNAP_SUBS_SET",
"WHERE "pred_qual,
"AND ACTIVATE > 0 )";
/**"AND LASTRUN > '"ftim"'", 98/10/19/GR**/
cc = DOSQL(stmt)
/**************************************************************/
/* No result: not run, not completely finished or deactivated */
/* Result : all subscriptions processed */
/**************************************************************/
if rxsql_0 > 0,
& temp.1.1 > 0
then do
wexec = 1
say time()": "space(temp.1.1)" subscriptions processed."
end
else do
say time()": Apply did not process all subscriptions till now."
/*********************************************************/
/* Wait a while (handle minutes as secs), at least 1 min */
/*********************************************************/
parm = "'"max(wtime,60)"'"
ADDRESS TSO "CALL *(anyWAIT) " PARM
/*CALL WAIT WTIME*/
end
wwait = time("E") /* elapsed time */
end
/*****************************************************************/
/* Wait time exceeded or subscriptions finished: */
/* Check success of subscriptions, issue WTO message */
/*****************************************************************/
CALL CHECK_SUBS "WATCH" "WTO YES"
return
/********************************************************************/
/* STOP_APPLY: Stop or cancel Apply Job */
/********************************************************************/
STOP_APPLY: Procedure
arg job
/*****************************************************************/
/* Job exists and is executing */
/*****************************************************************/
x = msg("ON")
x = outtrap("line.","*")
address TSO "STATUS "job
cc = rc
x = outtrap("OFF")
x = msg("OFF")
found = 0
do i=1 to line.0
if pos("EXECUTING",line.i)
then do
found = 1
j=pos("(",line.i)
k=pos(")",line.i)
if j>0 &k>0
then jobid = substr(line.i,j+1,k-j-1)
else jobid = ''
end
end
if found = 0
then do
say " "
say "APPLYIS: DPropR Apply job "job" is not executing - cannot cancel!"
say " Current status: "line.1
do i=2 to line.0
say " "line.i
end
return
end
/*****************************************************************/
/* Cancel Job */
/*****************************************************************/
if jobid = ""
then address TSO "CANCEL "job
else address TSO "CANCEL "job"("jobid")"
say " "
say "APPLYIS: Cancel "job" ended with RC="rc
return
/********************************************************************/
/* WAIT: Wait a specified time of seconds */
/********************************************************************/
WAIT: Procedure
arg secs
s = time("R")
s = time("R")
do while s<secs
s = time("E")
end
s = time("R")
return
/********************************************************************/
/* CHECK_SUBS: Check DPropR Apply Subscription sets */
/********************************************************************/
CHECK_SUBS:
arg mode wto
/*****************************************************************/
/* Mode WATCH issues query on APPLY_TRAIL - tolerance */
/* (tolerance already computed in preceeding procedure) */
/*****************************************************************/
if mode = "WATCH"
then do
add_predicate = "AND B.LASTRUN >= '"ftim"'"
add_predicate2 = "OR B.LASTRUN < '"ftim"'"
end
else do
add_predicate = ""
add_predicate2 = ""
end
/*****************************************************************/
/* Successful Subscriptions */
/*****************************************************************/
unsucc = 0
running = 0
stmt = "SELECT B.APPLY_QUAL, B.SET_NAME, B.LASTRUN",
"FROM ASN.IBMSNAP_SUBS_SET A,",
" ASN.IBMSNAP_APPLYTRAIL B",
"WHERE A."pred_qual,
"AND A.APPLY_QUAL = B.APPLY_QUAL",
"AND A.SET_NAME = B.SET_NAME",
"AND A.LASTRUN = B.LASTRUN",
"AND A.STATUS = 0",
add_predicate,
"AND SUBSTR(CHAR(A.LASTRUN),1,19) = ",
" SUBSTR(CHAR(A.LASTSUCCESS),1,19)",
"AND ( B.SQLCODE IS NULL",
" OR ( B.SQLCODE IS NOT NULL",
" AND B.SQLCODE IN (0,100) ) )",
"ORDER BY 1 , 2 , 3";
cc = DOSQL(stmt)
say " "
say "LIST OF SUCC. PROCESSED SUBSCRIPTION SETS: "rxsql_0" list entries"
say "=========================================="
if mode = "WATCH"
then say "(Must have execution end time after "ftim".)"
/*****************************************************************/
/* Process all result rows */
/*****************************************************************/
say "Apply Qualifier !Set Name !Last Run "
say "------------------+------------------+--------------------------"
do i=1 to rxsql_0
say left(b.apply_qual.i,18)"!" !!,
left(b.set_name.i,18)"!" !!,
b.lastrun.i
end
/*****************************************************************/
/* Unsuccessful subscriptions */
/*****************************************************************/
stmt = "SELECT B.APPLY_QUAL, B.SET_NAME, B.LASTRUN, ",
" B.SQLCODE*1, B.LASTSUCCESS, B.SQLSTATE",
"FROM ASN.IBMSNAP_SUBS_SET A,",
" ASN.IBMSNAP_APPLYTRAIL B",
"WHERE A."pred_qual,
"AND A.APPLY_QUAL = B.APPLY_QUAL",
"AND A.SET_NAME = B.SET_NAME",
"AND A.LASTRUN = B.LASTRUN",
"AND ( ( B.SQLCODE IS NOT NULL",
" AND B.SQLCODE NOT IN (0,100) )",
add_predicate2,
" OR (SUBSTR(CHAR(A.LASTRUN),1,19) <> ",
" SUBSTR(CHAR(A.LASTSUCCESS),1,19) ) )",
"ORDER BY 1 , 2 , 3";
cc = DOSQL(stmt)
say " "
say "LIST OF UNSUCC. PROCESSED SUBSCRIPTION SETS: "rxsql_0" list entries"
say "============================================"
/*****************************************************************/
/* Process all result rows */
/*****************************************************************/
say "Apply Qualifier !Set Name !Last Run / Last Success !SQLCODE"
say "------------------+------------------+--------------------------+-------"
do i=1 to rxsql_0
unsucc = 1
say left(b.apply_qual.i,18)"!" !!,
left(b.set_name.i,18)"!" !!,
left(b.lastrun.i,26)"!"!!,
temp.1.i
if mode = "WATCH",
& space(b.lastsuccess) < ftim,
& ( space(temp.1.i) = "",
! space(temp.1.i) = 0,
! space(temp.1.i) = 100)
then say left(" ",18)"!" !!,
left(" ",18)"!" !!,
left(b.lastsuccess.i,26)"!"!!,
"outside"
else say left(" ",18)"!" !!,
left(" ",18)"!" !!,
left(b.lastsuccess.i,26)"!"!!,
b.sqlstate.i
end
/*****************************************************************/
/* Subscriptions just running */
/*****************************************************************/
stmt = "SELECT A.APPLY_QUAL, A.SET_NAME, A.LASTRUN, A.ACTIVATE",
"FROM ASN.IBMSNAP_SUBS_SET A",
"WHERE A."pred_qual,
"AND A.STATUS IN (1 , 2)",
"ORDER BY 1 , 2 , 3";
cc = DOSQL(stmt)
say " "
say "LIST OF EXECUTING SUBSCRIPTION SETS: "rxsql_0" list entries"
say "===================================="
/*****************************************************************/
/* Process all result rows */
/*****************************************************************/
say "Apply Qualifier !Set Name !Last Run "
say "------------------+------------------+--------------------------"
do i=1 to rxsql_0
unsucc = 1
running = 1
say left(a.apply_qual.i,18)"!" !!,
left(a.set_name.i,18)"!" !!,
left(a.lastrun.i,26)
end
/*****************************************************************/
/* Subscriptions never run */
/*****************************************************************/
stmt = "SELECT A.APPLY_QUAL, A.SET_NAME, A.LASTRUN, A.ACTIVATE",
"FROM ASN.IBMSNAP_SUBS_SET A",
"WHERE A."pred_qual,
"AND A.STATUS <> 1",
"AND NOT EXISTS (SELECT 1",
" FROM ASN.IBMSNAP_APPLYTRAIL",
" WHERE A.APPLY_QUAL = APPLY_QUAL",
" AND A.SET_NAME = SET_NAME",
" AND A.LASTRUN = LASTRUN )",
"ORDER BY 1 , 2 , 3";
cc = DOSQL(stmt)
say " "
say "LIST OF NEVER PROCESSED SUBSCRIPTION SETS: "rxsql_0" list entries"
say "=========================================="
/*****************************************************************/
/* Process all result rows */
/*****************************************************************/
say "Apply Qualifier !Set Name !Last Run !ACTIVE"
say "------------------+------------------+--------------------------+------"
do i=1 to rxsql_0
unsucc = 1
say left(a.apply_qual.i,18)"!" !!,
left(a.set_name.i,18)"!" !!,
left(a.lastrun.i,26)"!"!!,
a.activate.i
end
/*****************************************************************/
/* Abend procedure whenever unsuccessful subscriptions found */
/*****************************************************************/
if unsucc = 1
then do
if running = 0
then do
CALL LIST_TRAIL
if wto = "WTO YES"
then do
msg = jobname()" APPLY UNSUCCUSSFUL. STOP APPLY."
ADDRESS TSO "SEND '"msg"' CN(00)"
say "WTO Message issued: "msg" (RC="rc")"
end
say " "
say "APPLYIS: Unsucessfully executed subscriptions found."
signal error
end
else do
if wto = "WTO YES"
then do
msg = jobname()" APPLY STILL RUNNING. STOP APPLY."
ADDRESS TSO "SEND '"msg"' CN(00)"
say "WTO Message issued: "msg" (RC="rc")"
end
say " "
say "APPLYIS: Apply is still executing subscriptions."
say " Increase watch time and/or check subscriptions."
say " Repeat last Apply cycle to complete pending subscriptions."
signal error
end
end
else do
if wto = "WTO YES"
then do
msg = jobname()" APPLY SUCCESSFULLY. STOP APPLY."
ADDRESS TSO "SEND '"msg"' CN(00)"
say "WTO Message issued: "msg" (RC="rc")"
end
end
return
/********************************************************************/
/* LIST_SUBS: List DPropR Apply Subscription sets */
/********************************************************************/
LIST_SUBS:
stmt = "SELECT *",
"FROM ASN.IBMSNAP_SUBS_SET",
"WHERE "pred_qual,
"ORDER BY APPLY_QUAL, SET_NAME";
cc = DOSQL(stmt)
say " "
say "LIST OF SUBSCRIPTION SETS: "rxsql_0" list entries"
say "=========================="
events = ""
/*****************************************************************/
/* Process all result rows */
/*****************************************************************/
do i=1 to rxsql_0
say copies("-",74)!!right(i,5,"0")
say "Apply Qualifier ...:"left(apply_qual.i,18),
"Set Name ..........:"space(set_name.i);
say "Source Server/Alias:"left(space(Source_server.i)"/"space(source_alias.i),18),
"Target Server/Alias:"space(target_server.i)"/"space(target_alias.i);
say "Last Run ..........:"lastrun.i
say "Last Success ......:"lastsuccess.i
say "Subscription active:"left(space(activate.i),5)" (0=deact 1=act 2=copy-able)"
say "Status ............:"left(space(status.i),5)" (-1=failed 0=ready 1=pend 2=cont)"
if refresh_timing.i = "R"
then say "Refresh Timing ....:Sleep "sleep_minutes.i" minutes"
if refresh_timing.i = "E"
then say "Refresh Timing ....:Event "event_name.i
if refresh_timing.i = "B"
then say "Refresh Timing ....:Sleep "sleep_minutes.i" minutes, Event "event_name.i
if space(event_name.i) <> ""
then events = events"'"space(event_name.i)"',"
say "Aux. Statements ...:"left(space(aux_stmts.i),5)" (# row in SUBS_STMTS)"
say "Synch. Point ......:"c2x(synchpoint.i)
say "Synch. Time .......:"synchtime.i
end
/*****************************************************************/
/* Subscription Set Members */
/*****************************************************************/
stmt = "SELECT *",
"FROM ASN.IBMSNAP_SUBS_MEMBR",
"WHERE "pred_qual,
"ORDER BY APPLY_QUAL, SET_NAME, TARGET_OWNER, TARGET_TABLE";
cc = DOSQL(stmt)
say " "
say "LIST OF SUBSCRIPTION SET BASE OBJECTS: "rxsql_0" list entries"
say "======================================"
/*****************************************************************/
/* Process all result rows */
/*****************************************************************/
last_qual = ""
last_set = ""
do i=1 to rxsql_0
if last_qual <> apply_qual.i,
! last_set <> set_name.i
then do
say ""
say copies("-",79)
say "Apply Qualifier: "space(apply_qual.i),
"Set Name: "space(set_name.i);
say copies("-",79)
say "Target Source QUAL COND COMP STRU"
say "---------------------------------------------------------------------------"
last_qual = apply_qual.i
last_set = set_name.i
end
say left(space(target_owner.i)"."space(target_table.i),27),
left(space(Source_owner.i)"."space(source_table.i),27),
right(source_view_qual.i,3),
center(target_condensed.i,4),
center(target_complete.i,4),
center(target_structure.i,4)
end
/*****************************************************************/
/* Events for Subscription Set Execution, if used */
/*****************************************************************/
if events <> ""
then do
events = substr(events,1,length(events)-1)
CALL LIST_EVENT events
end
return
/********************************************************************/
/* LIST_EVENT: List Subscription Events */
/********************************************************************/
LIST_EVENT:
arg events
stmt = "SELECT *",
"FROM ASN.IBMSNAP_SUBS_EVENT",
"WHERE EVENT_NAME IN ("events")",
"ORDER BY EVENT_NAME, EVENT_TIME";
cc = DOSQL(stmt)
say " "
say "LIST OF SUBSCRIPTION EVENTS: "rxsql_0" list entries"
say "============================"
/************************************************************/
/* Process all result rows */
/************************************************************/
say "Event Name ! Event Time ! End of Period"
say "-------------------+----------------------------+---------------------------"
do i=1 to rxsql_0
say left(event_name.i,18)" !",
left(event_time.i,26)" !",
end_of_period.i
end
return
/********************************************************************/
/* LIST_TRAIL: List DPropR Apply log entries */
/********************************************************************/
LIST_TRAIL:
stmt = "SELECT *",
"FROM ASN.IBMSNAP_APPLYTRAIL",
"WHERE "pred_qual,
"ORDER BY APPLY_QUAL, SET_NAME, LASTRUN DESC";
cc = DOSQL(stmt)
say " "
say "LIST OF LATEST APPLY LOG ENTRIES:"
say "================================="
/*****************************************************************/
/* Process all result rows */
/*****************************************************************/
last_qual = ""
last_set = ""
j = 0
do i=1 to rxsql_0
if last_qual <> apply_qual.i,
! last_set <> set_name.i
then do
j = j + 1
say copies("-",74)!!right(i,5,"0")
say "Apply Qualifier ...:"left(apply_qual.i,18),
"Set Name ..........:"space(set_name.i);
say "Last Run ..........:"lastrun.i
say "Last Success ......:"lastsuccess.i
say "Status ............:"space(status.i),
" (-1=failed 0=ready 1=pending 2=divided)"
say "ASNLOAD ...........:"left(space(asnload.i),18),
"Mass Delete .......:"space(mass_delete.i);
say "Inserted ..........:"left(space(set_inserted.i),18),
"Reworked ..........:"space(set_reworked.i);
say "Deleted ...........:"left(space(set_deleted.i),18),
"Rejected TX's .....:"space(set_rejected_trxs.i);
say "Updated ...........:"left(space(set_updated.i),18)
if sqlcode.i <> 0,
& sqlcode.i <> ""
then do
say "SQLSTATE/SQLCODE ..:"space(sqlstate.i)"/"space(sqlcode.i)
say "SQLERRP ...........:"space(sqlerrp.i)
say "SQLERRM ...........:"space(sqlerrm.i)
end
last_qual = apply_qual.i
last_set = set_name.i
end
end
say space(j)" Apply Log entries reported."
return
/********************************************************************/
/* MAINT_TRAIL: Delete DPropR Apply log entries */
/********************************************************************/
MAINT_TRAIL:
say " "
say "DELETION OF OBSOLETE APPLY LOG AND EVENT ENTRIES:"
say "================================================="
say "Apply Qualifier .................: "asnqual
say "Keep DProp data for period of ...: "mtime
say " "
say "* Deletes log entries vom APPLYTRAIL table, which do not refer"
say " to the latest subscription execution."
say "* Deletes obsolete event entries from SUBS_EVENT."
say " "
/*****************************************************************/
/* Delete every unreferenced data or keep data for specif. period*/
/*****************************************************************/
if mtime = ""
then sqlstmt = "DELETE FROM ASN.IBMSNAP_APPLYTRAIL A",
"WHERE "pred_qual,
"AND NOT EXISTS (SELECT 1 FROM ASN.IBMSNAP_SUBS_SET",
" WHERE A.APPLY_QUAL = APPLY_QUAL",
" AND A.SET_NAME = SET_NAME",
" AND A.LASTRUN = LASTRUN ) ";
else sqlstmt = "DELETE FROM ASN.IBMSNAP_APPLYTRAIL A",
"WHERE "pred_qual,
"AND NOT EXISTS (SELECT 1 FROM ASN.IBMSNAP_SUBS_SET",
" WHERE A.APPLY_QUAL = APPLY_QUAL",
" AND A.SET_NAME = SET_NAME",
" AND A.LASTRUN = LASTRUN )",
"AND (CURRENT TIMESTAMP - "mtime") > A.LASTRUN";
cc = DOSQL(sqlstmt)
if cc = 0
then say space(rxsql_sqlerrd.3)" rows deleted from APPLYTRAIL table."
else say "Nothing to maintain, 0 rows deleted from APPLYTRAIL table."
say " "
/*****************************************************************/
/* Delete every unreferenced data or keep data for specif. period*/
/*****************************************************************/
if mtime = ""
then sqlstmt = "DELETE FROM ASN.IBMSNAP_SUBS_EVENT A",
"WHERE EXISTS (SELECT 1 FROM ASN.IBMSNAP_SUBS_SET",
" WHERE "pred_qual,
" AND EVENT_NAME = A.EVENT_NAME",
" AND A.EVENT_TIME < LASTSUCCESS ) ";
else sqlstmt = "DELETE FROM ASN.IBMSNAP_SUBS_EVENT A",
"WHERE EXISTS (SELECT 1 FROM ASN.IBMSNAP_SUBS_SET",
" WHERE "pred_qual,
" AND EVENT_NAME = A.EVENT_NAME",
" AND A.EVENT_TIME < LASTSUCCESS )",
"AND CURRENT TIMESTAMP - "mtime" > A.EVENT_TIME";
cc = DOSQL(sqlstmt)
if cc = 0
then say space(rxsql_sqlerrd.3)" rows deleted from EVENT table."
else say "Nothing to maintain, 0 rows deleted from EVENT table."
return
/********************************************************************/
/* MODIFY_SUBS: Activate or deactivate subscription sets */
/********************************************************************/
MODIFY_SUBS:
arg mode
say " "
say "DE-/ACTIVATION OF SUBSCRIPTION SETS:"
say "===================================="
say "Apply Qualifier ...................: "asnqual
say "Type of operation (act/deact) .....: "mode
if mode = "ACT" then modeconv = 1
if mode = "DEACT" then modeconv = 0
stmt = "UPDATE ASN.IBMSNAP_SUBS_SET",
"SET ACTIVATE = "modeconv,
"WHERE "pred_qual
cc = DOSQL(stmt)
if cc = 0
then say space(rxsql_sqlerrd.3)" rows modified on Subscription Set table."
else say "Nothing modified, invalid apply qualifier."
return
/********************************************************************/
/* STOP_TASK: Sends mesage to console to initiate MVS STOP via AOC */
/********************************************************************/
STOP_TASK: Procedure
arg message
if message = "MESSAGE" ! message = ""
then msg = jobname()" SYSLOG MSG TO STOP APPLY VIA AOC."
ADDRESS TSO "SEND '"msg"' CN(00)"
say "WTO Message issued: "msg" (RC="rc")"
return
/********************************************************************/
/* ADD_EVENT: Adds an event for specific subscriptions */
/********************************************************************/
ADD_EVENT:
say " "
say "EVENT GENERATION FOR SUBSCRIPTION SETS : "
say "========================================"
say "Apply Qualifier .......................: "asnqual
say "End-of-Copy-Period passed as parameter : "etime
if etime = ""
then sqlstmt = "SELECT CURRENT TIMESTAMP,",
" CURRENT TIMESTAMP - 1 MINUTE",
"FROM SYSIBM.SYSDUMMY1";
else do
/*************************************************************/
/* Determine end-of-period timestamp, absolute or RESTART */
/*************************************************************/
if etime = "RESTART"
then do
/********************************************************/
/* RESTART = max. of all entries for subs set */
/********************************************************/
say "Warning: RESTART specified! Procedure tries to get maximum"
say " end-of-period timestamp for specified subscription"
say " set or group if sets (if using %_ mask)."
sqlstmt = "SELECT CURRENT TIMESTAMP,",
" VALUE(MAX(END_OF_PERIOD),",
" CURRENT TIMESTAMP - 1 MINUTE)",
"FROM ASN.IBMSNAP_SUBS_SET A,",
" ASN.IBMSNAP_SUBS_EVENT B ",
"WHERE A."pred_qual,
"AND A.EVENT_NAME = B.EVENT_NAME",
"AND A.ACTIVATE = 1";
end
else do
/********************************************************/
/* time = specific time (date may be added automatic.) */
/********************************************************/
if length(etime) <= 6
then wetime = date("S")!!left(etime,6,"0")
else wetime = left(etime,14,"0")
sqlstmt = "SELECT CURRENT TIMESTAMP,",
" TIMESTAMP('"wetime"')",
"FROM SYSIBM.SYSDUMMY1";
end
end
cc = DOSQL(sqlstmt)
etim = temp.1.1
utim = temp.2.1
say "Event Timestamp set ...................: "etim
say "End-of-Copy-Period Timestamp set ......: "utim
drop temp.1.1 temp.2.1
say "Apply Qualifier ! Event Name ! Timestamp set ("rxsql_0" list entries)"
say "-------------------+--------------------+--------------"
sqlstmt = "SELECT DISTINCT EVENT_NAME, APPLY_QUAL",
"FROM ASN.IBMSNAP_SUBS_SET",
"WHERE "pred_qual,
"AND ACTIVATE = 1";
cc = DOSQL(sqlstmt)
events = ""
do i=1 to rxsql_0
sqlstmt = "INSERT INTO ASN.IBMSNAP_SUBS_EVENT",
"VALUES('"event_name.i"','"etim"','"utim"')";
cc = DOSQL(sqlstmt)
if cc = 0
then say left(apply_qual.1,18)" ! "left(event_name.i,18)" ! done"
else say left(apply_qual.1,18)" ! "left(event_name.i,18)" ! insert failed"
events = events"'"event_name.i"',"
end
if events <> ""
then do
events = substr(events,1,length(events)-1)
CALL LIST_EVENT events
end
return
/********************************************************************/
/* DOSQL: Performs a DB2 SQL statement */
/********************************************************************/
DOSQL:
arg stmt
/*****************************************************************/
/* Perform a SQL Statement using RXSQL */
/*****************************************************************/
rxsql_db2subsys = ssid /* DB2 Subsystem*/
call RXSQL stmt
if stmt = "COMMIT",
! stmt = "ROLLBACK"
then call RXSQLEND /* Termination */
else cc = SQLMSG()
return cc
/********************************************************************/
/* SQLMSG: react on SQL warnings and errors */
/********************************************************************/
SQLMSG:
/*****************************************************************/
/* Preparations for issueing messages */
/*****************************************************************/
sqlcode = rxsql_sqlcode
sqlerrm = rxsql_sqlerrm
/*****************************************************************/
/* Unavailable resource */
/*****************************************************************/
if rxsql_sqlcode = -904
then do
say "APPLYIS: Unavailable ressource - contact local ",
"DB2/MVS database administrator."
say " SQLSTMT="sqlstmt
say " SQLCODE="sqlcode
say " SQLERRM="sqlerrm
call RXSQL "ROLLBACK"
call RXSQLEND
signal error
end
/*****************************************************************/
/* Data failure (quote used) */
/*****************************************************************/
if rxsql_sqlcode = -104 ! rxsql_sqlcode = -10 ! rxsql_sqlcode = -180
then do
say "APPLYIS: Input data failure - missing quotation mark or formal error"
say " SQLSTMT="sqlstmt
say " SQLCODE="sqlcode
say " SQLERRM="sqlerrm
call RXSQL "ROLLBACK"
call RXSQLEND
signal error
end
/*****************************************************************/
/* Duplicate value */
/*****************************************************************/
if rxsql_sqlcode = -803
then do
say "APPLYIS: An inserted or updated value is duplicate!"
say " SQLSTMT="sqlstmt
say " SQLCODE="sqlcode
say " SQLERRM="sqlerrm
call RXSQL "ROLLBACK"
call RXSQLEND
signal error
end
/*****************************************************************/
/* Record(s) found or nothing found */
/*****************************************************************/
if rxsql_sqlcode = 100 & rxsql_0 = 0 then return 100
if rxsql_sqlcode = 100 & rxsql_0 > 0 then return 0
/*****************************************************************/
/* Row locked, try again */
/*****************************************************************/
if Rxsql_sqlcode = -911 ! rxsql_sqlcode = -913
then do
say "APPLYIS: Deadlock detection"
say " SQLSTMT="sqlstmt
say " SQLCODE="sqlcode
say " SQLERRM="sqlerrm
call RXSQL "ROLLBACK"
call RXSQLEND
signal error
end
/*****************************************************************/
/* Row limit reached (SELECT) or OK (immediate class) */
/*****************************************************************/
if rxsql_sqlcode = 0,
& word(stmt,1) ^= "SELECT"
then return 0
else nop /* produces the following message*/
/*****************************************************************/
/* Any other unexpected SQL return code */
/*****************************************************************/
say "APPLYIS: Unexpected SQL error"
say " SQLSTMT="stmt
say " SQLCODE="sqlcode
say " SQLERRM="sqlerrm
call RXSQL "ROLLBACK"
call RXSQLEND
signal error
return sqlcode /* ^RXSQLCODE */
/********************************************************************/
/* JOBNAME: Get Jobname stored in MVS internal area */
/********************************************************************/
JOBNAME: Procedure
/* ALLE FELDER SIND BESTANDTEIL DES GENERAL USE PRG'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
/********************************************************************/
/* Handle problems with syntax or handle error situations */
/********************************************************************/
Syntax:
say "APPLYIS Syntax Error."
say "APPLYIS ssid,qualifier,option"
say " where ssid - DB2 subsystem id"
say " qualifier - DPropR Apply Qualifier (SQL-like mask)"
say " opt.: qualifier.setname) "
say " option - options, as ..."
say " SUBS - list subscriptions (dflt)"
say " TRAIL - list log (APPLYTRAIL)"
say " CHECK - check sub's success"
say " WATCH m t - watch execution for specified minutes (m),"
say " abend if elapsed or unsuccessful Apply sub's."
say " List un/successful Apply subscriptions."
say " Allow tolerance (t), e.g. 2 HOURS, 1 DAY etc."
say " MAINT [p] - delete from APPLYTRAIL and SUBSEVENT,"
say " keep info for a specified time period (e.g. 1 WEEK)"
say " dflt.: every unreferenced entry will be deleted"
say " ACT/DEACT - activate/deactivate subscription"
say " SYNCH - synchronize source register -> target subs-sets"
say " EVENT [t| - add event timestamp (event and end-of-period time),"
say " RESTART] - add new event but use previous end-of-period time,"
say " dflt.: current timestamp and curr. tstmp - 1 minute,"
say " opt.: t=end-of-copy-period [yyyymmdd]hhmmss"
say " RESTART=use previous end-of-copy period"
say " STOP [msg]- send AOC STOP APPLY message to MVS SYSLOG"
ERROR:
say ""
if sysvar(SYSISPF) = "NOT ACTIVE"
then do
say "Rexx Procedure APPLYIS abends with U0001."
address ATTACH "D2PABND"
exit 0
end
say "Rexx Procedure APPLYIS abends with RC=20."
exit 20
Comments
Comments are closed.