Cusror in DB2 ---> Part I

==> Click Here to Read Part II <==

When we write a Sql query in an application program and if it returns more than one row (Qualifying rows are more than 1) then DB2 cannot handle it and throws the SQLCODE -811.

What if, i have to select/update/delete more than one row from the table via application program??--> DB2 cursors is all what you need.

Cursor will process all the qualifying rows one by one sequentially.

To have cursor in your program , you need to follow below four steps.

 1. Declare (Define) cursor

 2. Open Cursor

 3. Fetch Cursor

 4. Close Cursor

1. Declare(Define) Cursor:

You declare it in Working storage section with below syntax

Declare cursor for < Your SQL Query >

You can also define your cursor in Procedure division,only condition is,it must be declare before Open cursor statement.

If you want to Update/delete the qualifying rows from the cursor then you have add the "FOR UPDATE OF" clause at the end of query in Declare cursor statement.

Declare cursor for< Your SQL Query >

For Update of /For UPDATE (If not sure about the column being updated)

For update of clause will take the lock on the rows being updated so that no one can change it before your update it.If you don't add "for update of" but update the rows ...lock will not be taken and there is danger of rows being updated by other process between your select and update statement and hence result can be unpredicted.

Declare(Define) Cursor is non executable statement.

2.Open Cursor :

This is an executable statement and executes the query from declare statement.Qualifying rows will be stored in buffer and pointer will be set just before the first qualified rows.If there are no qualifying rows( The predicate in your query yields no rows) pointer will be set after last row.

3.Fetch Cursor :

Fetch will first move the pointer to next row and then fetch that row from buffer until end of rows.Once the end of row happens SQLCODE +100 will be returned.

4.Close Cursor:

This will close the cursor which you have open.

Points to remember :

1. If you want to handle more than one row in your application program, use cursor

2. If multiple rows are returned by query and you didn't use cursor ,then first row will be fetched from the table and db2 will throw SQLCODE -811.

3. If you want only one row(any) from multiple qualifying rows from your query, you can avoid using cursor in this  case. Use fetch first 1 row only (Depend on your DB2 version)

4. Cursor can either be defined in working storage section or procedure division. The condition for declaring it in procedure division is it should be defined prior to open cursor statement.

5. DB2 can process a cursor in two different ways:

a> It can create the result table(all qualifying rows) during the execution of the OPEN statement.

b> It can derive the result table rows as they are needed during the execution of later FETCH statements.If the result table is not read-only, DB2 uses the latter method. If the result table is read-only, either method could be used.

6. All the Cursors used in program will be automatically closed at the end of program or at COMMIT AND ROLLBACK statement (if used in the program).

If you don't want to close the cursor at COMMIT OR ROLLBACK. Define CURSOR WITH HOLD ,it will not allow the cursor to be closed and keep the position of pointer.

In next part we will discuss the different types of cursor and there use.

==> Click Here to Read Part II <==