Mainframe Tutorial
JCL Tutorial
Introduction
Mainframe Interview Q&A
Mainframe Quiz
Mainframe Memes
Contact us
About usModule 14:- Database Interface
Backward
DB2 Cursors
- Cursor is used when more than one row of table is to be processed.
- Cursor allows COBOL program to retrieve the set of rows (result set) and then process that returned data one row at a time.
- Cursor can be thought of as data structure that holds all the resultant records of query.
- By using cursor, program can retrieve each row sequentially from the result table until End of Data is reached (SQLCODE =100)
- Cursor handling involves below four operations: -
-
Step 1: - DECLARE
Step 2: - OPEN
Step 3: - FETCH
Step 4: - CLOSE
- Step 1: DECLARE
- A cursor is created using the DECLARE statement which defines the name of the cursor and specifies its associated query
- This declaration can be done in WORKING-STORAGE SECTION or PROCEDURE DIVISION
- It is non-executable statement
- Example:-
EXEC SQL DECLARE EMP-CUR CURSOR FOR SELECT EMP_ID, EMP_NAME WHERE EMP_ID > :WS-EMPL-ID END-EXEC.
- Step 2: OPEN
- This operation runs the query specified in Declare cursor statement, builds the result set and prepares the cursor for retrieval of first row.
- This operation can be done in PROCEDURE DIVISION.
- Before cursor is FETCHed, it must be OPENed.
- Example:-
EXEC SQL OPEN EMP-CUR END-EXEC.
- Step 3: FETCH
- Retrieves one row of the result set and assigns the values of that row to host variables
- Fetches are usually executed repeatedly until all rows of the result set have been retrieved.
- This operation can be done in PROCEDURE DIVISION.
- Example:-
EXEC SQL FETCH EMP-CUR INTO :WS-EMPL-ID, :WS-EMPL-NAME END-EXEC.
- Step 4: CLOSE
- Terminates cursor and releases any resources it is using.
- Cursor can be opened again, when needed.
- If cursor is not closed, COMMIT statement closes the cursor. To keep cursor opened even after COMMIT, specify ‘WITH HOLD’ option in DECLARE statement. This works only in Batch environment
- ROLLBACK operation closes all the cursors including cursors specified with ‘WITH HOLD’ option
- This operation can be done in PROCEDURE DIVISION.
- Example:-
EXEC SQL CLOSE EMP-CUR END-EXEC.
Example of using CURSOR:-
Let’s assume there is one table EMP containing below records:-
| EMP_ID | EMP_NAME |
|---|---|
| 10000 | ANKIT |
| 20000 | RAMESH |
| 30000 | BHAVIN |
| 40000 | DEVEN |
COBOL program to fetch and display all employee records whose EMP_ID is greater than ‘20000’:-
IDENTIFICATION DIVISION.
PROGRAM-ID. SQLCURSR.
DATA DIVISION.
WORKING-STORAGE SECTION.
01 WS-EMPL.
05 WS-EMPL-ID PIC X(05).
05 WS-EMPL-NAME PIC X(15).
******** INCLUDING SQLCA COPYBOOK **************
EXEC SQL
INCLUDE SQLCA
END-EXEC.
******** INCLUDING DCLGEN OF EMP TABLE*********
EXEC SQL
INCLUDE DCLEMP
END-EXEC.
********** DECLARING CURSOR EMP-CUR ***********
EXEC SQL
DECLARE EMP-CUR CURSOR FOR
SELECT EMP_ID, EMP_NAME
WHERE EMP_ID > :WS-EMPL-ID
END-EXEC.
PROCEDURE DIVISION.
MAIN-PARA.
MOVE '20000' TO WS-EMPL-ID
EXEC SQL
OPEN EMP-CUR
END-EXEC.
EVALUATE SQLCODE
WHEN ZERO
CONTINUE
WHEN OTHER
DISPLAY 'ERROR OPENING CURSOR EMP-CUR'
END-EVALUATE.
PERFORM UNTIL SQLCODE = 100
EXEC SQL
FETCH EMP-CUR
INTO :WS-EMPL-ID, :WS-EMPL-NAME
END-EXEC
EVALUATE SQLCODE
WHEN ZERO
DISPLAY WS-EMPL
WHEN 100
DISPLAY 'NO MORE ROWS IN CURSOR RESULT SET'
WHEN OTHER
DISPLAY 'ERROR FETCHING CURSOR EMP-CUR'
END-EVALUATE
END-PERFORM.
EXEC SQL
CLOSE EMP-CUR
END-EXEC.
EVALUATE SQLCODE
WHEN ZERO
CONTINUE
WHEN OTHER
DISPLAY 'ERROR CLOSING CURSOR EMP-CUR'
END-EVALUATE.
STOP RUN.
Mainframe Job Step to execute above COBOL-DB2 program:-
//STEP01 EXEC PGM=SQLCURSR
//STEPLIB DD DSN=USER.TEST.LOADLIB,DISP=SHR
//SYSOUT DD SYSOUT=*
//SYSIN DD *
DSN SYSTEM(SSID)
RUN PROGRAM(SQLCURSR) PLAN(SQLCURSR)
END
/*
SYSOUT Display after successful execution above job: -
30000BHAVIN
40000DEVEN
NO MORE ROWS IN CURSOR RESULT SET