DB2 SQL CURSOR

  1. DECLARE
DECLARE cursor-name CURSOR FOR select-statement
OPEN cursor-name
FETCH FROM cursor-name INTO :host-variable1,:host-variable2
CLOSE cursor-name
  1. SERIAL CURSOR
  2. SCROLL-ABLE CURSOR

1. SERIAL CURSOR

  1. A serial cursor is one defined without the SCROLL keyword.
  2. A serial cursor can only move in a forwarding direction.
  3. For a serial cursor, each row of the result table can be fetched only once per OPEN of the cursor.
  4. To use a serial cursor again, you must first close the cursor and then re-issue the OPEN statement.
  5. When the cursor is opened, it is positioned before the first row in the result table.
  6. On the first FETCH, the first record of the result table will be read, and the cursor will be positioned between the 1st and 2nd records of the result table.
DECLARE @Customer CURSOR FOR
(SELECT * FROM CUSTOMER Where CUS_COUNTRY = 'INDIA')
OPEN @Customer
FETCH @Customer 
into :CustomerId,:CustomerName,:CustomerCountry
Exec SQL FETCH @Customer 
into :CustomerId,:CustomerName,:CustomerCountry;
DoW SQLCODE = 0;
// some code to process date read from Cursor.
Exec SQL FETCH @Customer
into :CustomerId,:CustomerName,:CustomerCountry;
EndDo;
CLOSE @Customer

2. SCROLLABLE CURSOR

  1. A scrollable cursor is one defined with the SCROLL keyword.
  2. A scrollable cursor can move in both forward and backward directions.
  3. For a scrollable cursor, each row of the result table can be fetched multiple times per OPEN of the cursor.
  4. When the cursor is opened, it is positioned before the first row in the result table.
DECLARE @Customer SCROLL CURSOR FOR
(SELECT * FROM CUSTOMER Where CUS_COUNTRY = 'INDIA')
OPEN @Customer
FETCH [direction-option] From cursor-name into :host-var1,:host-var2
FETCH LAST From @Customer 
into :CustomerId,:CustomerName,:CustomerCountry
FETCH ABSOLUTE 2 From @Customer 
into :CustomerId,:CustomerName,:CustomerCountry
FETCH PRIOR From @Customer 
into :CustomerId,:CustomerName,:CustomerCountry
CLOSE @Customer

CURSOR and COMMITMENT CONTROL

  1. When you use COMMIT or ROLLBACK statements with CURSOR, the behavior of the CURSOR depends on whether or not it is declared using the WITH HOLD clause
  2. If the CURSOR is declared using the WITHOUT HOLD clause, all of its resources (cursor, locks, and large-object datatype, or LOB, locators) are released upon either COMMIT or ROLLBACK. Therefore, if you need to use the cursor after completing a transaction, you will have to re-open the cursor and traverse it again from the first row.
  3. Defining a cursor using WITH HOLD will cause the cursor to maintain its position and some locks across transactions.
  1. The cursor will remain open.
  2. The cursor will be positioned before the next logical row.
  3. The only permitted operations on cursors immediately after the COMMIT statement is FETCH and CLOSE.
  4. Positioned delete and positioned update are valid only for rows that are fetched within the same unit of work.
  5. All LOB locators will be released.
  6. All locks are released, except locks protecting the current cursor position of open WITH HOLD cursors. The locks held include the locks on the table and, for parallel environments, the locks on the rows where the cursors are currently positioned. Locks on packages and dynamic SQL sections (if any) are held.
  7. The set of rows modified by
  • A data change statement
  • Routines that modify SQL data embedded within open WITH HOLD cursors are committed
  1. All open cursors will be closed.
  2. All locks acquired during the UOW will be released.
  3. All LOB locators are freed.

--

--

--

A programmer.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Why is Manual Testing not sufficient for Continuous Delivery?

My First 1 Million Installs as an Independent Android Developer

Creating a Strong, Healthy Software Development Team

Top 10 Best Android Frameworks for App Development in 2019

Play Spotify like Music in Ionic 4 apps

Play Spotify like Music in Ionic 4 apps

How to send sensor data to firebase Firestore using google app script — Part 1

Understanding the Web!

Golang — C++ interoperability:

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Sumit goyal

Sumit goyal

A programmer.

More from Medium

History of SQL

SELECT statement for SQL

SELECT statement for SQL

SQL Cheatsheet

SQL Server Primary Keys