Improvements in query efficiency

Queries almost always execute faster on a local server than they do when the same query is sent to a remote 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 much slower.
• When possible, do not bind application plans and packages with ISOLATION(RR). If your application does not need to refer again to rows it reads 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. However, parameter markers are needed for effective dynamic statement caching.

  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 that are sent and received for these operations is reduced from two to one.