DB2 SQL CURSOR

The result of a SELECT Statement which returns more than one row, is easy to view on an SQL interface like STRSQL. But How can we handle this result set pragmatically in Embedded SQL or SQL/PL code?

The answer is SQL CURSOR.

CURSOR provides a simple way to access Result Set returned by SELECT Statement. You can think of CURSOR as a pointer to one row in a ResultSet containing multiple rows. So at a time cursor can reference only one row, but It can move to other rows of ResultSet as required.

To use CURSOR, there are 4 control statements

1. DECLARE

DECLARE Statement defines a CURSOR for a given, select statement. There is no limit on the number of CURSORs you can define in one program, but the name of each CURSOR must be unique within the program scope.

The DECLARE CURSOR statement basic syntax

DECLARE cursor-name CURSOR FOR select-statement

The DECLARE CURSOR statement complete syntax

2. OPEN

When you OPEN the CURSOR, the System actually runs CURSOR’s SELECT statement to build the result set.

OPEN cursor-name

3. FETCH

FETCH statement gets data from CURSOR and populate give host variables(or SQL variable in SQL/PL) and move CURSOR to the next record of the result set. The behavior of FETCH is a little different for SERIAL and SCROLL-ABLE CURSORS(We will discuss these in more detail in their respective sections)

At the most, basic level FETCH looks like this.

FETCH FROM cursor-name INTO :host-variable1,:host-variable2

4. CLOSE

The CLOSE statement closes the CURSOR and frees the resources being used by the CURSOR. After CLOSE, you can not use the FETCH statement, but you can OPEN the CURSOR again, and after that, you can use FETCH.

CLOSE cursor-name

DB2 SQL supports 2 types of CURSORS

1. SERIAL CURSOR
2. SCROLL-ABLE CURSOR

1. SERIAL CURSOR

1. A serial cursor is one defined without the SCROLL keyword.
2. A serial cursor can only move in a forwarding direction.
3. For a serial cursor, each row of the result table can be fetched only once per OPEN of the cursor.
4. To use a serial cursor again, you must first close the cursor and then re-issue the OPEN statement.
5. When the cursor is opened, it is positioned before the first row in the result table.
6. On the first FETCH, the first record of the result table will be read, and the cursor will be positioned between the 1st and 2nd records of the result table.

Example

Let's say we have a table CUSTOMER with the following data.

Now we want to fetch only customers from INDIA.

a. DECLARE CURSOR

DECLARE @Customer CURSOR FOR
(SELECT * FROM CUSTOMER Where CUS_COUNTRY = 'INDIA')

This will define a cursor with the name @Customer. The absence of the SCROLL keyword makes it a SERIAL CURSOR.

b. OPEN CURSOR

OPEN @Customer

The OPEN statement will execute CURSOR’s SELECT-statement “SELECT * FROM CUSTOMER Where CUS_COUNTRY = ‘INDIA’” and create a result table. like this

Now cursor @Customer will act as a pointer to this result table which is pointed before the first record of the result table.

c. FETCH CURSOR

Now you are ready to read the result table using CURSOR.

FETCH @Customer 
into :CustomerId,:CustomerName,:CustomerCountry

This code will read all three columns of 1st row from the result table and populate corresponding host variables(OR SQL variable in case of SQL/PL).

After this FETCH, CURSOR will move between 1st and 2nd row(After 1st row and before 2nd row). To read 2nd row, FETCH again means one FETCH statement for each row to read. Because of this FETCH statement is used inside some loop statement.

After no more record is available to read, FECTH will return SQLCODE = 100.

Here is an example of FETCH inside RPG DoW loop(Do-while)

Exec SQL FETCH @Customer 
        into :CustomerId,:CustomerName,:CustomerCountry;
   DoW SQLCODE = 0;
     // some code to process date read from Cursor. 
     Exec SQL FETCH @Customer 
          into :CustomerId,:CustomerName,:CustomerCountry; 
   EndDo;

d. CLOSE CURSOR

After FETCH is complete, CURSOR should release all resources it uses, including memory for the result table. To do this, CURSOR should be closed using the CLOSE statement.

CLOSE @Customer

To read the CURSOR after closing, the CURSOR should be reopened. After reopen CURSOR will point before the 1st record again.

2. SCROLLABLE CURSOR

1. A scrollable cursor is one defined with the SCROLL keyword.
2. A scrollable cursor can move in both forward and backward directions.
3. For a scrollable cursor, each row of the result table can be fetched multiple times per OPEN of the cursor.
4. When the cursor is opened, it is positioned before the first row in the result table.

We are using the same CUSTOMER table as used in the Serial Cursor section above.

Now we want to fetch only customers from INDIA.

a. DECLARE CURSOR

DECLARE @Customer SCROLL CURSOR FOR
(SELECT * FROM CUSTOMER Where CUS_COUNTRY = 'INDIA')

This will define a cursor with the name @Customer. SCROLL keyword makes it a SCROLLABLE CURSOR.

b. OPEN CURSOR

OPEN @Customer

The OPEN statement will execute CURSOR’s SELECT-statement “SELECT * FROM CUSTOMER Where CUS_COUNTRY = ‘INDIA’” and create a result table. like this

Now cursor @Customer will act as a pointer to this result table which is pointed before the first record of the result table.

c. FETCH CURSOR

FETCH statement for SCROLLABLE CURSOR has one extra parameter than SERIAL CURSOR to define direction options for CURSOR.

FETCH [direction-option] From cursor-name into :host-var1,:host-var2

direction-option details

Read the LAST record of the result table.

FETCH LAST From @Customer 
into :CustomerId,:CustomerName,:CustomerCountry

Read 2nd record of the result table

FETCH ABSOLUTE 2 From @Customer 
into :CustomerId,:CustomerName,:CustomerCountry

Read the previous record of the result table

FETCH PRIOR From @Customer 
into :CustomerId,:CustomerName,:CustomerCountry

d. CLOSE CURSOR

After FETCH is complete, CURSOR should release all resources it uses, including memory for the result table. To do this, CURSOR should be closed using the CLOSE statement.

CLOSE @Customer

To read the CURSOR after closing, the CURSOR should be reopened. After reopen CURSOR will point before the 1st record again.

CURSOR and COMMITMENT CONTROL

1. When you use COMMIT or ROLLBACK statements with CURSOR, the behavior of the CURSOR depends on whether or not it is declared using the WITH HOLD clause
2. If the CURSOR is declared using the WITHOUT HOLD clause, all of its resources (cursor, locks, and large-object datatype, or LOB, locators) are released upon either COMMIT or ROLLBACK. Therefore, if you need to use the cursor after completing a transaction, you will have to re-open the cursor and traverse it again from the first row.
3. Defining a cursor using WITH HOLD will cause the cursor to maintain its position and some locks across transactions.

For cursors defined WITH HOLD after COMMIT:

1. The cursor will remain open.
2. The cursor will be positioned before the next logical row.
3. The only permitted operations on cursors immediately after the COMMIT statement is FETCH and CLOSE.
4. Positioned delete and positioned update are valid only for rows that are fetched within the same unit of work.
5. All LOB locators will be released.
6. All locks are released, except locks protecting the current cursor position of open WITH HOLD cursors. The locks held include the locks on the table and, for parallel environments, the locks on the rows where the cursors are currently positioned. Locks on packages and dynamic SQL sections (if any) are held.
7. The set of rows modified by

• A data change statement
• Routines that modify SQL data embedded within open WITH HOLD cursors are committed

For cursors defined WITH HOLD after ROLLBACK

1. All open cursors will be closed.
2. All locks acquired during the UOW will be released.
3. All LOB locators are freed.