What is the Difference Between Scrollable and Non Scrollable Cursors in DB2

The main difference between scrollable and non scrollable cursors in DB2 is that scrollable cursors are used to move randomly through the result set while non-scrollable cursors are used to move sequentially forward through the result set.

DB2 is a Relational Database Management System developed by IBM. It is a high-performance database that can handle a large amount of data. Multiple users can access the data simultaneously using DB2. Cursor is a concept in DB2. It is a temporary work area created in the system memory when executing an SQL statement. It can store data from the database and manipulate the data. A cursor can process a single row, but it can hold multiple rows at a time. In DB2, a programmer can make the cursor scrollable or non-scrollable.

Key Areas Covered

1. What are Scrollable Cursors in DB2
     – Definition, Features
2. What are Non Scrollable Cursors in DB2
     – Definition, Features
3. What is the Difference Between Scrollable and Non Scrollable Cursors in DB2
     – Comparison of Key Differences

Key Terms

DB2, Non Scrollable Cursors, RDBMS, Scrollable Cursors

What are Scrollable Cursors in DB2

Programmers can create a scrollable cursor by declaring it scrollable. He can execute FETCH statements that specify where he wants to locate the cursor. There should be a scrollable cursor when it is required to update the cursor. It is also possible to move backward or move to a particular position in a result table with a scrollable cursor. Declaring a scrollable cursor as SENSITIVE will display only the latest data. Furthermore, the programmer can use the ORDER BY clause in the declaration of an updateable cursor only if it scrollable.

What are Non Scrollable Cursors in DB2

Non scrollable cursor moves one after the other to the forward direction in the result table. Therefore, it is not possible to move to a specific position in a result table or to retrieve rows backward with a non-scrollable cursor.

The cursor is located before the first row when the application opens that cursor. The cursor is placed on the first row when the application executes the first FETCH statement. However, when the application executes the subsequent FETCH statements, the cursor moves one row ahead in every FETCH. After the application executes a positioned UPDATE or positioned DELETE statement, the cursor stays at the current row of the result table.

Difference Between Scrollable and Non Scrollable Cursors in DB2

Definition

A scrollable cursor is a cursor type available in DB2 that allows the applications to access small to medium level result tables. Whereas, non-scrollable cursor is a cursor type available in DB2 that allows the applications to access large result tables. Thus, this is the main difference between scrollable and non scrollable cursors.

Purpose

Programmers can use scrollable cursors to go through the result set randomly while programmers can use non scrollable cursor to move sequentially forward through the data in the result table. Hence, this is a major difference between scrollable and non scrollable cursors.

Processing

Moreover, scrollable cursors require more DB2 processing than non-scrollable cursors.

Application

Also, another difference between scrollable and non scrollable cursors is their application. Scrollable cursors are suitable when the application requires small to moderate level result tables while non scrollable cursors are suitable when the application requires large result tables.

Conclusion

In DB2, the programmer can declare or eliminate a cursor as scrollable or non-scrollable by including or removing the SCROLL clause. The difference between scrollable and non scrollable cursors in DB2 is that scrollable cursors are used to move randomly through the result set while non-scrollable cursors are used to move sequentially forward through the result set.

Reference:

1. The Analytics Maturity Model (IT Best Kept Secret Is Optimization), IBM Corporation, Available here.
2. The Analytics Maturity Model (IT Best Kept Secret Is Optimization), IBM Corporation, Available here.

Image Courtesy:

1. “DB2 Tablespace RAM and Disk” By Scifipete – Own work (CC BY-SA 3.0) via Commons Wikimedia