/*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