Blog
DB2utor

Categories

January 11, 2011

DB2 Session Variables

Have you ever wished you could find the name of the plan your program is executing in? Or have you wanted to know which DB2 version you're running in, what DB2 mode you're executing under or the type of encoding that's being used?

DB2 provides a way of getting server and application process information through built-in session variables. These session variables are accessed through the GETVARIABLE SQL function using the variable name. For example, my program needs to know the name of the plan being executed.

Use: SET :hostvar1 = GETVARIABLE(SYSIBM.SYSPLAN);

The following lists session variables by DB2 version. For detailed descriptions, see the DB2 10 SQL Reference Guide.

Function    DB2 V8    DB2 V9    DB2 V10
SYSIBM.APPLICATION_ENCODING_SCHEME        X    X
SYSIBM.COBOL_STRING_DELIMITER        X    X
SYSIBM.DATA_SHARING_GROUP_NAME    X    X    X
SYSIBM.DATE_FORMAT        X    X
SYSIBM.DATE_LENGTH        X    X
SYSIBM.DECIMAL_ARITHMETIC        X    X
SYSIBM.DECIMAL_POINT        X    X
SYSIBM.DEFAULT_DECFLOAT_ROUND_MODE        X    X
SYSIBM.DEFAULT_DEFAULT_SSID        X    X
SYSIBM.DEFAULT_LANGUAGE        X    X
SYSIBM.DEFAULT_LOCALE_LC_CTYPE        X    X
SYSIBM.DISTRIBUTED_SQL_STRING_DELIMITER        X    X
SYSIBM.DSNHDECP_NAME        X    X
SYSIBM.DYNAMIC_RULES        X    X
SYSIBM.ENCODING_SCHEME        X    X
SYSIBM.MIXED_DATA        X    X
SYSIBM.NEWFUN        X    X
SYSIBM.PACKAGE_NAME    X    X    X
SYSIBM.PACKAGE_SCHEMA    X    X    X
SYSIBM.PACKAGE_VERSION    X    X    X
SYSIBM.PAD_NUL_TERMINATED        X    X
SYSIBM.PLAN_NAME    X    X    X
SYSIBM.SECLABEL    X    X    X
SYSIBM.SQL_STRING_DELIMITER        X    X
SYSIBM.SSID        X    X
SYSIBM.STANDARD_SQL        X    X
SYSIBM.SYSTEM_NAME    X    X    X
SYSIBM.SYSTEM_ASCII_CCSID    X    X    X
SYSIBM.SYSTEM_EBCDIC_CCSID    X    X    X
SYSIBM.SYSTEM_UNICODE_CCSID    X    X    X
SYSIBM.TIME_FORMAT        X    X
SYSIBM.TIME_LENGTH        X    X
SYSIBM.VERSION    X    X    X

You can also use the SQL SELECT statement to view the value for a given session variable. The following SQL statement displays the application encoding, plan name, SSID and DB2 version:

SELECT                                                   
       SUBSTR(                                           
       GETVARIABLE('SYSIBM.APPLICATION_ENCODING_SCHEME') 
       ,1,8) AS ENCODING
     , SUBSTR(                                           
       GETVARIABLE('SYSIBM.PLAN_NAME')                   
       ,1,8) AS PLAN_NAME                                
     , SUBSTR(                                           
       GETVARIABLE('SYSIBM.SSID')                        
       ,1,8) AS SSID                                     
     , SUBSTR(                                           
       GETVARIABLE('SYSIBM.VERSION')                     
       ,1,8) AS VERSION                                  
FROM SYSIBM.SYSDUMMY1;                                   

Here are the results of the above SQL statement once it's executed in SPUFI on a DB2 V9 subsystem:

ENCODING   PLAN_NAME  SSID      VERSION 
-------------+--------------+--------+-----------+
EBCDIC        DSNESPCS    D91A     DSN09015

I can see where session variables would help application vendors. Knowing the DB2 release they're connected to can enable vendors to more easily take advantage of the unique features available in a given DB2 version. But what other potential uses are there? If you've used session variables in your applications, please post a comment and share your experiences.