DB2 Cursor | Different Stages Of DB2 Cursor | Life Cycle Of DB2 Cursor | DB2 Cursor Examples | COBOL+DB2 Application.

DB2 Cursor Stages.

Are you planning to use the cursor in your COBOL+DB2 applications? If yes, this article would help you in understanding the underlying concept and the different stages of Cursors in DB2. You’ll learn what the host language is and why the host language is different from the SQL?

Let's get started with today's agenda.

Agenda.

• Introduction.
• What is Host-language and why it's different from SQL?
• DB2 Cursor Definition.
• Different Stages of DB2 Cursor.
• Cursor in DB2 Example.
• Conclusion.

Introduction.

DB2 Cursor or simply a Cursor is a type of method to access data from the result set created by an SQL statement. You generally used the cursor in COBOL+DB2 applications. Now, you might be thinking why do you need a DB2 cursor? 

Well, it's not mandatory to use DB2 cursors. You can use a singleton SELECT statement to fetch data from the DB2 table instead. But there is a specific situation where you require the DB2 cursor to handle multiple rows. If the requirement is to process multiple rows for the same account then you need a cursor because COBOL or any other programming language can only process one row at a time. If you don't use Cursor then the application program will fail with SQL Code -811. 

If the requirement is to process the first row out of the multiple return rows, then you can use a singleton SELECT statement. Also, you can use an error-handling routine to prevent application programs from failing due to multiple rows.   

What is Host-language and why it's different from SQL?

The languages such as COBOL, C++, Java, etc are traditionally used for application programming. These applications generally access data from the database or sequential file or VSAM clusters. These programming languages are known as Host-languages.

Host languages such as COBOL, C++ were designed to process a single record at a time (i.e. record level processing) but ironically SQL was capable of processing multiple rows at a time (i.e. set level processing). Thus, It’s not possible for host languages to handle such a limitation with an Embedded SELECT SQL statement.

An IBM solution to this problem is a structure known as a symbolic cursor or cursor.

DB2 Cursor Definition.

DB2 Cursors are used to retrieve and process individual rows in an application program. As a result, host languages like COBOL, C++ etc. can process each record separately. The COBOL-DB2 application program navigates through a set of rows returned by an embedded SQL SELECT statement by using DB2 cursors. You can link a DB2 Cursor to a pointer.

Different Stages of DB2 Cursor.

The DB2 Cursor retrieves rows when the SELECT statement associated with it is executed. As a result, or result set, the set of rows returned is known as the result table. Each row in the result set must be retrieved into host variables by an application program. Cursors in DB2 perform this function. A COBOL+DB2 application program can have multiple DB2 Cursors. These cursors can be open at the same time. A cursor life cycle is classified into four stages. All four stages are outlined in the following diagram.

Cursor Life Cycle.

DECLARE the Cursor.

DECLARE CURSOR is used to declare and identify a set of rows that will be accessed by the cursor. In this statement, a cursor is named and a SELECT statement is provided. SELECT statements define which rows will be included in the result tables. Here's how you can declare a cursor:

EXEC SQL
    DECLARE EMPCR CURSOR FOR
    SELECT EMPNO, EMPNAME, EMPCODE
    FROM DB2USER1.EMPLOYEE
    WHERE EMPNO = :HEMPNO
END-EXEC.

Cursor with Hold Option.

Cursors that are declared with HOLD do not close after COMMIT. COMMIT automatically closes a cursor that hasn't been declared WITH HOLD. By including or excluding the WITH HOLD clause, DB2 determines if a cursor will be held. Holding the cursor after a COMMIT operation prevents subsequent logical rows of the result table from being returned after the last row is retrieved.

EXEC SQL

   DECLARE EMPCR  CURSOR WITH HOLD FOR

      SELECT EMPNO, EMPNAME, EMPCODE

      FROM DB2USER1.EMPLOYEE

      WHERE EMPNO = :HEMPNO

END-EXEC.

OPEN the Cursor.

You need to issue an OPEN statement when you are ready to process the first row of the result table or result set. DB2 then uses the SELECT statement within the DECLARE CURSOR statement to identify a set of rows. DB2 selects rows from the table based on the current values of host variables in the SELECT statements. Depending on the search conditions, there can be zero, one, or many rows in the result table. Here is how the OPEN statement looks:

EXEC SQL 

 OPEN EMPCR 

END-EXEC.

FETCH Statement.

You can use the FETCH statement to accomplish one of the following:

• Copying data from a row of a result table into a host variable.
• An update or delete operation is performed by positioning the cursor before it is performed.

The FETCH statement can be written as follows:

EXEC SQL 

    FETCH EMPCR 

    INTO :HEMPNO,:HEMPNAME, ... 

END-EXEC.

CLOSE Statement.

Once the result table has been processed, close the cursor by issuing the CLOSE statement:

EXEC SQL 

     CLOSE EMPCR 

END-EXEC.

Conclusion. 

Finally, this marks an end to the COBOL DB2 Cursors or Cursors in DB2 stages. This article covers different stages of DB2 Cursors and how you can use them in your COBOL+DB2 Applications. Do check out DB2 Cursors.

►Subscribe to Topictrick & Don't forget to press THE BELL ICON to never miss any updates. Also, Please visit mention the link below to stay connected with Topictrick and the Mainframe forum on - 

► Youtube

► Follow us on Twitter  

► Facebook 

► Linkedin

► Reddit

► Mainframe Blog

► Medium Blog

Thank you for your support. 

Mainframe Forum™

Created with Artisteer