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 ACCELERATOR Inherited from the invoking application6; otherwise, no preferred accelerator is used and Db2 will determine the target accelerator The ACCELERATOR bind option value if specified for the user-defined function or stored procedure package; otherwise, no preferred accelerator is used and Db2 will determine the target accelerator Yes
CURRENT APPLICATION COMPATIBILITY The value of bind option APPLCOMPAT for the user-defined function or stored procedure package The value of bind option APPLCOMPAT for the user-defined function or stored procedure package Yes
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
CURRENT EXPLAIN MODE Inherited from the invoking application NO Yes
CURRENT GET_ACCEL_ARCHIVE Inherited from the invoking application6; otherwise, the subsystem parameter value will be used The GETACCELARCHIVE bind option value if specified for the user-defined function or stored procedure package; otherwise, the subsystem parameter value will be used Yes
CURRENT LOCALE LC_CTYPE Inherited from the invoking application The value of field CURRENT LC_CTYPE on installation panel DSNTIPF Yes
Start of changeFL 500 CURRENT LOCK TIMEOUTEnd of change Start of changeInherited from the invoking applicationEnd of change Start of changeInitial value of the special registerEnd of change Start of changeYesEnd of change
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
CURRENT QUERY ACCELERATION Inherited from the invoking application6; otherwise, the subsystem parameter value will be used The QUERYACCELERATION bind option value if specified for the user-defined function or stored procedure package; otherwise, the subsystem parameter value will be used Yes
CURRENT QUERY ACCELERATION WAITFORDATA Inherited from the invoking application6; otherwise, the subsystem parameter value will be used The ACCELERATIONWAITFORDATA bind option value if specified for the user-defined function or stored procedure package; otherwise, the subsystem parameter value will be used Yes
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 TEMPORAL BUSINESS_TIME Inherited from the invoking application NULL Yes
CURRENT TEMPORAL SYSTEM_TIME Inherited from the invoking application NULL Yes
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
CURRENT TIMESTAMP WITH TIME ZONE 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 TIME ZONE 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
SESSION TIME ZONE Inherited from the invoking application The value of CURRENT TIME ZONE when the routine is entered Yes
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.