How does cursor work in db2

You can let DB2 automatically close the cursor when the application program terminates but this practice is not recommended. Only one row is updated, the current row.

Only one row is deleted, the current row. Remember Me. Create a new account. Remember Me Forgot Password? Link Text. Open link in a new tab. No search term specified. Showing recent items. Once it has been issued, the SQl statement gets executed and a result set will be built and all the rows retrieved will be put in the result set. The cursor will be pointed to the first row in the result set, so it is ready to process the rows.

Fetch statement will fetches one set of values at a time in to the host variables, and this can be kept in loop to process all the rows until no more rows in the result set. Your email address will not be published. Save my name, email, and website in this browser for the next time I comment.

Skip to content. Db2-Cursor: Cursors in the application program are used to retrieve the multiple rows and process them one by one. Contrast this with a "normal" cursor, in which data can only be accessed one row at a time in a forward direction using the FETCH statement.

A scrollable cursor makes navigating through SQL result sets much easier. More details on cursor sensitivity are provided later in this section. Scrollable cursors allow developers to move through the results of a query in multiple ways. The FETCH statement is still used, but it is combined with keywords to move the fetching in a specific direction.

The following keywords are supported when fetching data from a scrollable cursor:. It can be either a positive or a negative number, and it can be represented as a numeric constant or as a host variable. For example, consider the following cursor logic:. Issuing these SQL statements will declare a scrollable cursor named csr1 , open that cursor, and then FETCH the last row from the cursor's results set.

Scrollable cursors reduce the amount of time and effort required to move backward and forward through the results of SQL queries. Update and delete holes pose a coding challenge for programmers.

Such a cursor is helpful for distributed applications where you do not know for sure whether the server supports sensitivity or scrollability. Declared temporary tables are discussed in-depth in Chapter 5, "Data Definition Guidelines. When a static scrollable cursor is opened, qualifying rows are copied to a declared temporary table that is created automatically by DB2.

DB2 drops the result table when the cursor is closed. So, before you begin to use scrollable cursors in V7, be sure to define a temporary database. Be careful when using scrollable cursors with DB2 V7. Every new scrollable cursor creates a physical table in the temporary database.

A dynamic scrollable cursor does not use a temporary table to store the results set. Instead, it simply accesses the data right out of the base table. Dynamic scrolling is possible because DB2 V8 allows us to backward scan through an index for the first time. This allows DB2 to move backward and forward through the scrollable cursor using an index.

If a result table has to be materialized at the time cursor is opened, dynamic scrolling is not possible. The following guidelines are offered to help you implement effective scrollable cursors in your DB2 application programs. As helpful as scrollable cursors are, do not make every cursor a scrollable cursor. Scrollable cursors require substantially more overhead than a traditional, non-scrollable cursor. Analyze the requirements of your applications and deploy scrollable cursors only where it makes sense to do so.

Pseudo-conversational CICS programs cannot use scrollable cursors. Do not attempt to code a scrollable cursor in a pseudo-conversational CICS program. If you need to see the changes made to data by concurrent processes, be sure to use a dynamic scrollable cursor. This is the only type of scrollable cursor that has visibility to underlying changes.

The resources required and the flexibility of processing are greatly affected by the proper sensitivity and dynamism settings for your scrollable cursors.

Consult Table The visibility column refers to whether the cursor has visibility to changes made to the data; the updatability column refers to whether data can be updated using the cursor. Be sure to choose the appropriate cursor for the type of processing you require. Consider the following precautions as you decide on your cursor type:. If you do not need to move forward and backward through your results set, do not use a scrollable cursor.