/*REXX */
/********************************************************************/
/*                                                                  */
/* Name:  EXPLN                                                     */
/*                                                                  */
/*    Description:  Generates Explain Report on fly  when           */
/*                  invoked from an App Engine Trace                */
/*                                                                  */
/********************************************************************/
/*                                                                  */
/*                    Log of Changes                                */
/*                                                                  */
/* Ver  Control  YY/MM/DD  Analyst            Description           */
/* ---  -------  --------  -----------------  -----------           */
/*                                                                  */
/* A00  None               Venkat  Srinivasan  Explain Report       */
/*                         Chinmay Bhatta                           */
/*                                                                  */
/********************************************************************/

ADDRESS "ISREDIT"
"MACRO (creator)"

/*------------------------------------------------------------------*/
/*  Initialize variables                                            */
/*------------------------------------------------------------------*/
 user  = USERID()
 out.  = ''
 out.0 = 0
 outn  = 0

 or.   = ''
 or.0  = 0
 orno  = 0

/*------------------------------------------------------------------*/
/*  Main Processing Loop                                            */
/*------------------------------------------------------------------*/
 CALL RECEIVE_INPUT

 IF RC = 0 THEN
   CALL CONNECT_TO_DB2

 IF RC = 0 THEN
   CALL EXPLAIN_STMT

 IF RC = 0 THEN
   DO
     IF Memname <> '' then
       CALL WRITE_OUTPUT
     ELSE
       DO
         CALL SHOW_EXPLAIN
         SAY 'Output Not Written to any dataset'
       END
   END

EXIT

/*------------------------------------------------------------------*/
/*                                                                  */
/*------------------------------------------------------------------*/
RECEIVE_INPUT:

creator = STRIP(creator)
UPPER creator
IF creator = '' THEN
  DO
    Say 'The macro expects CREATOR NAME as Argument'
    Say 'Enter EXPLN <CREATOR> in the Command Line'
    RC = 4
    RETURN
  END

  "(curow,curcol) = CURSOR"
   IF curcol > 0 THEN
     DO
       "(data) = LINE " curow
       data = SUBSTR(data,curcol)
     END

  SELECT
     WHEN SUBSTR(data,1,6) = 'SELECT' THEN NOP
     WHEN SUBSTR(data,1,6) = 'INSERT' THEN NOP
     WHEN SUBSTR(data,1,6) = 'DELETE' THEN NOP
     WHEN SUBSTR(data,1,6) = 'UPDATE' THEN NOP
     OTHERWISE
       DO
         SAY '*** Cursor must be on <S>elect or <I>nsert or <U>pdate or <D>elete Stmt ***'
         RC = 4
         RETURN
       END
  END

  outn = outn + 1
  out.outn = data

  row = curow + 1
  ln  = ''
  DO WHILE WORDPOS('/',ln) <> 1
    data = data ³³ ln
    "(ln) = LINE " row
    outn = outn + 1
    IF WORDPOS('/',ln) <> 1 THEN
       out.outn = ln
     ELSE
       out.outn = ";"
    row = row + 1
  END


  SAY 'Enter the Subsystem Name :'
    Pull SUBSYSID
    SUBSYSID = STRIP(SUBSYSID)
    UPPER SUBSYSID
    IF SUBSYSID = '' THEN
      DO
        Say '*** DB2 Subsystem ID cannot be spaces ***'
        RC = 4
        RETURN
      END

  SAY 'Enter the Query No :'
    Pull Queryno
    Queryno = STRIP(Queryno)
    UPPER Queryno
    IF Queryno = '' THEN
      DO
        Say '*** QUERY NO must be Numeric ***'
        RC = 4
        RETURN
      END

  SAY 'Enter the Member Name :'
  SAY '( This Member will have saved SQL in ' || user ||'.Z.EXPLIB)'
    Pull Memname
    Memname = STRIP(Memname)
    UPPER Memname

RETURN

/*------------------------------------------------------------------*/
/*                                                                  */
/*------------------------------------------------------------------*/
 CONNECT_TO_DB2:

     CALL RXSUBCOM 'ADD', 'SQL', 'RXTASQL'
     IF RC > 4 THEN
       DO
         SAY 'RXSUBCOM FAILED. CREATE TERMINATING.'
         EXIT
       END
     ELSE
       DO
         CALL DSNALI 'OPEN', SUBSYSID, 'RXTCS'
         IF RC <> 0 THEN
           DO
             SAY 'DSNALI OPEN RC='RC 'REASON='REASON
             SAY 'OPEN FAILED. DB201 TERMINATING.'
             EXIT
           END
         ELSE RC = 0
       END

 RETURN


/*------------------------------------------------------------------*/
/*                                                                  */
/*------------------------------------------------------------------*/
 EXPLAIN_STMT:

   sql_sqlid  = 'SET CURRENT SQLID = ' || "'" || creator || "'"

   sql_delete = "DELETE FROM "||creator||".PLAN_TABLE WHERE QUERYNO = ",
                || Queryno

   sql_explnm = 'EXPLAIN PLAN SET QUERYNO = '|| Queryno|| ' FOR ' || data

   ADDRESS SQL sql_sqlid
     CALL CHECK_SQLCODE '*** Error in setting CURRENT SQLID ***'

   IF RC = 0 THEN
     DO
       ADDRESS SQL sql_delete
       CALL CHECK_SQLCODE '*** Error in DELETING the QUERYNO from PLAN_TABLE ***'
     END

   IF RC = 0 THEN
     DO
        ADDRESS SQL sql_explnm
        CALL CHECK_SQLCODE '*** Error in executing EXPLAIN statement ***'
     END

   IF RC = 0 THEN
     DO
      ADDRESS SQL "SELECT METHOD,TNAME, ACCESSTYPE, MATCHCOLS, ACCESSNAME, INDEXONLY,",
                  "PREFETCH FROM PLAN_TABLE WHERE QUERYNO = "|| Queryno || " ORDER BY",
                  "QUERYNO, QBLOCKNO, PLANNO, MIXOPSEQ"
      CALL CHECK_SQLCODE '*** Error in Fetching the Plan Table row ***'
     END

 RETURN

/*------------------------------------------------------------------*/
/*                                                                  */
/*------------------------------------------------------------------*/
 CHECK_SQLCODE:

  arg the_msg
  If sQLCA.SQLCODE = 0 Then Return
  If SQLCA.SQLCODE > 0 Then Return

  If SQLCA.SQLCODE < 0 Then SAY the_msg
  SAY '*** SQLCA VARIABLES ***'
  RC = 4
  SAY "SQLCODE  = " sqlca.SQLCODE
  SAY 'SQLSTATE = ' sqlca.SQLSTATE
  SAY "SQLERRP  = " sqlca.SQLERRP
  SAY "SQLERRMC = " sqlca.SQLERRMC
  SAY "SQLWARN  = " sqlca.SQLWARN.0",",
                    sqlca.SQLWARN.1",",
                    sqlca.SQLWARN.2",",
                    sqlca.SQLWARN.3",",
                    sqlca.SQLWARN.4",",
                    sqlca.SQLWARN.5",",
                    sqlca.SQLWARN.6",",
                    sqlca.SQLWARN.7",",
                    sqlca.SQLWARN.8",",
                    sqlca.SQLWARN.9",",
                    sqlca.SQLWARN.10

   RETURN

/*------------------------------------------------------------------*/
/*                                                                  */
/*------------------------------------------------------------------*/
 WRITE_OUTPUT:

 ADDRESS TSO
 "FREE F(outfile)"
 IF LENGTH(Memname) > 9 THEN
   Memname = SUBSTR(Memname,1,8)
 ODS = user || '.Z.EXPLIB'
 ODS = ODS  || "(" ||Memname||")"

 orno = orno + 1
 or.orno = sql_sqlid || ";"

 orno = orno + 1
 or.orno = ' '

 orno = orno + 1
 or.orno = sql_delete || ";"

 orno = orno + 1
 or.orno = 'COMMIT;'

 orno = orno + 1
 or.orno = ' '

 orno = orno + 1
 or.orno = 'EXPLAIN PLAN SET QUERYNO = '|| Queryno ||' FOR'

 orno = orno + 1
 or.orno = ' '

 CALL SHOW_EXPLAIN

 orno = orno + 1
 DO i = 1 TO outn
   or.orno = out.i
   orno = orno + 1
 END

 "ALLOC DS('"ODS"') F(outfile) SHR REUSE"
 "EXECIO * DISKW outfile (STEM or. FINIS"
 "FREE F(outfile)"

 SAY 'The above result is written to the dataset ' || ODS
RETURN

SHOW_EXPLAIN:

  show.  = ''
  show.0 = 0
  sh     = 1

  show.sh = ' '
  sh = sh + 1

  show.sh = '----------------------------------------------------------------------'
  sh = sh + 1

  show.sh = 'MTHD TNAME              ACTYPE MTHCOL ACCESSNAME         IXONLY PRFTCH'
  sh = sh + 1

  show.sh = '---- ------------------ ------ ------ ------------------ ------ ------'
  sh = sh + 1

  DO i = 1 TO SQLCA.SQLROWS
     show1 = RIGHT(method.i,4,' ') || ' '  || LEFT(tname.i,18,' ') || ' '
     show2 = RIGHT(accesstype.i,6,' ') || ' ' ||RIGHT(matchcols.i,6,' ') || ' '
     show3 = LEFT(accessname.i,18,' ') || ' ' ||RIGHT(indexonly.i,6,' ') || ' '
     show4 = RIGHT(prefetch.i,6,' ')
     show.sh = show1 || show2 || show3 || show4
     sh = sh + 1
  END

  show.sh = '----------------------------------------------------------------------'
  sh = sh + 1

  DO i = 1 TO sh
     SAY show.i
     outn = outn + 1
     out.outn = "--" || show.i
  END

RETURN