Static cursors and Dynamic cursors in DB2
Coming to the cursor types, before DB2 v7, we could only move/scroll through the cursor in forward position.So when the cursor is opened, the pointer would be positioned before the first row in the result set. Once the FETCH has been issued, the pointer would move one row forward.
NO SCROLL is the default and indicates that the cursors are not scrollable.This is the normal cursor which commonly gets used in application program.
DB2 V7 introduced STATIC SCROLLABLE CURSOR which can be scrolled backwards or
forwards, and to an absolute position or to a relative position.When the CURSOR is opened, the qualifying rows would be placed in a declared temporary table automatically created by DB2.Scrolling is performed on the temporary table in both the forward direction and backward direction. DB2 deletes the result table when the cursor is closed.
With the introduction of DB2 v8, the concept of DYNAMIC SCROLLABLE CURSOR has been introduced. Dynamic cursors DO NOT need any temporary table.This is one of the main difference between static and dynamic cursors. They SCROLL on the main table itself, and therefore are sensitive to all the committed inserts, updates, and deletes. Thus the overhead in building the temporary table(as in static cursor) and maintaining the synchronization between the base table and result table is not there in case of dynamic cursor.
Declare cursor statements:
NO SCROLL is the default and indicates that the cursors are not scrollable. If you specify
SCROLL, the cursors become scrollable.
For Dynamic CURSORS we can have the below attributes:
1. SENSITIVE DYNAMIC SCROLLABLE
2. ASENSITIVE SCROLLABLE
NO SCROLL is the default and indicates that the cursors are not scrollable.This is the normal cursor which commonly gets used in application program.
DB2 V7 introduced STATIC SCROLLABLE CURSOR which can be scrolled backwards or
forwards, and to an absolute position or to a relative position.When the CURSOR is opened, the qualifying rows would be placed in a declared temporary table automatically created by DB2.Scrolling is performed on the temporary table in both the forward direction and backward direction. DB2 deletes the result table when the cursor is closed.
With the introduction of DB2 v8, the concept of DYNAMIC SCROLLABLE CURSOR has been introduced. Dynamic cursors DO NOT need any temporary table.This is one of the main difference between static and dynamic cursors. They SCROLL on the main table itself, and therefore are sensitive to all the committed inserts, updates, and deletes. Thus the overhead in building the temporary table(as in static cursor) and maintaining the synchronization between the base table and result table is not there in case of dynamic cursor.
Declare cursor statements:
 |
| DECLARE CURSOR statements for static and dynamic cursors |
NO SCROLL is the default and indicates that the cursors are not scrollable. If you specify
SCROLL, the cursors become scrollable.
For Dynamic CURSORS we can have the below attributes:
1. SENSITIVE DYNAMIC SCROLLABLE
2. ASENSITIVE SCROLLABLE
No comments:
Post a Comment