SQL Cursor DB2

SQL Cursor Processing with DB2

Q: How can I retrieve data one row at a time with DB2?
To retrieve data with SQL one row at a time you need to use cursor processing.
Not all relational databases support this, but many do. Here I show this with DB2 and embedded SQL, which is SQL that is "embedded" in a program like COBOL, C, or Java.
Cursor processing is done in several steps:
1. Define the rows you want to retrieve. This is called declaring the cursor.
2. Open the cursor. This activates the cursor and loads the data. Note that defining the cursor doesn't load data, opening the cursor does.
3. Fetch the data into host variables.
4. Close the cursor.

Step 1 - declare the cursor

 Declare CursorJamesCameron Cursor for
 Select Film_Title
   From Director_Film_Table,
  Where Director_Last_Name equals "Cameron"
    and Director_First_Name equals "James"
  Order By Film_Title;
Note: That this example fetches only one column - Film_Title - but multiple columns can be fetched simultaneously.

Step 2 - open the cursor

 Open CursorJamesCameron;
Note: Not much seems to happen in step 2. Quite a bit really does happen behind the scenes, as cursor CursorJamesCameron is loaded with data in step 2.

Step 3 - fetch the data into a host variable

 Fetch CursorJamesCameron 
  Into :CameronMovieName;
Result after the first fetch
Host variable :CameronMovieName will equal "Aliens"
The fetch will take the current sequential row and put it into the host variable. It will then set the next sequential row to the current sequential row.
The host variable must be able to accommodate the data that the cursor has defined. If more than one column is fetched each column must have a correlating host variable.
You can repeat doing the fetch until you finish reading each row. When all rows are read the host variable will be set to null or spaces. Usually a special host variable is set by the dbms to indicate that the cursor is empty. In DB2 the SQLCODE is set to +100.

Step 4 - close the cursor

 Close CursorJamesCameron;
Comments Comments are left by visitors to FluffyCat.com and may or may not be accurate.
Comment by Anonymous on 2011-02-09 Rate this Comment

Not sure what 'CR' is - did you perhaps mean 'CS'? If so, these would correspond to 'Cursor Stability' and 'Uncommitted Read', respectively. These define what happens when you've opened a cursor (thus loading your table with data) and, while the cursor is live, another process then updates the source table your cursor was defined against.

Comment by archive on 2011-04-13 Rate this Comment

I am working in Mainframe Technplogy. In Db2 i got one doubt.

Doubt is: What is CR and UR related to Cursor in Db2?

Here i searched in your site. I didn't get. That'swhy
i am mailing. Pl. clarify my doubt.

Comment by Larry on 2011-01-18 Rate this Comment

Yes, the syntax for an Oracle cursor, whcih can be seen here is quite different. Same basic concept, though.

Comment by krishna_sri98 on 2013-05-22 Rate this Comment

Its different in syntax compared to Oracle

Sign in to comment on SQL Cursor DB2.