BIND command
The BIND command invokes the bind utility, which prepares SQL statements stored in the bind file generated by the precompiler, and creates a package that is stored in the database.
Scope
This command can be issued from any database partition in db2nodes.cfg. It updates the database catalogs on the catalog database partition. Its effects are visible to all database partitions.
Authorization
- DBADM authority
- If EXPLAIN ONLY is specified, EXPLAIN authority or an authority that implicitly includes EXPLAIN is sufficient.
- If a package does not exist, one of the following privileges:
- If the schema name of the package does not exist, BINDADD and IMPLICIT_SCHEMA authority on the database.
- If the schema name of the package does exist:
- SCHEMAADM authority on the schema
- BINDADD authority and CREATEIN privilege on the schema
- If the package exists, one of the following privileges:
- SCHEMAADM authority on the schema
- ALTERIN privilege on the schema
- BIND privilege on the package
- INSERT privilege on the explain tables
- DATAACCESS authority
The user also needs all privileges required to compile any static SQL statements in the application. Privileges granted to groups are not used for authorization checking of static statements.
Required connection
Database. If implicit connect is enabled, a connection to the default database is established.
Command syntax
- For Db2®
- For Db2 on servers other than Linux®, Windows and UNIX
- Notes:
- 1 If the server does not support the DATETIME DEF option, it is mapped to DATETIME ISO.
- 2 The DEGREE option is only supported by DRDA Level 2 Application Servers.
- 3 DRDA defines the EXPLAIN option to have the value YES or NO. If the server does not support the EXPLAIN YES option, the value is mapped to EXPLAIN ALL.
Command parameters
- filename
- Specifies the name of the bind file that was generated when the application program was
precompiled, or a list file containing the names of several bind files. Bind files have the
extension
.bnd
. The full path name can be specified.If a list file is specified, the @ character must be the first character of the list file name. The list file can contain several lines of bind file names. Bind files listed on the same line must be separated by plus (+) characters, but a + cannot appear in front of the first file listed on each line, or after the last bind file listed. For example,
is a list file that contains the following bind files:/u/smith/sqllib/bnd/@all.lst
mybind1.bnd+mybind.bnd2+mybind3.bnd+ mybind4.bnd+mybind5.bnd+ mybind6.bnd+ mybind7.bnd
When just the bind file name without any path is specified, the file would be first searched for in the current directory. If the file is found, it would be picked up and the package would be created. If the file is not found in the current directory, then the file would be automatically picked up from the instance or install path.
- ACTION
- Indicates whether the package can be added or replaced.
- ADD
- Indicates that the named package does not exist, and that a new package is to be created. If the package already exists, execution stops, and a diagnostic error message is returned.
- REPLACE
- Indicates that the existing package is to be replaced by a new
one with the same package name and creator. This is the default value
for the ACTION option.
- RETAIN
- Indicates whether BIND and EXECUTE authorities are to be preserved
when a package is replaced. If ownership of the package changes,
the new owner grants the BIND and EXECUTE authority to the previous
package owner.
- NO
- Does not preserve BIND and EXECUTE authorities when a package is replaced. This value is not supported by Db2.
- YES
- Preserves BIND and EXECUTE authorities when a package is replaced. This is the default value.
- REPLVER version-id
- Replaces a specific version of a package. The version identifier specifies which version of the package is to be replaced. If the specified version does not exist, an error is returned. If the REPLVER option of REPLACE is not specified, and a package already exists that matches the package name, creator, and version of the package being bound, that package will be replaced; if not, a new package will be added.
- APREUSE
- Specifies whether static SQL access plans are to be reused. When
this option is enabled, the query compiler will attempt to reuse the
access plans for the statement in any existing packages during the
bind and during future implicit and explicit rebinds.
- YES
- The query compiler will attempt to reuse the access plans for the statements in the package. If there is an existing package, the query compiler will attempt to reuse the access plan for every statement that can be matched with a statement in the new bind file. For a statement to match, the statement text must be identical and the section number for the statement in the existing package must match what the section number will be for the statement in the new package.
- NO
- The query compiler will not attempt to reuse access plans for the statements in the package. This is the default setting.
- BLOCKING
- Specifies the type of row blocking for cursors. The blocking of
row data that contains references to LOB column data types is also
supported in partitioned database environments.
- ALL
- For cursors that are specified with the FOR READ ONLY clause or
cursors not specified as FOR UPDATE, blocking occurs.
Ambiguous cursors are treated as read-only.
- NO
- Blocking does not occur for any cursor.
For the definition of a read-only cursor and an ambiguous cursor, refer to DECLARE CURSOR statement.
Ambiguous cursors are treated as updatable.
- UNAMBIG
- For cursors that are specified with the FOR READ ONLY clause,
blocking occurs.
Cursors that are not declared with the FOR READ ONLY or FOR UPDATE clause which are not ambiguous and are read-only will be blocked. Ambiguous cursors will not be blocked.
Ambiguous cursors are treated as updatable.
- BUSTIMESENSITIVE
- Indicates whether references to application-period temporal tables
in static and dynamic SQL statements are affected by the value of
the CURRENT TEMPORAL BUSINESS_TIME special register.
- YES
- References to application-period temporal tables are affected by the value of the CURRENT TEMPORAL BUSINESS_TIME special register. This value is the default value.
- NO
- References to application-period temporal tables are not affected by the value of the CURRENT TEMPORAL BUSINESS_TIME special register.
- CCSIDG double-ccsid
- An integer specifying the coded character set identifier (CCSID) to be used for double byte characters in character column definitions (without a specific CCSID clause) in CREATE and ALTER TABLE SQL statements. This option is not supported by the server for Db2. The DRDA server will use a built-in default value if this option is not specified.
- CCSIDM mixed-ccsid
- An integer specifying the coded character set identifier (CCSID) to be used for mixed byte characters in character column definitions (without a specific CCSID clause) in CREATE and ALTER TABLE SQL statements. This option is not supported by the server for Db2. The DRDA server will use a built-in default value if this option is not specified.
- CCSIDS sbcs-ccsid
- An integer specifying the coded character set identifier (CCSID) to be used for single byte characters in character column definitions (without a specific CCSID clause) in CREATE and ALTER TABLE SQL statements. This option is not supported by Db2. The DRDA server will use a built-in default value if this option is not specified.
- CHARSUB
- Designates the default character sub-type that is to be used for
column definitions in CREATE and ALTER TABLE SQL statements. This
precompile/bind option is not supported by the server for Db2.
- BIT
- Use the FOR BIT DATA SQL character sub-type in all new character columns for which an explicit sub-type is not specified.
- DEFAULT
- Use the target built-in default in all new character columns for which an explicit sub-type is not specified.
- MIXED
- Use the FOR MIXED DATA SQL character sub-type in all new character columns for which an explicit sub-type is not specified.
- SBCS
- Use the FOR SBCS DATA SQL character sub-type in all new character columns for which an explicit sub-type is not specified.
- CLIPKG cli-packages
- An integer between 3 and 30 specifying the number of CLI large packages to be created when binding CLI bind files against a database.
- CNULREQD
- This option is related to the LANGLEVEL precompile
option. It is valid only if the bind file is created from a C or a
C++ application. This bind option is not supported by the server for Db2.
- NO
- The application was coded on the basis of the LANGLEVEL SAA1 precompile option with respect to the null terminator in C string host variables.
- YES
- The application was coded on the basis of the LANGLEVEL MIA precompile option with respect to the null terminator in C string host variables.
- COLLECTION schema-name
- Specifies a 128-byte collection identifier for the package. If not specified, the authorization identifier for the user processing the package is used.
- CONCURRENTACCESSRESOLUTION
- Specifies the concurrent access resolution to use for statements
in the package.
- USE CURRENTLY COMMITTED
- Specifies that the database manager can use the currently committed version of the data for applicable scans when it is in the process of being updated or deleted. Rows in the process of being inserted can be skipped. This clause applies when the isolation level in effect is Cursor Stability or Read Stability (for Read Stability it skips uncommited inserts only) and is ignored otherwise. Applicable scans include read-only scans that can be part of a read-only statement as well as a non read-only statement. The settings for the registry variables DB2_EVALUNCOMMITTED, DB2_SKIPDELETED, and DB2_SKIPINSERTED do not apply to scans using currently committed. However, the settings for these registry variables still apply to scans that do not use currently committed.
- WAIT FOR OUTCOME
- Specifies Cursor Stability and higher scans wait for the commit or rollback when encountering data in the process of being updated or deleted. Rows in the process of being inserted are not skipped. The settings for the registry variables DB2_EVALUNCOMMITTED, DB2_SKIPDELETED, and DB2_SKIPINSERTED no longer apply.
- DATETIME
- Specifies the date and time format to be used.
- DEF
- Use a date and time format associated with the territory code of the database.
- EUR
- Use the IBM standard for Europe date and time format.
- ISO
- Use the date and time format of the International Standards Organization.
- JIS
- Use the date and time format of the Japanese Industrial Standard.
- LOC
- Use the date and time format in local form associated with the territory code of the database.
- USA
- Use the IBM standard for U.S. date and time format.
- DBPROTOCOL
- Specifies what protocol to use when connecting to a remote site that is identified by a three-part name statement. Supported by Db2 for z/OS® only. For a list of supported option values, refer to the documentation for Db2 for z/OS.
- DEC
- Specifies the maximum precision to be used in decimal arithmetic
operations. This precompile/bind option is not supported by the server
for Db2.
The DRDA server will use a
built-in default value if this option is not specified.
- 15
- 15-digit precision is used in decimal arithmetic operations.
- 31
- 31-digit precision is used in decimal arithmetic operations.
- DECDEL
- Designates whether a period (.) or a comma (,) will be used as
the decimal point indicator in decimal and floating point literals.
This precompile/bind option is not supported by the server for Db2.
The DRDA server will use a
built-in default value if this option is not specified.
- COMMA
- Use a comma (,) as the decimal point indicator.
- PERIOD
- Use a period (.) as the decimal point indicator.
- DEGREE
- Specifies the degree of parallelism for the execution of static
SQL statements in an SMP system. This option does not affect CREATE
INDEX parallelism.
- 1
- The execution of the statement will not use parallelism.
- degree-of-parallelism
- Specifies the degree of parallelism with which the statement can be executed, a value between 2 and 32 767 (inclusive).
- ANY
- Specifies that the execution of the statement can involve parallelism using a degree determined by the database manager.
- DYNAMICRULES
- Defines which rules apply to dynamic SQL at run time for the initial
setting of the values used for authorization ID and for the implicit
qualification of unqualified object references.
- RUN
- Specifies that the authorization ID of the user executing the package is to be used for authorization checking of dynamic SQL statements. The authorization ID will also be used as the default package qualifier for implicit qualification of unqualified object references within dynamic SQL statements. This is the default value.
- BIND
- Specifies that all of the rules that apply to static SQL for authorization and qualification are to be used at run time. That is, the authorization ID of the package owner is to be used for authorization checking of dynamic SQL statements, and the default package qualifier is to be used for implicit qualification of unqualified object references within dynamic SQL statements.
- DEFINERUN
- If the package is used within a routine context, the authorization
ID of the routine definer is to be used for authorization checking
and for implicit qualification of unqualified object references within
dynamic SQL statements within the routine.
If the package is used as a stand-alone application, dynamic SQL statements are processed as if the package were bound with DYNAMICRULES RUN.
- DEFINEBIND
- If the package is used within a routine context, the authorization
ID of the routine definer is to be used for authorization checking
and for implicit qualification of unqualified object references within
dynamic SQL statements within the routine.
If the package is used as a stand-alone application, dynamic SQL statements are processed as if the package were bound with DYNAMICRULES BIND.
- INVOKERUN
- If the package is used within a routine context, the current statement
authorization ID in effect when the routine is invoked is to be used
for authorization checking of dynamic SQL statements and for implicit
qualification of unqualified object references within dynamic SQL
statements within that routine.
If the package is used as a stand-alone application, dynamic SQL statements are processed as if the package were bound with DYNAMICRULES RUN.
- INVOKEBIND
- If the package is used within a routine context, the current statement
authorization ID in effect when the routine is invoked is to be used
for authorization checking of dynamic SQL statements and for implicit
qualification of unqualified object references within dynamic SQL
statements within that routine.
If the package is used as a stand-alone application, dynamic SQL statements are processed as if the package were bound with DYNAMICRULES BIND.
Because dynamic SQL statements will be using the authorization ID of the package owner in a package exhibiting bind behavior, the binder of the package should not have any authorities granted to them that the user of the package should not receive. Similarly, when defining a routine that will exhibit define behavior, the definer of the routine should not have any authorities granted to them that the user of the package should not receive since a dynamic statement will be using the authorization ID of the routine's definer.
The following dynamically prepared SQL statements cannot be used within a package that was not bound with DYNAMICRULES RUN: GRANT, REVOKE, ALTER, CREATE, DROP, COMMENT ON, RENAME, SET INTEGRITY, and SET EVENT MONITOR STATE.
- ENCODING
- Specifies the encoding for all host variables in static statements in the plan or package. Supported by Db2 for OS/390® only. For a list of supported option values, refer to the documentation for Db2 for OS/390.
- EXPLAIN
- Stores information in the Explain tables about the access plans
chosen for each SQL statement in the package.
- NO
- Explain information will not be captured.
- YES
- Explain tables will be populated with information about the chosen
access plan at prep/bind time for static statements and at run time
for incremental bind statements.
If the package is to be used for a routine and the package contains incremental bind statements, then the routine must be defined as MODIFIES SQL DATA. If this is not done, incremental bind statements in the package will cause a run time error (SQLSTATE 42985).
- REOPT
- Explain information for each reoptimizable incremental bind SQL
statement is placed in the explain tables at run time. In addition,
explain information is gathered for reoptimizable dynamic SQL statements
at run time, even if the CURRENT EXPLAIN MODE register is set to NO.
If the package is to be used for a routine, the routine must be defined as MODIFIES SQL DATA, otherwise incremental bind and dynamic statements in the package will cause a run time error (SQLSTATE 42985).
- ONLY
- The ONLY option allows you to explain statements without having the privilege to execute them. The explain tables are populated but no persistent package is created. If an existing package with the same name and version is encountered during the bind process, the existing package is neither dropped nor replaced even if you specified ACTION REPLACE. If an error occurs during population of the explain tables, explain information is not added for the statement that returned the error and for any statements that follow it.
- ALL
- Explain information for each eligible static SQL statement will
be placed in the Explain tables at prep/bind time. Explain information
for each eligible incremental bind SQL statement will be placed in
the explain tables at run time. In addition, explain information will
be gathered for eligible dynamic SQL statements at run time, even
if the CURRENT EXPLAIN MODE register is set to NO.
If the package is to be used for a routine, the routine must be defined as MODIFIES SQL DATA, otherwise incremental bind and dynamic statements in the package will cause a run time error (SQLSTATE 42985).
- EXPLSNAP
- Stores Explain Snapshot information in the Explain tables.
- NO
- An Explain Snapshot will not be captured.
- YES
- An Explain Snapshot for each eligible static SQL statement will
be placed in the Explain tables at prep/bind time for static statements
and at run time for incremental bind statements.
If the package is to be used for a routine and the package contains incremental bind statements, then the routine must be defined as MODIFIES SQL DATA or incremental bind statements in the package will cause a run time error (SQLSTATE 42985).
- REOPT
- Explain snapshot information for each reoptimizable incremental
bind SQL statement is placed in the explain tables at run time.
In addition, explain snapshot information is gathered for reoptimizable
dynamic SQL statements at run time, even if the CURRENT EXPLAIN SNAPSHOT
register is set to NO.
If the package is to be used for a routine, the routine must be defined as MODIFIES SQL DATA, otherwise incremental bind and dynamic statements in the package will cause a run time error (SQLSTATE 42985).
- ALL
- An Explain Snapshot for each eligible static SQL statement will
be placed in the Explain tables at prep/bind time. Explain snapshot
information for each eligible incremental bind SQL statement will
be placed in the Explain tables at run time. In addition, explain
snapshot information will be gathered for eligible dynamic SQL statements
at run time, even if the CURRENT EXPLAIN SNAPSHOT register is set
to
NO
.If the package is to be used for a routine, then the routine must be defined as MODIFIES SQL DATA, otherwise incremental bind and dynamic statements in the package will cause a run time error (SQLSTATE 42985).
- EXTENDEDINDICATOR
- Enables the recognition of extended indicator variable values
during the execution of the associated plan or package.
- NO
- Extended indicator variable values are not recognized. Indicator variables are normal indicator variables; negative indicator variable values imply null, and positive or zero values imply non-null. This is the default condition.
- YES
- Extended indicator variable values are recognized. Using any non-recognized indicator variable values, or using the default or unassigned indicator variable-based values in a non-supported location will cause Db2 database manager to generate an error message during execution of the bound statement.
- FEDERATED
- Specifies whether a static SQL statement in a package references
a nickname or a federated view. If this option is not specified and
a static SQL statement in the package references a nickname or a federated
view, a warning is returned and the package is created.
- NO
- A nickname or federated view is not referenced in the static SQL statements of the package. If a nickname or federated view is encountered in a static SQL statement during the prepare or bind phase of this package, an error is returned and the package is not created.
- YES
- A nickname or federated view can be referenced in the static SQL statements of the package. If no nicknames or federated views are encountered in static SQL statements during the prepare or bind of the package, no errors or warnings are returned and the package is created.
- FEDERATED_ASYNCHRONY
- Specifies the maximum number of asynchrony table queues (ATQs)
that the federated server supports in the access plan for programs
that use embedded SQL.
- ANY
- The optimizer determines the number of ATQs for the access plan. The optimizer assigns an ATQ to all eligible SHIP or remote pushdown operators in the plan. The value that is specified for DB2_MAX_ASYNC_REQUESTS_PER_QUERY server option limits the number of asynchronous requests.
- number_of_atqs_in_the_plan
- The number of ATQs in the plan. You specify a number in the range 0 to 32767.
- FUNCPATH
- Specifies the function path to be used in resolving user-defined
distinct types and functions in static SQL. If this option is not
specified, the default function path is "SYSIBM","SYSFUN",USER where
USER is the value of the USER special register.
- schema-name
- An SQL identifier, either ordinary or delimited, which identifies a schema that exists at the application server. No validation that the schema exists is made at precompile or at bind time. The same schema cannot appear more than once in the function path. The schema name SYSPUBLIC cannot be specified for the function path. The number of schemas that can be specified is limited by the length of the resulting function path, which cannot exceed 2048 bytes. The schema SYSIBM does not need to be explicitly specified; it is implicitly assumed to be the first schema if it is not included in the function path.
- GENERIC "string"
- Supports the binding of packages with
bind options that are supported by the target server, but that are not predefined in the
BIND PACKAGE or REBIND PACKAGE command. Do not use the
GENERIC bind option to specify bind options that are predefined in the
BIND or PRECOMPILE command syntax. For example, do not specify
predefined bind option such as KEEPDYNAMIC in the GENERIC bind
option. The syntax for the GENERIC bind option is as follows:
generic "option1 value1 option2 value2 ..."
Each option and value must be separated by one or more blank spaces. For example, you can use the following GENERIC bind option syntax to bind a package with OPT1, OPT2, and OPT3 options values:generic "opt1 value1 opt2 value2 opt3 value3"
The maximum length of the string is 4096 bytes. The maximum length of each option name in the string is 255 bytes.
The client passes the BIND command to the Db2 server without any client validation. The Db2 server processes the BIND command and returns an appropriate message.
In Version 10.5 Fix Pack 2 and later, you can specify the application compatibility special register setting (APPLCOMPAT) with the GENERIC option of the BIND command when you are connecting to Db2 for z/OS Version 11 and later servers.
For a list of GENERIC options that are supported by Db2 for z/OS servers, see
Specification of Db2 for z/OS bind options from Db2 clients
.For a list of GENERIC options that are supported by Db2 for IBM® i servers, see Table 1 in
How can unlike clients override package options such as NLSS sort sequences, system naming and separate date/time formats?
. - GRANT
- Note: If more than one of the GRANT, GRANT_GROUP, GRANT_USER, and GRANT_ROLE options are specified, only the last option specified is executed.
- authid
- Grants EXECUTE and BIND privileges to a specified user name, role name or group ID. The SQL GRANT statement and its rules are used to determine the type of authid when none of USER, GROUP, or ROLE is provided to specify the type of the grantee on a GRANT statement. For the rules, see GRANT (Role) statement.
- PUBLIC
- Grants EXECUTE and BIND privileges to PUBLIC.
- GRANT_GROUP group-name
- Grants EXECUTE and BIND privileges to a specified group name.
- GRANT_USER user-name
- Grants EXECUTE and BIND privileges to a specified user name.
- GRANT_ROLE role-name
- Grants EXECUTE and BIND privileges to a specified role name.
- INSERT
- Allows a program being precompiled or bound against a Db2 Enterprise Server
Edition server
to request that data inserts be buffered to increase performance.
- BUF
- Specifies that inserts from an application should be buffered.
- DEF
- Specifies that inserts from an application should not be buffered.
- ISOLATION
- Determines how far a program bound to this package can be isolated
from the effect of other executing programs.
- CS
- Specifies Cursor Stability as the isolation level.
- NC
- No Commit. Specifies that commitment control is not to be used. This isolation level is not supported by Db2.
- RR
- Specifies Repeatable Read as the isolation level.
- RS
- Specifies Read Stability as the isolation level. Read Stability ensures that the execution of SQL statements in the package is isolated from other application processes for rows read and changed by the application.
- UR
- Specifies Uncommitted Read as the isolation level.
- IMMEDWRITE
- Indicates whether immediate writes will be done for updates made to group buffer pool dependent page sets or database partitions. Supported by Db2 for OS/390 only. For a list of supported option values, refer to the documentation for Db2 for OS/390.
- KEEPDYNAMIC
This parameter specifies whether dynamic SQL statements are to be kept after commit points.
For a list of supported option values supported for Db2 for z/OS, see the Db2 for z/OS documentation.
Starting with Db2 Version 9.8 Fix Pack 2, you can modify the value of the KEEPDYNAMIC bind option for a package without requiring a fresh bind operation, thereby avoiding unnecessary recompilation until the next bind operation occurs. The KEEPDYNAMIC bind option controls how long the statement text and section associated with a prepared statement are kept in the SQL context, specifying whether dynamic SQL statements are kept after a COMMIT or ROLLBACK.
- YES
Instructs the SQL context to keep the statement text and section associated with prepared statements indefinitely. Dynamic SQL statements are kept across transactions. All packages bound with KEEPDYNAMIC YES are by default compatible with the existing package cache behavior.
- No
Instructs the SQL context to remove the statement text and section associated with prepared statements at the end of each unit of work. Inactive dynamic SQL statements prepared in a package bound with KEEPDYNAMIC NO are removed from the SQL context during a COMMIT or ROLLBACK operation. The statements must be prepared again in a new transaction. The client, driver, or application needs to prepare any dynamic SQL statement it wants to reuse in a new unit of work again. If the package is executed by a remote application, executable versions for prepared statements are disassociated from the application SQL context when the transaction ends.
Active dynamic SQL statements must be kept until the next COMMIT or ROLLBACK operation where they are inactive. Here are some situations where dynamic SQL statements can be active at transaction boundaries:- Cursors declared using the WITH HOLD option are open at a commit point.
- A dynamic SQL statement is executing a COMMIT or ROLLBACK operation.
- A dynamic SQL statement invokes a stored procedure or a user defined function that is executing COMMIT or ROLLBACK operation.
- MESSAGES message-file
- Specifies the destination for warning, error, and completion status messages. A message file is created whether the bind is successful or not. If a message file name is not specified, the messages are written to standard output. If the complete path to the file is not specified, the current directory is used. If the name of an existing file is specified, the contents of the file are overwritten.
- OPTHINT
- Controls whether query optimization hints are used for static SQL. Supported by Db2 for OS/390 only. For a list of supported option values, refer to the documentation for Db2 for OS/390.
- OPTPROFILE optimization-profile-name
- Specifies the name of an existing optimization profile to be used
for all static statements in the package. The default value of the
option is an empty string. The value also applies as the default for
dynamic preparation of DML statements for which the CURRENT OPTIMIZATION
PROFILE special register is null. If the specified name is unqualified,
it is an SQL identifier, which is implicitly qualified by the QUALIFIER bind
option.
The BIND command does not process the optimization file, but only validates that the name is syntactically valid. Therefore if the optimization profile does not exist or is invalid, an SQL0437W warning with reason code 13 will not occur until a DML statement is optimized using that optimization profile.
- OS400NAMING
- Specifies which naming option is to be used when accessing Db2 for System i® data. Supported by Db2 for System i only. For a list of supported option values, refer to the documentation for Db2 for IBM i.
- OWNER authorization-id
- Designates a 128-byte authorization identifier for the package owner. The owner must have the privileges required to execute the SQL statements contained in the package. Only a user with DBADM authority can specify an authorization identifier other than the user ID. The default value is the authorization ID of the invoker of the precompile/bind process. SYSIBM, SYSCAT, and SYSSTAT are not valid values for this option. The authorization-id must be a user. A role or a group cannot be specified using the OWNER option.
- PATH
- Specifies the function path to be used in resolving user-defined
distinct types and functions in static SQL. If this option is not
specified, the default function path is "SYSIBM","SYSFUN",USER where
USER is the value of the USER special register.
- schema-name
- An SQL identifier, either ordinary or delimited, which identifies a schema that exists at the application server. No validation that the schema exists is made at precompile or at bind time.
- QUALIFIER qualifier-name
- Provides a 128-byte implicit qualifier for unqualified objects contained in the package. The default is the owner's authorization ID, whether or not OWNER is explicitly specified.
- QUERYOPT optimization-level
- Indicates the required level of optimization for all static SQL statements contained in the package. The default value is 5. The SET CURRENT QUERY OPTIMIZATION statement describes the complete range of optimization levels available.
- RELEASE
- Indicates whether resources are
released at each COMMIT point, or when the application terminates. This DRDA precompile/bind option is not supported by Db2. For Db2 for z/OS Version 10 servers
or later, the default value is DEALLOCATE.
- COMMIT
- Release resources at each COMMIT point. Used for dynamic SQL statements.
- DEALLOCATE
- Release resources only when the application terminates.
- SORTSEQ
- Specifies which sort sequence table to use on System i. Supported by Db2 for IBM i only. For a list of supported option values, refer to the documentation for Db2 for System i.
- SQLERROR
- Indicates whether to create a package or a bind file if an error
is encountered.
- CHECK
- Specifies that the target system performs all syntax and semantic checks on the SQL statements being bound. A package will not be created as part of this process. If, while binding, an existing package with the same name and version is encountered, the existing package is neither dropped nor replaced even if ACTION REPLACE was specified.
- CONTINUE
- Creates a package, even if errors occur when binding SQL statements. Those statements that failed to bind for authorization or existence reasons can be incrementally bound at execution time if VALIDATE RUN is also specified. Any attempt to execute them at run time generates an error (SQLCODE -525, SQLSTATE 51015).
- NOPACKAGE
- A package or a bind file is not created if an error is encountered.
- REOPT
- Specifies whether to have Db2 determine
an access path at run time using values for host variables, parameter markers, global variables, and
special registers. Valid values are:
- NONE
- The access path for a given SQL statement containing host variables, parameter markers, global variables, or special registers will not be optimized using real values. The default estimates for the these variables is used, and the plan is cached and will be used subsequently. This is the default value.
- ONCE
- The access path for a given SQL statement will be optimized using the real values of the host variables, parameter markers, global variables, or special registers when the query is first executed. This plan is cached and used subsequently.
- ALWAYS
- The access path for a given SQL statement will always be compiled and reoptimized using the values of the host variables, parameter markers, global variables, or special registers that are known each time the query is executed.
- REOPT | NOREOPT VARS
- These options have been replaced by REOPT ALWAYS and REOPT NONE; however, they are still supported for previous compatibility. Specifies whether to have Db2 determine an access path at run time using values for host variables, global variables, parameter markers, and special registers. Supported by Db2 for OS/390 only. For a list of supported option values, refer to the documentation for Db2 for OS/390.
- SQLWARN
- Indicates whether warnings will be returned from the compilation
of dynamic SQL statements (via PREPARE or EXECUTE IMMEDIATE), or from
describe processing (via PREPARE...INTO or DESCRIBE).
- NO
- Warnings will not be returned from the SQL compiler.
- YES
- Warnings will be returned from the SQL compiler.
- STATICREADONLY
- Determines whether static cursors will be treated as being READ
ONLY or INSENSITIVE.
- NO
- All static cursors will take on the attributes as would normally be generated given the statement text and the setting of the LANGLEVEL precompile option. This is the default value.
- YES
- Any static cursor that does not contain the FOR UPDATE or FOR READ ONLY clause will be considered READ ONLY.
- INSENSITIVE
- Any static cursor that does not contain the FOR UPDATE clause will be considered READ ONLY and INSENSITIVE.
- STRDEL
- Designates whether an apostrophe (') or double quotation marks
(") will be used as the string delimiter within SQL statements. This
precompile/bind option is not supported by the server for Db2.
The DRDA server will use a
built-in default value if this option is not specified.
- APOSTROPHE
- Use an apostrophe (') as the string delimiter.
- QUOTE
- Use double quotation marks (") as the string delimiter.
- STRING_UNITS
- Specifies the string units when character and graphic data types
are used without explicit string units in static SQL statements. The
default is based on the string_units database configuration
parameter setting for the database.
- SYSTEM
- In a Unicode database, the setting has the following effect:
- CHAR, VARCHAR, and CLOB data types defined without specifying the CODEUNITS32 keyword will default to OCTETS.
- GRAPHIC, VARGRAPHIC, and DBCLOB data types defined without specifying the CODEUNITS32 keyword will default to CODEUNITS16.
- CHAR, VARCHAR, and CLOB data types defined without specifying the CODEUNITS32 keyword will default to OCTETS.
- GRAPHIC, VARGRAPHIC, and DBCLOB data types have implicit string units of double bytes.
- CODEUNITS32
- This setting is only valid for a Unicode database and has the
following effect:
- CHAR, VARCHAR, and CLOB data types defined without specifying the BYTE or OCTETS keywords will default to CODEUNITS32.
- GRAPHIC, VARGRAPHIC, and DBCLOB data types defined without specifying the CODEUNITS16 keyword will default to CODEUNITS32.
- SYSTIMESENSITIVE
- Indicates whether references to system-period temporal tables
in static and dynamic SQL statements are affected by the value of
the CURRENT TEMPORAL SYSTEM_TIME special register.
- YES
- References to system-period temporal tables are affected by the value of the CURRENT TEMPORAL SYSTEM_TIME special register. This value is the default value.
- NO
- References to system-period temporal tables are not affected by the value of the CURRENT TEMPORAL SYSTEM_TIME special register.
- TEXT label
- The description of a package. Maximum length is 255 characters. The default value is blanks. This precompile/bind option is not supported by the server for Db2.
- TRANSFORM GROUP
- Specifies the transform group name to be used by static SQL statements
for exchanging user-defined structured type values with host programs.
This transform group is not used for dynamic SQL statements or for
the exchange of parameters and results with external functions or
methods.
- groupname
- An SQL identifier of up to 18 bytes in length. A group name cannot
include a qualifier prefix and cannot begin with the prefix SYS since
this is reserved for database use. In a static SQL statement that
interacts with host variables, the name of the transform group to
be used for exchanging values of a structured type is as follows:
- The group name in the TRANSFORM GROUP bind option, if any
- The group name in the TRANSFORM GROUP prep option as specified at the original precompilation time, if any
- The DB2_PROGRAM group, if a transform exists for the given type whose group name is DB2_PROGRAM
- No transform group is used if none of the previously listed conditions exist.
The following errors are possible during the bind of a static SQL statement:- SQLCODE yyyyy, SQLSTATE xxxxx: A transform is needed, but no static transform group has been selected.
- SQLCODE yyyyy, SQLSTATE xxxxx: The selected transform group does not include a necessary transform (TO SQL for input variables, FROM SQL for output variables) for the data type that needs to be exchanged.
- SQLCODE yyyyy, SQLSTATE xxxxx: The result type of the FROM SQL transform is not compatible with the type of the output variable, or the parameter type of the TO SQL transform is not compatible with the type of the input variable.
- VALIDATE
- Determines when the database manager checks
for authorization errors and object not found errors. The package
owner authorization ID is used for validity checking.
- BIND
- Validation is performed at precompile/bind time. If all objects do not exist, or all authority is not held, error messages are produced. If SQLERROR CONTINUE is specified, a package/bind file is produced despite the error message, but the statements in error are not executable.
- RUN
- Validation is attempted at bind time. If all objects exist, and
all authority is held, no further checking is performed at execution
time.
If all objects do not exist, or all authority is not held at precompile/bind time, warning messages are produced, and the package is successfully bound, regardless of the SQLERROR CONTINUE option setting. However, authority checking and existence checking for SQL statements that failed these checks during the precompile/bind process can be redone at execution time.
Examples
db2 bind myapp.bnd
Any messages resulting from the bind process are sent to standard output.
Usage notes
Binding a package using the REOPT option with the ONCE or ALWAYS value specified might change the static and dynamic statement compilation and performance.
Binding can be done as part of the precompile process for an application program source file, or as a separate step at a later time. Use BIND when binding is performed as a separate process.
The name used to create the package is stored in the bind file, and is based on the source file name from which it was generated (existing paths or extensions are discarded). For example, a precompiled source file called myapp.sql generates a default bind file called myapp.bnd and a default package name of MYAPP. However, the bind file name and the package name can be overridden at precompile time by using the BINDFILE and the PACKAGE options.
Binding a package with a schema name that does not already exist results in the implicit creation of that schema. The schema owner is SYSIBM. The CREATEIN privilege on the schema is granted to PUBLIC.
BIND executes under the transaction that was started. After performing the bind, BIND issues a COMMIT or a ROLLBACK to terminate the current transaction and start another one.
Binding stops if a fatal error or more than 100 errors occur. If a fatal error occurs, the utility stops binding, attempts to close all files, and discards the package.
- The implicit or explicit value of the BIND option OWNER will be used for authorization checking of dynamic SQL statements.
- The implicit or explicit value of the BIND option QUALIFIER will be used as the implicit qualifier for qualification of unqualified objects within dynamic SQL statements.
- The value of the special register CURRENT SCHEMA has no effect on qualification.
In the event that multiple packages are referenced during a single connection, all dynamic SQL statements prepared by those packages will exhibit the behavior as specified by the DYNAMICRULES option for that specific package and the environment they are used in.
Parameters displayed in the SQL0020W message are correctly noted as errors, and will be ignored as indicated by the message.
If an SQL statement is found to be in error and the BIND option SQLERROR CONTINUE was specified, the statement will be marked as invalid. In order to change the state of the SQL statement, another BIND must be issued . Implicit and explicit rebind will not change the state of an invalid statement. In a package bound with VALIDATE RUN, a statement can change from static to incremental bind or incremental bind to static across implicit and explicit rebinds depending on whether or not object existence or authority problems exist during the rebind.
The privileges from the roles granted to the authorization identifier used to bind the package (the value of the OWNER bind option) or to PUBLIC, are taken into account when binding a package. Roles acquired through groups, in which the authorization identifier used to bind the package is a member, will not be used.
For an embedded SQL program, if the bind option is not explicitly specified the static statements in the package are bound using the federated_async configuration parameter. If the FEDERATED_ASYNCHRONY bind option is specified explicitly, that value is used for binding the packages and is also the initial value of the special register. Otherwise, the value of the database manager configuration parameter is used as the initial value of the special register. The FEDERATED_ASYNCHRONY bind option influences dynamic SQL only when it is explicitly set.
The
value of the FEDERATED_ASYNCHRONY bind option
is recorded in the FEDERATED_ASYNCHRONY column in the SYSCAT.PACKAGES
catalog table. When the bind option is not explicitly specified, the
value of federated_async configuration parameter
is used and the catalog shows a value of -2
for the FEDERATED_ASYNCHRONY column.
If the FEDERATED_ASYNCHRONY bind option is not explicitly specified when a package is bound, and if this package is implicitly or explicitly rebound, the package is rebound using the current value of the federated_async configuration parameter.