Wednesday, August 15, 2012

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
The application encoding scheme specified for the subsystem. 
The string delimiter. Value will be DEFAULT, , or .
Name of the data sharing group.
The date format in use. Value will be ISO, JIS, USA, EUR, or LOCAL.
The LOCAL DATE LENGTH install parameter. Value is 10-254, or 0 for no exit.
The DECIMAL ARITHMETIC install parameter. Value is DEC15, DEC31, 15, or 31.
The DECIMAL POINT install parameter. Value is ‘.’ or ‘,’.
The DECFLOAT ROUNDING MODE install parameter.
The value of GROUP ATTACH field on the DSNTIPK installation panel or the SUBSYSTEM NAME field on the DSNTIPM installation panel. 
The LANGUAGE DEFAULT install parameter. Value is ASM, C, CPP, IBMCOB, FORTRAN, or PL/I.

The value of LOCALE LC_CTYPE install parameter.
The fully qualified data set name of the data set from which the DSNHDECP or a user-specified application defaults module was loaded. 

The USE FOR DYNAMICRULES install parameter. Value is YES or NO.
The DEF ENCODING SCHEME install parameter. Value is EBCDIC, ASCII, or UNICODE.
The MIXED DATA install parameter. Value is YES or NO.
The INSTALL TYPE parameter. Value is INSTALL, UPDATE, MIGRATE, or ENFM; reflects the setting of the DSNHDECP variable NEWFUN.

Name of the package currently in use.
Version of the current package.
Schema name of the current package.
 The PAD NUL-TERMINATED install parameter. Value is YES or NO.
Name of the plan currently being run.
The user’s security label (if any); null if not defined.
The SQL STRING DELIMITER install parameter. Value is DEFAULT, or 
DB2 subsystem identifier.
The STD SQL LANGUAGE install parameter. Value is YES or NO.
Name of the system, as defined in field SUBSYSTEM NAME on installation panel DSNTIPM.
A comma-delimited string of the ASCII CCSIDs in use on this system.
A comma-delimited string of the EBCDIC CCSIDs in use on this system.
A comma-delimited string of the UNICODE CCSIDs in use on this system.

The TIME FORMAT install parameter. Value is ISO, JIS, USA, EUR, or LOCAL.
The LOCAL TIME LENGTH install parameter. Value is 8-254, or 0 for no exit.
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:

  SELECT column-list
  FROM   table-name

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:


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.

No comments: