Mainframegeek: August 2011

Thursday, August 18, 2011

Submitting Batch JOB( JCL) from CICS Online program.

How do we submit JCL's from CICS Online program?, we can use a TDQ or CICS SPOOL Verbs. So how these CICS SPOOL Verbs differs from TDQ?. The problem with TDQ is that in most of the sites, application developers are not authorized to create TDQ (its sysadmin task ) , not that much flexible, and we are responsible to read TDQ sequentially . So thats how CICS JES commands comes into picture. We can read the Spool data and write into spool. today we are gonna look only submitting the jobs.

The JES-CICS interface is totally depends on SPOOL initialization parm of CICS TS, check with your sysadmin guys whether CICS SPOOL parm is YES or NO. To use JES-CICS, SPOOL keyword must be YES.

Submitting a batch job has 3 steps

1. SPOOLOPEN OUTPUT

We are opening SPOOL for submitting JCL, we need to provide USERID and need to store TOKEN for the connection. We must use the same token till we close the spool. The userId is not RACF ID it must be INTRDR (Internal reader), the token is a 8 bit alphanumeric dataname (PIC x(8).

Snippet.

EXEC CICS SPOOLOPEN OUTPUT
   NODE('LOCAL')
   USERID('INTRDR')
   TOKEN(WS-TOKEN)
   RESP(WS-RESP)
END-EXEC.

2. SPOOLWRITE

Is for writing the lines of JCL statements into spool with INTRDR. we should define the data name which holds the JCL statements as a 80 bit length Alpha numeric field (PIC X(80). we must provide the length in FLENGTH of SPOOLWRITE. we must provide the token which we got when we opened spool.

Snippet.

EXEC CICS SPOOLWRITE
     FROM(WS-LINE(WS-CNTR))
     FLENGTH(LENGTH OF WS-LINE(WS-CNTR))
     RESP(WS-RESP)
     TOKEN(WS-TOKEN)
END-EXEC

3. SPOOLCLOSE

Okay, we have written our jcl statements to JES; now we need to close the spool connection so the JCL will get submitted. we must provide the token which we got when we opened spool.

Snippet.

EXEC CICS SPOOLCLOSE
   RESP(WS-RESP)
   TOKEN(WS-TOKEN)
END-EXEC.

Common Abends/Errors.

1. ALLOCERROR

occurs when Dynamic allocation rejected request to allocate input dataset.

2. INVREQ

Can occur if any of the following happens. Unsupported function, Unsupported language, From dataname is missing etc.

3. SPOLBUSY

JES interface is used by another task.

4. LENGERR

Happens when the from dataname contents and FLENGTH value are mismatching, we can always make use of 'LENGTH OF' keyword to avoid this.

5. NOTOPEN

Spool report has not been opened.

finally

6 NOSPOOL

this happens when we have no JES subsystem.

Sample program for Submitting JCL from CICS

IDENTIFICATION DIVISION.
PROGRAM-ID. SPOOL01.
AUTHOR.     SHIBU.T.
*
DATA DIVISION.
WORKING-STORAGE SECTION.
01 WS-JCL.
     05 WS-LINE                 PIC X(80) OCCURS 13 TIMES.
01 WS-TEMP.
     05 WS-MSG                  PIC X(40).
     05 WS-RESP                 PIC S9(8) COMP.
     05 WS-CNTR                 PIC S9(4) COMP.
     05 WS-TOKEN                PIC X(8).
COPY DFHAID.
*
PROCEDURE DIVISION.
A00100-MAIN-PARA.
     MOVE LOW-VALUES             TO WS-JCL.
     MOVE 'TEST MESSAGE'         TO WS-MSG.
     MOVE '//R0318BJJ JOB REGION=0M'
                                 TO WS-LINE(1).
     MOVE '//MODEL    EXEC PGM=IEFBR14'
                                 TO WS-LINE(2).
     MOVE '//JPAYSLP DD DSN=TSHRCI.PAYROLL.PAYSLIP.GROUP(+1),'
                                 TO WS-LINE(4).
     MOVE '//            DISP=(NEW,CATLG,DELETE),'
                                 TO WS-LINE(5).
     MOVE '//            SPACE=(TRK,5),'
                                 TO WS-LINE(6).
     MOVE '//            DCB=TSHRCI.PAYROLL.PAYSLIP.MODEL,'
                                 TO WS-LINE(7).
     MOVE '//*           VOL=SER=ETRU04,'
                                 TO WS-LINE(8).
     MOVE '//            UNIT=SYSDA'
                                 TO WS-LINE(9).
     MOVE '//SYSIN     DD   *'   TO WS-LINE(10).
     MOVE '//SYSPRINT DD   SYSOUT=*'
                                 TO WS-LINE(11).
     MOVE '/*'                   TO WS-LINE(12).
     MOVE '//'                   TO WS-LINE(13).
     EXEC CICS SEND
        FROM(WS-MSG)
        LENGTH(LENGTH OF WS-MSG)
     END-EXEC.
* OPEN SPOOL.
     EXEC CICS SPOOLOPEN OUTPUT
        NODE('LOCAL')
        USERID('INTRDR')
        TOKEN(WS-TOKEN)
        RESP(WS-RESP)
     END-EXEC.
     IF WS-RESP NOT = DFHRESP(NORMAL)
        MOVE SPACES              TO WS-MSG
        MOVE '* OPEN SPOOL.'     TO WS-MSG
        EXEC CICS SEND
        ERASE
           FROM(WS-MSG)
           LENGTH(LENGTH OF WS-MSG)
        END-EXEC
        PERFORM Z00100-EXIT-PARA
     END-IF.
* WRITE RECORDS INTO SPOOL
     PERFORM VARYING WS-CNTR FROM 1 BY 1 UNTIL
             WS-CNTR = 14
        EXEC CICS SPOOLWRITE
             FROM(WS-LINE(WS-CNTR))
             FLENGTH(LENGTH OF WS-LINE(WS-CNTR))
             RESP(WS-RESP)
             TOKEN(WS-TOKEN)
        END-EXEC
     END-PERFORM.
     IF WS-RESP NOT = DFHRESP(NORMAL)
        MOVE SPACES              TO WS-MSG
        MOVE '* WRITE JCL '      TO WS-MSG
        EXEC CICS SEND
        ERASE
           FROM(WS-MSG)
           LENGTH(LENGTH OF WS-MSG)
        END-EXEC
        PERFORM Z00100-EXIT-PARA
     END-IF.
* CLOSE SPOOL
     EXEC CICS SPOOLCLOSE
        RESP(WS-RESP)
        TOKEN(WS-TOKEN)
     END-EXEC.
     IF WS-RESP NOT = DFHRESP(NORMAL)
        MOVE SPACES              TO WS-MSG
        MOVE '* CLOSE SPOOL'     TO WS-MSG
        EXEC CICS SEND
        ERASE
           FROM(WS-MSG)
           LENGTH(LENGTH OF WS-MSG)
        END-EXEC
     END-IF.
        PERFORM Z00100-EXIT-PARA.
*
Z00100-EXIT-PARA.
     EXEC CICS RETURN
     END-EXEC.

When the application finished the execution you can see the job in SPOOL ( with SDSF or whatever).

Wednesday, August 17, 2011

CURSORS in DB2

When we are using the DB2 in our applications we can only have one row of data at a time. So what we will do if we don't know which row exactly we need?, what if we have more than 1 row to work with? well the answer is "CURSORS".

Cursor is used when more than one row are to be selected. Cursors has mainly 4 control statements.

1. Declare.

A name will be assigned for particular SQL statement. The name should be unique in the scope of the program. there are no limits for the number of cursors which we can have in one application program. We can declare cursor in Working storage section or Procedure division.
E.g.
EXEC SQL
    DECLARE CURREAD1 CURSOR FOR
        SELECT NAME,SEQ FROM IBMGRP.MYNAM
END-EXEC.

2. Open.

This statement builds the resultant table.
E.g.
EXEC SQL
   OPEN CURREAD1
END-EXEC.

3. Fetch.

Fetch statement will returns data from the resultant table (One row at a time) and assigns values to the specified host variables.
E.g.
EXEC SQL
   FETCH CURREAD1 INTO :WS-NAME,:WS-SEQ
END-EXEC

4. Close

Empty all the resources used by the cursor.
E.g.
EXEC SQL
   CLOSE CURREAD1
END-EXEC.

All these control statements will throw specific SQLCODES.

Few Snippets.

Read table.

EXEC SQL
    DECLARE CURREAD1 CURSOR FOR
        SELECT NAME,SEQ FROM IBMGRP.MYNAM
END-EXEC.
Open
EXEC SQL
   OPEN CURREAD1
END-EXEC.

PERFORM UNTIL SQLCODE = 100
   EXEC SQL
      FETCH CURREAD1 INTO :WS-NAME,:WS-SEQ
   END-EXEC
   MOVE SQLCODE             TO WS-SQLCODE
   DISPLAY 'SQLCODE FETCH ' WS-SQLCODE
   IF SQLCODE = 0 THEN
    MOVE SPACES TO TEMP-MSG
    STRING
    'NAME: ' DELIMITED BY SPACE ' ' DELIMITED BY SIZE
    WS-NAME DELIMITED BY SPACE ',' DELIMITED BY SIZE
    'SEQ#' DELIMITED BY SPACE ' ' DELIMITED BY SIZE
    WS-SEQ DELIMITED BY SIZE INTO TEMP-MSG
    DISPLAY TEMP-MSG
   END-IF
END-PERFORM.

Fetch name and SEQ till we hit SQLCODE 100 and display the data.

Update Table

We need to mention FOR UPDATE OF and the field name in declare statement.

EXEC SQL
    DECLARE CURUPDT1 CURSOR FOR
        SELECT NAME,SEQ FROM IBMGRP.MYNAM
        WHERE SEQ = :WS-SEQ
        FOR UPDATE OF NAME
END-EXEC.
here I will be updating NAME field of MYNAM table
EXEC SQL
   OPEN CURUPDT1
END-EXEC.
MOVE '002'               TO WS-SEQ.
EXEC SQL
   FETCH CURUPDT1 INTO :WS-NAME,:WS-SEQ-TMP
END-EXEC
EXEC SQL
   UPDATE IBMGRP.MYNAM
   SET NAME = :WS-NAME
   WHERE CURRENT OF CURUPDT1
END-EXEC.
"CURRENT OF CURUPDT1" statement will pick the current row to update.
EXEC SQL
   CLOSE CURUPDT1
END-EXEC.

Delete Record

Like cursor for updating a record we need to mention FOR UPDATE OF in cursor declaration statement.

EXEC SQL
    DECLARE CURDELT1 CURSOR FOR
        SELECT NAME,SEQ FROM IBMGRP.MYNAM
        WHERE SEQ = :WS-SEQ
        FOR UPDATE OF NAME
END-EXEC.
EXEC SQL
   OPEN CURDELT1
END-EXEC.
EXEC SQL
   FETCH CURDELT1 INTO :WS-NAME,:WS-SEQ-TMP
END-EXEC
EXEC SQL
   DELETE FROM IBMGRP.MYNAM
   WHERE SEQ = :WS-SEQ
END-EXEC.
EXEC SQL
   CLOSE CURDELT1
END-EXEC.

Sample DB2-COBOL-CURSOR Code
Please refer http://mainframegeek.wordpress.com/2011/05/12/steps-in-a-cobol-db2-program to get JCL for compiling and executing DB2-COBOL-CURSOR program

----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
       IDENTIFICATION DIVISION.
       PROGRAM-ID. DBPGM01.
       AUTHOR    . SHIBU.T.
      *
       DATA DIVISION.
       WORKING-STORAGE SECTION.
           EXEC SQL
               INCLUDE MYNAM
           END-EXEC.
           EXEC SQL
               INCLUDE SQLCA
           END-EXEC.
       01 WS-TEMP-VAR.
           05 TEMP                    PIC X(30).
           05 TEMP-MSG                PIC X(60).
       01 WS-TBLE-DTA.
           05 WS-SEQ                  PIC X(3).
           05 WS-SEQ-TMP              PIC X(3).
           05 WS-NAME                 PIC X(15).
       01 WS-SQLCODE                  PIC ------9.
      *
       PROCEDURE DIVISION.
           PERFORM A00100-READ-PARA.
           PERFORM A00200-UPDATE-PARA.
           PERFORM A00100-READ-PARA.
           PERFORM A00400-INSERT-PARA.
           PERFORM A00100-READ-PARA.
           PERFORM A00300-DELETE-PARA.
           PERFORM A00100-READ-PARA.
           STOP RUN.
       A00100-READ-PARA.
           DISPLAY '         A00100-READ-PARA.           '
           EXEC SQL
               DECLARE CURREAD1 CURSOR FOR
                   SELECT NAME,SEQ FROM IBMGRP.MYNAM
           END-EXEC.
           EXEC SQL
              OPEN CURREAD1
           END-EXEC.
           MOVE SQLCODE             TO WS-SQLCODE.
           DISPLAY 'SQLCODE OPEN ' WS-SQLCODE.
           DISPLAY '****DATA FROM TABLE***'
           PERFORM UNTIL SQLCODE = 100
              EXEC SQL
                 FETCH CURREAD1 INTO :WS-NAME,:WS-SEQ
              END-EXEC
              MOVE SQLCODE             TO WS-SQLCODE
              DISPLAY 'SQLCODE FETCH ' WS-SQLCODE
              IF SQLCODE = 0 THEN
               MOVE SPACES TO TEMP-MSG
               STRING
               'NAME: ' DELIMITED BY SPACE ' ' DELIMITED BY SIZE
               WS-NAME DELIMITED BY SPACE ',' DELIMITED BY SIZE
               'SEQ#' DELIMITED BY SPACE ' ' DELIMITED BY SIZE
               WS-SEQ DELIMITED BY SIZE INTO TEMP-MSG
               DISPLAY TEMP-MSG
              END-IF
           END-PERFORM.
           DISPLAY '****END OF TABLE DATA****'
           EXEC SQL
              CLOSE CURREAD1
           END-EXEC.
           MOVE SQLCODE             TO WS-SQLCODE.
           DISPLAY 'SQLCODE CLOSE ' WS-SQLCODE.
      *
       A00200-UPDATE-PARA.
           DISPLAY '         A00200-UPDATE-PARA.         '
           EXEC SQL
               DECLARE CURUPDT1 CURSOR FOR
                   SELECT NAME,SEQ FROM IBMGRP.MYNAM
                   WHERE SEQ = :WS-SEQ
                   FOR UPDATE OF NAME
           END-EXEC.
           EXEC SQL
              OPEN CURUPDT1
           END-EXEC.
           MOVE SQLCODE             TO WS-SQLCODE.
           DISPLAY 'SQLCODE OPEN ' WS-SQLCODE.
           MOVE '002'               TO WS-SEQ.
           EXEC SQL
              FETCH CURUPDT1 INTO :WS-NAME,:WS-SEQ-TMP
           END-EXEC
           MOVE SQLCODE             TO WS-SQLCODE.
           DISPLAY 'SQLCODE FETCH ' WS-SQLCODE.
           MOVE 'SHYAM-KUMAR'       TO WS-NAME.
           EXEC SQL
              UPDATE IBMGRP.MYNAM
              SET NAME = :WS-NAME
              WHERE SEQ = :WS-SEQ
           END-EXEC.
           MOVE SQLCODE             TO WS-SQLCODE.
           DISPLAY 'SQLCODE UPDT ' WS-SQLCODE.
           EXEC SQL
              CLOSE CURUPDT1
           END-EXEC.
           MOVE SQLCODE             TO WS-SQLCODE.
           DISPLAY 'SQLCODE CLOSE ' WS-SQLCODE.
           EXIT.
      *
       A00400-INSERT-PARA.
           DISPLAY '         A00400-INSERT-PARA.         '
           MOVE 'TEMP-NAME'         TO WS-NAME.
           MOVE 007                 TO WS-SEQ.
           EXEC SQL
               INSERT INTO IBMGRP.MYNAM
               VALUES ( :WS-SEQ,:WS-NAME)
           END-EXEC.
           MOVE SQLCODE             TO WS-SQLCODE.
           DISPLAY 'SQLCODE INSRT ' WS-SQLCODE.
           EXIT.
      *
       A00300-DELETE-PARA.
           DISPLAY '         A00300-DELETE-PARA.         '
           EXEC SQL
               DECLARE CURDELT1 CURSOR FOR
                   SELECT NAME,SEQ FROM IBMGRP.MYNAM
                   WHERE SEQ = :WS-SEQ
                   FOR UPDATE OF NAME
           END-EXEC.
           EXEC SQL
              OPEN CURDELT1
           END-EXEC.
           MOVE SQLCODE             TO WS-SQLCODE.
           DISPLAY 'SQLCODE OPEN ' WS-SQLCODE.
           MOVE '007'               TO WS-SEQ.
           EXEC SQL
              FETCH CURDELT1 INTO :WS-NAME,:WS-SEQ-TMP
           END-EXEC
           MOVE SQLCODE             TO WS-SQLCODE.
           DISPLAY 'SQLCODE FETCH ' WS-SQLCODE.
           EXEC SQL
              DELETE FROM IBMGRP.MYNAM
              WHERE SEQ = :WS-SEQ
           END-EXEC.
           MOVE SQLCODE             TO WS-SQLCODE.
           DISPLAY 'SQLCODE DELT ' WS-SQLCODE.
           EXEC SQL
              CLOSE CURDELT1
           END-EXEC.
           MOVE SQLCODE             TO WS-SQLCODE.
           DISPLAY 'SQLCODE CLOSE ' WS-SQLCODE.
           EXIT.

Screen Shots

Please tryout the code yourself and let me know if you have any concerns.

Pages

Thursday, August 18, 2011

Submitting Batch JOB( JCL) from CICS Online program.

1. SPOOLOPEN OUTPUT

2. SPOOLWRITE

3. SPOOLCLOSE

Common Abends/Errors.

1. ALLOCERROR

2. INVREQ

3. SPOLBUSY

4. LENGERR

5. NOTOPEN

6 NOSPOOL

Wednesday, August 17, 2011

CURSORS in DB2

1. Declare.

2. Open.

3. Fetch.

4. Close

Few Snippets.

Read table.

Update Table

Delete Record