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:

Use the bind options that are shown in the following table:

Table 1. Recommended bind option values for 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: Reduce the number of packages per collection that Db2 must search. The following example specifies only one package in each collection: `PKLIST(S1.COLLA.PGM1, S1.COLLB.PGM2)` Reduce the number of package collections at each location that Db2 must search. The following example specifies only one package collection at each location: `PKLIST(S1.COLLA., S2.COLLB.)` Reduce the number of collections that are used for each application. The following example specifies only one collection to search: `PKLIST(.COLLA.)`	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: `PKLIST(LOCB.COLLA., LOCB.COLLB.)` If the intended package is in collection COLLB, ensure that Db2 searches that collection first by executing the following SQL statement: `SET CURRENT PACKAGESET = 'COLLB';` Alternatively, you can list COLLB first in the PKLIST bind option: `PKLIST(LOCB.COLLB., LOCB.COLLA.)` 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: Use the REOPT(AUTO) option when the following conditions are true: You are using the dynamic statement cache. You want Db2 to decide if a new access path is needed. Your dynamic SQL statements are executed many times with possibly different input variables. Similar input variables tend to be executed consecutively. Use the REOPT(ALWAYS) option on only packages or plans that contain statements that perform poorly because of a bad access path. If you specify REOPT(ALWAYS) when you bind a plan that contains statements that use Db2 private protocol access to access remote data, Db2 prepares those statements twice. Use the REOPT(ONCE) option when the following conditions are true: You are using the dynamic statement cache. You have plans or packages that contain dynamic SQL statements that perform poorly because of access path selection. Your dynamic SQL statements are executed many times with possibly different input variables. Use the REOPT(NONE) option when you bind a plan or package that contains statements that use Db2 private protocol access.	Because of performance costs when Db2 reoptimizes the access path at run time, minimize reoptimization when possible.