DB2 10.5 for Linux, UNIX, and Windows

Conservative binding semantics

Object resolution takes place when defining an SQL object or processing a package bind operation.

The database manager chooses which particular defined SQL object to use for an SQL object referenced in a DDL statement or coded in an application.

At a later time, the database manager might resolve to a different SQL object, even though the original SQL object did not change in any way. This resolution to a different SQL object happens as a result of defining another SQL object (or adding a privilege to an existing function) that the object resolution algorithm defines as resolved ahead of the SQL object originally chosen. Examples of SQL objects and situations to which this resolution to a different SQL object applies include the following situations:

Routines - a new routine could be defined that is a better fit or that is an equally good fit but earlier in the SQL path; or a privilege could be granted to an existing routine that is a better fit or that is an equally good fit but earlier in the SQL path
User-defined data types - a new user-defined data type could be defined with the same name and in a schema that is earlier in the SQL path
Global variables - a new global variable could be defined with the same name and in a schema that is earlier in the SQL path
Tables or views that resolve using a public alias - an actual table, view, or private alias could be defined with the same name in the current schema
Sequences that resolve using a public sequence alias - an actual sequence or private sequence alias could be defined with the same name in the current schema
Modules that resolve to a public module alias - an actual module or private module alias could be defined with the same name in a schema that is in the SQL path

There are instances where the database manager must be able to repeat the resolution of SQL objects as originally resolved when the statement was processed. This is true when the following static objects are used:

Static DML statements in packages
Views
Triggers
Check constraints
SQL routines
Global variables with a user-defined type or default expression
Routines with a user-defined parameter type or default expression

For static DML statements in packages, SQL object references are resolved during a bind operation. SQL object references in views, triggers, SQL routines, and check constraints are resolved when the SQL object is defined or revalidated. When an existing static object is used, conservative binding semantics are applied unless the object has been marked invalid or inoperative by a change in the database schema.

Conservative binding semantics ensure that SQL object references will be resolved using the same SQL path, default schema, and set of routines that were used when it was previously resolved. It also ensures that the timestamp of the definition of the SQL objects considered during conservative binding resolution is not later than the timestamp when the statement was last bound or validated using non-conservative binding semantics. Non-conservative binding semantics use the same SQL path and default schema as the original generation of the package or statement, but does not consider the timestamp of the definition of the SQL objects and does not consider any previously resolved set of routines.

Some changes to the database schema, such as dropping objects, altering objects, or revoking privileges, can impact an SQL object so that the database manager can no longer resolve all dependent SQL objects of an existing SQL object using conservative binding semantics.

When this happens for a static statement in an SQL package, the package is marked inoperative. The next use of a statement in this package will cause an implicit rebind of the package using non-conservative binding semantics in order to be able to resolve to SQL objects considering the latest changes in the database schema that caused that package to become inoperative.
When this happens for a view, trigger, check constraint, or SQL routine, the SQL object is marked invalid. The next use of the object causes an implicit revalidation of the SQL object using non-conservative binding semantics.

Consider a database with two functions that have the signatures SCHEMA1.BAR(INTEGER) and SCHEMA2.BAR(DOUBLE). Assume the SQL path contains both schemas SCHEMA1 and SCHEMA2 (although their order within the SQL path does not matter). USER1 has been granted the EXECUTE privilege on the function SCHEMA2.BAR(DOUBLE). Suppose USER1 creates a view that invokes BAR(INT_VAL), where INT_VAL is a column or global variable defined with the INTEGER data type. This function reference in the view resolves to the function SCHEMA2.BAR(DOUBLE) because USER1 does not have the EXECUTE privilege on SCHEMA1.BAR(INTEGER). If USER1 is granted the EXECUTE privilege on SCHEMA1.BAR(INTEGER) after the view has been created, the view will continue to use SCHEMA2.BAR(DOUBLE) unless a database schema change causes the view to be marked invalid. The view is marked invalid if a required privilege is revoked or an object it depends on gets dropped or altered.

For static DML in packages, packages can rebind implicitly, or by explicitly issuing the REBIND command (or corresponding API), or the BIND command (or corresponding API). The implicit rebind is performed with conservative binding semantics if the package is marked invalid, but uses non-conservative binding semantics when the package is marked inoperative. A package is marked invalid only if an index on which it depends is dropped or altered. The REBIND command provides the option to resolve with conservative binding semantics (RESOLVE CONSERVATIVE) or to resolve by considering new routines, data types, or global variables (RESOLVE ANY, which is the default option). The RESOLVE CONSERVATIVE option can be used only if the package has not been marked inoperative by the database manager (SQLSTATE 51028).

The description of conservative binding semantics in this topic has assumed that the database configuration parameter auto_reval has a setting other than DISABLED. The default setting for auto_reval for new databases is DEFERRED. The default setting for auto_reval for databases upgraded to Version 9.7 is DISABLED. If auto_reval is set to DISABLED, then conservative binding semantics, invalidation, and revalidation behavior are the same as in releases previous to Version 9.7. Under a auto_reval setting of DISABLED, conservative binding semantics only considers the timestamp of the definition of the SQL objects for functions, methods, user-defined types, and global variables. For invalidation and revalidation behavior, this means, in the case of the DROP, REVOKE, and ALTER statements, that either the semantics are more restrictive or the impact on dependent objects is to cascade and drop the object. In the case of packages, most database schema changes result in marking the package invalid and using conservative binding semantics during implicit rebind. However, when the schema is changed by dropping a dependent function and auto_reval is set to DISABLED, the package dependent on the function is marked inoperative and there is no implicit rebind of the inoperative package.