SUBSTR COMPATIBILITY field (SUBSTR_COMPATIBILITY subsystem parameter)

This subsystem parameter is new in Db2 12. It can be set at any function level, but it takes effect only after activation of function level 500 or higher.

Starting in function level V12R1M500 or higher (and not controlled by the APPLCOMPAT level), the SUBSTR_COMPATIBILITY subsystem parameter specifies whether calls to the SUBSTR built-in function consistently return the SQLCODE -138 error for invalid arguments that do not meet the requirements described in SUBSTR scalar function. That is, if SUBSTR_COMPATIBILITY is set to CURRENT, Db2 always enforces the documented rules for the SUBSTR built-in function and returns an SQL error code for invalid input. For more information about rules for valid input the SUBSTR built-in function, see . The behavior with the CURRENT setting improves compatibility with Db2 on other platforms.

Before you set the SUBSTR_COMPATIBILITY subsystem parameter to CURRENT, you might need to modify some of your applications to handle this change.

PREVIOUS

Specifies that the SUBSTR built-in function exhibits the previous incorrect behavior from before the PTF for APAR PH36071 was applied in Db2 12. That is, Db2 might incorrectly return a result for invalid input instead of an error code. This value is the default setting. For more information, see APAR PH36071.

CURRENT

Specifies that the SUBSTR built-in function always enforces the rules that are documented in the SUBSTR scalar function. If these rules are not met, Db2 returns an SQL error code.

If the SUBSTR_COMPATIBILITY subsystem parameter is set to PREVIOUS and the SUBSTR built-in function incorrectly returns a result for invalid input, Db2 writes an incompatibility trace record, IFCID 0376, with function code 0014 in QW0376FN. You can these trace records to identify and correct affected applications so that they will be compatible if the SUBSTR_COMPATIBILITY subsystem parameter is set to CURRENT in the future.