The effect of the OPTIMIZE FOR n ROWS clause in distributed applications

You can specify the OPTIMIZE FOR n ROWS clause to improve the performance of certain queries. For queries that access distributed data, this clause can have a significant performance impact because it helps limit the amount of data that is sent over the network. It also limits the number of network transmissions.

When you specify the OPTIMIZE FOR n ROWS clause in your query, the number of rows that DB2® transmits on each network transmission depends on the following factors:
  • If n rows of the SQL result set fit within a single DRDA query block, a DB2 server can send n rows to any DRDA client. In this case, DB2 sends n rows in each network transmission until the entire query result set is returned.
  • If n rows of the SQL result set exceed a single DRDA query block, the number of rows that are contained in each network transmission depends on the client's DRDA software level and configuration. The following conditions apply:
    • If the client does not support extra query blocks, the DB2 server automatically reduces the value of n to match the number of rows that fit within a DRDA query block.
    • If the client supports extra query blocks, the DRDA client can choose to accept multiple DRDA query blocks in a single data transmission. DRDA allows the client to establish an upper limit on the number of DRDA query blocks in each network transmission.
      The number of rows that a DB2 server sends is the smaller of the following values:
      • n rows
      • the number of rows that fit within the maximum number of extra DRDA query blocks that the DB2 server returns to a client in a single network transmission. (This value is specified in the EXTRA BLOCKS SRV field on installation panel DSNTIP5 at the DB2 server.)
      • the number of rows that fit within the client's extra query block limit, which is obtained from the DDM MAXBLKEXT parameter that is received from the client. (When DB2 acts as a DRDA client, the DDM MAXBLKEXT parameter is set to the value of EXTRA BLOCKS REQ on installation panel DSNTIP5.)
Depending on the value that you specify for n, the OPTIMIZE FOR n ROWS clause can improve performance in the following ways:
  • If n is less than the number of rows that fit in the DRDA query block, OPTIMIZE FOR n ROWS can improve performance by preventing the DB2 server from fetching rows that might never be used by the DRDA client application.
  • If n is greater than the number of rows that fit in a DRDA query block, OPTIMIZE FOR n ROWS lets the DRDA client request multiple blocks of query data on each network transmission. This use of OPTIMIZE FOR n ROWS can significantly improve elapsed time for applications that download large amounts of data.

Although the OPTIMIZE FOR n ROWS clause can improve performance, this same function can degrade performance if you do not use it properly. The following examples demonstrate the performance problems that can occur when you do not use this clause judiciously.

In the following figure, the DRDA client opens a cursor and fetches rows from the cursor. At some point before all rows in the query result set are returned, the application issues an SQL INSERT statement.
Figure 1. Message flows without the OPTIMIZE FOR n ROWS clause
Begin figure summary.This figure shows the interaction between a DRDA client and DB2 while a cursor retrieves data.Detailed description available.
In this case, DB2 uses normal DRDA message blocking, which has the following advantages over the message blocking that is used for the OPTIMIZE FOR n ROWS clause:
  • If the application issues an SQL statement other than FETCH (for example, an INSERT statement in this case), the DRDA client can transmit the SQL statement immediately, because the DRDA connection is not in use after the SQL OPEN.
  • The DRDA query block size places an upper limit on the number of rows that are fetched unnecessarily. If the SQL application closes the cursor before fetching all the rows in the query result set, the server fetches only the number of rows that fit in one query block, which is 100 rows of the result set.
In the following figure, the DRDA client opens a cursor and fetches rows from the cursor by using OPTIMIZE FOR n ROWS clause. Both the DRDA client and the DB2 server are configured to support multiple DRDA query blocks. At some time before the end of the query result set, the application issues an SQL INSERT.
Figure 2. Message flows with the OPTIMIZE FOR 1000 ROWS clause
Begin figure summary.This figure shows the interaction between a DRDA client and DB2 while a cursor retrieves data.Detailed description available.
Because the query uses the OPTIMIZE FOR n ROWS clause, the DRDA connection is not available when the SQL INSERT is issued. The connection is still being used to receive the DRDA query blocks for 1000 rows of data. This situation causes the following performance problems:
  • Application elapsed time can increase if the DRDA client waits for a large query result set to be transmitted before the DRDA connection can be used for other SQL statements. In this example, the SQL INSERT statement is delayed because of a large query result set.
  • If the application closes the cursor before fetching all the rows in the SQL result set, the server might fetch a large number of rows unnecessarily.