DB2 Embedded Select and DB2 Cursor Coding Guidelines | COBOL+DB2 Application Programming Tips.

DB2 Embedded Select Statement and DB2 Cursor Coding Guideline.

DB2 Cursor or SQL Cursor or simply cursor is used to access data from the SQL result set. DB2 Cursor has embedded SQL statements. In this article, you’ll learn about the DB2 Embedded Select statement and DB2 Cursor coding guidelines. You’ll learn how to improve the performance of a COBOL+DB2 application by following these simple but important programming tips. Let’s get started with DB2 Embedded Select and Cursor Coding Guidelines.

Agenda.

• Introduction.
• What is DB2 Cursor?
• What is the Singleton SELECT Statement?
• DB2 Cursor Coding guidelines.
• Conclusion.

Introduction.

To ensure better performance of the application program it is always better to prevent problems than to fix them. As a programmer, you should consider all important aspects when creating an application module.

What is DB2 Cursor?

A cursor in DB2 is a mechanism by which you access data row-by-row from the result set. Cursors in DB2 keep track of records in the results set, and with the help of cursors, you can update, delete, or select individual rows from the table.

What is Singleton SELECT Statement or Embedded SQL Statement?

The singleton select statement is a simple SQL statement that returns just one row. It can therefore be coded and embedded into most host language programs without too much trouble: The singleton SELECT returns one row, and the application program processes one row. A singleton SELECT is coded as follows:

EXEC SQL

     SELECT DEPTNAME, MGRNO

       INTO :HOSTVAR-DEPTNAME,

            :HOSTVAR-MGRNO

       FROM DEPT WHERE DEPTNO = 'C11'

END-EXEC.

DB2 Cursor Coding guidelines (DB2 Cursor Programming Tips.).

The following are the DB2 Cursor Coding guidelines (i.e. DB2 Cursor Programming Tips). You must follow them to ensure better performance of the COBOL+DB2 application program.

• You can reduce overhead by utilizing Singleton Selects. In DB2 application programs, singleton SELECTs should be used instead of cursors whenever possible because defining and processing cursors adds extra overhead. Whenever a single SELECT statement returns multiple rows, a cursor must be implemented.

• To determine whether the data exists, use SQL. In some cases, the data in the table only needs to be checked, and it does not actually need to be retrieved. It is most efficient to use correlated SQL queries against IBM's SYSIBM.SYSBUMMY1 table. A good example would be: 

                 

SELECT 1 FROM SYSTEM.SYSDUMMY1 A

        WHERE EXISTS (SELECT 1 FROM EMP WHERE
        FIRSTNAME = 'TOPICTRICK' AND A.IBMREQD = A.IBMREQD)

above query will result in '1' in case data exists in the employee table and SQLCODE is zero.


• You should consider browsing with SQL CURSOR. If the program needs to query the rows of a column based on an index for a single column. The SQL statement can be looped until there are no more rows in the table.

• You should consider browsing with DB2 CURSOR. If the program needs to query the rows of a column based on an index for a single column. The SQL statement can be looped until there are no more rows in the table.

• When editing data, do not use joins, unions, distinct clauses, order by, group by, subqueries, etc.

• The DECLARE CURSOR statement should be placed first, even though it is not an executable statement and should not be included in the PROCEDURE DIVISION of an application program.

• Whenever updating columns, include them in the list. Don't use * in SQL statements.

• The cursor must be used when updating with a cursor, though it is not required (i.e. Optional). As a result of this technique, data is effectively locked before being deleted, ensuring that it cannot be accessed by any other process.

• The cursor can be used to delete a single row using WHERE CURRENT OF. For any update or delete statement that changes only one row, use the current clause of the where clause. In the event that the WHERE CURRENT OF clause is not coded, all rows in the table will be modified or deleted.

• For non-updateable cursors, do not use the FOR UPDATE OF clause. 

• Prior to executing the FETCH statement in the application program, all host variables were initialized and the cursor was opened.

• Closing cursor explicitly. However, DB2 closes the cursor implicitly when the application program terminates.

• Keep the cursor in its current position by using the WITH HOLD clause. Open cursors are closed when a Commit is issued by the application program unless the WITH HOLD option is coded for the cursor.

Conclusion. 

Finally, this marks an end to the DB2 Embedded SELECT and DB2 Cursor Coding Guidelines. This article clearly outlines the important tips that all programs should keep in mind while writing COBOL+DB2 application programs. Do check out COBOL LEVEL 88 Condition.

►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™