Administrative API Reference

sqlarbnd - Rebind

Allows the user to recreate a package stored in the database without the need for a bind file.

Authorization

One of the following:

• sysadm or dbadm authority
• ALTERIN privilege 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. Note that this default qualifier may 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

Version

sql.h

C API Syntax

/* File: sql.h */ /* API: Rebind */ /* ... */ SQL_API_RC SQL_API_FN sqlarbnd ( char * pPackageName, struct sqlca * pSqlca, struct sqlopt * pRebindOptions); /* ... */

Generic API Syntax

/* File: sql.h */ /* API: Rebind */ /* ... */ SQL_API_RC SQL_API_FN sqlgrbnd ( unsigned short PackageNameLen, char * pPackageName, struct sqlca * pSqlca, struct sqlopt * pRebindOptions); /* ... */

API Parameters

PackageNameLen

Input. A 2-byte unsigned integer representing the length of the package name in bytes.

pPackageName

Input. A string containing the qualified or unqualified name that designates the package to be rebound. An unqualified package name is implicitly qualified by the current authorization ID.

pSqlca

Output. A pointer to the sqlca structure. For more information about this structure, see SQLCA.

pRebindOptions

Input. A pointer to the sqlopt structure, used to pass rebind options to the API. For more information about this structure, see SQLOPT.

REXX API Syntax

This API can be called from REXX through the SQLDB2 interface. See How the API Descriptions are Organized, or the Application Development Guide. For a description of the syntax, see the Command Reference.

Sample Programs

C

\sqllib\samples\c\rebind.sqc

COBOL

\sqllib\samples\cobol\rebind.sqb

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.

This API:

• Provides a quick way to recreate 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, REBIND can be used to recreate the package. REBIND can also be used to recreate packages after sqlustat - Runstats has been executed, thereby taking advantage of the new statistics.
• Provides a method to recreate 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 may result in a noticeable delay in the execution of the first SQL request for the invalid package. It may be desirable to explicitly rebind invalid packages, rather than allow the system to automatically rebind them, in order to eliminate the initial delay and to prevent unexpected SQL error messages which may be returned in case the implicit rebind fails. For example, following migration, all packages stored in the database will be invalidated by the DB2 Version 5 migration process. Given that this may involve a large number of packages, it may be desirable to explicitly rebind all of the invalid packages at one time. This explicit rebinding can be accomplished using BIND, REBIND, or the db2rbind tool (see "db2rbind - Rebind all Packages" in the Command Reference).

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 the user wishes to modify any of the bind options as part of the rebind. REBIND does not support any bind options. For example, if the user wishes to have privileges on the package granted as part of the bind process, BIND must be used, since it has an SQL_GRANT_OPT option.
• When the package does not currently exist in the database.
• When detection of all bind errors is desired. REBIND only returns the first error it detects, and then ends, 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 recreates 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.

The Explain tables are populated during REBIND if either SQL_EXPLSNAP_OPT or SQL_EXPLAIN_OPT have been set to YES or ALL (check EXPLAIN_SNAPSHOT and EXPLAIN_MODE columns in the catalog). The Explain tables used are those of the REBIND requester, not the original binder.

The following table lists valid values for the type and the val fields of the rebind options structure (see SQLOPT), as well as their corresponding CLP options. For a description of the rebind options (including default values), see the Command Reference.

Table 6. REBIND Option Types and Values

CLP Option	Option Type	Option Value
RESOLVE ANY	SQL_RESOLVE_OPT	SQL_RESOLVE_ANY
RESOLVE CONSERVATIVE	SQL_RESOLVE_OPT	SQL_RESOLVE_CONSERVATIVE

See Also

sqlabndx - Bind

sqlustat - Runstats.