BIND options for distributed applications
Certain bind options can improve the performance of SQL statements that run as part of distributed applications.
Procedure
To improve the performance of applications that access distributed data:
Bind option | Recommended value and actions | Reason |
---|---|---|
CURRENTDATA | CURRENTDATA(NO) | Use this bind option to force block fetch for ambiguous queries. |
ISOLATION | ISOLATION (CS), or any option other than ISOLATION (RR) | When possible, do not bind application plans and packages with ISOLATION(RR). If your application does not need to reference rows that it has already read, another isolation level might reduce lock contention and message overhead during commit processing. |
KEEPDYNAMIC | KEEPDYNAMIC(YES) | Use this bind option to improve performance for queries that use cursors that are defined with the WITH HOLD option. With KEEPDYNAMIC(YES), Db2 automatically closes the cursor when no more data exists for retrieval. The client does not need to send a network message to tell Db2 to close the cursor. This option is not needed for clients that close the cursor even if the cursor is defined with the WITH HOLD option. |
NODEFER and DEFER | DEFER(PREPARE) | This option reduces network traffic, because the PREPARE and EXECUTE statements and responses are transmitted together. |
PKLIST and NOPKLIST | PKLIST Specify the package collections for this bind option according to the following recommendations:
|
The order in which you specify package collections
in a package list can affect the performance of your application program.
When a local instance of Db2 attempts
to execute an SQL statement at a remote server, the local Db2 subsystem must determine which
package collection the SQL statement is in. Db2 must send a message to the server to request
that the server check each collection ID for the SQL statement until
the statement is found or no more collection IDs are in the package
list. You can reduce the amount of network traffic, and thereby improve
performance, by reducing the number of package collections that each
server must search. As an alternative to specifying the package collections on the PKLIST bind option, you can specify the package collection that is associated with an SQL statement in your application program. Execute the SET CURRENT PACKAGESET statement before you execute an SQL statement to tell Db2 which package collection to search for the statement. |
PKLIST and NOPKLIST (cont.) |
Requirement: When you specify the DEFER(PREPARE) bind option with DRDA access, the package that contains the statements whose preparation you want to defer must be the first qualifying entry in the package search sequence that Db2 uses.
For example, assume that the package list for a plan contains two entries:
If the intended package is in collection COLLB, ensure that Db2 searches that collection first by executing the following SQL statement:
Alternatively, you can list COLLB first in the PKLIST bind option:
For the NODEFER(PREPARE) bind option, the collections in the package list can be in any order, but if the package is not found in the first qualifying PKLIST entry, significant network overhead might result from Db2 searching through the list. |
|
REOPT | Use the following guidelines to decide
which option to choose:
|
Because of performance costs when Db2 reoptimizes the access path at run time, minimize reoptimization when possible. |