Intelligent Enterprises of the 21st Century

The following suggestions can help you work with result sets more efficiently.

Cursor Considerations

When you create a cursor, you can specify what kind of operations the cursor may be performing. If you do not provide such a specification, the cursor is referred to as ambiguous, and DB2 will only be able to provide minor optimization. You specify the type of cursor by providing the FOR READ ONLY or FOR UPDATE clause in the cursor's SELECT statement. DB2 will perform specific optimizations for the type of cursor defined.

Row blocking is very useful when SQL procedures are returning large result sets. Instead of sending a row at a time to the client, sets of rows are sent together.

On LUW, when FOR READ ONLY is specified, DB2 is able to perform row blocking (unless the BLOCKING NO bind option has been used). You can also specify to use blocking for ambiguous cursors by providing the BLOCKING ALL bind option.

The BLOCKING bind option can be set using a command similar to the following:

db2set DB2_SQLROUTINE_PREOPTS="BLOCKING ALL"

To set blocking options on iSeries, see the section on Precompile Options on iSeries later in this chapter.

Limiting Result Sets

You can limit query result set size using the FETCH FIRST n ROWS clause, which is useful when you do not need the entire result set. For example, SELECT * FROM table1 FETCH FIRST 10 ROWS ONLY would fetch only the first 10 rows of the result set.

Optimizing Result Set Returns

When you only need a subset of the query at one time (for example, displaying results to a user's screen), you may want to obtain that subset as quickly as possible. You can optimize query return time with the OPTIMIZE FOR n ROWS clause. This will cause DB2 to optimize the return of the specified number of rows, instead of preparing to return all satisfying rows. In addition, the READ ONLY clause will influence the number of rows that will be returned in each block (there will not be more than n rows in a block). This does not limit the number of rows that can be fetched, but may degrade performance if more than n rows are fetched.

Note

On LUW, in order for this clause to have an impact on data buffers, the value of n * row size cannot exceed the size of the communication buffer (defined by DBM CFG RQRIOBLK or ASLHEAPSZ).

An example of the statement would be

SELECT * FROM table1 OPTIMIZE FOR 10 ROWS

The OPTIMIZE FOR n ROWS clause also applies to DB2 for zSeries, though it is not used for blocking. It does influence the optimizer to choose a different access path. For example, if you add OPTIMIZE FOR 1 ROWS in your query, the optimizer will likely not use sorting.

Minimize Result Set Columns

It is often easy to code quick statements with SELECT * FROM ...; however, selecting columns that are not required by the calling application can have performance impacts. The obvious benefit to reducing the number of columns is that less data would need to be passed back and forth between the calling application and the database manager. Additionally, a reduced column list may increases the likelihood of an access plan to use an existing index, if one exists.