Dynamic rules options for dynamic SQL statements

The DYNAMICRULES bind option and the runtime environment determine the rules for the dynamic SQL attributes.

The BIND or REBIND option DYNAMICRULES determines what values apply at run time for the following 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
In addition, 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 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.

Table 1. How DYNAMICRULES and the runtime environment determine dynamic SQL statement 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.

Table 2. Definitions of dynamic SQL statement behaviors
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:
  1. If the invoker is the primary authorization ID of the process or the CURRENT SQLID value, secondary authorization IDs are also checked if they are needed for the required authorization. Otherwise, only one ID, the ID of the invoker, is checked for the required authorization.
  2. Db2 uses the value of CURRENT SQLID as the authorization ID for dynamic SQL statements only for plans and packages that have run behavior. For the other dynamic SQL behaviors, Db2 uses the authorization ID that is associated with each dynamic SQL behavior, as shown in this table.

    The value to which CURRENT SQLID is initialized is independent of the dynamic SQL behavior. For stand-alone programs, CURRENT SQLID is initialized to the primary authorization ID.

    You can execute the SET CURRENT SQLID statement to change the value of CURRENT SQLID for packages with any dynamic SQL behavior, but Db2 uses the CURRENT SQLID value only for plans and packages with run behavior.

  3. The value of DSNHDECP or a user-specified application defaults module parameter DYNRULS, which you specify in field USE FOR DYNAMICRULES in installation panel DSNTIP4, determines whether Db2 uses the SQL statement processing options or the application programming defaults for dynamic SQL statements. See Options for SQL statement processing for more information.