Use the Captured SQL Bind Options window to specify the options for binding the
captured SQL statements in a package.
To open the Captured SQL Bind Options window:
In the Captured SQL Files viewer, click Bind Options.
DEFER
Indicates when to prepare dynamic SQL statements that refer to remote objects.
The following two keyword choices are available for this bind option:
DEFER(PREPARE)
Defers the preparation of dynamic SQL statements. A dynamic statement
prepares when DB2® first encounters a statement of the
type EXECUTE, OPEN, or DESCRIBE that refers to the dynamic statement.
If you choose the bind option REOPT(ALWAYS)
or REOPT(ONCE), DB2 sets this keyword choice
automatically.
NODEFER(PREPARE)
Prepares dynamic SQL statements immediately.
PKLIST
Lists the IDs of all the collections that are on the target server.
Include collections in the package list by selecting their
corresponding check boxes.
Tip: The order in which you select the
check boxes is the order in which the collections are ordered
in the package list. This order can affect the runtime
performance of your application program.
Determines the access path again at run time each time the
statement is run. DB2 determines access paths at both bind
time and run time for statements that contain one or more of
the following variables:
Host variables
Parameter markers
Special registers
At run time, DB2 uses the values in those variables to determine
the access paths.
Determines the access path for any dynamic statement only once,
at the first run time or at the first time the statement is opened.
This access path is used until the prepared statement is invalidated
or removed from the dynamic statement cache and needs to be prepared
again.
Does not determine an access path at run time. This keyword is the
default choice for this bind option.
CURRENTDATA
Determines whether to require data currency for read-only and ambiguous
cursors when the isolation level of cursor stability is in effect.
It also determines whether block fetching can be used for distributed,
ambiguous cursors.
The following two keyword choices are available for this bind option:
YES
Specifies that currency is required for read-only and ambiguous
cursors. DB2 acquires page or row locks to ensure data currency.
Block fetching for distributed, ambiguous cursors is inhibited.
NO
Specifies that currency is not required for read-only and ambiguous
cursors. Block fetching for distributed, ambiguous cursors is allowed.
If your application attempts to dynamically prepare and run a
DELETE WHERE CURRENT OF statement against
an ambiguous cursor after that cursor is opened, this choice is
not recommended.
You receive a negative SQLCODE if your application attempts a
DELETE WHERE CURRENT OF statement for
any of the following cursors:
A cursor that is using block fetching
A cursor that is using query parallelism
A cursor that is positioned on a row that is modified by
this or another application process
This keyword is the default choice for this bind option.
KEEPDYNAMIC
Determines whether DB2 keeps dynamic SQL statements after commit points.
The following two keyword choices are available for this bind option:
YES
Specifies that DB2 keeps dynamic SQL statements after commit points.
If you select this choice, the application does not need to
prepare an SQL statement after every commit point.
DB2 keeps the dynamic SQL statement until one of the following events
occurs:
The application process ends.
A rollback operation occurs.
The application runs an explicit PREPARE statement with the
same statement identifier.
If you select this choice and the prepared statement cache
is active, DB2 keeps a copy of the prepared statement in the cache.
If the prepared statement cache is not active, DB2 keeps only the
SQL statement string past a commit point. DB2 then implicitly prepares
the SQL statement if the application runs an OPEN, EXECUTE, or
DESCRIBE operation for that statement.
If you select this choice, DDF server threads that are used
to run KEEPDYNAMIC(YES) packages will remain active.
Active DDF server threads are subject to idle thread timeouts.
If you select this choice, you must not select
REOPT(ALWAYS) because the two are
mutually exclusive. However, you can use this choice with
REOPT(ONCE).
Performance tip: This choice results in
improved performance if your DRDA client application uses a cursor
that is defined with the WITH HOLD attribute.
DB2 automatically closes a held cursor when there are no more rows
to retrieve, which eliminates an extra network message.
NO
Specifies that DB2 does not keep dynamic SQL statements after commit
points. This keyword is the default choice for this bind option.
DBPROTOCOL
Indicates which protocol to use when connecting to a remote site that
is identified by a three-part name statement.
The following two keyword choices are available for this bind option:
PRIVATE
DB2 uses DB2 private protocol access for accessing remote data that is
specified with three-part names.
A warning is issued if you bind a package with this keyword.
An application that uses DB2 private protocol access cannot
include SQL statements that were added to DB2 after Version 7.
DRDA
DB2 uses DRDA access to access remote data that is specified with
three-part names. Select this choice to inform DB2 that the
three-part name statements in the package are to be converted
to DRDA protocol.
This keyword is the default choice for this bind option.
With this choice, a package must be bound to each remote site that
is referenced by a three-part name statement.
Options
Specify additional options to use for binding the captured SQL statements
in a package.