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

Hack the Box — Preignition

How One DC Engineering Team Helped Hundreds of Businesses Access the PPP Loan — Hatchpad

Installing Wkhtmltopdf in Laradock (or any docker container)

Is Business App A Gateway to Freedom?

How I learned 1,000 digits of Pi… and what it taught me!

Learn Programming with Python — Introduction to Compound Data Types: Sets and Tuples

How to integrate and run R in Python

Extracting And Analysing Spotify Tracks With Python

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

SQL 4주차

Microsoft Azure Fundamentals Training Series | 7-Azure Compute Services Series — Azure Virtual…

How to use row_number in SQL | SQL RANK() function | dense_rank SQL

How to use row_number in SQL | SQL RANK() function | dense_rank SQL