REBIND command

The REBIND command allows the user to re-create a package stored in the database without the need for a bind file.

Authorization

One of the following authorities:

DBADM authority
ALTERIN privilege or SCHEMAADM authority on the schema
BIND privilege on the package.

The authorization ID logged in the BOUNDBY column of the SYSCAT.PACKAGES system catalog table, which is the ID of the most recent binder of the package, is used as the binder authorization ID for the rebind, and for the default schema for table references in the package. This default qualifier can be different from the authorization ID of the user executing the rebind request. REBIND will use the same bind options that were specified when the package was created.

Required connection

Database. If no database connection exists, and if implicit connect is enabled, a connection to the default database is made.

Command syntax

Command parameters

PACKAGE package-name

The qualified or unqualified name that designates the package to be rebound.

VERSION version-name

The specific version of the package to be rebound. When the version is not specified, it is taken to be "" (the empty string).

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 static SQL statements in the existing package during the rebind and during future implicit and explicit rebinds. The default is the value used during the previous invocation of the BIND or REBIND command or the ALTER PACKAGE statement. To determine the value, query the APREUSE column for the package in SYSCAT.PACKAGES.

YES: The query compiler will attempt to reuse the access plans for the statements in the package.
NO: The query compiler will not attempt to reuse access plans for the statements in the package.

RESOLVE

Specifies whether rebinding of the package is to be performed with or without conservative binding semantics. This affects whether new objects that use the SQL path for resolution are considered during resolution on static DML statements in the package. This option is not supported by DRDA. Valid values are:

ANY: All possible matches in the SQL path are considered for resolving references to any objects that use the SQL path for object resolution. Conservative binding semantics are not used. This is the default.
CONSERVATIVE: Only those objects in the SQL path that were defined before the last explicit bind time stamp are considered for resolving references to any objects that use the SQL path for object resolution. Conservative binding semantics are used. This option is not supported for an inoperative package.

REOPT

Specifies whether to have Db2® optimize an access path using values for host variables, parameter markers, global variables, and special registers.

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 for these variables. The default estimates for these variables will be used instead, and this plan is cached and used subsequently. This is the default behavior.
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 re-optimized using the values of the host variables, parameter markers, global variables, or special registers known at each execution time.

FUNCPATH

Specifies the function path to use in resolving user-defined distinct types and functions in static SQL. The default is the value that was used during the previous invocation of the BIND or REBIND command for the package. To determine the value, query the FUNC_PATH column for the package in SYSCAT.PACKAGES view.

schema-name: An SQL identifier, either ordinary or delimited, that identifies a schema that exists at the application server. No validation that the schema exists is made at precompile or at bind time. You cannot use the same schema more than once in the function path. You cannot specify the SYSPUBLIC schema name for the function path. The number of schemas that you can specify is limited by the length of the resulting function path, which cannot exceed 2048 bytes. You do not have to specify the SYSIBM schema: if you do not include it in the function path, the SYSIBM schema is assumed to be the first schema.

Usage notes

REBIND does not automatically commit the transaction following a successful rebind. The user must explicitly commit the transaction. This enables "what if" analysis, in which the user updates certain statistics, and then tries to rebind the package to see what changes. It also permits multiple rebinds within a unit of work.

The REBIND command will commit the transaction if auto-commit is enabled.

This command:

Provides a quick way to re-create a package. This enables the user to take advantage of a change in the system without a need for the original bind file. For example, if it is likely that a particular SQL statement can take advantage of a newly created index, the REBIND command can be used to re-create the package. REBIND can also be used to re-create packages after RUNSTATS has been executed, thereby taking advantage of the new statistics.
Provides a method to re-create inoperative packages. Inoperative packages must be explicitly rebound by invoking either the bind utility or the rebind utility. A package will be marked inoperative (the VALID column of the SYSCAT.PACKAGES system catalog will be set to X) if a function instance on which the package depends is dropped.
Gives users control over the rebinding of invalid packages. Invalid packages will be automatically, or implicitly, rebound by the database manager when they are executed. This is done in an autonomous transaction which commits if the rebind is successful and allows all users immediate access to the package. The implicit rebinding of an invalid package might result in a noticeable delay in the execution of the first SQL request for the invalid package.

If multiple versions of a package (many versions with the same package name and creator) exist, only one version can be rebound at once. If not specified in the VERSION option, the package version defaults to be "". Even if there exists only one package with a name that matches, it will not be rebound unless its version matches the one specified or the default.

The choice of whether to use BIND or REBIND to explicitly rebind a package depends on the circumstances. It is recommended that REBIND be used whenever the situation does not specifically require the use of BIND, since the performance of REBIND is significantly better than that of BIND. BIND must be used, however:

When there have been modifications to the program (for example, when SQL statements have been added or deleted, or when the package does not match the executable for the program).
When you want to modify any of the bind options as part of the rebind that REBIND command does not support. The REBIND command does not support all bind options. For example, if you want to have privileges on the package granted as part of the bind process, you must use the BIND command, because it has a GRANT option.
When the package does not currently exist in the database.
When detection of all bind errors is required. REBIND only returns the first error it detects, whereas the BIND command returns the first 100 errors that occur during binding.

REBIND is supported by Db2 Connect.

If REBIND is executed on a package that is in use by another user, the rebind will not occur until the other user's logical unit of work ends, because an exclusive lock is held on the package's record in the SYSCAT.PACKAGES system catalog table during the rebind.

When REBIND is executed, the database manager re-creates the package from the SQL statements stored in the SYSCAT.STATEMENTS system catalog table.

If REBIND encounters an error, processing stops, and an error message is returned.

REBIND will re-explain packages that were created with the EXPLSNAP bind option set to YES or ALL (indicated in the EXPLAIN_SNAPSHOT column in the SYSCAT.PACKAGES catalog table entry for the package) or with the EXPLAIN bind option set to YES or ALL (indicated in the EXPLAIN_MODE column in the SYSCAT.PACKAGES catalog table entry for the package). The Explain tables used are those of the REBIND requester, not the original binder.

If an SQL statement was found to be in error and the BIND option SQLERROR CONTINUE was specified, the statement will be marked as invalid even if the problem has been corrected. 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 a REBIND depending on whether or not object existence or authority problems exist during the REBIND.

Rebinding a package with REOPT ONCE or ALWAYS might change static and dynamic statement compilation and performance.

If REOPT is not specified, REBIND will preserve the existing REOPT value used at PRECOMPILE or BIND time.

Every compiled SQL object has a dependent package. The package can be rebound at any time by using the REBIND_ROUTINE_PACKAGE procedure. Explicitly rebinding the dependent package does not revalidate an invalid object. Revalidate an invalid object with automatic revalidation or explicitly by using the ADMIN_REVALIDATE_DB_OBJECTS procedure. Object revalidation automatically rebinds the dependent package.