Don’t Forget About DB2 Session Variables

Session variables, set by DB2 or by the user, offer another way to provide additional information to applications. Session variables are set by DB2, and are accessible to application programs and end users. By accessing session variables, an application program can determine many aspects of its environment and the DB2 features in effect.

There are a plethora of session variables available to the DB2 programmer as outlined in the accompanying table. 

Table 1. DB2 Session Variables.

Session Variable	Description
APPLICATION_ENCODING_SCHEME	The application encoding scheme specified for the subsystem.
COBOL_STRING_DELIMITER	The string delimiter. Value will be DEFAULT, “, or ‘.
DATA_SHARING_GROUP_NAME	Name of the data sharing group.
DATE_FORMAT	The date format in use. Value will be ISO, JIS, USA, EUR, or LOCAL.
DATE_LENGTH	The LOCAL DATE LENGTH install parameter. Value is 10-254, or 0 for no exit.
DECIMAL_ARITHMETIC	The DECIMAL ARITHMETIC install parameter. Value is DEC15, DEC31, 15, or 31.
DECIMAL_POINT	The DECIMAL POINT install parameter. Value is ‘.’ or ‘,’.
DEFAULT_DECFLOAT_ROUND_MODE	The DECFLOAT ROUNDING MODE install parameter.
DEFAULT_DEFAULT_SSID	The value of GROUP ATTACH field on the DSNTIPK installation panel or the SUBSYSTEM NAME field on the DSNTIPM installation panel.
DEFAULT_LANGUAGE	The LANGUAGE DEFAULT install parameter. Value is ASM, C, CPP, IBMCOB, FORTRAN, or PL/I.
DEFAULT_LOCALE_LC_CTYPE	The value of LOCALE LC_CTYPE install parameter.
DSNHDECP_NAME	The fully qualified data set name of the data set from which the DSNHDECP or a user-specified application defaults module was loaded.
DYNAMIC_RULES	The USE FOR DYNAMICRULES install parameter. Value is YES or NO.
ENCODING_SCHEME	The DEF ENCODING SCHEME install parameter. Value is EBCDIC, ASCII, or UNICODE.
MIXED_DATA	The MIXED DATA install parameter. Value is YES or NO.
NEWFUN	The INSTALL TYPE parameter. Value is INSTALL, UPDATE, MIGRATE, or ENFM; reflects the setting of the DSNHDECP variable NEWFUN.
PACKAGE_NAME	Name of the package currently in use.
PACKAGE_VERSION	Version of the current package.
PACKAGE_SCHEMA	Schema name of the current package.
PAD_NUL_TERMINATED	The PAD NUL-TERMINATEDinstall parameter. Value is YES or NO.
PLAN_NAME	Name of the plan currently being run.
SECLABEL	The user’s security label (if any); null if not defined.
SQL_STRING_DELIMITER	The SQL STRING DELIMITER install parameter. Value is DEFAULT, “, or ‘.
SSID	DB2 subsystem identifier.
STANDARD_SQL	The STD SQL LANGUAGE install parameter. Value is YES or NO.
SYSTEM_NAME	Name of the system, as defined in field SUBSYSTEM NAME on installation panel DSNTIPM.
SYSTEM_ASCII_CCSID	A comma-delimited string of the ASCII CCSIDs in use on this system.
SYSTEM_EBCDIC_CCSID	A comma-delimited string of the EBCDIC CCSIDs in use on this system.
SYSTEM_UNICODE_CCSID	A comma-delimited string of the UNICODE CCSIDs in use on this system.
TIME_FORMAT	The TIME FORMAT install parameter. Value is ISO, JIS, USA, EUR, or LOCAL.
TIME_LENGTH	The LOCAL TIME LENGTH install parameter. Value is 8-254, or 0 for no exit.
VERSION	Version of the DB2 subsystem. This value is a string, formatted as pppvvrrm where: ppp is a product string set to the value ‘DSN’. vv is a two-digit version identifier such as ‘09’. rr is a two-digit release identifier such as ‘01’. m is a one-digit maintenance level identifier.

Each session variable must be qualified by SYSIBM. A built-in function named GETVARIABLE can retrieve session variable values. So, you could create a view based on a security label, for example:

CREAT VIEW VSECLBL AS

  SELECT column-list

  FROM   table-name

  WHERE  SECLABEL_COL = GETVARIABLE(SYSIBM.SECLABEL);

The GETVARIABLE built-in function can be used in views, triggers, stored procedures, and constraints to enforce a security policy.

Users can add up to ten session variables by setting the name and value in their connection or sign-on exits. User-created session variables are qualified by SESSION. For example, the customer might have a connection or sign-on exit that examines the SQL user’s IP address, and maps the IP address to the user’s site within the company. This is recorded in a session variable, named say, USER_SITE. This session variable is then accessible using the built-in function, for example:

GETVARIABLE(SESSION.USER_SITE)

Using session variables much more information is available to application programs as they execute, and more control and security is provided, as well. Additionally, session variables can be trusted. They are set by DB2 and an application cannot modify them.