During the DB2 read in the COBOL program, If WHERE condition retrieves more than one row then the programmer needs to handle the scenario by validating SQLCODE.
To do this, Programmer need to include SQLCA to retrieve the SQLCODE from the executed query.
If the query returns more than one row, then it will return -811 as SQLCODE.
In most of the cases it will return the first retrieved row data and throw the error -811.
Programmer can’t able to retrieve the further rows which satisfies the same condition.
If the programmer requirement is to retrieve the first row even though the WHERE condition coded retrieving more than one row then handling the SQLCODE is sufficient.
But if the programmer needs to handle the multiple rows one by one, then there is only one solution. i.e.CURSOR.
CURSOR is used to process set of rows one by one from table(s).
CURSOR used to retrieve and process one row from a set of rows retrieved by the application program.
It will process the rows one by one sequentially after retrieved. It’s like the sequential access of the file.
The rows can be retrieved based on the search condition provided.
The Cursor life cycle is like below.
- Declare Cursor
- Open Cursor
- Fetch Cursor
- Close Cursor
DECLARE CURSOR is used to declare a cursor in the application program.
SELECT statement must be used within the DECLARE CURSOR.
SELECT statement should not use INTO clause.
The cursor name provided with DECLARE CURSOR will act as a result table.
DECLARE CURSOR will not create any result table by declaring it.
DECLARE CURSOR only identifies the set of rows to retrieve with SELECT during the execution.
DECLARE CURSOR can be coded in DATA DIVISION and PROCEDURE DIVISION.
The SQL SELECT statement that required in retrieving the data from table(s) will be declared in DECLARE CURSOR.
DECLARE CURSOR may have GROUP BY, ORDER BY in it.
EXEC SQL DECLARE cursor-name [NO SCROLL/SCROLL] [DYNAMIC/STATIC] CURSOR [WITH/WITHOUT HOLD] FOR select-statement FOR UPDATE OF columns-list/ FOR FETCH ONLY] END-EXEC.
Let’s discuss the parameter one by one.
Cursor-name is the name of the cursor which is used in application program to refer the cursor.
The length of the cursor name is 30 characters for the cursors declared WITH RETURN.
The length of the cursor-name is 128 characters for the normal cursors.
Specifies the cursor is scrollable or non scrollable.
NO SCROLL specifies the cursor is non scrollable.
NO SCROLL is default.
SCROLL specifies the cursor is scrollable.
Specifies the cursor should be closed or not during the commit operation performed.
WITHOUT HOLD specifies the cursor can be closed if any commit operation is performed before CLOSE CURSOR.
WITHOUT HOLD is the default option.
WITH HOLD specifies that the cursor should not be closed even though commit operation performed before CLOSE CURSOR.
If WITH HOLD option specifies, COMMIT only commits the current unit of work.
If no option specified or WITHOUT HOLD option specifies, COMMIT closes the CURSOR along with commit of current work.
FOR UPDATE OF:
FOR UPDATE OF used to declare the updatable cursors.
Positioned UPDATE and positioned DELETE can be allowed in updatable cursors.
WHERE CURRENT OF used to update or delete the rows in updatable cursors.
FOR FETCH ONLY:
Specifies when declaring READ ONLY Cursors.
Positioned UPDATE and positioned DELETE not allowed in read only cursors.
The Opening of the declared cursor will retrieve the data from table and make it ready for processing.
If the GROUP BY, ORDER BY was coded in declare cursor, then the temporary result table will be built to process it.
On opening of CURSOR, DB2 system will perform 2 tasks.
1. Uses the SELECT statement in DECLARE CURSOR to identify the set of rows.
2. Stores the data in a temporary location and make it ready for processing.
If any host variables used in the DECLARE CUSOR, the host variables should have the value before OPEN CURSOR performed.
Otherwise DB2 will use the current value of the host variables which may be LOW-VALUES also.
EXEC SQL OPEN Cursor-name USING host-variables END-EXEC.
FETCH CURSOR retrieved the data to host variables in the order how it got retrieved from tables based on the conditions coded in declaration of cursor and fetch it one by one.
It fetches the one row at a time.
To fetch data from table, the host variables need to be coded in the fetch cursor.
FETCH CURSOR always fetches only one row and makes it as a current row.
When next FETCH executed, the next row will be fetched and makes that as a current one.
Updatable cursors will use the concept of current row.
FETCH will move the fetched data to the host variables coded along with INTO.
The order of host variables should be the same as the columns or attributes declared with DECLARE CURSOR.
FETCH statement retrieves the rows from temporary result table.
EXEC SQL FETCH Cursor-name INTO :Host-variable-col1, :Host-variable-col2, . . :Host-variable-coln END-EXEC.
While fetching the data, there might be chance of the data not available in source table.
The column contains the value which is other than actual data called as NULL.
If any Null valued column existed in the retrieved columns list, the null indicator should be coded along with host variable.
If the NULL indicator is not coded with FETCH and column returns NULL value then the FETCH will fail with -305.
The NULL variables can be declared always with –NI at end of field name.
The declaration of NULL indicator is S9(04) COMP.
The NULL indicator handling in FETCH statement syntax was shown below.
EXEC SQL FETCH Cursor-name INTO :Host-variable-col1 :Host-variable-col1-NI, :Host-variable-col2 :Host-variable-col2-NI, . . :Host-variable-coln END-EXEC.
The NULL indicator will be validated like below.
If NULL indicator value = -1 (NULL existed in the retrieved column) = 0 (Retrieved column has the proper value) = 2 (Truncated value retrieved)
CLOSE CURSOR closes the cursor specified with it.
CLOSE CURSOR releases all the resources used by the cursor.
EXEC SQL CLOSE cursor-name END-EXEC.Note:
If any COMMIT performed in the middle of cursor processing, the cursor will be closed automatically.
If the control returns from the executing program, all the cursors will be closed automatically.
If the Cursor declared WITH HOLD option, then ROLLBACK command needs to be coded at the end of the program. That closes all WITH HOLD cursors coded in the program.
If any questions on the above, post your queries on DB2 Discussion forum
DB2 SQLCA DB2 CURSOR TYPES