ReCreate DBRM from DB2Catalog

Have recreated DBRM from DB2Catalog using the following REXX program. Thanks to Venkat Pillai for his DBRMCNV rexx program (I just modified it to recreate the DBRM from DB2Catalog)

GETDBRM - REXX:

/*                REXX                                      */
/*                                                          */
/* FUNCATIONALITY: TO CREATE DBRM FROM DB2 CATALOG. POST 2.2*/
/* VERSION.                                                 */
/*                                                          */
/* CONTENTS OF THE INPUT DATASET:                           */
/*                                                          */
/* <DBRM NAME> <PLANNAME>                                   */
/* EXAMPLE:-                                                */
/* PGM1 PLAN1                                               */
/*                                                          */
/*  FOLLOWING SQLS COULD BE USED TO GENERATE THE INPUT FILE */
/*                                                          */
/*  PLAN OPTION:                                            */
/*                                                          */
/*   SELECT NAME,PLNAME                                     */
/*   FROM SYSIBM.SYSSTMT                                    */
/*   WHERE SEQNO = 0  AND STMTNO = 0 AND SECTNO = 0         */
/*   WITH UR;                                               */
/*                                                          */
/*                                                          */
FLD1 = SUBSYS
"ISPEXEC ADDPOP ROW(2) COLUMN(2)"
DISP_SCR:
"ISPEXEC DISPLAY PANEL(GETDBRM) CURSOR("FLD1") "
IF RC = 8 THEN EXIT
CALL CHECKDS
/*                                                          */
/*    OPEN DB2 CONNECTION                                   */
/*                                                          */
  CALL OPEN_DB2_CONN
K = 0
/*                                                          */
/*    MAIN LOGIC                                            */
/*                                                          */
DO LN = 1 TO INL.0   /* RUN FOR EACH INPUT LINE */
  NAME1    = STRIP(WORD(INL.LN,1))
  PLNAME   = STRIP(WORD(INL.LN,2))

  CALL GET_CONTOKEN
  FIRST_FETCH = 'TRUE '
  LEAVE_SWITCH = 0

  DO FOREVER
     CALL READ_DATA  /* READ THE SYSIBM.SYSSTMT */
     IF (FIRST_FETCH = 'FALSE' & LEAVE_SWITCH = 0) THEN DO
       CALL CREATE_NEW_FORMAT
     END
     IF LEAVE_SWITCH = 1 THEN DO
        CALL GENERATE_DBRM
        LEAVE
     END
     ELSE FIRST_FETCH = 'FALSE'
     LEAVE_SWITCH = 0
  END
  ADDRESS DSNREXX "EXECSQL" "CLOSE C1"
END
CALL GENERATE_DBRM
EXIT
/*                       */
/*   SUBROUTINES BELOW   */
/*                       */
OPEN_DB2_CONN:
  'SUBCOM DSNREXX'
  IF RC THEN
     S_RC = RXSUBCOM('ADD','DSNREXX','DSNREXX')
  ADDRESS DSNREXX 'CONNECT' SUBSYS
  SQL = "SELECT SEQNO,STMTNO,SECTNO,TEXT FROM SYSIBM.SYSSTMT ",
      " WHERE NAME = ? AND PLNAME = ? ORDER BY 2,1,3 WITH UR "
  ADDRESS DSNREXX "EXECSQL" "DECLARE C1 CURSOR FOR S1"
  ADDRESS DSNREXX "EXECSQL" "PREPARE S1 FROM :SQL"
  IF SQLCODE ^= 0 THEN
  DO
     SAY  'ERROR IN PREPARE S1 '
     CALL DISP_SQLCA
     RETURN(8)
  END
  SQL1 = "SELECT PLCREATOR,TIMESTAMP FROM SYSIBM.SYSDBRM ",
      " WHERE NAME = ? AND PLNAME = ? WITH UR"
RETURN
/*                       */
GET_CONTOKEN:
  ADDRESS DSNREXX "EXECSQL" "OPEN C1 USING :NAME1,:PLNAME"
  IF SQLCODE ^= 0 THEN
  DO
     SAY  'ERROR IN OPEN S1 '
     CALL DISP_SQLCA
     RETURN(8)
  END
  ADDRESS DSNREXX "EXECSQL" "DECLARE C2 CURSOR FOR S2"
  ADDRESS DSNREXX "EXECSQL" "PREPARE S2 FROM :SQL1"
  IF SQLCODE ^= 0 THEN
  DO
     SAY  'ERROR IN PREPARE S2 '
     CALL DISP_SQLCA
     RETURN(8)
  END
  ADDRESS DSNREXX "EXECSQL" "OPEN C2 USING :NAME1,:PLNAME"
    IF SQLCODE ^= 0 THEN
    DO
       SAY  'ERROR IN OPEN C2 '
       CALL DISP_SQLCA
       RETURN(8)
    END
  ADDRESS DSNREXX "EXECSQL" "FETCH C2 INTO :PLOWNER,:CONTOKEN "
  IF SQLCODE = 100 THEN ITERATE
    IF SQLCODE < 0 THEN
    DO
       SAY  'ERROR IN FETCH C2'
       CALL DISP_SQLCA
       RETURN(8)
    END
  ADDRESS DSNREXX "EXECSQL" "CLOSE C2 "
    IF SQLCODE <> 0 THEN
    DO
       SAY  'ERROR IN CLOSE C2'
       CALL DISP_SQLCA
       RETURN(8)
    END
RETURN
/*                       */
READ_DATA:
  ADDRESS DSNREXX "EXECSQL" "FETCH C1 INTO :SEQNO,:STMTNO, ",
  ":SECTNO, :TEXT "
  IF SQLCODE = 100 THEN LEAVE_SWITCH = 1
  IF SQLCODE = 0 & FIRST_FETCH = 'TRUE ' THEN CALL HEADER_FORMAT
    IF SQLCODE <  0 THEN
    DO
       SAY  'ERROR IN FETCH C1'
       CALL DISP_SQLCA
       RETURN(8)
    END
RETURN
/*                       */
HEADER_FORMAT:
  SAY 'PROCESSING ...   'NAME1
  TEXT = SUBSTR(TEXT,1,40)
 K = K + 1
  OUT.K = 'DBRM'||'000000A0'X||PLOWNER||,
   LEFT(NAME1,8,' ')||CONTOKEN||TEXT||'0000F100008000C8'X
 K = K + 1
 OUT.K = '0000'X||COPIES(' ',64)||COPIES('00'X,14)
RETURN
GENERATE_DBRM:
DSNM = "'"||DBRMDSN||'('||STRIP(NAME1)||")'"
 "ALLOC DD(OUP) DS("DSNM") SHR REU"
 "EXECIO "K" DISKW OUP (STEM OUT. FINIS"
 "FREE DD(OUP)"
 K = 0
RETURN
CREATE_NEW_FORMAT:
  STMTNO = D2C(STMTNO,2); SECTNO = D2C(SECTNO,2)
  TOT_LEN  = C2D(SUBSTR(TEXT,1,4))
  IF TOT_LEN > 254 THEN CALL GET_NEXT_LINE
  SQL_TEXT = SUBSTR(TEXT,5,TOT_LEN)
  NEW_TEXT = SECTNO||'00000000'X||STMTNO||'00000014'X||SQL_TEXT
/*D2C(LENGTH(SQL_TEXT),4)||SQL_TEXT */
  TOT_STMT = 'DBRM'||D2C(LENGTH(NEW_TEXT)+8,4)||NEW_TEXT
  CALL SPLIT_80
RETURN
/*                       */
GET_NEXT_LINE:
  HOW_MANY_LINES = (TOT_LEN+4) % 254
  DO I = 1 TO HOW_MANY_LINES
    REM_TEXT = TEXT
    ADDRESS DSNREXX "EXECSQL" "FETCH C1 INTO :SEQNO,:STMTNO, ",
    ":SECTNO, :TEXT "
   IF SQLCODE = 100 THEN LEAVE_SWITCH = 1
   IF SQLCODE <  0 THEN
   DO
      SAY  'ERROR IN FETCH C1 FOR MORE LINES '
      CALL DISP_SQLCA
      RETURN(8)
   END
     TEXT = REM_TEXT || TEXT
  END
  STMTNO = D2C(STMTNO,2)
  SECTNO = D2C(SECTNO,2)
RETURN
/*                       */
SPLIT_80:
START_POS = 1
STMT_LEN = LENGTH(TOT_STMT)
  DO FOREVER
     K = K + 1
     OUT.K = SUBSTR(TOT_STMT,START_POS,80,' ')
     START_POS = START_POS + 80
     IF START_POS > STMT_LEN THEN LEAVE
  END
RETURN
/*                       */
CHECKDS:
 SUBSYS = STRIP(SUBSYS)
 UDSN = STRIP(UDSN)
 DSNM1 = UDSN
 DSNM = "'"||DSNM1||"'"
 MEM = STRIP(MEM)
 IF SYSDSN(DSNM) <> 'OK' THEN
    DO
      ZEDSMSG = 'OUTPUT DATASET NOT FOUND'
      ZEDLMSG = DSNM  'NOT FOUND IN MVS CATALOG'
      ADDRESS ISPEXEC  "SETMSG MSG(ISRZ001)"
      FLD1 = 'UDSN'
      SIGNAL DISP_SCR
    END
 ELSE
   DO
     ADDRESS TSO
     X=LISTDSI(DSNM)
     IF SYSDSORG = 'PO' THEN
       UDSN = "'"||DSNM1||"("||MEM||")'"
     ELSE UDSN = DSNM
  END
 DSNM1 = DBRMDSN
 DSNM = "'"||DSNM1||"'"
 IF SYSDSN(DSNM) <> 'OK' THEN
    DO
      ZEDSMSG = 'DBRM DATASET NOT FOUND'
      ZEDLMSG = DSNM  'NOT FOUND IN MVS CATALOG'
      ADDRESS ISPEXEC  "SETMSG MSG(ISRZ001)"
      FLD1 = 'DBRMDSN'
      SIGNAL DISP_SCR
    END
 ELSE
   DO
     ADDRESS TSO
     X=LISTDSI(DSNM)
     IF SYSDSORG <> 'PO' THEN   DO
        ZEDSMSG = 'DBRM DATASET MUST BE  '
        ZEDLMSG = DSNM  'PDS '
        ADDRESS ISPEXEC  "SETMSG MSG(ISRZ001)"
        FLD1 = 'DBRMDSN'
        SIGNAL DISP_SCR
      END
  END
"ALLOC DD(INP) DS("UDSN") SHR REU"
"EXECIO * DISKR INP (STEM INL. FINIS"
"FREE DD(INP)"
NO_OF_WORDS = WORDS(INL.1)
IF NO_OF_WORDS  <> 2  THEN DO
   SAY 'FOR OPTION PLAN THERE MUST BE <DBRMNAME PLANNAME> '
   SAY 'SPECIFIED. EXITING ... '
   EXIT
END
RETURN
 DISP_SQLCA:
  SAY 'SQL ERROR. SQLCA FOLLOWING'
  SAY 'SQLCODE  = 'SQLCODE
  SAY 'SQLERRMC = 'SQLERRMC
  SAY 'SQLSTATE = 'SQLSTATE
  SAY 'SQLWARN  = 'SQLWARN.0',' || SQLWARN.1',' || SQLWARN.2',' ||,
                   SQLWARN.3',' || SQLWARN.4',' || SQLWARN.5',' ||,
                   SQLWARN.6',' || SQLWARN.7',' || SQLWARN.8',' ||,
                   SQLWARN.9',' || SQLWARN.10
  SAY 'SQLERRD  = 'SQLERRD.1',' || SQLERRD.2',' || SQLERRD.3',' ||,
                   SQLERRD.4',' || SQLERRD.5',' || SQLERRD.6
  SAY 'SQLERRP  = 'SQLERRP
  SAY 'SQLERRM  = 'SQLERRM
  S_RC=  RXSUBCOM('DELETE','DSNREXX','DSNREXX')
  EXIT
RETURN(0)

GETDBRM Panel:

)ATTR                                                                  
    , type(input)   Intens(low)  caps(on) just(left)                   
)BODY EXPAND ({}) Window (69,15)                                       
%{-} RETRIEVE DBRMS FROM DB2 CATALOG {-}%                              
% COMMAND ===>,ZCMD                                                    
+                                                                      
+ %SUB SYSTEM NAME  ===>,SUBSYS%                                       
+                                                                      
+ %Please specify the dataset names without quotes      %              
+ %DBRM O/P DATASET ===>,DBRMDSN                                 =     
   %                                                                   
+ %INPUT DATASET    ===>,UDSN                                    =     
   %                                                                   
+ %MEMBER NAME      ===>,MEM     % Only for PDS      %                 
+                                                                      
+                    Enter%PF3+to terminate                            
)INIT                                                                  
 VGET (SUBSYS,DBRMDSN,UDSN) PROFILE                                    
)REINIT                                                                
  REFRESH(*)                                                           
)PROC                                                                  
 VER(&SUBSYS,NB)                                                       
 VER(&DBRMDSN,NB)                                                      
 VER(&UDSN,NB)                                                         
 VPUT (SUBSYS,DBRMDSN,UDSN) PROFILE                                    
)END                                                                   
/*                                                                   */
/* THIS PANEL IS USED TO SPECIFY THE PROCESSING OPTIONS FOR %GETDBRM */
/* - WHICH RETRIVES DBRM FROM DB2 CATALOG                            */

REGEDIT - After a long time

After a long long time I used REGEDIT to get rid of some s/w I installed in my system.. SVN and TortoiseSVN and Git Extensions.. I don't understand where is the user friendliness of these tools?? Anyways for my one man project I don't need a version control tool and so removed all of those s/w..

Every problem has a solutions!!

AFP [Advanced Function Printing] to PDF [Portable Document Format]

As I am in support for one of the legacy TCS tool called CasePac.. I am forced to learn quite a lot things about mainframes.. I never thought Mainframes had so much importance during 80's and 90's.. What is quiet amazing is the tool architecture, design, coding and support.. Its just too great.. In my little IT experience I have never seen such a great work @ TCS..

Some of the manuals were in Mainframes and I was wondering how this can be moved to Windows.. I got a hard copy by printing it directly from mainframes, I am not happy holding those many papers and wasting that many papers for every one.. As the printout was done by a laser printer, it much not be an legacy thing, so did some googling and found about AFP.. Then one more day to thinking and googling, got to know about the AFP Viewer plugin.. Its free and it worked perfectly.. I have got soft copy of CasePac 4.3 Manual without wasting any paper..

It feels really good making contribution to Green Earth despite how little the contribution may be.. :-)

I got a chance to use it for some other legacy systems manuals too.. Not sure what sort of feedback I will get. I just over reacted on providing my solution.. :-( I should have made it more polite way..

System Variables and User Defined Variables

Windows Server System Environment Variables and User Variables.

Last week I developed an applet program for an xml masking process in Java.. I was referring to an environment variable in my program. When it was tested by clients they reported an IO Exception for which I couldn't find a cause.. Luckily getMessage() function was handy and I had learned that you can set Environment Variables @ User level and @ System Level.. I have set-up that environment variable @ user level and the program had run successfully, when the client tested the variable was not defined and the program failed..

Code snippet to read OS environment variables

Map<String, String> variables = System.getenv();

for (Map.Entry<String, String> entry : variables.entrySet())
{
   String name = entry.getKey();
   String value = entry.getValue();
   System.out.println(name + "=" + value);
}

My First Contribution in Mighty

CALLTREE

a.     Overview of the Tool

          This REXX tool provides the list of static calls details in the given program, this list proceeds to the end of the call tree.

b.     Execution Procedure

·         Include the REXX source code in your XXXXXXXX.REXX.EXEC dataset.

·         The tool in the REXX.EXEC source is directly called using the JCL with the following required data successful execution

1.      Program name

2.      Source PDS: The list of datasets where the tool can look for the called programs in the input program.

3.      Output dataset name.

4.      Exclude PDS: The PDS list containing program that can be avoided from processing by the tool.

·         The output dataset generated in CSV file format need to be downloaded to the local system with .CSV extension in output file name in TEXT mode.

b.     Limitations of the tool

a.      Applicable only for COBOL code.

b.      The tool has no internal logic to identify the DYNAMIC calls.

c.      Scope of the tool

·         The Tool can be used for find the flow of the code till the end point.

·         The Tool also provides the details of the dataset from where the code is taken for processing.

·         The Tool helps in finding the impact of the code changes any where in the flow from the source program to codes down the order to the last code.

·         The Tool can be used to get the call structure of multiple programs in a single run, which will be useful to get the scope of a particular module.