Monday, 2 September 2013

DB2 Cursor | Different Stages Of DB2 Cursor | Life Cycle Of DB2 Cursor.

Host language such as COBOL, C++ were design to process single record at a time (i.e. record level processing) but ironically SQL capable of processing multiple rows at a time (i.e. set level processing).

It is not possible to handle such impedance or mismatch with Embedded SELECT SQL statement.
IBM's solution is the structure know as a symbolic cursor or simply cursor.

DB2 Cursor Definition: A DB2 Cursor is used in an application program to retrieve and process individual rows from a result set. Thus enable host language such as COBOL, C++ etc. to process single record at a time. 

COBOL-DB2 application program use DB2 cursor to navigate through a set of rows returned by an embedded SQL SELECT statement. A DB2 Cursor can be linked to pointer. 

As the programmer, you can declare a SQL cursor and define an SQL statement for that DB2 Cursor. After that you can use the cursor in much the same manner as a sequential file.

A cursor life cycle is classified into for stages. Refer below fig:

Cursor Life Cycle.
  • DECLARE the cursor. The declare statement has the SQL text that the cursor will run. If the cursor is declared "with hold", it will remain open after a commit, otherwise it will be closed at commit time.
  • OPEN the cursor. This is when the contents of on any host variables referenced by the DB2 cursor (in the predicate part of the query) are transferred to DB2 Table.
  • FETCH rows from the cursor. DB2 Fetch command is used to fetch data from DB2 table. One does as many fetches as is needed. If no row is found, the SQLCODE from the fetch will be 100.
  • CLOSE the cursor. Is used to close cursor. 
DB2 Cursor can be broadly classified into two category :
  1. Scrollable DB2 Cursor 
  2. Non Scrollable Db2 Cursor or Simple Cursor.

NOTE: The declare cursor statement is not actually executed when the program is run. It simply defines the query that will be run.
Created with Artisteer

No comments:

Post a comment