BIF COMPATIBILITY field (BIF_COMPATIBILITY subsystem parameter)

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

Acceptable values:	V9_DECIMAL_VARCHAR, V9_TRIM, or CURRENT
Default:	for installation: CURRENT; for migration: V9_DECIMAL_VARCHAR
Update:	option 33 on panel DSNTIPB
DSNZPxxx:	DSN6SPRM.BIF_COMPATIBILITY

In DB2 10, when the input data is in decimal format, the CHAR, VARCHAR, CAST(decimal-expression AS CHAR), and CAST(decimal-expression AS VARCHAR) functions no longer return values with any of the following attributes:

• Leading zeros.
• The trailing decimal point character.
• Leading blanks for positive decimal values. This change applies to the CHAR built-in function only.

In DB2 10, invalid EBCDIC mixed data strings that are provided as arguments to LTRIM, RTRIM or STRIP cause the function to issue SQLCODE -171.

When the string-expression is an EBCDIC mixed data string, a shift-out control character (X'0E') followed immediately by a shift-in control character (X'0F') can be trimmed along with instances of the trim-character.

If you are migrating from Version 8 or DB2 9, applications that use these functions or cast specifications can produce unexpected output. You might need to modify some of your applications to handle this change. While you prepare for this change, you can use the following options of the BIF_COMPATIBILITY subsystem parameter to temporarily revert to the pre-DB2 10 behavior. Before you use these options, apply the PTFs for APARs PM29124 and PM66095.

If the BIF_COMPATIBILITY subsystem parameter is set to a value other than CURRENT, DB2 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. Apply the PTF for APAR PM48741 to identify applications that use the 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. See String representations of datetime values for supported formats.

V9_DECIMAL_VARCHAR

For decimal input, the CHAR and VARCHAR functions and the CAST(decimal-expression AS CHAR) and CAST(decimal-expression AS VARCHAR) specifications return data in the DB2 9 format. When you run the installation CLIST in MIGRATE mode with a DSNTIDXA or DSNTIDXB input member, V9_DECIMAL_VARCHAR is the default setting for this parameter.

V9_TRIM

For decimal input, the CHAR, VARCHAR, CAST( ... AS CHAR) and CAST( ... as VARCHAR) built-in functions behave as they do when BIF_COMPATIBILITY is set to V9_DECIMAL_VARCHAR. In addition, the LTRIM, RTRIM and STRIP built-in functions behave like DB2 9 with respect to tolerating invalid EBCDIC mixed data strings and treatment of EBCDIC control characters X'0E0F'.

CURRENT

For decimal input, the CHAR and VARCHAR functions and the CAST(decimal-expression AS CHAR) and CAST(decimal-expression AS VARCHAR) specifications return data in the DB2 10 format. When you run the installation CLIST in INSTALL mode, CURRENT is the default setting for this parameter.

Recommendation: If you need the result of the CHAR and VARCHAR functions and CAST specification to be in Version 9 format when the input data is decimal data, specify value V9_DECIMAL_VARCHAR for the BIF_COMPATIBILILTY subsystem parameter. Value V9 is supported only for compatibility with previous releases.

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 '

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 '