DYNAMICRULES bind option

The DYNAMICRULES bind option and the run time environment determine the values 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 run time environment of a package controls how dynamic SQL statements behave at run time. The two possible run time 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 run time environment are collectively called the dynamic SQL statement behavior. The four behaviors are:
  • Run behavior
  • Bind behavior
  • Define behavior
  • Invoke behavior

The following table shows the combination of DYNAMICRULES value and run time environment that yield each dynamic SQL behavior.

Table 1. How DYNAMICRULES and the run time environment determine dynamic SQL statement behavior
DYNAMICRULES value Behavior of dynamic SQL statements in a stand-alone program environment Behavior of dynamic SQL statements in a user-defined function or stored procedure 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
Note: The BIND and RUN values can be specified for packages and plans. The other values can be specified only for packages.

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 attributes
Bind behavior Run behavior Define behavior Invoke behavior
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
Start of changeSource for application programming optionsEnd of change Start of changeDetermined by DSNHDECP or a user-specified application defaults module parameter DYNRULS3End of change Start of changeInstall panel DSNTIP4End of change Start of changeDetermined by DSNHDECP or a user-specified application defaults module parameter DYNRULS3End of change Start of changeDetermined by DSNHDECP or a user-specified application defaults module parameter DYNRULS3End of change
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. Start of changeThe 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.End of change