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
******* NOTE: There is no DYNAMIC option for INSENSITIVE CURSOR. *********