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 |