Remote Query Performance

The performance of remote queries can be improved in many ways.

OPTIMIZE FOR n ROWS

Use the OPTIMIZE FOR n ROWS for distributed applications to limit the number of rows returned in a DRDA client query block so that only a portion of the answer set is fetched. This option can also optimize cases when a large number of rows are fetched, allowing a client to request multiple query blocks from a DRDA requester in a single network transmission. This allows you to specify the number of query blocks to retrieve.

OPTIMIZE FOR n ROWS is supported for result sets returned by a SELECT statement or by a stored procedure. DRDA level 3 is implemented to enable the client to specify the number of extra query blocks it wants to receiveinstead of the default of one at a time. Any value n will allow the DRDA server to send multiple query blocks in a single transmission.

DB2 will send n number of rows of the result set in each network transmission if the result set fits in a query block. If the rows do not fit in a query block, extra query blocks will be sent in the same transmission up to either the query-block limit set for the server (DSNZPARM EXTRASRV) or the query-block limit set by the client (DSNZPARM EXTRAREQ, which is also the value in DDM MAXBLKEXT on the DRDA requester). You want to be careful with these parameters, as the default is 100 query blocks per transmission, and this default can negatively affect the performance of your applications, depending on your workload mix.

You want to take advantage of this clause when the value n is less than the total number of rows that will fit in a DRDA query block and the application is going to process only the same n number of rows. You can also significantly reduce elapsed time for large query result sets by using OPTIMIZE FOR n ROWS if n is greater than the number of rows that can fit in one DRDA query block, because this will allow the requester to get multiple query blocks with each network transmission.

If your DRDA application meets the following criteria, you may want to use OPTIMIZE FOR n ROWS:

• Read-only queries fetching a large number of rows

• SQL cursors rarely being closed before fetching the entire result set

• When cursor is open, no statements other than FETCH used

• Multiple cursors defined with OPTIMIZE FOR n ROWS not issuing a FETCH

You do not want to set a large number of query blocks for cursors defined WITH HOLD, because if the requester application commits while several blocks are in the network, DB2 will buffer the blocks in memory. A WITH HOLD cursor will also prevent a thread from going inactive.

Inactive Threads

Access threads that do not hold any cursors are known as inactive threads. Two types of inactive threads are supported: type 1 and type 2. Type 2 threads are available only for DRDA connections; use a pool of database threads, which can be switched as needed among connections; and use less storage than type 1. You want to use type 2 inactive threads when possible.

Displaying Distributed Threads

Distributed threads can be displayed to find out information about their status by using the DISPLAY THREAD command, using the LOCATION option. An example of this command follows:

 DISPLAY THREAD(*) LOCATION(*) DETAIL 

Distributed Application Tuning Guidelines

• Use stored procedures to reduce network traffic.

• Use COMMIT ON RETURN YES to issue an implicit commit for a stored procedure when there is a return from the CALL statement.

• Commit frequently.

• Limit the number of SQL statements; the fewer, the better.

• Use the SQL RELEASE statement to release the remote connection at commit time, and also use the DISCONNECT(EXPLICIT).

• Use the CURRENT RULES of DB2, not STD, special register so that DB2 can send multiple blocks of data back to the program when requesting LOB data.

• Use DRDA-type connections instead of the DB2 private protocol.