Command Reference

BIND

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 node in db2nodes.cfg. It updates the database catalogs on the catalog node. Its effects are visible to all nodes.

Authorization

One of the following:

sysadm or dbadm authority
BINDADD privilege if a package does not exist and one of:
- IMPLICIT_SCHEMA authority on the database if the schema name of the package does not exist
- CREATEIN privilege on the schema if the schema name of the package exists
ALTERIN privilege on the schema if the package exists
BIND privilege on the package if it exists.

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. If the user has sysadm authority, but not explicit privileges to complete the bind, the database manager grants explicit dbadm authority automatically.

Required Connection

Database. If implicit connect is enabled, a connection to the default database is established.

Command Syntax

For DB2

>>-BIND--filename----+-------------------------+---------------->
                     |           .-UNAMBIG--.  |
                     '-BLOCKING--+-ALL------+--'
                                 '-NO-------'
 
>-----+--------------------------+---+---------------------+---->
      '-COLLECTION--schema-name--'   |           .-DEF--.  |
                                     '-DATETIME--+-EUR--+--'
                                                 +-ISO--+
                                                 +-JIS--+
                                                 +-LOC--+
                                                 '-USA--'
 
>-----+------------------------------------+-------------------->
      |         .-1---------------------.  |
      '-DEGREE--+-degree-of-parallelism-+--'
                '-ANY-------------------'
 
>-----+-------------------------+---+-------------------+------->
      |               .-RUN--.  |   |          .-NO--.  |
      '-DYNAMICRULES--+-BIND-+--'   '-EXPLAIN--+-ALL-+--'
                                               '-YES-'
 
>-----+--------------------+------------------------------------>
      |           .-NO--.  |
      '-EXPLSNAP--+-ALL-+--'
                  '-YES-'
 
>-----+-------------------------------+------------------------->
      |           .-,--------------.  |
      |           V                |  |
      '-FUNCPATH-----schema-name---+--'
 
>-----+--------------------+---+--------------------------+----->
      '-GRANT--+-authid-+--'   '-GRANT_GROUP--group-name--'
               '-PUBLIC-'
 
>-----+------------------------+---+-------------------+-------->
      '-GRANT_USER--user-name--'   |         .-DEF--.  |
                                   '-INSERT--+-BUF--+--'
 
>-----+---------------------+---+-------------------------+----->
      |            .-CS--.  |   '-MESSAGES--message-file--'
      '-ISOLATION--+-RR--+--'
                   +-RS--+
                   '-UR--'
 
>-----+--------------------------+------------------------------>
      '-OWNER--authorization-id--'
 
>-----+----------------------------+---------------------------->
      '-QUALIFIER--qualifier-name--'
 
>-----+-------------------------------+------------------------->
      '-QUERYOPT--optimization-level--'
 
>-----+---------------------------+---+--------------------+---><
      |           .-NOPACKAGE--.  |   |          .-YES--.  |
      '-SQLERROR--+-CHECK------+--'   '-SQLWARN--+-NO---+--'

For DRDA

>>-BIND--filename----------------------------------------------->
 
>-----+----------------------------------------------------------------------------+>
      '-ACTION--+-ADD-----------------------------------------------------------+--'
                | .-REPLACE-.                                                   |
                '-+---------+--+-------------------+---+----------------------+-'
                               |         .-YES--.  |   '-REPLVER--version-id--'
                               '-RETAIN--+-NO---+--'
 
>-----+-------------------------+---+-----------------------+--->
      |           .-UNAMBIG--.  |   '-CCSIDG--double-ccsid--'
      '-BLOCKING--+-ALL------+--'
                  '-NO-------'
 
>-----+----------------------+---+---------------------+-------->
      '-CCSIDM--mixed-ccsid--'   '-CCSIDS--sbcs-ccsid--'
 
>-----+------------------------+---+---------------------+------>
      |          .-DEFAULT--.  |   |           .-YES--.  |
      '-CHARSUB--+-BIT------+--'   '-CNULREQD--+-NO---+--'
                 +-MIXED----+
                 '-SBCS-----'
 
>-----+--------------------------+------------------------------>
      '-COLLECTION--schema-name--'
 
>-----+----------------------------+---+--------------+--------->
      |  (1)             .-DEF--.  |   '-DEC--+-15-+--'
      '--------DATETIME--+-EUR--+--'          '-31-'
                         +-ISO--+
                         +-JIS--+
                         +-LOC--+
                         '-USA--'
 
>-----+----------------------+---------------------------------->
      |         .-PERIOD--.  |
      '-DECDEL--+-COMMA---+--'
 
>-----+-------------------------------------------+------------->
      |  (2)           .-1---------------------.  |
      '--------DEGREE--+-degree-of-parallelism-+--'
                       '-ANY-------------------'
 
>-----+---------------------------+----------------------------->
      |               .-RUN----.  |
      '-DYNAMICRULES--+-BIND---+--'
                      +-DEFINE-+
                      '-INVOKE-'
 
>-----+--------------------------+---+------------------+------->
      |  (3)            .-NO--.  |   '-GENERIC--string--'
      '--------EXPLAIN--+-YES-+--'
 
>-----+--------------------+---+--------------------------+----->
      '-GRANT--+-authid-+--'   '-GRANT_GROUP--group-name--'
               '-PUBLIC-'
 
>-----+------------------------+---+-------------------+-------->
      '-GRANT_USER--user-name--'   |         .-DEF--.  |
                                   '-INSERT--+-BUF--+--'
 
>-----+---------------------+---+-------------------------+----->
      |            .-CS--.  |   '-MESSAGES--message-file--'
      '-ISOLATION--+-NC--+--'
                   +-RR--+
                   +-RS--+
                   '-UR--'
 
>-----+--------------------------+------------------------------>
      '-OWNER--authorization-id--'
 
>-----+----------------------------+---------------------------->
      '-QUALIFIER--qualifier-name--'
 
>-----+--------------------------+------------------------------>
      |          .-COMMIT-----.  |
      '-RELEASE--+-DEALLOCATE-+--'
 
>-----+---------------------------+----------------------------->
      |           .-NOPACKAGE--.  |
      '-SQLERROR--+-CHECK------+--'
                  '-CONTINUE---'
 
>-----+--------------------------+---+--------------+----------->
      |         .-APOSTROPHE--.  |   '-TEXT--label--'
      '-STRDEL--+-QUOTE-------+--'
 
>-----+---------------------+----------------------------------><
      |           .-RUN--.  |
      '-VALIDATE--+-BIND-+--'

Notes:

The DATETIME DEF option is not supported by DRDA, and is mapped to ISO when going through DB2 Connect.
The DEGREE option is only supported by DRDA Level 2 Application Servers.
DRDA defines the EXPLAIN option to have the value YES or NO. If the EXPLAIN option is not specified, this maps to DRDA "EXPLAIN NO".

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,

   /u/smith/sqllib/bnd/@all.lst

is a list file that contains the following bind files:

   mybind1.bnd+mybind.bnd2+mybind3.bnd+
   mybind4.bnd+mybind5.bnd+
   mybind6.bnd+
   mybind7.bnd

ACTION

Indicates whether the package can be added or replaced. This DRDA precompile/bind option is not supported by DB2.

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 old package is to be replaced by a new one with the same location, collection, and package name.

RETAIN

Indicates whether 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 EXECUTE authorities when a package is replaced.
YES: Preserves EXECUTE authorities when a package is replaced.

REPLVER version-id

Replaces a specific version of a package. The version identifier specifies which version of the package is to be replaced. Maximum length is 254 characters.

BLOCKING

For information about row blocking, see the Administration Guide or the Application Development Guide.

ALL

Specifies to block for:

Read-only cursors
Cursors not specified as FOR UPDATE OF

Ambiguous cursors are treated as read-only.

NO

Specifies not to block any cursors. Ambiguous cursors are treated as updatable.

UNAMBIG

Specifies to block for:

Read-only cursors
Cursors not specified as FOR UPDATE OF

Ambiguous cursors are treated as updatable.

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 DRDA precompile/bind option is not supported by DB2. The DRDA server will use a system defined 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 DRDA precompile/bind option is not supported by DB2. The DRDA server will use a system defined 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 DRDA precompile/bind option is not supported by DB2. The DRDA server will use a system defined 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 DRDA precompile/bind option is not supported by 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 system defined 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.

CNULREQD

This option is related to the langlevel precompile option, which is not supported by DRDA. It is valid only if the bind file is created from a C or a C++ application. This DRDA bind option is not supported by 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 an 8-character collection identifier for the package. If not specified, the authorization identifier for the user processing the package is used.

DATETIME

Specifies the date and time format to be used. For more information about date and time formats, see the SQL Reference.

DEF: Use a date and time format associated with the country 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 country code of the database.
USA: Use the IBM standard for U.S. date and time format.

DEC

Specifies the maximum precision to be used in decimal arithmetic operations. This DRDA precompile/bind option is not supported by DB2. The DRDA server will use a system defined 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 DRDA precompile/bind option is not supported by DB2. The DRDA server will use a system defined 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. 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.

When binding a package with this option, the binder of the package should not have any authorities that the user of the package should not receive, because dynamic SQL statements will be using the authorization ID of the package owner. The following dynamically prepared SQL statements cannot be used within a package that has been bound with this option: GRANT, REVOKE, ALTER, CREATE, DROP, COMMENT ON, RENAME, SET CONSTRAINTS, and SET EVENT MONITOR STATE.

DEFINE

Indicates that the authorization identifier used for the execution of dynamic SQL statements in a UDF or stored procedure is the definer of the UDF or stored procedure. This option is not supported by DB2.

INVOKE

Indicates that the authorization identifier used for the execution of dynamic SQL statements in a UDF or stored procedure is the invoker of the UDF or stored procedure. This option is not supported by DB2.

EXPLAIN

Stores information in the Explain tables about the access plans chosen for each SQL statement in the package. DRDA does not support the ALL value for this option.

NO

Explain information will not be captured.

YES

Explain tables will be populated with information about the chosen access plan.

ALL

Explain information for each eligible static SQL statement will be placed in the Explain tables. In addition, Explain information will be gathered for eligible dynamic SQL statements at run time, even if the CURRENT EXPLAIN SNAPSHOT register is set to NO. For more information about special registers, see the SQL Reference.

Note:

This value for EXPLAIN is not supported by DRDA.

EXPLSNAP

Stores Explain Snapshot information in the Explain tables. This DB2 precompile/bind option is not supported by DRDA.

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.
ALL: An Explain Snapshot for each eligible static SQL statement will be placed in the Explain tables. 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. For more information about special registers, see the SQL Reference.

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. This DB2 precompile/bind option is not supported by DRDA.

schema-name: A short 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 number of schemas that can be specified is limited by the length of the resulting function path, which cannot exceed 254 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. For more information, see the SQL Reference.

GENERIC string

Provides a means of passing new bind options to a target DRDA database. Supports the binding of new options that are defined in the target database, but that are not known to the local command. Do not use this option to pass bind options that are defined in BIND or PRECOMPILE PROGRAM. This option can substantially improve dynamic SQL performance. The syntax is as follows:

   generic "option1 value1 option2 value2 ..."

Each option and value must be separated by one or more blank spaces. For example, if the target DRDA database is DB2 MVS Version 5, one could use:

   generic "keepdynamic yes"

to bind the new keepdynamic YES option, which is not defined locally on the PRECOMPILE PROGRAM or the BIND command.

The maximum length of the string is 1023 bytes. This DRDA bind option is currently only supported by DB2 MVS Version 5; it is not supported by DB2.

GRANT

authid: Grants EXECUTE and BIND privileges to a specified user name or group ID.
PUBLIC: Grants EXECUTE and BIND privileges to PUBLIC.

GRANT_GROUP group-name

Grants EXECUTE and BIND privileges to a specified group ID.

GRANT_USER user-name

Grants EXECUTE and BIND privileges to a specified user name.

INSERT

Allows a program being precompiled or bound against a DB2 Universal Database Extended Enterprise 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. For more information about isolation levels, see the SQL Reference.

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.

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.

OWNER authorization-id

Designates an 8-character 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 SYSADM or DBADM authority can specify an authorization identifier other than the user ID. The default value is the primary authorization ID of the precompile/bind process. SYSIBM, SYSCAT, and SYSSTAT are not valid values for this option.

QUALIFIER qualifier-name

Provides an 8-character 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 desired level of optimization for all static SQL statements contained in the package. The default value is 5. For the complete range of optimization levels available, see the SET CURRENT QUERY OPTIMIZATION statement in the SQL Reference. This DB2 precompile/bind option is not supported by DRDA.

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.

COMMIT: Release resources at each COMMIT point. Used for dynamic SQL statements.
DEALLOCATE: Release resources only when the application terminates.

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: A package or a bind file is created even when SQL errors are encountered. This option is not supported by DB2.
NOPACKAGE: A package or a bind file is not created if an error is encountered.

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). This DB2 precompile/bind option is not supported by DRDA.

NO: Warnings will not be returned from the SQL compiler.
YES: Warnings will be returned from the SQL compiler.

Note: SQLCODE +238 is an exception. It is returned regardless of the sqlwarn option value.

STRDEL

Designates whether an apostrophe (') or double quotation marks (") will be used as the string delimiter within SQL statements. This DRDA precompile/bind option is not supported by DB2. The DRDA server will use a system defined 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.

TEXT label

The description of a package. Maximum length is 255 characters. The default value is blanks. This DRDA precompile/bind option is not supported by DB2.

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. This DRDA precompile/bind option is not supported by DB2.

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 may be redone at execution time.

Examples

The following example binds myapp.bnd (the bind file generated when the myapp.sqc program was precompiled) to the database to which a connection has been established:

   db2 bind myapp.bnd

Any messages resulting from the bind process are sent to standard output.

Usage Notes

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.

If a package is bound with dynamicrules bind, the implicit or explicit value of the bind option owner is used for authorization checking of dynamic SQL statements, and the implicit or explicit value of the bind option qualifier is used as the implicit qualifier of unqualified objects within dynamic SQL statements. If multiple packages are referenced during a single connection, dynamic SQL statements prepared by a specific package will behave according to the bind options for that package. The value of the special register CURRENT SCHEMA has no effect on qualification in a package bound with dynamicrules bind.

Binding application programs has prerequisite requirements and restrictions beyond the scope of this manual. For more detailed information about binding application programs to databases, see the Application Development Guide.

See Also

PRECOMPILE PROGRAM.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]