Embedded SELECT and Cursor Coding Guidelines

Embedded SELECT and Cursor Coding Guidelines

Ensure efficient and accurate embedded SQL by following the subsequent guidelines.

Use Singleton SELECT s to Reduce Overhead

Whenever possible, try to use singleton SELECT s rather than cursors because the definition and processing of cursors adds overhead to a DB2 application program. However, be sure that the singleton SELECT returns only one row. You can ensure that only a single row is to be returned in several ways:

• Selecting data only by the primary key column(s)

• Selecting data only by columns defined in a unique index

• Using the FETCH FIRST 1 ROW ONLY clause on the singleton SELECT statement

If the program requires a SELECT statement that returns more than one row, you must use cursors. In other words, a singleton SELECT cannot be used to return more than one row.

Code SQL for Efficient Existence Checking

There are times when a program just needs to know that some given data exists and does not need to actually retrieve and use that data. For these situations, you will need to develop the most efficient SQL possible that just checks whether the specific data exists. But what is the best way to accomplish an existence check?

Prior to DB2 V7, the best way to check for existence is to use a correlated query against the SYSIBM.SYSDUMMY1 table. For example, to check for the existence of an employee with the last name of Jones in the EMP table, the SQL would look like this:

 SELECT 1 FROM   SYSIBM.SYSDUMMY1 A WHERE  EXISTS (SELECT 1                FROM   EMP B                WHERE  LASTNAME = 'JONES'                AND A.IBMREQD = A.IBMREQD); 

First, notice that we just SELECT the constant 1 . Because the data does not need to be returned to the program, the SQL statement need not specify any columns in the SELECT -list. We simply check the SQLCODE . If the SQLCODE is zero, data exists; if not, the data does not exist.

Next , notice that we use the EXISTS predicate in the correlated subquery. This is an efficient formulation because EXISTS causes DB2 to stop processing as soon as one row is found that matches the WHERE clause in the subquery. This is fine, because all we want to know is that at least one row exists that matches the predicate(s). If you do not use a correlated query with EXISTS , but instead simply issued the SELECT statement, performance can suffer as DB2 scans to find subsequent occurrences of the data ”which might be many, especially for a common name such as Jones.

Which brings us to DB2 V7. In this version, there is a new clause called FETCH FIRST n ROWS ONLY . This solves our problem for existence checking and can become a new standard once you move to DB2 V7. Recoding the previous example using this new clause becomes

 SELECT 1 FROM   EMP B WHERE  LASTNAME = 'JONES' FETCH FIRST 1 ROW ONLY; 

We still do not need to specify columns in the SELECT -list, but we no longer need the correlated query. DB2 will stop after one row has been checked, which is the desired result.

NOTE

You might still choose to use the correlated subquery approach even after DB2 V7. This is particularly so when the next operation requires you to request data from DB2. If you can combine this next work into the subquery formulation, then it can be more efficient because you will not be going back and forth between the program and the DB2 engine in between existence checks.

Consider Cursor-Free Browsing

When a program needs to browse through the rows of a table based on a single column where a unique index exists, consider avoiding a cursor in favor of the following two SQL statements:

 SELECT VALUE(MIN(SALARY),0) INTO   :NEW-SAL-HVAR FROM   EMP WHERE  SALARY > :OLD-SAL-HVAR SELECT EMPNO, LASTNAME, SALARY, BONUS INTO   :HV-EMPNO, :HV-LASTNAME, :HV-SALARY, :HV-BONUS FROM   EMP WHERE  SALARY = :NEW-SAL-HVAR 

The first time through the program, the host variable OLD-SAL-HVAR should be set to a value just lower than the lowest value that needs to be retrieved. By looping through the preceding two SQL statements, the program can avoid a cursor and browse the table rows until no more rows exist or the highest value is obtained. This technique can outperform a cursor in some situations.

Declare As Many Cursors As Needed

You can declare and open more than one cursor in any given program at any time. No limit is placed on the number of cursors permitted per application program.

Avoid Using Certain Cursors for Data Modification

You cannot use a cursor for updates or deletes if the DECLARE CURSOR statement includes any of the following:

UNION clause

DISTINCT clause

GROUP BY clause

ORDER BY clause

Joins

Subqueries

Correlated subqueries

Tables in read-only mode, ACCESS(RO)

Tables in utility mode, ACCESS(UT)

Read-only views

Place the DECLARE CURSOR Statement First

The DECLARE CURSOR statement must precede any other commands (such as OPEN , CLOSE , and FETCH ) relating to the cursor because of the way the DB2 precompiler parses and extracts the SQL statements from the program.

The DECLARE CURSOR statement is not an executable statement and should not be coded in the PROCEDURE DIVISION of an application program. Although doing so does not cause a problem, it makes your program difficult to understand and could cause others to think that DECLARE is an executable statement.

You should place all cursor declarations in the WORKING-STORAGE section of the application program, immediately before PROCEDURE DIVISION . All host variable declarations must precede the DECLARE CURSOR statement in the application program.

Include Only the Columns Being Updated

When you're coding the FOR UPDATE OF clause of the DECLARE CURSOR statement, you should specify only the columns that will be updated. Coding more columns than is necessary can degrade performance.

In the FOR UPDATE OF clause of the DECLARE CURSOR statement, you must include all columns to be modified. Otherwise, subsequent UPDATE...WHERE CURRENT OF statements will not be allowed for those columns.

Use FOR UPDATE OF When Updating with a Cursor

Although doing so is not mandatory, you should code the FOR UPDATE OF clause of a DECLARE CURSOR statement used for deleting rows. This technique effectively locks the row before it is deleted so that no other process can access it. If rows earmarked for deletion are accessible by other programs and ad hoc users, the integrity of the data could be compromised.

Use WHERE CURRENT OF to Delete Single Rows Using a Cursor

Use the WHERE CURRENT OF clause on UPDATE and DELETE statements that are meant to modify only a single row. Failure to code the WHERE CURRENT OF clause results in the modification or deletion of every row in the table being processed .

Avoid the FOR UPDATE OF Clause on Non-Updateable Cursors

You cannot code the FOR UPDATE OF clause on cursors that access read-only data. These cursors contain SELECT statements that

• Access read-only views

• Join any tables

• Issue subqueries for two or more tables

• Access two or more tables using UNION

• Use built-in functions

• Use ORDER BY , GROUP BY , or HAVING

• Specify DISTINCT

• Specify literals or arithmetic expressions in the SELECT list

Open Cursors Before Fetching

Similar to a sequential file, a cursor must be opened before it can be fetched from or closed. You also cannot open a cursor twice without first closing it.

Initialize Host Variables

Initialize all host variables used by the cursor before opening the cursor. All host variables used in a cursor SELECT are evaluated when the cursor is opened, not when the cursor is declared or fetched from.

Use Care When Specifying Host Variables Used with FETCH

The FETCH statement retrieves data one row at a time only in a forward motion. In other words, rows that have already been retrieved cannot be retrieved again.

Synchronize the host variables fetched (or selected) with the SELECT list specified in the cursor declaration (or singleton SELECT ). If the data type of the columns does not match the host variable, and the data cannot be converted, a compilation error results. This error can occur if host variables are transposed as follows :

 EXEC SQL     DECLARE C1 CURSOR     SELECT  DEPTNO, ADMRDEPT     FROM    DEPT END-EXEC. EXEC SQL     FETCH C1     INTO  :ADMRDEPT, :DEPTNO END-EXEC. 

The DEPTNO host variable is switched with the ADMRDEPT host variable in the FETCH statement. This switch does not cause a compilation error because both columns are the same data type and length, but it does cause data integrity problems.

Explicitly Close Cursors

When a DB2 program is finished, DB2 implicitly closes all cursors opened by the program. To increase performance, however, you should explicitly code the CLOSE statement for each cursor when the cursor is no longer required. The CLOSE statement can be executed only against previously OPEN ed cursors.

Use the WITH HOLD Clause to Retain Cursor Position

When a COMMIT is issued by the program, open cursors are closed unless the WITH HOLD option is coded for the cursor.

You can add the WITH HOLD parameter to a cursor as shown in the following example:

 EXEC SQL     DECLARE CSR1 CURSOR WITH HOLD FOR         SELECT  EMPNO, LASTNAME         FROM    EMP         WHERE   SALARY > 30000 END-EXEC. 

WITH HOLD prevents subsequent COMMIT s from destroying the intermediate results table for the SELECT statement, thereby saving positioning within the cursor. This technique will not hold the cursor position over separate tasks in pseudo-conversational programs.

NOTE

Be aware that the manner in which DB2 handles locks for held cursors is influenced by the value of the RELCURHL DSNZPARM parameter. The default value is YES , which causes DB2 to release the row or page lock at COMMIT for the row where the cursor is positioned. Prior to V7, the default was NO , causing DB2 to hold the lock at COMMIT . The lock is not necessary for maintaining cursor position, so specify YES to improve concurrency.

Open Cursors Only When Needed

Do not open a cursor until just before you need it. Close the cursor immediately after your program receives an SQLCODE of +100 , which means that the program has finished processing the cursor. This way, you can reduce the consumption of system resources.