Dynamic rules options for dynamic SQL statements
The DYNAMICRULES bind option and the runtime environment determine the rules for the dynamic SQL attributes.
- The authorization ID that is used to check authorization
- The qualifier that is used for unqualified objects
- The source for application programming options that Db2 uses to parse and semantically verify dynamic SQL statements
- Whether dynamic SQL statements can include GRANT, REVOKE, ALTER, CREATE, DROP, and RENAME statements
- The package runs as part of a stand-alone program.
- The package runs as a stored procedure or user-defined function package, or it runs under a stored procedure or user-defined function. A package that runs under a stored procedure or user-defined function is a package whose associated program meets one of the following conditions:
- The program is called by a stored procedure or user-defined function.
- The program is in a series of nested calls that start with a stored procedure or user-defined function.
Dynamic SQL statement behavior
The dynamic SQL attributes that are determined by the value of the DYNAMICRULES bind option and the runtime environment are collectively called the dynamic SQL statement behavior or the dynamic rules behavior. The four dynamic rules behaviors are: run, bind, define, and invoke.
The following table shows the combination of DYNAMICRULES value and runtime environment that yield each dynamic SQL behavior.
DYNAMICRULES value | Behavior of dynamic SQL statements | |
---|---|---|
Stand-alone program environment | User-defined function or stored procedure environment | |
RUN | Run | Run |
BIND | Bind | Bind |
DEFINERUN | Run | Define |
DEFINEBIND | Bind | Define |
INVOKERUN | Run | Invoke |
INVOKEBIND | Bind | Invoke |
Note: BIND and RUN values can be specified for packages, plans, and native SQL procedures. The other values can be specified for packages and native SQL procedures but not for plans.
|
The following table shows the dynamic SQL attribute values for each type of dynamic SQL behavior.
Dynamic SQL attribute | Setting for dynamic SQL behavior attributes | |||
---|---|---|---|---|
Bind | Run | Define | Invoke | |
Authorization ID | Plan or package owner | Current SQLID | User-defined function or stored procedure owner | Authorization ID of invoker1 |
Default qualifier for unqualified objects | Bind OWNER or QUALIFIER value | CURRENT SCHEMA | User-defined function or stored procedure owner | Authorization ID of invoker |
CURRENT SQLID2 | Not applicable | Applies | Not applicable | Not applicable |
Source for application programming options | Determined by DSNHDECP or a user-specified application defaults module parameter DYNRULS3 | Install panel DSNTIP4 | Determined by DSNHDECP or a user-specified application defaults module parameter DYNRULS3 | Determined by DSNHDECP or a user-specified application defaults module parameter DYNRULS3 |
Can execute GRANT, REVOKE, CREATE, ALTER, DROP, RENAME? | No | Yes | No | No |
Notes:
|