Scrollable cursors in DB2:
Processing large volumes of data on your mainframe can be challenging. In this blog post, we introduce a valuable DB2 feature called scrollable cursors that simplifies managing extensive datasets. We’ll cover what scrollable cursors are, why they’re beneficial, and how you can start using them to improve your data handling.
What Are Scrollable Cursors?
Scrollable cursors in DB2 offer more flexibility when working with data. Unlike regular cursors that only move forward through the result set, scrollable cursors let you move forward, backward, or jump to a specific point in the data. This makes it easier to navigate and work with the result table.
Benefits of Scrollable Cursors:
- Move both forward and backward within the result set.
- Skip directly to specific rows without retrieving all previous rows.
- Retrieve only the needed rows, minimizing unnecessary data processing.
Types of scrollable cursors:
- Sensitive Scrollable Cursors:
Sensitive scrollable cursors are designed to reflect any changes made to the database while the cursor is open. This means that if other transactions update, insert, or delete data in the result set, these changes will be visible as you scroll through the cursor.
There are two types of sensitive scrollable cursors:
- Sensitive Static Scrollable Cursor: This cursor type shows updates, inserts, or deletes that occur after the cursor is opened, but only for those changes that were made before the cursor’s position is moved.
- Sensitive Dynamic Scrollable Cursor: This cursor type displays real-time updates, inserts, or deletes as they happen, ensuring that the data you see is always current as you navigate through the result set.
Sample Declaration:
EXEC SQL
DECLARE EMP_CUR SENSITIVE STATIC SCROLL CURSOR FOR
SELECT EMPNO, ENAME, DEPT_ID
FROM DBTST.EMP_TBL
ORDER BY EMPNO
END-EXEC.
EXEC SQL
DECLARE EMP_CUR SENSITIVE DYNAMIC SCROLL CURSOR FOR
SELECT EMPNO, ENAME, DEPT_ID
FROM DBTST.EMP_TBL
ORDER BY EMPNO
END-EXEC.
- Insensitive Scrollable Cursors:
Insensitive scrollable cursors do not show changes made to the database while they are open. Any updates, inserts, or deletes performed by other transactions will not be visible as you scroll through the cursor. The result set remains static and unchanged throughout the cursor’s use.
Sample Declaration:
EXEC SQL
DECLARE EMP_CUR INSENSITIVE SCROLL CURSOR FOR
SELECT EMPNO, ENAME, DEPT_ID
FROM DBTST.EMP_TBL
ORDER BY EMPNO
END-EXEC.
Note:The default scrollable cursor in Db2 is the “INSENSITIVE” scrollable cursor. This means that once the result set is retrieved, it does not reflect any changes made to the underlying data (such as updates, inserts, or deletes) after the cursor is opened. The result set remains static and unchanging during the cursor’s lifetime.
Fetching the rows using Scrollable cursor:
Scrollable cursors let you move around the result table and start fetching data from any point. Unlike normal cursors, which only read rows one by one from the start, scrollable cursors allow you to jump to different positions using the Fetch Orientation keyword.
Here are the different Fetch Orientation Keywords:
- BEFORE – Positions the cursor before the first row of the result table.
- AFTER – Positions the cursor after the last row of the result table.
- FIRST – Positions the cursor on the first row.
- LAST – Positions the cursor on the last row.
- NEXT – Positions the cursor on the next row. This is the default.
- CURRENT – Fetches the current row from the result table.
- PRIOR – Fetches the previous row.
- ABSOLUTE n – Positions the cursor on the nth row. ‘n’ can be positive, negative, or zero, and it calculates the nth row from the start of the result table.
Example code using Scrollable cursors:
— Declare the scrollable cursor
DECLARE my_cursor SCROLL CURSOR FOR
SELECT emp_id, emp_name, salary
FROM employees
ORDER BY emp_id;
— Open the cursor
OPEN my_cursor;
— Fetch the first row
FETCH FIRST FROM my_cursor INTO :emp_id, :emp_name, :salary;
— Fetch the next row
FETCH NEXT FROM my_cursor INTO :emp_id, :emp_name, :salary;
— Fetch the previous row
FETCH PRIOR FROM my_cursor INTO :emp_id, :emp_name, :salary;
— Position the cursor before the first row of the result table
FETCH BEFORE FROM my_cursor INTO :emp_id, :emp_name, :salary;
— Position the cursor before the first row of the result table
FETCH AFTER FROM my_cursor INTO :emp_id, :emp_name, :salary;
— Position the cursor 3 rows before the current cursor position
FETCH RELATIVE -3 FROM my_cursor INTO :emp_id, :emp_name, :salary;
— Position the cursor 4 rows after the current cursor position
FETCH RELATIVE +4 FROM my_cursor INTO :emp_id, :emp_name, :salary;
— Fetch an absolute row
FETCH ABSOLUTE 3 FROM my_cursor INTO :emp_id, :emp_name, :salary;
— Close the cursor
CLOSE my_cursor;
Sample COBOL Program:
Here is a COBOL program that demonstrates how to use the scrollable cursor to fetch the last 10 rows:
IDENTIFICATION DIVISION.
PROGRAM-ID. FetchLast10Rows.
DATA DIVISION.
WORKING-STORAGE SECTION.
01 emp_id PIC 9(5).
01 emp_name PIC X(20).
01 salary PIC 9(7)V99.
01 sqlcode PIC S9(9) COMP.
01 ws-row-count PIC 9(5) VALUE 0.
01 ws-max-rows PIC 9(5) VALUE 10.
EXEC SQL INCLUDE SQLCA END-EXEC.
PROCEDURE DIVISION.
MAIN-PARA.
EXEC SQL
OPEN my_cursor
END-EXEC.
IF SQLCODE = 0 THEN
PERFORM FETCH-LAST-10-ROWS
ELSE
DISPLAY ‘Error opening cursor: ‘ SQLCODE
END-IF.
EXEC SQL
CLOSE my_cursor
END-EXEC.
STOP RUN.
FETCH-LAST-10-ROWS.
EXEC SQL
FETCH LAST FROM my_cursor
INTO :emp_id, :emp_name, :salary
END-EXEC.
IF SQLCODE = 0 THEN
DISPLAY ‘Row: ‘ emp_id ‘, ‘ emp_name ‘, ‘ salary
PERFORM VARYING ws-row-count FROM 1 BY 1 UNTIL ws-row-count >= ws-max-rows
EXEC SQL
FETCH PRIOR FROM my_cursor
INTO :emp_id, :emp_name, :salary
END-EXEC
IF SQLCODE = 0 THEN
DISPLAY ‘Row: ‘ emp_id ‘, ‘ emp_name ‘, ‘ salary
ELSE
EXIT PERFORM
END-IF
END-PERFORM
ELSE
DISPLAY ‘Error fetching last row: ‘ SQLCODE
END-IF.
Advantages and Disadvantages of Scrollable Cursors in DB2:
Advantages:
- Move forward and backward through data easily.
- Access only the rows you need, saving time.
- Ideal for interactive applications needing dynamic data access.
Disadvantages:
- Adds complexity to the code and requires careful error handling.
- Best for specific scenarios, not always needed for simple tasks.
Conclusion:
Scrollable cursors in DB2 are excellent for navigating large datasets more efficiently. They enhance data retrieval and boost your DB2 application’s performance. Experiment with different fetch orientations and use scrollable cursors when needed to make the most of this feature.