Built-in session variables
Db2 provides several built-in session variables that contain information about the server and application process. The value of a built-in session variable can be obtained by invoking the GETVARIABLE function with the name of the built-in session variable.
Db2 provides the following built-in session variables:
- SYSIBM.APPLICATION_ENCODING_SCHEME
- The SYSIBM.APPLICATION_ENCODING_SCHEME session variable contains a string that corresponds to the value that is specified for the APPLICATION ENCODING field on the DSNTIPF installation panel. The value is EBCDIC, ASCII, UNICODE, or 1–65533, and this session variable can never be null.
- SYSIBM.COBOL_STRING_DELIMITER
- The session SYSIBM.COBOL_STRING_DELIMITER variable contains a string that corresponds to the value that is specified for the STRING DELIMITER field on the DSNTIPF installation panel. The value is DEFAULT, ", or ', and this session variable can never be null.
- SYSIBM.DATA_SHARING_GROUP_NAME
- The SYSIBM.DATA_SHARING_GROUP_NAME session variable contains a string that corresponds to the name of the data sharing group for this Db2 subsystem. If the subsystem is not part of data sharing group, the null value is returned.
- SYSIBM.DATE_FORMAT
- The SYSIBM.DATE_FORMAT session variable contains a string that corresponds to the value that is specified for the DATE FORMAT field on the DSNTIP4 installation panel. The value is ISO, JIS, USA, EUR, or LOCAL, and this session variable can never be null.
- SYSIBM.DATE_LENGTH
- The SYSIBM.DATE_LENGTH session variable contains a string that corresponds to the value that is specified for the LOCAL DATE LENGTH field on the DSNTIP4 installation panel. The value is 10–254, or 0 for no exit, and this session variable can never be null.
- SYSIBM.DECIMAL_ARITHMETIC
- The session variable contains a string that corresponds to the value that is specified for the DECIMAL ARITHMETIC field on the DSNTIP4 installation panel. The value is DEC15, DEC31, 15, or 31, and this session variable can never be null.
- SYSIBM.DECIMAL_POINT
- The session variable contains a string that corresponds to the value that is specified for the DECIMAL POINT IS field on the DSNTIPF installation panel. The value is '.' or ',' and this session variable can never be null.
- SYSIBM.DEFAULT_DECFLOAT_ROUND_MODE
- The SYSIBM.DEFAULT_DECFLOAT_ROUND_MODE session variable contains a string that corresponds to the value that is specified for the DECFLOAT ROUNDING MODE field on the DSNTIPF installation panel. This session variable can never be null.
- SYSIBM.DEFAULT_SSID
- The SYSIBM.DEFAULT_SSID session variable contains a string that corresponds to the value that is specified for the GROUP ATTACH field on the DSNTIPK installation panel or the SUBSYSTEM NAME field on the DSNTIPM installation panel. This session variable can never be null.
- SYSIBM.DEFAULT_LANGUAGE
- The SYSIBM.DEFAULT_LANGUAGE session variable contains a string that corresponds to the value that is specified for the LANGUAGE DEFAULT field on the DSNTIPF installation panel. The value is ASM, C, CPP, IBMCOB, FORTRAN, or PL/I, and this session variable can never be null.
- SYSIBM.DEFAULT_LOCALE_LC_CTYPE
- The SYSIBM.DEFAULT_LOCALE_LC_CTYPE session variable contains a string that corresponds to the value that is specified for the LOCALE LC_CTYPE field on the DSNTIPF installation panel. This session variable can never be null.
- SYSIBM.DISTRIBUTED_SQL_STRING_DELIMITER
- The SYSIBM.DISTRIBUTED_SQL_STRING_DELIMITER session variable contains a string that corresponds to the value that is specified for the DIST SQL STR DELIMTR field on the DSNTIPF installation panel. The value is ", or ', and this session variable can never be null.
- SYSIBM.DSNHDECP_NAME
- The SYSIBM.DSNHDECP_NAME session variable contains a string that corresponds to the fully qualified data set name of the data set from which the DSNHDECP or a user-specified application defaults module was loaded. For instance, 'DSN910.SDSNEXIT(DSNHDECP)'. This session variable can never be null.
- SYSIBM.DYNAMIC_RULES
- The SYSIBM.DYNAMIC_RULES session variable contains a string that corresponds to the value that is specified for the USE FOR DYNAMICRULES field on the DSNTIP4 installation panel. The value is YES or NO, and this session variable can never be null.
- SYSIBM.ENCODING_SCHEME
- The SYSIBM.ENCODING_SCHEME session variable contains a string that corresponds to the value that is specified for the DEF ENCODING SCHEME field on the DSNTIPF installation panel. The value is EBCDIC, ASCII, or UNICODE, and this session variable can never be null.
- SYSIBM.MIXED_DATA
- The SYSIBM.MIXED_DATA session variable contains a string that corresponds to the value that is specified for the MIXED DATA field on the DSNTIPF installation panel. The value is YES or NO, and this session variable can never be null.
- SYSIBM.NEWFUN
- The SYSIBM.NEWFUN session variable contains a string that represents the default maximum level of the SQL function syntax that is accepted by the Db2 precompiler or the Db2 coprocessor, regardless of the Db2 function level. For more information about the NEWFUN option, see Descriptions of SQL processing options.
- SYSIBM.PACKAGE_NAME
- The SYSIBM.PACKAGE_NAME session variable contains a string that corresponds to the name of the package that is currently being executed. If a package is not currently being executed, the null value is returned. (This situation can occur when the plan that is being executed bound one or more DBRMs directly).
- SYSIBM.PACKAGE_SCHEMA
- The SYSIBM.PACKAGE_SCHEMA session variable contains a string that corresponds to the collection ID of the package that is currently being executed. If a package is not currently being executed, the null value is returned.
- SYSIBM.PACKAGE_VERSION
- The SYSIBM.PACKAGE_VERSION session variable contains a string that corresponds to the version of the package that is currently being executed. If a package is not currently being executed, the null value is returned.
- SYSIBM.PAD_NUL_TERMINATED
- The SYSIBM.PAD_NUL_TERMINATED session variable contains a string that corresponds to the value that is specified for the PAD NUL-TERMINATED field on the DSNTIP4 installation panel. The value is YES or NO, and this session variable can never be null.
- SYSIBM.PLAN_NAME
- The SYSIBM.PLAN_NAME session variable contains a string that corresponds to the name to the plan that is currently being executed. This session variable can never be null.
- SYSIBM.SECLABEL
- The SYSIBM.SECLABEL session variable contains a string that corresponds to the RACF® SECLABEL value if any is defined for the current userid. If a value has not been defined, the null value is returned.
- SYSIBM.SQL_STRING_DELIMITER
- The SYSIBM.SQL_STRING_DELIMITER session variable contains a string that corresponds to the value that is specified for the SQL STRING DELIMITER field on the DSNTIPF installation panel. The value is DEFAULT, ", or ', and this session variable can never be null.
- SYSIBM.SSID
- The SYSIBM.SSID session variable contains a string that corresponds to the actual Db2 subsystem identifier for this Db2 subsystem. This session variable can never be null.
- SYSIBM.STANDARD_SQL
- The SYSIBM.STANDARD_SQL session variable contains a string that corresponds to the value that is specified for the STD SQL LANGUAGE field on the DSNTIP4 installation panel. The value is YES or NO, and this session variable can never be null.
- SYSIBM.SYSTEM_NAME
- The SYSIBM.SYSTEM_NAME session variable contains a string that corresponds to the name of the Db2 for z/OS subsystem, as defined in field SUBSYSTEM NAME on installation panel DSNTIPM. This session variable can never be null.
- SYSIBM.SYSTEM_ASCII_CCSID
- The SYSIBM.SYSTEM_ASCII_CCSID session variable contains a value that represents the ASCII CCSIDs that are in use on this system. The information is returned as a comma-delimited string that corresponds to the ASCII CCSID that was specified on installation panel DSNTIPF. The three values that are returned correspond to the SBCS, MIXED, and graphic CCSID that are in use for ASCII data on this system. A value of 65534 for the MIXED or graphic CCSID indicates that this system does not support storing data in that CCSID. This session variable can never be null.
- SYSIBM.SYSTEM_EBCDIC_CCSID
- The SYSIBM.SYSTEM_EBCDIC_CCSID session variable contains a value that represents the EBCDIC CCSIDs that are in use on this system. The information is returned as a comma-delimited string that corresponds to the EBCDIC CCSID that was specified on installation panel DSNTIPF. The three values that are returned correspond to the SBCS, MIXED, and graphic CCSID that are in use for EBCDIC data on this system. A value of 65534 for the MIXED or graphic CCSID indicates that this system does not support storing data in that CCSID. This session variable can never be null.
- SYSIBM.SYSTEM_UNICODE_CCSID
- The SYSIBM.SYSTEM_UNICODE_CCSID session variable contains a value that represents the Unicode CCSIDs that are in use on this system. The information is returned as a comma-delimited string that corresponds to the UNICODE CCSID that was specified on installation panel DSNTIPF. The three values that are returned correspond to the SBCS, MIXED, and graphic CCSID that are in use for Unicode data on this system. This session variable can never be null.
- SYSIBM.TIME_FORMAT
- The SYSIBM.TIME_FORMAT session variable contains a string that corresponds to the value that is specified for the TIME FORMAT field on the DSNTIP4 installation panel. The value is ISO, JIS, USA, EUR, or LOCAL, and this session variable can never be null.
- SYSIBM.TIME_LENGTH
- The SYSIBM.TIME_LENGTH session variable contains a string that corresponds to the value that is specified for the LOCAL TIME LENGTH field on the DSNTIP4 installation panel. The value is 8–254 or 0 for no exit, and this session variable can never be null.
- SYSIBM.VERSION
- The SYSIBM.VERSION session variable contains a string that represents the version of Db2. The product identifier (PRDID) value is an 8-byte character value in pppvvrrm format, where: ppp is a 3-letter product code; vv is the version;rr is the release; and m is the modification level. In Db2 12 for z/OS, the modification level indicates a range of function levels:
- DSN12015 for V12R1M500 or higher.
- DSN12010 for V12R1M100.
For more information, see Product identifier (PRDID) values in Db2 for z/OS.
This session variable can never be null.
Example GETVARIABLE call
For example, the following statement sets the value of host variable hv1 to the name of the plan that is currently being executed:
SET :hv1 = GETVARIABLE('SYSIBM.PLAN_NAME'); For more information about the GETVARIABLE function, see GETVARIABLE scalar function.