TL;DR Summary: Bind Parameters in Db2 12 provides essential insights into using bind parameters effectively, optimizing SQL statement performance. This guide covers key parameters, their valid values, and resource management.
- Explains key bind parameters and their valid values.
- Offers guidance on SQL execution strategies and resource management.
| Option | Valid values | Plan | Pckg | Trigr | Qry |
|---|---|---|---|---|---|
|
ACQUIRE Whether to acquire resources specified in the DBRM at first access or allocation |
USE, ALLOCATE | X | |||
|
ACTION |
REPLACE, ADD | X, BO | X, BO | ||
|
ACTION |
REPLACE(RPLVER) | X, BO | |||
|
ACTION |
REPLACE(RETAIN) | X,BO | |||
|
ACOMPARE Determines whether the new access paths are different from the older access paths |
NO, NONE, WARN, ERROR | X | X | X | |
|
APPLCOMPAT Specifies the package compatibility level behavior for statis SQL |
V10R1, V11R1 | X | X | ||
|
APRETAINUP Whether or not DB2 retains an old package copy when access paths of the old copy are identical to the incoming copy. Applies to PLANMGMT(BASIC) or PLANMGMT(EXTENDED) |
YES, NO | RO | X | ||
|
APREUSE Specifies whether DB2 tries to reuse previous access paths for SQL statements in a package |
NO, NONE, ERROR, WARN | X | X | ||
|
APPREUSE SOURCE |
CURRENT, PREVIOUS, ORIGINAL | RO | X | ||
|
ARCHIVE SENSITIVE Whether references to archive-enabled tables are affected by SYSIBMADM.GET_ARCHIVE |
YES, NO | X | X | ||
|
BUSTIME SENSITIVE |
YES, NO | X | X | ||
|
CACHESIZE Size (in bytes) of the authorization cache acquired in the EDM pool for the plan |
Value of PLAN AUTH CACHE; Decimal |
X | |||
|
COPY Determines that you are copying an existing package and names the package |
Collection-id, package-id, COPYVER | X, BO | |||
|
CONCENTRATE STMT |
NO, YES | X | |||
|
CONCURRENT ACCESS RESOLUTION |
WAITFOROUTCOME USECURRENTLYCOMMITTED Default depends on SKIPUNCI setting |
X | X | X | |
|
CURRENTDATA Whether to require data currency for RO and ambiguous cursors when isolation level is CS |
YES, NO | X | X | X | |
|
CURRENT SERVER |
Location-name | X | |||
|
DBPROTOCOL Protocol to use when connecting to a remote site that is identified by a three-part name |
DRDA, DRDACBF (package only) | X | X | ||
|
DESCSTAT Whether DB2 builds a DESCRIBE SQL descriptor when binding statis SQL statements |
NO, YES | X | X | ||
|
DEFER Whether to defer preparation of dynamic SQL statements that refer to remote objects or to prepare them immediately. DEFER(PREPARE) is assumed for REOPT(AUTO, ALWAYS and ONCE) |
DEFER(PREPARE) NODEFER(PREPARE) DEFER(INHERITFROMPLAN) |
X | X | ||
|
DEGREE Whether to attempt to run a query using parallel processing to maximise performance |
1, ANY | X | X | ||
|
DEPLOY Deploys a native SQL procedure |
(collection-id.package-id), COPYVER(version-id) |
X | |||
|
DISCONNECT Determines which remote connections to destroy during commit operations |
EXPLICIT, AUTOMATIC, CONDITIONAL | X | |||
| DYNAMICRULES | RUN, BIND, DEFINEBIND(PKG ONLY), DEFINERUN(PKG ONLY), INVOKEBIND(PKG ONLY), INVOKERUN(PKG ONLY) | X | X | ||
|
DYNAMICRULES |
DEFINEBIND, DEFINERUN, INVOKEBIND, INVOKE RUN |
X | |||
|
Option Determines which values apply at runtime for dynamic SQL attributes |
Valid values | Plan | Pckg | Trigr | Qry |
|
ENABLE/DISABLE Determines which connections can use the plan or package |
BATCH, CICS, DB2CALL, DLIBATCH, IMS, IMSBMP, IMSMPP, RRSAF, * | X | X | ||
|
|
REMOTE | X | |||
|
ENCODING Application encoding for all static statements in the plan or package (defaults to installed selection) |
ASCII, EBCDIC, UNICODE, ccsid | X | X | ||
|
EXPLAIN Whether to populate the PLAN_TABLE with information about the SQL statements |
NO, YES, ONLY | X | X | X | |
|
EXTENDED INDICATOR |
NO, YES | X | |||
|
FILTER Allows you to delete a set of queries in the SYSIBM.SYSQUERY table under a {tag} value specified by the SYSQUERY.USERFILTER column. Also works with FREE QUERY |
‘filter-name’ | FQ | |||
|
FLAG Determines what messages to display |
I, W, E, C | X | X | X | |
|
GENERIC Specifies one or more bind options that are supported by the target server, but are not supported by DB2 for z/OS as options for BIND PACKAGE or REBIND PACKAGE |
‘string’ | X | X | ||
|
GETACCEL ARCHIVE |
NO, YES | X | X | ||
|
IMMEDIATE Whether immediate writes will be done for updates made to GBP-dependent page sets/partitions |
NO, YES, INHERITFROMPLAN | X | X | ||
|
ISOLATION Determines how far to isolate an application from the effects of other running applications |
RR, RS, CS, UR, NC | X | X | X | |
|
KEEPDYNAMIC Determines whether DB2 keeps dynamic SQL statements after commit points |
NO, YES | X | X | ||
|
LIBRARY Determines which partitioned data set to search for DBRMs listed in the member option |
Dbrm-pds-name | X, BO | |||
|
LOOKUP Determines whether a query has matching access plan hint information in the SYSIBM.SYSQUERYPLAN table |
NO, YES | BQ | |||
|
MEMBER Determines what DBRMs to include in the package |
Dbrm-member-name | X, BO | |||
|
OPTHINT Controls whether query optimization hints are used for static SQL |
Hint-id | X | X | ||
|
OPTIONS Specifies which bind options to use for the new package |
COMPOSITE, COMMAND | X, BC | |||
|
OWNER Determines the authorization ID or the owner of the object (plan or package) |
Authorization-id | X | X | ||
|
PACKAGE |
Location-name.collection-id. package-id(version-id) |
X | |||
|
PACKAGE |
(*) – Rebind Only | X,RO | |||
|
PATH SQL path that DB2 uses to resolve unqualified UDTs, functions and stored procedure names |
Schema-name, USER, (schema-name, (USER)…) | X | X | ||
|
PATHDEFAULT Resets PATH for package or plan to “SYSIBM”, “SYSFUN”, “SYSPROC”, or plan/package qualifier |
Mutually exclusive with PATH | X | X | ||
|
PKLIST or NOPKLIST Determines which package to include for the package list in the plan |
(Location-name.collection-id.package-id…) PKLIST only | X | |||
|
PROGAUTH Whether DB2 performs authorization checking to determine whether DB2 can execute a plan |
DISABLE, ENABLE | X | |||
|
PLANMGMT Retains, during a rebind operation, all relevant package information (metadata, query text, dependencies, authorizations, access paths, and so on) in catalog tables and in the directory |
OFF, BASIC, EXTENDED | X | X | ||
|
QUALIFIER Determines the implicit qualifier for unqualified names of objects in the plan or package |
Qualifier-name | X | X | ||
| QUERY ACCELERATION | NONE, ENABLE, ALL ENABLEWITHFALLBACK, ELIGIBLE | X | X | ||
|
Option Whether a static SQL query is bound for acceleration |
Valid values | Plan | Pckg | Trigr | Qry |
|
QUERYID Frees entries from SYSIBM.SYSQUERY with same value (or ALL), and corresponding entries in SYSIBM.SYSQUERYPLAN table or SYSIBM.SYSQUERYOPTS table |
‘number’ ALL | FQ | |||
|
RECORD TEMPORAL HISTORY |
YES, NO | X | |||
|
RELEASE Determines when to release resources that the program uses, either at commit or at termination |
COMMIT, DEALLOCATE, INHERITFROMPLAN | X | X | X | |
|
REOPT If access path is determined at runtime (host variables, parameter markers, special registers) |
ONCE, ALWAYS, AUTO, NONE | X | X | ||
|
ROUNDING Specifies the rounding mode at bind time |
CEILING, DOWN, FLOOR HALFDOWN, HALFEVEN, HALFUP, UP | X | X | ||
|
SQLERROR Whether to create a package if the package contains an SQL error |
NOPACKAGE, CONTINUE, CHECK | X | |||
|
SQLRULES Whether a Type 2 connection can bemade according to DB2 rules for an existing connection |
DB2, STD | X | |||
|
SWITCH Restores all previous or original package information in the catalog tables and directory to that of the specified package copy |
PREVIOUS, ORIGINAL | X | X | ||
|
SYSTIME SENSITIVE |
YES, NO | X | X | ||
|
VALIDATE Whether to recheck at runtime ‘not found’ and ‘not authorized’ errors found at bind time |
RUN, BIND | X | X |
BO = BIND only, BC = BIND copy, RO = REBIND only, FQ = FREE QUERY, BQ = BIND QUERY
BOLD/UNDERSCORE = default
Disclaimer: This Db2® 12 for z/OS Reference Guide was developed to help users in their daily activities in administrating and programming in Db2 for z/OS. There are no guarantees expressed or implied with the contents in this guide. We want to provide a quality and useful reference for users. Please notify us of any mistakes or errors in this reference guide at blogs@bmc.com. Db2 is a registered trademark of the IBM Corporation.
Db2 Guide
- Db2 12 for z/OS: An Introduction
- Scalar Functions in Db2 12
- How to Read IBM<sup>®</sup> Syntax Diagrams for Db2 12
- Language Elements in Db2 12
- Aggregate Functions in Db2 12
- Table & Row Functions in Db2 12
- Queries in Db2 12
- Commands in Db2 12
- SQL Control Statements in Db2 12
- Explain Tables in Db2 12
- DSNZPARMs in Db2 12
- Bind Parameters in Db2 12
- Db2 Limits in Db2 12
- SQL Communication Area (SQLCA) in Db2 12
- REXX SQLCA in Db2 12
- Get Diagnostics in Db2 12
- IFCIDS in Db2 12
- Catalog Tables in Db2 12
- Exceptions in Db2 12
- XML Schema Repository Tables in Db2 12
- Updatable Catalog Statistics in Db2 12
- IBM Utilities in Db2 12
- SQL Positive Return Codes in Db2 12
- SQL Error Return Codes in Db2 12
These postings are my own and do not necessarily represent BMC's position, strategies, or opinion.