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.
No comments:
Post a Comment