GETVARIABLE

The GETVARIABLE function returns a varying-length character-string representation of the current value of the session variable that is identified by the argument.

>>-GETVARIABLE(string-constant-+------------------------------------+-)-><
                               +-,--default-value-------------------+     
                               '-,--CAST--(--NULL AS -VARCHAR(1)--)-'

The schema is SYSIBM.

string-constant

Specifies a string constant that contains the name of the session variable whose value is to be returned. The string constant:

Must have a length that does not exceed 142 bytes.
Must contain the fully qualified name of the variable, with no embedded blanks. Delimited identifiers must not be specified.
Must not contain lowercase letters or characters that cannot be specified in an ordinary identifier.

The schema qualifier for the variable must be:

SYSIBM for built-in session variables. For a list of the built-in session variables, see Built-in session variables.
SESSION for user-defined session variables. User-defined session variables are established via the connection or signon exit routines.

default-value

Specifies a string constant that contains the value to be returned if the specified variable does not exist or is not supported by DB2. default-value must be a string constant that does not exceed 255 bytes.

If default-value is not specified and the specified user-defined session variable does not exist or the built-in session variable is not supported by DB2, an error is returned.

CAST(NULL AS VARCHAR(1))

Specifies that a null value is to be returned if the specified variable does not exist or is not supported by DB2.

The data type of the result is VARCHAR(255). The result can be null.

The CCSID of the result is the CCSID for Unicode mixed data.

Example 1: Use the GETVARIABLE function to set the value of host variable :hv1 to the name of the plan that is currently being executed. The name of the built-in session variable that contains the name of the plan is SYSIBM.PLAN_NAME.

   SET :hv1 = GETVARIABLE('SYSIBM.PLAN_NAME');

If DB2 does not support the name of the session variable, an error is returned. For example, the following statement returns an error because DB2 does not support a built-in session variable that is named SYSIBM.XYZ.

   SET :hv1 = GETVARIABLE('SYSIBM.XYZ');

Example 2: Use the GETVARIABLE function to set the value of host variable :hv2 to the value for the user that is defined in user-defined session variable TEST. If the session variable has not been set or cannot be found, have the function return the value 'TEST FAILED'.

   SET :hv2 = GETVARIABLE('SESSION.TEST','TEST FAILED');

Example 3: Use the GETVARIABLE function to set the value of host variable :hv3 to a string representation of the SYSTEM EBCDIC CCSIDs. The name of the built-in session variable that contains the system EBCDIC CCSIDs is SYSIBM.SYSTEM_EBCDIC_CCSID.

   SET :hv3 = GETVARIABLE('SYSIBM.SYSTEM_EBCDIC_CCSID');

Regardless of the setting of the field MIXED DATA on the installation panel (YES or NO), the function returns three comma-delimited values that correspond to the SBCS, MIXED, and GRAPHIC CCSIDs for the encoding scheme.

For example, if the statement were issued on a system with the field MIXED DATA on the installation panel equal to NO and the default system CCSID of 37, this string would be returned:

'37,65534,65534'

If the statement were issued on a system with the field MIXED DATA on the installation panel equal to YES and a default system CCSID of 930 (the mixed CCSID for the system), this string would be returned:

'290,930,300'