Special registers in a user-defined function or a stored procedure

You can use all special registers in a user-defined function or a stored procedure. However, you can modify only some of those special registers.

After a user-defined function or a stored procedure completes, DB2® restores all special registers to the values they had before invocation.

The following table shows information that you need when you use special registers in a user-defined function or stored procedure.

Table 1. Characteristics of special registers in a user-defined function or a stored procedure
Special register Initial value when INHERIT SPECIAL REGISTERS option is specified Initial value when DEFAULT SPECIAL REGISTERS option is specified Routine can use SET statement to modify?
CURRENT APPLICATION ENCODING SCHEME The value of bind option ENCODING for the user-defined function or stored procedure package The value of bind option ENCODING for the user-defined function or stored procedure package Yes
CURRENT CLIENT_ACCTNG Inherited from the invoking application Inherited from the invoking application Not applicable5
CURRENT CLIENT_APPLNAME Inherited from the invoking application Inherited from the invoking application Not applicable5
CURRENT CLIENT_USERID Inherited from the invoking application Inherited from the invoking application Not applicable5
CURRENT CLIENT_WRKSTNNAME Inherited from the invoking application Inherited from the invoking application Not applicable5
CURRENT DATE New value for each SQL statement in the user-defined function or stored procedure package1 New value for each SQL statement in the user-defined function or stored procedure package1 Not applicable5
CURRENT DEBUG MODE Inherited from the invoking application DISALLOW Yes
CURRENT DECFLOAT ROUNDING MODE Inherited from the invoking application The value of bind option ROUNDING for the user-defined function or stored procedure package Yes
CURRENT DEGREE CURRENT DEGREE2 The value of field CURRENT DEGREE on installation panel DSNTIP8 Yes
Start of changeCURRENT EXPLAIN MODEEnd of change Start of changeInherited from the invoking applicationEnd of change Start of changeNOEnd of change Start of changeYesEnd of change
Start of changeCURRENT GET_ACCEL_ARCHIVEEnd of change Start of changeInherited from the invoking applicationEnd of change Start of changeSystem default valueEnd of change Start of changeYesEnd of change
CURRENT LOCALE LC_CTYPE Inherited from the invoking application The value of field CURRENT LC_CTYPE on installation panel DSNTIPF Yes
CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION Inherited from the invoking application System default value Yes
CURRENT MEMBER New value for each SET host-variable=CURRENT MEMBER statement New value for each SET host-variable=CURRENT MEMBER statement Not applicable5
CURRENT OPTIMIZATION HINT The value of bind option OPTHINT for the user-defined function or stored procedure package or inherited from the invoking application6 The value of bind option OPTHINT for the user-defined function or stored procedure package Yes
CURRENT PACKAGE PATH An empty string if the routine was defined with a COLLID value; otherwise, inherited from the invoking application4 An empty string, regardless of whether a COLLID value was specified for the routine4 Yes
CURRENT PACKAGESET Inherited from the invoking application3 Inherited from the invoking application3 Yes
CURRENT PATH The value of bind option PATH for the user-defined function or stored procedure package or inherited from the invoking application6 The value of bind option PATH for the user-defined function or stored procedure package Yes
CURRENT PRECISION Inherited from the invoking application The value of field DECIMAL ARITHMETIC on installation panel DSNTIP4 Yes
Start of changeCURRENT QUERY ACCELERATIONEnd of change Start of changeInherited from the invoking applicationEnd of change Start of changeSystem default valueEnd of change Start of changeYesEnd of change
CURRENT REFRESH AGE Inherited from the invoking application System default value Yes
CURRENT ROUTINE VERSION Inherited from the invoking application The empty string Yes
CURRENT RULES Inherited from the invoking application The value of bind option SQLRULES for the plan that invokes a user-defined function or stored procedure Yes
CURRENT SCHEMA Inherited from the invoking application The value of CURRENT SCHEMA when the routine is entered Yes
CURRENT SERVER Inherited from the invoking application Inherited from the invoking application Yes
CURRENT SQLID The primary authorization ID of the application process or inherited from the invoking application7 The primary authorization ID of the application process Yes8
CURRENT TIME New value for each SQL statement in the user-defined function or stored procedure package1 New value for each SQL statement in the user-defined function or stored procedure package1 Not applicable5
CURRENT TIMESTAMP New value for each SQL statement in the user-defined function or stored procedure package1 New value for each SQL statement in the user-defined function or stored procedure package1 Not applicable5
Start of changeCURRENT TIMESTAMP WITH TIME ZONEEnd of change Start of changeNew value for each SQL statement in the user-defined function or stored procedure package1End of change Start of changeNew value for each SQL statement in the user-defined function or stored procedure package1End of change Start of changeNot applicable5End of change
Start of changeCURRENT TIME ZONEEnd of change Inherited from the invoking application Inherited from the invoking application Not applicable5
ENCRYPTION PASSWORD Inherited from the invoking application Inherited from the invoking application Yes
Start of changeSESSION TIME ZONEEnd of change Start of changeInherited from the invoking applicationEnd of change Start of changeThe value of CURRENT TIME ZONE when the routine is enteredEnd of change Start of changeYesEnd of change
SESSION_USER or USER Primary authorization ID of the application process Primary authorization ID of the application process Not applicable5
Notes:
  1. If the user-defined function or stored procedure is invoked within the scope of a trigger, DB2 uses the timestamp for the triggering SQL statement as the timestamp for all SQL statements in the package.
  2. DB2 allows parallelism at only one level of a nested SQL statement. If you set the value of the CURRENT DEGREE special register to ANY, and parallelism is disabled, DB2 ignores the CURRENT DEGREE value.
  3. If the routine definition includes a specification for COLLID, DB2 sets CURRENT PACKAGESET to the value of COLLID. If both CURRENT PACKAGE PATH and COLLID are specified, the CURRENT PACKAGE PATH value takes precedence and COLLID is ignored.
  4. If the function definition includes a specification for PACKAGE PATH, DB2 sets CURRENT PACKAGE PATH to the value of PACKAGE PATH.
  5. Not applicable because no SET statement exists for the special register.
  6. If a program within the scope of the invoking program issues a SET statement for the special register before the user-defined function or stored procedure is invoked, the special register inherits the value from the SET statement. Otherwise, the special register contains the value that is set by the bind option for the user-defined function or stored procedure package.
  7. If a program within the scope of the invoking program issues a SET CURRENT SQLID statement before the user-defined function or stored procedure is invoked, the special register inherits the value from the SET statement. Otherwise, CURRENT SQLID contains the authorization ID of the application process.
  8. If the user-defined function or stored procedure package uses a value other than RUN for the DYNAMICRULES bind option, the SET CURRENT SQLID statement can be executed. However, it does not affect the authorization ID that is used for the dynamic SQL statements in the package. The DYNAMICRULES value determines the authorization ID that is used for dynamic SQL statements.