SQL Reference

Dynamic SQL Characteristics at run-time

The BIND and PRECOMPILE command option OWNER defines the authorization ID of the package.

The BIND and PRECOMPILE command option QUALIFIER defines implicit qualifier for unqualified objects contained in the package.

The BIND and PRECOMPILE command option DYNAMICRULES determines whether dynamic SQL statements are processed at run time with run time rules, DYNAMICRULES RUN, or with bind time rules, DYNAMICRULES BIND. These rules indicate the setting of the value used as authorization ID and for the setting of the value used for implicit qualification of unqualified object references within dynamic SQL statements. The DYNAMICRULES options have the effects described in the following tables:

Table 1. Static SQL Characteristics affected by OWNER and QUALIFIER

Feature	Only OWNER Specified	Only QUALIFIER Specified	QUALIFIER and OWNER specified
Authorization ID Used	ID of User specified in the OWNER option on the BIND command	ID of User Binding the Package	ID of User specified in the OWNER option on the BIND command
Unqualified Object Qualification Value Used	ID of User specified in the OWNER option on the BIND command	ID of User specified in the QUALIFIER option on the BIND command	ID of User specified in the QUALIFIER option on the BIND command

Table 2. Dynamic SQL Characteristics affected by DYNAMICRULES, OWNER and QUALIFIER

Feature	RUN	BIND
Authorization ID Used	ID of User Executing Package	Authorization ID for the package
Unqualified Object Qualification Value Used	CURRENT SCHEMA Special Register	Authorization ID for the package

Considerations regarding the DYNAMICRULES option:

• The CURRENT SCHEMA special register will not be used to qualify unqualified object references within dynamic SQL statements executed from a package bound with DYNAMICRULES BIND. Instead, DB2 will use the package default qualifier as is shown in the table. This is true even after you issue the statement SET CURRENT SCHEMA in order to change the CURRENT SCHEMA special register; the register value will be changed but not used.

• The following dynamically prepared SQL statements can not be used within a package bound with DYNAMICRULES BIND option: GRANT, REVOKE, ALTER, CREATE, DROP, COMMENT ON, RENAME, SET INTEGRITY, SET EVENT MONITOR STATE and queries that reference a nickname.

• If multiple packages are referenced during a single connection, dynamic SQL will behave according to the BIND options for the package in which a statement is bound.

• It is important to keep in mind that when binding a package with DYNAMICRULES BIND, the binder of the package should not have any authorities granted to them that you would not want the user of the package to have since a dynamic statement will be using the authorization ID of the package owner.

[ DB2 List of Books | Search the DB2 Books ]