Start of change

SUBSTR COMPATIBILITY field (SUBSTR_COMPATIBILITY subsystem parameter)

The SUBSTR_COMPATIBILITY subsystem parameter specifies whether the Db2 built-in function SUBSTR always returns an error message for invalid input.

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.

Acceptable values: PREVIOUS, CURRENT
Default: PREVIOUS
Update: option 47 on panel DSNTIPB
DSNZPxxx: DSN6SPRM.SUBSTR_COMPATIBILITY
Data sharing scope: Group
Online changeable: Yes

Previously, during execution of the SUBSTR built-in function, Db2 sometimes incorrectly returned a result for invalid input instead of issuing an appropriate error message. After the PTF for APAR PH36071 is applied and Db2 12 function level 500 or higher is activated, the SUBSTR_COMPATIBILITY subsystem parameter is set to PREVIOUS by default and Db2 continues to behave as before the PTF was applied. If the SUBSTR_COMPATIBILITY subsystem parameter is set to CURRENT, Db2 always enforces the rules for the SUBSTR built-in function that are documented in the SQL Reference and returns an SQL error code if the rules are not met.

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 behavior from before installation of APAR PH36071. Db2 might incorrectly return a result for invalid input instead of an error code. This value is the default setting.
CURRENT
Specifies that the SUBSTR built-in function always enforces the rules that are documented in the SQL Reference. 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. Use 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.

Examples

The following table indicates how the result of the SUBSTR built-in function differs based on the SUBSTR_COMPATIBILITY setting:
Example input for SUBSTR built-in function Result with SUBSTR_COMPATIBILITY set to PREVIOUS Result with SUBSTR_COMPATIBILITY set to CURRENT
SUBSTR('ABCD',2+1,3) 'CD ' SQLCODE -138
SUBSTR('ABCD',3,3) SQLCODE -138 SQLCODE -138
SUBSTR('ABCD',2+1,2+1) SQLCODE -138 SQLCODE -138
End of change