Mainframe Blog

Bind Parameters in Db2 12

5 minute read
Stephen Watts

(This article is part of our Db2 Guide. Use the right-hand menu to navigate.)

Option Valid values Plan Pckg Trigr Qry
ACQUIRE USE, ALLOCATE X
Whether to acquire resources specified in the DBRM at first access or allocation
ACTION REPLACE, ADD X, BO X, BO
REPLACE(RPLVER) X, BO
REPLACE(RETAIN) X,BO
Whether object (plan or package) replaces an existing object with same name or is new
ACOMPARE NO, NONE, WARN, ERROR X X X
Determines whether the new access paths are different from the older access paths
APPLCOMPAT V10R1, V11R1 X X
Specifies the package compatibility level behavior for statis SQL
APRETAINUP YES, NO RO X
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)
APREUSE NO, NONE, ERROR, WARN X X
Specifies whether DB2 tries to reuse previous access paths for SQL statements in a package
APPREUSE

SOURCE

CURRENT, PREVIOUS, ORIGINAL RO X
Specifies whether DB2 tries to reuse previous access paths for SQL statements in a package
ARCHIVE SENSITIVE YES, NO X X
Whether references to archive-enabled tables are affected by SYSIBMADM.GET_ARCHIVE
BUSTIME

SENSITIVE

YES, NO X X
Whether references to application-period temporal tables are affected by CURRENT TEMPORAL BUSINESS_TIME special register
CACHESIZE Value of PLAN AUTH CACHE;

Decimal

X
Size (in bytes) of the authorization cache acquired in the EDM pool for the plan
COPY Collection-id, package-id, COPYVER X, BO
Determines that you are copying an existing package and names the package
CONCENTRATE

STMT

NO, YES X
Whether to enforce statement concentration at the package level
CONCURRENT

ACCESS

RESOLUTION

WAITFOROUTCOME

USECURRENTLYCOMMITTED

Default depends on SKIPUNCI setting

X X X
Determines which concurrent access resolution option to use for statements in a package
CURRENTDATA YES, NO X X X
Whether to require data currency for RO and ambiguous cursors when isolation level is CS
CURRENT

SERVER

Location-name X
Determines the location to connect to before running the plan
DBPROTOCOL DRDA, DRDACBF (package only) X X
Protocol to use when connecting to a remote site that is identified by a three-part name
DESCSTAT NO, YES X X
Whether DB2 builds a DESCRIBE SQL descriptor when binding statis SQL statements
DEFER DEFER(PREPARE)

NODEFER(PREPARE)

DEFER(INHERITFROMPLAN)

X X
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)

DEGREE 1, ANY X X
Whether to attempt to run a query using parallel processing to maximise performance
DEPLOY (collection-id.package-id),

COPYVER(version-id)

X
Deploys a native SQL procedure
DISCONNECT EXPLICIT, AUTOMATIC, CONDITIONAL X
Determines which remote connections to destroy during commit operations
DYNAMICRULES RUN, BIND, DEFINEBIND(PKG ONLY), DEFINERUN(PKG ONLY), INVOKEBIND(PKG ONLY), INVOKERUN(PKG ONLY) X X
DEFINEBIND, DEFINERUN,

INVOKEBIND, INVOKE RUN

X
Option Valid values Plan Pckg Trigr Qry
Determines which values apply at runtime for dynamic SQL attributes
ENABLE/

DISABLE

BATCH, CICS, DB2CALL, DLIBATCH, IMS, IMSBMP, IMSMPP, RRSAF, * X X
REMOTE X
Determines which connections can use the plan or package
ENCODING ASCII, EBCDIC, UNICODE, ccsid X X
Application encoding for all static statements in the plan or package (defaults to installed selection)
EXPLAIN NO, YES, ONLY X X X
Whether to populate the PLAN_TABLE with information about the SQL statements
EXTENDED

INDICATOR

NO, YES X
Determines if DB2 recognizes extended indicator variables when associated package is run
FILTER ‘filter-name’ FQ
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
FLAG I, W, E, C X X X
Determines what messages to display
GENERIC ‘string’ X X
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
GETACCEL

ARCHIVE

NO, YES X X
Whether a static SQL query bound for acceleration retrieves archive data
IMMEDIATE NO, YES, INHERITFROMPLAN X X
Whether immediate writes will be done for updates made to GBP-dependent page sets/partitions
ISOLATION RR, RS, CS, UR, NC X X X
Determines how far to isolate an application from the effects of other running applications
KEEPDYNAMIC NO, YES X X
Determines whether DB2 keeps dynamic SQL statements after commit points
LIBRARY Dbrm-pds-name X, BO
Determines which partitioned data set to search for DBRMs listed in the member option
LOOKUP NO, YES BQ
Determines whether a query has matching access plan hint information in the SYSIBM.SYSQUERYPLAN table
MEMBER Dbrm-member-name X, BO
Determines what DBRMs to include in the package
OPTHINT Hint-id X X
Controls whether query optimization hints are used for static SQL
OPTIONS COMPOSITE, COMMAND X, BC
Specifies which bind options to use for the new package
OWNER Authorization-id X X
Determines the authorization ID or the owner of the object (plan or package)
PACKAGE Location-name.collection-id.

package-id(version-id)

X
(*) – Rebind Only X,RO
Determines which package or packages to bind or rebind
PATH Schema-name, USER, (schema-name, (USER)…) X X
SQL path that DB2 uses to resolve unqualified UDTs, functions and stored procedure names
PATHDEFAULT Mutually exclusive with PATH X X
Resets PATH for package or plan to “SYSIBM”, “SYSFUN”, “SYSPROC”, or plan/package qualifier
PKLIST or NOPKLIST (Location-name.collection-id.package-id…) PKLIST only X
Determines which package to include for the package list in the plan
PROGAUTH DISABLE, ENABLE X
Whether DB2 performs authorization checking to determine whether DB2 can execute a plan
PLANMGMT OFF, BASIC, EXTENDED X X
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
QUALIFIER Qualifier-name X X
Determines the implicit qualifier for unqualified names of objects in the plan or package
QUERY ACCELERATION NONE, ENABLE, ALL ENABLEWITHFALLBACK, ELIGIBLE X X
Option Valid values Plan Pckg Trigr Qry
Whether a static SQL query is bound for acceleration
QUERYID number’ ALL FQ
Frees entries from SYSIBM.SYSQUERY with same value (or ALL), and corresponding entries in SYSIBM.SYSQUERYPLAN table or SYSIBM.SYSQUERYOPTS table
RECORD

TEMPORAL

HISTORY

YES, NO X
Whether changes to data in a system-period temporal table that are made by static or dynamic SQL statements cause changes to corresponding history table of the system-period temporal table
RELEASE COMMIT, DEALLOCATE, INHERITFROMPLAN X X X
Determines when to release resources that the program uses, either at commit or at termination
REOPT ONCE, ALWAYS, AUTO, NONE X X
If access path is determined at runtime (host variables, parameter markers, special registers)
ROUNDING CEILING, DOWN, FLOOR HALFDOWN, HALFEVEN, HALFUP, UP X X
Specifies the rounding mode at bind time
SQLERROR NOPACKAGE, CONTINUE, CHECK X
Whether to create a package if the package contains an SQL error
SQLRULES DB2, STD X
Whether a Type 2 connection can bemade according to DB2 rules for an existing connection
SWITCH PREVIOUS, ORIGINAL X X
Restores all previous or original package information in the catalog tables and directory to that of the specified package copy
SYSTIME

SENSITIVE

YES, NO X X
Whether references to system-period temporal tables are affected by value of CURRENT TEMPORAL TEMPORAL SYSTEM_TIME
VALIDATE RUN, BIND X X
Whether to recheck at runtime ‘not found’ and ‘not authorized’ errors found at bind time

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 [email protected]. Db2 is a registered trademark of the IBM Corporation.

Order now! Get your free Db2 collateral from BMC!

Get your free Reference Guide and Catalog Tables Poster for z/OS from BMC. This collateral helps Db2 for z/OS users with daily activities in administering and programming for Db2 on z/OS.


These postings are my own and do not necessarily represent BMC's position, strategies, or opinion.

See an error or have a suggestion? Please let us know by emailing [email protected].

Business, Faster than Humanly Possible

BMC empowers 86% of the Forbes Global 50 to accelerate business value faster than humanly possible. Our industry-leading portfolio unlocks human and machine potential to drive business growth, innovation, and sustainable success. BMC does this in a simple and optimized way by connecting people, systems, and data that power the world’s largest organizations so they can seize a competitive advantage.
Learn more about BMC ›

About the author

Stephen Watts

Stephen Watts (Birmingham, AL) contributes to a variety of publications including, Search Engine Journal, ITSM.Tools, IT Chronicles, DZone, and CompTIA.