The PRECOMPILE command and BIND command
parameter DYNAMICRULES determines which rules
apply to dynamic SQL at run time.
In particular, the
DYNAMICRULES parameter
determines what values apply at run time for the following dynamic
SQL attributes:
- The authorization ID that is used during authorization checking.
- The qualifier that is used for qualification of unqualified objects.
- Whether the package can be used to dynamically prepare the following
statements: GRANT, REVOKE, ALTER, CREATE, DROP, COMMENT ON, RENAME, SET
INTEGRITY, and SET EVENT MONITOR STATE statements.
In addition to the
DYNAMICRULES value, the
runtime environment of a package controls how dynamic SQL statements
behave at run time. The two possible runtime environments are:
- The package runs as part of a stand-alone program
- The package runs within a routine context
The combination of the
DYNAMICRULES value
and the runtime environment determine the values for the dynamic SQL
attributes. That set of attribute values is called the dynamic SQL
statement behavior. The four behaviors are:
- Run behavior
- DB2® for Linux, UNIX, and Windows uses
the authorization ID of the user (the ID that initially connected
to the DB2 database) executing
the package as the value to be used for authorization checking of
dynamic SQL statements and for the initial value used for implicit
qualification of unqualified object references within dynamic SQL statements.
- Bind behavior
- At run time, DB2 for Linux, UNIX, and Windows uses
all the rules that apply to static SQL for authorization and qualification.
That is, take the authorization ID of the package owner as the value
to be used for authorization checking of dynamic SQL statements and
the package default qualifier for implicit qualification of unqualified
object references within dynamic SQL statements.
- Define behavior
- Define behavior applies only if the dynamic SQL statement is in
a package that is run within a routine context, and the package was
bound with DYNAMICRULES DEFINEBIND or DYNAMICRULES DEFINERUN.
DB2 for Linux, UNIX, and Windows uses
the authorization ID of the routine definer (not the routine's package
binder) as the value to be used for authorization checking of dynamic
SQL statements and for implicit qualification of unqualified object
references within dynamic SQL statements within that routine.
- Invoke behavior
- Invoke behavior applies only if the dynamic SQL statement is in
a package that is run within a routine context, and the package was
bound with DYNAMICRULES INVOKEBIND or DYNAMICRULES INVOKERUN.
DB2 for Linux, UNIX, and Windows uses
the current statement authorization ID in effect when the routine
is invoked as the value to be used for authorization checking of dynamic
SQL and for implicit qualification of unqualified object references
within dynamic SQL statements within that routine. This is summarized
by the following table:
Invoking Environment |
ID Used |
Any static SQL |
Implicit or explicit value of the OWNER of
the package the SQL invoking the routine came from. |
Used in definition of view or trigger |
Definer of the view or trigger. |
Dynamic SQL from a run behavior package |
ID used to make the initial connection to the DB2 database. |
Dynamic SQL from a define behavior package |
Definer of the routine that uses the package
that the SQL invoking the routine came from. |
Dynamic SQL from an invoke behavior package |
Current authorization ID invoking the routine. |
The following table shows the combination of the
DYNAMICRULES value
and the runtime environment that yields each dynamic SQL behavior.
Table 1. How DYNAMICRULES and the Runtime Environment
Determine Dynamic SQL Statement BehaviorDYNAMICRULES Value |
Behavior of Dynamic SQL Statements
in a Standalone Program Environment |
Behavior of Dynamic SQL Statements
in a Routine Environment |
BIND |
Bind behavior |
Bind behavior |
RUN |
Run behavior |
Run behavior |
DEFINEBIND |
Bind behavior |
Define behavior |
DEFINERUN |
Run behavior |
Define behavior |
INVOKEBIND |
Bind behavior |
Invoke behavior |
INVOKERUN |
Run behavior |
Invoke behavior |
The following table shows the dynamic SQL attribute values for
each type of dynamic SQL behavior.
Table 2. Definitions
of Dynamic SQL Statement BehaviorsDynamic SQL Attribute |
Setting for Dynamic SQL Attributes:
Bind Behavior |
Setting for Dynamic SQL Attributes:
Run Behavior |
Setting for Dynamic SQL Attributes:
Define Behavior |
Setting for Dynamic SQL Attributes: Invoke
Behavior |
Authorization ID |
The implicit or explicit value of the BIND OWNER command
parameter |
ID of User Executing Package |
Routine definer (not the routine's package owner) |
Current statement authorization ID when routine
is invoked. |
Default qualifier for unqualified objects |
The implicit or explicit value of the BIND QUALIFIER command
parameter |
CURRENT SCHEMA Special Register |
Routine definer (not the routine's package owner) |
Current statement authorization ID when routine
is invoked. |
Can execute GRANT, REVOKE, ALTER, CREATE, DROP,
COMMENT ON, RENAME, SET INTEGRITY, and SET EVENT MONITOR STATE |
No |
Yes |
No |
No |