//***************************************************************
//* IBM            DBRMCHK JCL                      01/2000
//***************************************************************
//* THE PURPOSE OF THIS JCL AND ASSOCIATED REXX PROGRAM IS TO
//* FIND WHETHER THERE ARE ANY FORMAT 1 DBRM'S WHICH HAVE HOST
//* VARIABLES WITH MISSING COLONS.
//*
//* FORMAT 1 DBRM'S WERE PRODUCED BY DB2 UP TO AND INCLUDING V2.2.
//* FORMAT 1 DBRM'S WITH HOST VARIABLES WITH MISSING COLONS WILL
//* NOT CURRENTLY REBIND IN DB2 V6 (AS OF 01/2000).
//*
//* THE SOLUTION IS TO EITHER ADD THE COLONS IN THE SOURCE CODE AND
//* RECOMPILE OR TO EDIT THE COLONS INTO THE DBRM IF THE SOURCE IS
//* NOT AVAILABLE.
//***************************************************************
//* JCL TAILORING INSTRUCTIONS
//***************************************************************
//*
//* CHANGE '<iscuser>'  'your-sysadm-userid'     ALL
//* CHANGE '<sysallda>' 'your-dasd-unit'         ALL
//* CHANGE '<db2hqual>' 'your-DB2-hilevel'       ALL
//* CHANGE '<ssid>'     'your-DB2-subsystem'     ALL
//* CHANGE '<dsntib51>' 'your-plan-for-DSNTIAUL' ALL
//*
//***************************************************************
//* EXECUTION INSTRUCTIONS: PLEASE READ
//***************************************************************
//*
//* THE JOB STEPS ARE:
//*
//* STEP 0A: DELETE TEMPORARY DATASETS
//* STEP 0B: BUILD TEMPORARY //SYSEXEC LIBRARY
//* STEP 1A: DELETE OLD EXTRACT FILE FROM DBRM/STMT (IF ANY)
//* STEP 1B: EXTRACT FROM SYSIBM.SYSDBRM AND SYSIBM.SYSSTMT
//* STEP 1C: DELETE OLD REPORT FILE IF IT EXISTS
//* STEP 1D: RUN REXX DBRMCHK TO READ AND REPORT ON EXTRACTED DATA
//* STEP 2A: DELETE EXTRACT FILE FROM SYSPACKAGE/SYSPACKSTMT (IF ANY)
//* STEP 2B: UNLOAD FROM SYSIBM.SYSPACKAGE AND SYSPACKSTMT
//* STEP 2C: DELETE OLD REPORT FILE IF IT EXISTS
//* STEP 2D: RUN REXX DBRMCHK TO READ AND REPORT ON EXTRACTED DATA
//* STEP 3A: PRINT PLAN REPORT FROM STEP1D
//* STEP 3B: PRINT PACK REPORT FROM STEP2D
//*
//* THE JOB STEPS CAN BE BROKEN UP INTO MULTIPLE JOBS IF REQUIRED.
//* (JES3 USERS WILL NEED TO DO THIS.)
//*
//* YOU WILL NEED TO AMEND THE HIGH LEVEL QUALIFIERS AND THE
//* DB2 SUB-SYSTEM NAMES AND SDSNLOAD LIBRARIES.
//* YOU MAY NEED TO ADJUST THE SPACE PARAMETERS.
//* (SEE THE JCL TAILORING SECTION ABOVE.)
//*
//* THE SQL EXTRACT IS DEFINED WITH ISOLATION LEVEL OF UR
//* FOR EFFICIENCY AND TO MINIMISE THE IMPACT ON A PRODUCTION
//* SYSTEM.  IT IS ASSUMED THAT NO MORE FORMAT 1 PACKAGES OR
//* DBRMS ARE BEING CREATED
//*
//***************************************************************
//* THIS CODE WAS DEVELOPED BY MIKE BRACEY OF THE PISC, IBM UK
//* PLEASE CONTACT MIKE_BRACEY@UK.IBM.COM OR TEL. +44 1962 816944
//* FOR FURTHER INFORMATION.
//* ALL RIGHTS RETAINED
//***************************************************************
//DELDSN  EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
   DELETE '<iscuser>.DBRMCHK.EXEC'
   DELETE '<iscuser>.DBRMCHK.DATA.PACK'
   DELETE '<iscuser>.DBRMCHK.DATA.PLAN'
   DELETE '<iscuser>.DBRMCHK.REPORT.PACK'
   DELETE '<iscuser>.DBRMCHK.REPORT.PLAN'
   SET MAXCC=0
/*
//REXX     EXEC PGM=IEBUPDTE,PARM=NEW
//SYSPRINT DD   SYSOUT=*
//SYSIN    DD   DATA,DLM=DL
./ ADD NAME=DBRMCHK
/* REXX    */
/*
  The purpose of this exec is to find DBRMs that may fail to
  REBIND in DB2 on OS/390 V6.
  The problem only occurs in DBRMs created in DB2 V2.2 or before
  where the colon has been omitted from host variables.

  The exec reads a file which has been created by DSNTIAUL or
  equivalent.


  WARNING: The SQL could take a long time to run depending on many
  factors such as the catalog size and number of format 1 DBRMs.
  The exec has been tested on an input file of 107273 records.
  If out of memory problems are encountered then split the extract
  unload file into multiple input files.

  This SQL unloads from:

    SYSIBM.SYSDBRM
    SYSIBM.SYSSTMT
    SYSIBM.SYSPACKAGE
    SYSIBM.SYSPACKSTMT

  only DBRMs that were created in DB2 V2.2 or previous versions.
  All SQL statements in a DBRM with host variables that are not preceded
  by a colon are detected and reported.

  The flow of the exec is:

  Read the header record of a DBRM
  Read each statement record (may be on multiple physical records)
    For each statement:
      Determine the host variable names and how often they occur.
      Count the number of times each host variable name occurs in
      the SQL statement, and for each of those how often it is preceded
      by a colon.
      If the host variable has fewer occurences in the SQL preceded
      by a colon than there should be, report a possible error.

  Using this simple approach it is NOT possible to precisely determine
  whether there are missing colons.  It is only possible to highlight
  statements that MAY have missing colons.

  A visual inspection should reveal whether or not a problem exists.

  The format of a DBRM is documented in DSNXDBRM and DSNXNBRM of
  SDSNMACS

  */
/* Author : Mike Bracey IBM UK Ltd            */
/* Date   : November 1999                     */
/* All rights reserved                        */
Trace 'o'
Signal On Error

/*initialise output report stem var */
p = 0 ; o. = ''
p=p+1 ; o.p = "Report produced by DBRMCHK;"
p=p+1 ; o.p = "Run on" Date() "at time" Time() "by User" Userid()

/* Initialise variables */

eof       = 0                /* input file end of file indicator */
record_no = 0                /* input file record counter */
stmt. = ''                   /* contains blocks of input records */
stmt.0 = 0                   /* initial value                 */
blksize = 1000               /* block factor for reads  */
dbrm_cnt = 0                 /* counts number of DBRMs examined */
stmt_cnt = 0                 /* counts number of statements examined */
dbrm_missing_colon_cnt = 0   /* counts DBRMs with missing colons */
tot_missing_colon_cnt = 0    /* counts maximum number missing colons */
plan_rep. = 0                /* controls output of REBIND commands */
dbrm_rep. = 0                /* controls output of DBRM header */
stmt_rep. = 0                /* controls output of statement header */

rebind. = ''                 /* accumulate REEBINFD commands   */
r = 0

/* start of main loop  */

Do while eof = 0
  Call Read_a_record
  If eof Then Leave
  If (sectno)(stmtno)(seqno) = '000000000000000' Then Do
    /* found a new DBRM but this record of no further interest */
    dbrm_cnt = dbrm_cnt + 1
    /* check size of output records stacked for writing out */
    If p > blksize Then Do
      o.0 = p
      "EXECIO * DISKW REPDD (STEM o. )"
      p = 0
      o. = ''
    End
  End /* Do .. */
  Else Do
    /* new statement */
    stmt_cnt = stmt_cnt + 1
    stmt_len = C2D(Substr(text,1,4))
    /* read all rows for this statement */
    statement = Left(text,rec_len)
    Do while Length(statement) <> stmt_len + 4
      Call Read_a_record
      statement = (statement)Left(text,rec_len)
    End /* Do while ...*/

    /* Having built whole statement now can break down into components*/

    sql_len = C2D(Substr(statement,5,2))
    no_host_var = C2D(Substr(statement,7+sql_len,2))

    /* Only interested if host variables are used */

    If no_host_var > 0 Then Do
      sql = Substr(statement,7,sql_len)
      /* squeeze out blanks either side of a .  */
      sql_ws = sql
      sql = ''
      Do Forever
        Parse Var sql_ws pre ' . ' sql_ws
        If sql_ws = '' Then Do
          sql = (sql)(pre)
          Leave
        End
        sql = (sql)(pre)'.'
      End

      host_vars = Substr(statement,11+sql_len)
      host_var_cnt. = 0
      host_var_names = ''
      Do hv = 1 to no_host_var
        /* extract the name of the host variable */
        hv_name_len = C2D(Substr(host_vars,5,2))
        host_var_name = Substr(host_vars,7,hv_name_len)
        /* remove qualifier if there is one */
        Parse Var host_var_name qualifier '.' host_var_name
        If host_var_name = '' Then host_var_name = qualifier
        /* increment counter for that host variable name */
        host_var_cnt.host_var_name = host_var_cnt.host_var_name + 1
        /* add name to list of host variable names */
        If Wordpos(host_var_name,host_var_names) = 0 Then Do
          host_var_names = host_var_names host_var_name
        End
        /* remove host variable just analysed and loop */
        host_vars = Substr(host_vars,7+hv_name_len)
      End /* Do hv = ... */

      /* For each host variable, find the number of times it */
      /* is preceded by a colon in the SQL statement */

      sql_ws = sql
      previous_word = ''
      good_host_var_cnt. = 0
      sql_host_var_cnt. = 0
      /* look at each word of the SQL statement */
      Do while sql_ws <> ''
        /* take next word from the SQL statement  */
        Parse Var sql_ws word sql_ws
        /* remove the qualifier if there is one */
        Parse Var word qualifier '.' word
        If word = '' Then word = qualifier
        /* check whether this word is a host variable */
        If host_var_cnt.word > 0 Then Do
          sql_host_var_cnt.word = sql_host_var_cnt.word + 1
          If previous_word = ':' Then ,
            good_host_var_cnt.word = good_host_var_cnt.word + 1
        End /* If host... */
        previous_word = word
      End /* Do while sql_ws ... */

      /* report any potential missing colons  */

      Do while host_var_names <> ''
        Parse Var host_var_names host_var_name host_var_names
        If good_host_var_cnt.host_var_name ,
            < host_var_cnt.host_var_name ,
            & sql_host_var_cnt.host_var_name > 0 Then Do

          /* write out dbrm header and accumulate dbrm stats */

          If dbrm_rep.collid_or_plan.name.version = 0 Then Do
            dbrm_missing_colon_cnt = dbrm_missing_colon_cnt + 1
            dbrm_rep.collid_or_plan.name.version = 1

            /* write out DBRM header */

            p=p+1; o.p = " "

            colplan = Strip(collid_or_plan)

            If planpack = 'PACK' Then Do
              p=p+1; o.p = "Collid:" collid_or_plan "DBRM name:" name
              p=p+1; o.p = "Version:" version
              /* build the rebind command */
              r=r+1;rebind.r = "REBIND PACKAGE ("colplan"."Strip(name)
              If version<>"" Then rebind.r=rebind.r".("Strip(version)")"
              rebind.r=rebind.r")"
            End
            Else Do
              p=p+1; o.p = "Plan name:" collid_or_plan "DBRM name:" name
              p=p+1; o.p = "Version:" version
              If plan_rep.collid_or_plan = 0 Then Do
                r=r+1
                rebind.r = "REBIND PLAN ("Strip(collid_or_plan)")"
                plan_rep.collid_or_plan = 1
              End
            End
            p=p+1; o.p = "Creator:" creator ,
                         "PDS:" pdsname
            p=p+1; o.p = "Precompile TS:" pctimestamp ,
                         "; Contoken(hex):" C2X(contoken)
          End

          /* write out statement header if not already done */

          If stmt_rep.collid_or_plan.name.version.stmtno = 0 Then Do
            p=p+1; o.p = " "
            p=p+1
            o.p = "  Statement No:" stmtno "may have missing colons."
            /* write out SQL statement in blocks of 60 characters */
            Do while sql <> ''
              Parse Var sql line 61 sql
              p=p+1; o.p = "  SQL:" line
            End
            p=p+1; o.p = " "
            p=p+1; o.p = "   " ,
                         Left("Host Variable",40) ,
                         Left("DBRM",10) ,
                         Left("SQL ",10) ,
                         Left("SQL with :",10)
            stmt_rep.collid_or_plan.name.version.stmtno = 1
          End

          /* write out host variable name that has missing colons */

          p=p+1; o.p = "   " ,
                       Left(host_var_name,40) ,
                       Left(host_var_cnt.host_var_name,10) ,
                       Left(sql_host_var_cnt.host_var_name,10) ,
                       Left(good_host_var_cnt.host_var_name,10)
          /* accumulate stats on maximum missing colons */
          tot_missing_colon_cnt = tot_missing_colon_cnt ,
                                  + host_var_cnt.host_var_name ,
                                  - good_host_var_cnt.host_var_name
        End /* If found potential missing colons */
      End /* Do while host_var_names... */
    End /* If no_host_var .... */
  End /* If .. new statement */
End /* Do while record_no..  */

/* write out report */
p=p+1;o.p=" "
p=p+1;o.p="End of Report; "
p=p+1;o.p=" "
p=p+1;o.p="DBRMs examined     :" dbrm_cnt
p=p+1;o.p="Statements examined:" stmt_cnt
p=p+1;o.p="DBRMs possibly missing colons:" dbrm_missing_colon_cnt
p=p+1;o.p="Maximum missing colons       :" tot_missing_colon_cnt

p=p+1;o.p="REBIND Commands:"
p=p+1;o.p=" "
o.0=p
/* write to report dataset */

"EXECIO * DISKW REPDD (STEM o. )"
"EXECIO * DISKW REPDD (STEM rebind. FINIS)"
"EXECIO 0 DISKR INDD (STEM FINIS"

/* comment when finished testing */

Exit rc

/* Read records from the input file */

Read_a_record:
/* Read in DBRM data from the file unloaded from the DB2 catalog */

If record_no = stmt.0 Then Do
  Signal Off Error
  "EXECIO" blksize "DISKR INDD (STEM stmt."
  Signal On Error
  If rc <> 0 Then If rc <> 2 Then Signal Error
  If stmt.0 = 0 Then Do
    eof = 1
    Return
  End
  record_no = 0
End  /* If record.... */
record_no = record_no + 1

/* parse statement          */
Parse Var stmt.record_no planpack 5 collid_or_plan 23 name ,
  31 version  95 sectno 100 stmtno 105 seqno 110 creator ,
  118 contoken 126 pctimestamp 152 pdsname 196 rec_len 198 text
rec_len = C2D(rec_len)
Return

Error:
exit_rc = rc
Say "Error message:"
Say "Non zero return code from external call"
Say "Return code: "exit_rc
Say "Call was in line "sigl
Say "Source line is: "
Say Sourceline(sigl)
Exit exit_rc
./ ENDUP
DL
//SYSUT2  DD DSN=<iscuser>.DBRMCHK.EXEC,DISP=(,CATLG),
//        UNIT=<sysallda>,SPACE=(TRK,(1,1,10)),
//        LRECL=80,RECFM=FB,BLKSIZE=3200
//***************************************************************
//* STEP 1A: DELETE OLD EXTRACT FILE FROM DBRM/STMT (IF ANY)
//***************************************************************
//DELPLAN1 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
   DELETE '<iscuser>.DBRMCHK.DATA.PLAN'
   SET MAXCC=0
/*
//***************************************************************
//* STEP 1B: EXTRACT FROM SYSIBM.SYSDBRM AND SYSIBM.SYSSTMT
//***************************************************************
//UNLPLAN1 EXEC PGM=IKJEFT01,DYNAMNBR=20,REGION=0M
//STEPLIB  DD DSN=<db2hqual>.SDSNLOAD,DISP=SHR
//SYSTSPRT DD  SYSOUT=*
//SYSTSIN  DD  *
 DSN SYSTEM(<ssid>)
 RUN  PROGRAM(DSNTIAUL) PLAN(<dsntib51>) PARMS('SQL') -
      LIB('<db2hqual>.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSPUNCH DD DUMMY
//SYSREC00 DD DISP=(NEW,CATLG),UNIT=<sysallda>,SPACE=(CYL,(10,2),RLSE),
//            DSN=<iscuser>.DBRMCHK.DATA.PLAN
//****************************************************************
//*  FOR BETTER PERFORMANCE CONSIDER AN INDEX ON
//*  PLNAME AND NAME IN BOTH SYSDBRM AND SYSSTMT
//****************************************************************
//SYSIN    DD *
SELECT SUBSTR('PLAN',1,4)
     , SUBSTR(D.PLNAME CONCAT '          ',1,18), D.NAME
     , SUBSTR(D.VERSION CONCAT ' ',1,64)
     , DIGITS(S.SECTNO), DIGITS(S.STMTNO), DIGITS(S.SEQNO)
     , D.PLCREATOR, D.TIMESTAMP, D.PRECOMPTS
     , SUBSTR(D.PDSNAME CONCAT ' ',1,44) , S.TEXT
FROM SYSIBM.SYSSTMT S
   , SYSIBM.SYSDBRM D
   , (SELECT NAME,PLNAME FROM SYSIBM.SYSSTMT
      WHERE SUBSTR(TEXT,43,1) <> '1'
        AND SECTNO = 0
        AND STMTNO = 0
        AND SEQNO  = 0) AS F1
WHERE S.PLNAME = D.PLNAME
  AND S.NAME   = D.NAME
  AND S.PLNAME = F1.PLNAME
  AND S.NAME = F1.NAME
  AND D.PLNAME = F1.PLNAME
  AND D.NAME = F1.NAME
ORDER BY 1,2,3,4,5,6,7 WITH UR
;
/*
//***************************************************************
//* STEP 1C: DELETE OLD REPORT FILE IF IT EXISTS
//***************************************************************
//DELREP1 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
   DELETE '<iscuser>.DBRMCHK.REPORT.PLAN'
   SET MAXCC=0
/*
//***************************************************************
//* STEP 1D: RUN REXX DBRMCHK TO READ AND REPORT ON EXTRACTED DATA
//* THE REPORT FILE MUST BE READ TO CHECK WHETHER THERE ARE
//* DBRM'S WITH HOST VARIABLES WITH MISSING COLONS.
//***************************************************************
//DBRMCHK1 EXEC PGM=IKJEFT01,DYNAMNBR=20,REGION=0M
//SYSEXEC  DD DSN=<iscuser>.DBRMCHK.EXEC,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//INDD     DD DSN=<iscuser>.DBRMCHK.DATA.PLAN,DISP=SHR
//REPDD    DD DISP=(NEW,CATLG),UNIT=<sysallda>,SPACE=(TRK,(5,5),RLSE),
//            DSN=<iscuser>.DBRMCHK.REPORT.PLAN
//SYSTSIN  DD  *
 DBRMCHK
/*
//***************************************************************
//* STEP 2A: DELETE EXTRACT FILE FROM SYSPACKAGE/SYSPACKSTMT (IF ANY)
//***************************************************************
//DELPACK2 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
   DELETE '<iscuser>.DBRMCHK.DATA.PACK'
   SET MAXCC=0
/*
//***************************************************************
//* STEP 2B: UNLOAD FROM SYSIBM.SYSPACKAGE AND SYSPACKSTMT
//***************************************************************
//UNLPACK2 EXEC PGM=IKJEFT01,DYNAMNBR=20,REGION=0M
//STEPLIB  DD DSN=<db2hqual>.SDSNLOAD,DISP=SHR
//SYSTSPRT DD  SYSOUT=*
//SYSTSIN  DD  *
 DSN SYSTEM(<ssid>)
 RUN  PROGRAM(DSNTIAUL) PLAN(<dsntib51>) PARMS('SQL') -
      LIB('<db2hqual>.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSPUNCH DD DUMMY
//SYSREC00 DD DISP=(NEW,CATLG),UNIT=<sysallda>,SPACE=(CYL,(10,2),RLSE),
//            DSN=<iscuser>.DBRMCHK.DATA.PACK
//SYSIN    DD *
SELECT SUBSTR('PACK',1,4), P.COLLID, P.NAME
     , SUBSTR(P.VERSION CONCAT ' ',1,64)
     , DIGITS(S.SECTNO), DIGITS(S.STMTNO), DIGITS(S.SEQNO)
     , P.CREATOR, P.CONTOKEN, P.PCTIMESTAMP
     , SUBSTR(P.PDSNAME CONCAT ' ',1,44) , S.STMT
FROM SYSIBM.SYSPACKAGE P
   , SYSIBM.SYSPACKSTMT S
   , (SELECT LOCATION , COLLID, NAME, CONTOKEN
      FROM SYSIBM.SYSPACKSTMT
      WHERE SUBSTR(STMT,43,1) <> '1'
        AND SEQNO  = 0
        AND STMTNO = 0
        AND SECTNO = 0 ) AS S1
WHERE P.LOCATION = S.LOCATION
  AND P.COLLID = S.COLLID
  AND P.NAME   = S.NAME
  AND P.CONTOKEN = S.CONTOKEN
  AND P.LOCATION = S1.LOCATION
  AND P.COLLID = S1.COLLID
  AND P.NAME   = S1.NAME
  AND P.CONTOKEN = S1.CONTOKEN
  AND S.LOCATION = S1.LOCATION
  AND S.COLLID = S1.COLLID
  AND S.NAME   = S1.NAME
  AND S.CONTOKEN = S1.CONTOKEN
  AND P.IBMREQD NOT IN ('E','F','G','H','I')
ORDER BY 1,2,3,4,5,6,7 WITH UR
;
//*
//***************************************************************
//* STEP 2C: DELETE OLD REPORT FILE IF IT EXISTS
//***************************************************************
//DELREP2 EXEC PGM=IDCAMS
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
   DELETE '<iscuser>.DBRMCHK.REPORT.PACK'
   SET MAXCC=0
/*
//***************************************************************
//* STEP 2D: RUN REXX DBRMCHK TO READ AND REPORT ON EXTRACTED DATA
//* THE REPORT FILE MUST BE READ TO CHECK WHETHER THERE ARE
//* DBRM'S WITH HOST VARIABLES WITH MISSING COLONS.
//***************************************************************
//DBRMCHK2 EXEC PGM=IKJEFT01,DYNAMNBR=20,REGION=0M
//SYSEXEC  DD DSN=<iscuser>.DBRMCHK.EXEC,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//INDD     DD DSN=<iscuser>.DBRMCHK.DATA.PACK,DISP=SHR
//REPDD    DD DISP=(NEW,CATLG),UNIT=<sysallda>,SPACE=(TRK,(5,5),RLSE),
//            DSN=<iscuser>.DBRMCHK.REPORT.PACK
//SYSTSIN  DD  *
 DBRMCHK
//***************************************************************
//* Print the PLAN report
//***************************************************************
//PRTPLAN  EXEC PGM=IEBGENER
//SYSIN    DD DUMMY
//SYSPRINT DD DUMMY
//SYSUT1   DD DSN=<iscuser>.DBRMCHK.REPORT.PLAN,DISP=OLD
//SYSUT2   DD SYSOUT=*
//***************************************************************
//* Print the PACK report
//***************************************************************
//PRTPACK  EXEC PGM=IEBGENER
//SYSIN    DD DUMMY
//SYSPRINT DD DUMMY
//SYSUT1   DD DSN=<iscuser>.DBRMCHK.REPORT.PACK,DISP=OLD
//SYSUT2   DD SYSOUT=*
//*
//* END OF JOB
//