This REXX procedure checks the status for DB2 for z/OS databases, tablespaces, utilities and threads. The user passes a valid DB2 command and the procedure checks the results.

Example 1: CHECK DATA, which checks your DB2-enforced referential integrity ends with return code RC=4 even if the DB2 utility has detected RI exceptions. Just pass -DIS DB(MYDB) SP(MYSPAC*) RESTRICT to CHK$DB. The procedure investigates the command’s output and issues an user abend code U0001 for findings, which is a tablespaces still in CHECK PENDING restricted mode.

Example 2: Before starting DB2 maintenance, or a system quiesce, or system copy, you want to verify that no distributed threads are running at the DB2 subsystem. Just pass -DIS THREAD(*)  to CHK$DB which verifies that no DDF threads are accessing the system. Otherwise it raises an U0001 user abend.

Without checking the objects status, processing would continue and fail whenever access to an object with accurate status is needed.

Checks realized:

• Objects no in read/write status: -DIS DB(…) [SP(…)]   (i.e. w/o USER|LOCK|RESTRICT)
• Objects in use: -DIS DB(…) [SP(…)] USE
• Objects with locks: -DIS DB(…) [SP(…)] LOCKS
• Objects in restricted use: -DIS DB(…) [SP(…)] RESTRICT
• DB2 utilities running: -DIS UTIL(…)
• Thread running: -DIS THREAD(…) […]

Whenever CHK$DB detects a problem it invokes an external assembler program called D2PABND. Its only purpose is to raise an user abend code U001. This definitivly stops scheduler activity (e.g. TWS/OPC), thus ending chains of automated z/OS processing.

The procedure is currently prepared for german speaking users. It will be translated into english in the next few days.

 /*REXX*******************  I  P  T  **********************************/
 /* CHK$DB.....: Check von DB2 Database/Tablespace/Utility Stati      */
 /*-------------------------------------------------------------------*/
 /* CALLED BY..: JES JOB    -                                         */
 /* ARGUMENTS..: SSID     I - DB2 Subsystem ID                        */
 /*              COMMAND  I - DB2 Command (-DIS)                      */
 /* CALLS......: DSN      M - DB2 Command Processor                   */
 /*              D2PABND  M - Abbruch Routine, ABEND U0001 (STEPLIB!) */
 /* FILES......: n/a        - (nur die normalen TSO DD-Namen)         */
 /*-------------------------------------------------------------------*/
 /* FUNCTION...: Überprüft diverse Stati anhand des jeweiligen DB2    */
 /*              DISPLAY Commands.                                    */
 /*-------------------------------------------------------------------*/
 /* HISTORY....: 23.06.95/GR - V1R0                                   */
 /* 27.11.96/GR: Erweiterung fuer Threads und Utilities               */
 /*********************************************************************/
 x = MSG("OFF")
 parse upper arg ssid "," command
 /********************************************************************/
 /* Header ausgeben                                                  */
 /********************************************************************/
 say copies("#",79)
 say "# CHK$DB - DB2 Database/Tablespace/Utility Check           ",
     date("O")" "time()" #";
 say copies("#",79)
 say "# 1. Parm: DB2 Subsystem ID:"ssid
 say "# 2. Parm: DB2 Command.....:"command
 say copies("#",79)
 say ""
 /********************************************************************/
 /* Argument prüfen                                                  */
 /********************************************************************/
 if length(ssid) ^= 4 ! ssid = "SSID"
 then do
      say "Subsystem (1. Parm) nicht korrekt!"
      address ATTACH "D2PABND"
      exit 0
 end
 command = space(command)
 if pos("-DIS",command) = "0" ! command = "COMMAND" ! command = ""
 then do
      say "DB2 Command (2. Parm) nicht korrekt!"
      address ATTACH "D2PABND"
      exit 0
 end
 /********************************************************************/
 /* Command auf zu prüfende Stati analysieren                        */
 /********************************************************************/
 if pos("USE",command) > 0
 then use = 1
 else use = 0
 if pos("LOCKS",command) > 0
 then locks = 1
 else locks = 0
 if pos("RESTRICT",command) > 0
 then restrict = 1
 else restrict = 0
 if pos("UTIL",command) > 0
 then util = 1
 else util = 0
 if pos("THREAD",command) > 0
 then thread = 1
 else thread = 0
 if pos("DB(",command) > 0 ! pos("DATABASE(",command) > 0
 then do
      if pos("LIMIT",command) = 0
      then command = command !! " LIMIT(0)"
      if ^use & ^restrict & ^locks
      then rw = 1
      else rw = 0
 end
 else rw = 0
 /********************************************************************/
 /* DB2 Command Processor ausführen, Ausgabe umleiten                */
 /********************************************************************/
 x = outtrap("LINE.","*")
 x = MSG("ON")
 push "END"
 push command
 address TSO "DSN SYSTEM("ssid")"
 cc = rc
 x = outtrap()
 x = MSG("OFF")
 /********************************************************************/
 /* alle Lines ausgeben                                              */
 /********************************************************************/
 do i=1 to line.0
    say ">>> "line.i
 end
 /********************************************************************/
 /* Abbruch, wenn Command Processor fehlerhaft                       */
 /********************************************************************/
 if cc ^= 0 ! line.0 = 0
 then do
      say "DB2 Command Processor nicht fehlerfrei ausgeführt!"
      address ATTACH "D2PABND"
      exit 0
 end
 /********************************************************************/
 /* alle Lines analysieren                                           */
 /********************************************************************/
 phase  = ""
 lines  = 0
 do i=1 to line.0
    /*****************************************************************/
    /* Analyse der DB2 Command Informationen                         */
    /*****************************************************************/
    w1 = word(line.i,1)
    if w1 = "DSN9023I" then phase = "R"
    if w1 = "DSN9022I" then phase = "E"
    /*****************************************************************/
    /* Analyse des DB2 Command Outputs                               */
    /*****************************************************************/
    if phase = "S",
    & word(line.i,1) ^= "--------",
    & word(line.i,1) ^= "********",
    & word(line.i,1) ^= "*******"
    then do
         if word(line.i,1) = "NAME",
         then do
              /*******************************************************/
              /* Ãœberschriftszeilen auswerten                        */
              /*******************************************************/
              do j=1 to words(line.i)
                 colname.j  = word(line.i,j)
                 colstart.j = wordindex(line.i,j)
              end
              colname.0 = words(line.i)
         end
         else do
              /*******************************************************/
              /* DB/TS-Zeilen retten                                 */
              /*******************************************************/
              lines = lines + 1
              dbts.lines = line.i
         end
    end
    /*****************************************************************/
    /* nach DSNT397I folgt die Objekt-Liste                          */
    /*****************************************************************/
    if w1 = "DSNT397I" then phase = "S"
 end
 /********************************************************************/
 /* Restricted Database/Tablespace                                   */
 /********************************************************************/
 if restrict,
 &  lines ^= 0
 then call MsgRestrict
 /********************************************************************/
 /* Utility (geht auf line.0, nicht lines)                           */
 /********************************************************************/
 if util
 then do
      do i=1 to line.0
         if pos("TERMINATED",line.i) > 0
         then call MsgUtil
         if pos("STOPPED",line.i) > 0
         then call MsgUtil
      end
 end
 /********************************************************************/
 /* Database/Tablespace Use                                          */
 /********************************************************************/
 if use
 then do
      do i=1 to lines
         if substr(dbts.i,colstart.5) ^= ""
         then call MsgUse
      end
 end
 /********************************************************************/
 /* Thread (diese Prozedur hat auch einen Thread!)                   */
 /********************************************************************/
 if thread
 then do
      if pos("DSNV419I",line.2) = 0,
      &  pos("DSNV420I",line.2) = 0,
      &  line.0 <= 6
      then nop
      else call MsgThread
 end
 /********************************************************************/
 /* Database/Tablespace nicht in RW Status                           */
 /********************************************************************/
 if rw
 then do
      do i=1 to lines
         if substr(dbts.i,colstart.4,3) ^= "RW "
         then call MsgRW
      end
 end
 /********************************************************************/
 /* Warnung ausgeben wenn "ABNORMAL COMPLETION"                      */
 /********************************************************************/
 if phase = "R"
 then say "Achtung: Befehl wurde nicht vollständig",
          "und fehlerlos ausgeführt";
exit 0

MsgUse:
   say copies("#",79)
   say "###########          Mindestens ein Tablespace ist"
   say "# Abbruch #          als 'IN USE' gekennzeichnet!"
   say "###########"
   say ""
   say "Empfohlene Aktion:"
   say "Sofern es sich um einen DDF-Thread handelt, kann dieser mit"
   say "-CANCEL DDF THREAD() gelöscht werden - andere Threads-Arten"
   say "können nicht mit DB2-Mitteln entfern werden. Mit -STO DB()..."
   say "AT(COMMIT) kann das Objekt nach dem Commit des Users gestoppt"
   say "werden."
   say copies("#",79)
   address ATTACH "D2PABND"
   exit 0
return

MsgRW:
   say copies("#",79)
   say "###########          Mindestens ein Tablespace ist"
   say "# Abbruch #          hat nicht den STATUS RW!"
   say "###########"
   say ""
   say "Empfohlene Aktion:"
   say "Tablespace mit -STA DB(db) SPACENAM(ts) ACC(RW) starten."
   say "Der Status RW,UT wird auf RW gesetzt, wenn das mit dem Objekt"
   say "laufende Utility endet oder abgebrochen wird."
   say copies("#",79)
   address ATTACH "D2PABND"
   exit 0
return

MsgUtil:
   say copies("#",79)
   say "###########          Mindestens ein Utility ist als"
   say "# Abbruch #          'TERMINATED' gekennzeichnet!"
   say "###########"
   say ""
   say "Empfohlene Aktion:"
   say "Die Utility-ID entspricht einem Job-Namen. Der dazu-"
   say "gehörende Job muß also abgebrochen sein. Beim Job-Restart"
   say "wird das Flag automatisch entfernt. Ist ein Restart nicht"
   say "vorgesehen, kann mit -TERM UTIL(id) das Utility gelöscht"
   say "werden."
   say copies("#",79)
   address ATTACH "D2PABND"
   exit 0
return

MsgThread:
   say copies("#",79)
   say "###########"
   say "# Abbruch #          Es bestehen gesuchte"
   say "###########          Verbindungen zum DB2 Subsystem!"
   say ""
   say "Empfohlene Aktion:"
   say "Abwarten, bis sich die gewueschten Bedingungen eingestellt"
   say "haben (active, indoubt, inactive) oder, bei active Threads"
   say "den Thread mit -CANCEL  THREAD(token/luwid) abbrechen."
   say copies("#",79)
   address ATTACH "D2PABND"
   exit 0
return

MsgRestrict:
   say copies("#",79)
   say "###########          Es ist/sind "lines" Objekt(e)"
   say "# Abbruch #          als 'RESTRICTED' gekennzeichnet!"
   say "###########"
   say ""
   say "Empfohlene Aktion:"
   say "Abhängig vom Flag (COPYP, CHECKP, RECOVERP etc) den"
   say "jeweiligen Job (COPY, CHECK, RECOVER etc) starten."
   say "Anschließend den Status erneut überprüfen lassen."
   say copies("#",79)
   address ATTACH "D2PABND"
   exit 0
return

 Source code of D2PABND – click here.