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
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
SELECT COLb FROM T1
WHERE COLa = :HV-COLa