Common attribute values for bind, define, and invoke behaviors

Certain attribute values apply to dynamic SQL statements in plans or packages that specify the bind, define, or invoke behavior.

The following attribute values apply:
  • Begin general-use programming interface information.You can execute the statement SET CURRENT SQLID in a package or plan that is bound with any DYNAMICRULES value. However, Db2 does not use the current SQL ID as the authorization ID for dynamic SQL statements.

    Db2 always uses the current SQL ID as the qualifier for the EXPLAIN output PLAN_TABLE.

  • If the value of installation option USE FOR DYNAMICRULES is YES, Db2 uses the application programming default values that were specified during installation to parse and semantically verify dynamic SQL statements. If the value of USE for DYNAMICRULES is NO, Db2 uses the precompiler options to parse and semantically verify dynamic SQL statements.
  • The GRANT, REVOKE, CREATE, ALTER, DROP, and RENAME statements cannot be executed dynamically.

The following table shows the DYNAMICRULES values and run time environments, and the dynamic SQL behaviors that they yield.

Table 1. How DYNAMICRULES and the run time environment determine dynamic SQL statement behavior
DYNAMICRULES value Dynamic SQL statements in a stand-alone program environment 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

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 Bind behavior Run behavior Define behavior Invoke behavior
Authorization ID Plan or package owner Authorization IDs of the process and role, if applicable User-defined function or stored procedure owner Authorization ID of invoker 1
Default qualifier for unqualified objects Bind OWNER or QUALIFIER value Current Schema register determines the qualifier User-defined function or stored procedure owner Authorization ID of invoker or role
CURRENT SQLID 2 Not applicable Applies Not applicable Not applicable
Source for application programming options Determined by dsnhdecp3 parameter DYNRULS 4 Install panel DSNTIPF Determined by dsnhdecp3 parameter DYNRULS 4 Determined by dsnhdecp3 parameter DYNRULS 4
Can execute GRANT, REVOKE, CREATE, ALTER, DROP, RENAME? No Yes No No
  1. If the invoker is the primary authorization ID of the process or the current SQL ID, the following rules apply:
    • The ID or role of the invoker is checked for the required authorization.
    • Secondary authorization IDs are also checked if they are needed for the required authorization.
  2. Db2 uses the current SQL ID as the authorization ID for dynamic SQL statements only for plans and packages that have DYNAMICRULES 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 initial current SQL ID is independent of the dynamic SQL behavior. For stand-alone programs, the current SQL ID is initialized to the primary authorization ID.You can execute the SET CURRENT SQLID statement to change the current SQL ID for packages with any dynamic SQL behavior, but Db2 uses the current SQL ID only for plans and packages with run behavior.

  3. dsnhdecp is the application default load module. The default name is DSNHDECP.
  4. The value of dsnhdecp parameter DYNRULS, which you specify in field USE FOR DYNAMICRULES in installation panel DSNTIPF, determines whether Db2 uses the precompiler options or the application programming defaults for dynamic SQL statements. End general-use programming interface information.