DB2 UDB for z/OS: Design Guidelines for High Performance and Availability

216 DB2 UDB for z/OS: Application Design for High Performance and Availability

9.1 Cursor versus singleton select

A common scenario encountered in application design is one in which a selection predicate

may qualify multiple rows, but in most cases will only qualify one. The simplest way to design

this access is to use a cursor using OPEN/FETCH/CLOSE processing, but this is also the

most expensive. For best performance, consider the following technique when designing your

application.

Use singleton SELECT first, then a cursor if the SQLCODE is -811

Implementing this mechanism depends a lot on the probability of multiple rows meeting the

selection criteria. Consider using this technique, in Figure 9-1, if only one row qualifies more

than 80% of the time.

Figure 9-1 Program structure for most likely retrieving one row

Perform the singleton SELECT statement:

򐂰 If SQLCODE = 0 or +100, you have the result.

򐂰 If SQLCODE = -811, perform cursor processing using OPEN/FETCH/CLOSE.

Also, consider using this only if it is acceptable to not get any rows back. The architected

behavior for negative SQLCODE is no data returned.

The benefit of this technique is the reduction of the CPU required to perform the OPEN and

CLOSE of the cursor when only a single row exists. Obviously, if multiple rows are expected

most of the time and you must return the rows, you should define a cursor and you should use

OPEN/FETCH/CLOSE processing.

SELECT COLb FROM T1

WHERE COLa = :HV-COLa

OPEN CURSOR

+100

SQLCODE

PROCESSING

FETCH CURSOR

CLOSE CURSOR

-811

PROCESSING

SQLCODE

0

0

+100