Network traffic reduction

The key to improving performance in a network computing environment is to minimize network traffic. As “Using an application program as a stored procedure” on page 202 explains, stored procedures are an excellent method for sending many SQL statements in a single network message and, as a result, running many SQL statements at the DB2 server. This section introduces you to other ways to improve performance when accessing remote servers.

Coding efficient queries

A query that is sent to a remote server almost always takes longer to execute than the same query that accesses tables of the same size on a local server. To increase efficiency when accessing remote servers, try to write queries that send few messages over the network. For example:

• Reduce the number of columns and rows in the result table that is returned to your application. Keep your SELECT lists as short as possible. Creative use of the clauses WHERE, GROUP BY, and HAVING can eliminate unwanted data at the remote server.

• Use FOR READ ONLY. For example, retrieving thousands of rows as a continuous stream is reasonable. Sending a separate message for each one can be significantly slower.

• When possible, do not bind application plans and packages with ISOLATION(RR), even though that is the default. If your application does not need to refer again to rows it has read once, another isolation level might reduce lock contention and message overhead during COMMIT processing.

• Minimize the use of parameter markers.

  When your program uses DRDA access, DB2 can streamline the processing of dynamic queries that do not have parameter markers.

  When a DB2 requester encounters a PREPARE statement for such a query, it anticipates that the application is going to open a cursor. The requester therefore sends the server a single message that contains a combined request for PREPARE, DESCRIBE, and OPEN. A DB2 server that receives this message sequence returns a single reply message sequence that includes the output from the PREPARE, DESCRIBE, and OPEN operations. As a result, the number of network messages sent and received for these operations is reduced from two to one.

Sending multiple rows in a single message

DB2 capabilities that combine multiple rows of data during fetch and insert operations can significantly reduce the number of messages that are sent across the network. Those capabilities include block fetch and rowset fetches and inserts.

Block fetch

DB2 uses a block fetch to group the rows that an SQL query retrieves into as large a “block” of rows as will fit in a message buffer, and then transmits the block over the network. By sending multiple rows in a block, DB2 avoids sending a message for every row. A block fetch is used only with cursors that will not update data. The size of a DRDA query block on z/OS is limited to 32 KB.

DB2 can use two different types of block fetch:

• Limited block fetch optimizes data transfer by guaranteeing the transfer of a minimum amount of data in response to each request from the requesting system.

• Continuous block fetch sends a single request from the requester to the server. The server fills a buffer with data it retrieves and transmits it back to the requester. Processing at the requester is asynchronous with the server; the server continues to send blocks of data to the requester with minimal or no further prompting.

To use block fetch, DB2 must determine that the cursor is not used for update or delete. You can indicate this in your program by adding the clause FOR READ ONLY to the query. If you do not specify FOR READ ONLY, whether or not DB2 uses block fetch depends on how you define the cursor.

For scrollable cursors, the sensitivity of the cursor and the bind option affect whether you can use block fetching.

Rowset fetches and inserts

For rowset-positioned cursors (described in “Retrieving a set of rows” on page 190), when the cursor is opened for rowset processing, the answer set is returned in a single query block. The query block contains exactly the number of rows specified for the rowset.

Because a rowset is returned in a single query block, the size of a rowset is limited to 10 MB. This rowset size minimizes the impact to the network when a large rowset is retrieved with a single fetch operation.

Rowset-positioned cursors also allow multiple-row inserts. The INSERT statement, through FOR n ROWS, inserts multiple rows into a table or view, using values that host-variable arrays provide. With multiple-row inserts, rather than INSERT statements being sent for each individual insert, all insert data is sent in a single network message.

Optimizing for large and small result sets

Enabling a DB2 client to request multiple query blocks on each transmission can reduce network activity and improve performance significantly for applications that use DRDA access to download large amounts of data.

You can specify a large value of n in the OPTIMIZE FOR n ROWS clause of a SELECT statement to increase the number of DRDA query blocks that a DB2 server returns in each network transmission for a nonscrollable cursor.

If n is greater than the number of rows that fit in a single DRDA query block, OPTIMIZE FOR n ROWS lets the DRDA client request multiple blocks of query data on each network transmission instead of requesting another block when the first block is full. This use of OPTIMIZE FOR n ROWS is intended for applications in which the application opens a cursor and downloads large amounts of data. The OPTIMIZE FOR n ROWS clause has no effect on scrollable cursors.

When a client does not need all the rows from a potentially large result set, preventing the DB2 server from returning all the rows for a query can reduce network activity and improve performance significantly for DRDA applications. You can use either the OPTIMIZE FOR n ROWS clause or the FETCH FIRST n ROWS ONLY clause of a SELECT statement to limit the number of rows returned to a client program.

Improving dynamic SQL performance

You can improve performance for dynamic SQL applications in a distributed environment in the following ways:

• Specify the DEFER(PREPARE) option.

  DB2 does not prepare a dynamic SQL statement until the statement runs. For dynamic SQL that is used in DRDA access, consider specifying the DEFER(PREPARE) option when you bind or rebind your plans or packages. When a dynamic SQL statement accesses remote data, the PREPARE and EXECUTE statements can be transmitted together over the network and processed at the remote server. The remote server can then send responses to both statements back to the local subsystem together, thereby reducing network traffic.

• Eliminate the WITH HOLD option.

  Defining a cursor WITH HOLD requires sending an extra network message to close the cursor. You can improve performance by eliminating the WITH HOLD option when your application doesn't need to hold cursors open across a commit. This is particularly true for dynamic SQL applications.