BIF COMPATIBILITY field (BIF_COMPATIBILITY subsystem parameter)

The BIF_COMPATIBILITY subsystem parameter specifies whether built-in functions and specifications are to return results in the newer format or revert to the pre-DB2 10 format.

Recommendation: Avoid making online changes to the BIF_COMPATIBILITY setting, even though they are supported. Such changes do not affect SQL statements in the dynamic SQL statement cache.

Acceptable values:	V9_DECIMAL_VARCHAR, V9_TRIM, CURRENT, or V9
Default:	For installation: CURRENT For migration: the setting that your Db2 11 DSNTIDXA or DSNTIDXB input member specifies
Update:	option 47 on panel DSNTIPB
DSNZP`xxx`:	DSN6SPRM.BIF_COMPATIBILITY

In Db2 12, when the input data to the CHAR or VARCHAR function is a decimal value or a decimal is input to a CAST specification where the target data type is CHAR or VARCHAR, the result does not have any of the following attributes:

Leading zeros.
A trailing decimal point character.
Leading blanks for positive decimal values.

You might need to modify some of your applications to handle this change, which differs from the behavior in DB2 9. While you prepare for this change, you can use the following options of the BIF_COMPATIBILITY subsystem parameter to temporarily revert to the DB2 9 behavior.

CURRENT: All built-in functions return data in the Db2 12 format, unless they are controlled by the APPLCOMPAT bind option. When you run the installation CLIST in INSTALL mode, CURRENT is the default setting.
V9_DECIMAL_VARCHAR: For decimal input, the CHAR and VARCHAR functions, and the CAST specification with decimal input and a target data type of CHAR or VARCHAR, return data in the DB2 9 format.
Tip: You can run with BIF_COMPATIBILITY set to CURRENT, and use the CHAR9 or VARCHAR9 functions instead for applications that depend on the DB2 9 behavior for the CHAR and VARCHAR functions with decimal input.

Db2 also continues to allow some unsupported timestamp strings for applications. Furthermore, as in previous Db2 releases, SQLCODE -180 is not issued for these unsupported timestamp strings. Because these strings are not supported by Db2, you must modify the application SQL to use a supported format for a timestamp string. For the supported formats, see String representations of datetime values.
V9_TRIM: The V9_DECIMAL_VARCHAR setting rules are in effect, and if the APPLCOMPAT bind option is V10R1, the LTRIM, RTRIM and STRIP built-in functions behave like in DB2 9 with respect to tolerating invalid EBCDIC mixed data strings and treatment of EBCDIC control characters X'0E0F'.
V9: For decimal input, the CHAR built-in function returns data in the DB2 9 format.
This value has no effect on the output of the VARCHAR built-in function with a decimal value as input or on the result of the CAST(decimal-expression AS VARCHAR) specification. Unless you need DB2 9 behavior only for CHAR output, specify the V9_DECIMAL_VARCHAR value.

Examples

The following examples show the effect of the BIF_COMPATIBILITY subsystem parameter on the output of these built-in functions and cast specifications.

Table 1. Example output for CHAR based on BIF_COMPATIBILITY settings
BIF_COMPATIBILITY setting	OUTPUT for CHAR(000.1)	OUTPUT for CHAR(1000.)	OUTPUT for CHAR(1.1)
V9_DECIMAL_VARCHAR	' 000.1'	' 1000.'	' 1.1'
CURRENT	'.1 '	'1000 '	'1.1 '
V9	' 000.1'	' 1000.'	' 1.1'

Table 2. Example output for VARCHAR and CAST(`decimal-expression` AS VARCHAR) based on BIF_COMPATIBILITY settings
BIF_COMPATIBILITY setting	Output for VARCHAR(000.1) or CAST(000.1 AS VARCHAR(3))	Output for VARCHAR(1000.) or CAST(1000. AS VARCHAR(5))	Output for VARCHAR(1.1) or CAST(1.1 AS VARCHAR(3))
V9_DECIMAL_VARCHAR	'0.1'	'1000.'	'1.1'
CURRENT	'.1'	'1000'	'1.1'

Table 3. Example output for CAST(`decimal-expression` AS CHAR) based on BIF_COMPATIBILITY settings
BIF_COMPATIBILITY setting	Output for CAST(000.1 AS CHAR(6))	Output for CAST(1000. AS CHAR(6))	Output for CAST(1.1 AS CHAR(4))
V9_DECIMAL_VARCHAR	'0.1 '	'1000. '	'1.1 '
CURRENT	'.1 '	'1000 '	'1.1 '
V9	'.1 '	'1000 '	'1.1 '