PH36071: DB SQLCODE901 WHEN USING SUBSTR FUNCTION IN PREDICATE ON IDAA ACCELERATED QUERY

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQLCODE901 when using SUBSTR function in predicate while
  offloading ( accelerate ) the query to IDAA.
  The very same statement is running on Db2 for z/OS without
  issuing an SQLCODE. IDAA is correct here, but Db2 for z/OS
  need to return an SQLCODE.
  

Local fix

• rather than using SUBSTR use SUBSTRING, e.g.:
  SELECT SUBSTR (col1,7+11,6) FROM tb1;
  
  Change the query to the following:
  
  SELECT SUBSTRING(col1,7+11,6 , OCTETS) FROM tb1;
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All Db2 12 for z/OS users of the SUBSTR                      *
  * built-in function and/or who offload such                    *
  * queries to IDAA.                                             *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * When using the SUBSTR built-in                               *
  * function with a 'length' parameter                           *
  * that when added to the 'start'                               *
  * parameter, exceeds the 'length of the                        *
  * referenced string-expression', an                            *
  * SQLCODE138 error is expected.                                *
  * However, an incorrect substring is                           *
  * returned instead.                                            *
  * So, the SUBSTR built-in function does                        *
  * not follow the rules documented                              *
  * in the SQL Reference and return an                           *
  * error if they are not met.                                   *
  * When offloading such a query to IDAA,                        *
  * since Db2z fails to detect an error,                         *
  * the query may not complete on offload                        *
  * successfully. However, when offloading                       *
  * to Db2 LUW, an error is returned. So,                        *
  * this inconsistency should not exist.                         *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Apply corrective PTF when available                          *
  ****************************************************************
  When using the SUBSTR built-in function with a 'length'
  parameter, that when added to the 'start' parameter exceeds
  the 'length of the referenced string-expression', an
  SQLCODE138 error is expected. However, an incorrect substring
  is returned instead. So, the SUBSTR built-in function does not
  follow the rules documented in the Db2 SQL Reference and
  return an error if they are not met.
  The following example illustrates a simple case.
     SELECT SUBSTR('ABCD',2+1,3) FROM SYSIBM.SYSDUMMY1;
  Here we have a character string-expression 'ABCD' which has a
  length of 4. We have a 'start' parameter 2 + 1, and we have a
  desired substring length of 3. The request is for a substring
  of length 3 starting at position 3 in the source string 'ABCD'.
  The problem scenario is that a user requests more data than
  there is, given the specified starting position and requested
  length. There are not 3 characters remaining in the source
  string starting at the 3rd character, 'C', only two.
  
  The documented rule that is violated is:
  The value (or requested substring length) must be greater than
  or equal to 0 and less than or equal to n, where
   n is the length attribute of string-expression - start + 1
  So, we have   n = 4 - 3 + 1 = 2.
  So, now our 'value' or substring length argument of 3 does
  not satisfy this rule ( 0 <= 3 <= 2 ) since 3 is greater
  than n which is 2. So, based on Db2 z/OS documentation, Db2
  should issue an error.
  Please note: The problem behavior is limited to the start
  parameter being an expression, as in this case, '2 + 1'.
  To avoid this reported problem, the following work around can
  be employed using the SUBSTRING built-in function. Please note
  that the SUBSTR and SUBSTRING functions behave differently. So,
  for the above example and the SUBSTR function, an SQLCODE138
  should be issued, whereas for the SUBSTRING function, a result
  should be issued.
  SELECT SUBSTRING('ABCD',2+1,3,OCTETS) FROM SYSIBM.SYSDUMMY1;
  
  
  
              +-----+
              |     |
              +-----+
            1_| CD  |
              +-----+
  

Problem conclusion

• The SUBSTR code in Db2 has been modified to enforce the
  documented rule and issue an SQLCODE138 error. This change
  however does not come alone. A new SUBSTR zparm and IFCID376
  tracing are also being introduced. This will also allow the
  query offload to complete as expected, making IDAA and LUW to
  behave consistently.
  New zparm.
  A new system parameter (zparm), SUBSTR_COMPATIBILITY, is being
  provided to dictate a system wide behavior PREVIOUS or CURRENT
  for the SUBSTR function. The default behavior after this APAR
  is applied should be the 'old' or PREVIOUS behavior. If the
  setting is PREVIOUS, the 'old' behavior will be prevalent. If
  the setting is CURRENT, Db2 will enforce the 'new' behavior to
  support the documented rule and issue the appropriate
  SQLCODE138 for the detected error. Please reference the ++HOLD
  for more detail.
  New IFCID376 tracing.
  For the 'old' or PREVIOUS behavior, Db2 will write an IFCID376
  trace record if so desired. Also, the 'old' behavior will
  continue to return an incorrect result or substring.
  Please reference the ++HOLD for further detail.
  For the 'new' or CURRENT behavior, specified by the new zparm,
  and running at the Db2 Function Level V12R1M500 or higher,
  the 'new' or CURRENT behavior will be enforced. That is, an
  SQLCODE138 will be issued if the documented rule is violated
  as mentioned above.
  Additional Keywords: SQLCODE138 SQLSUBSTR INCORROUT
                       DB2INCORR/K SQLINCORROUT SQLINCORR
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UI76575

Modules/Macros

• DSNXRDBC DSNTINMF DSNDQW05 DSNDQWPZ DSNWZIFC DSNTIDXA DSNTIVMD
  DSNTIJUZ DSNTINST DSNTIDXC DSNTIWIA DSNTIDXB DSNTIVIA DSNTIWMD
  DSNTIWIE DSNTIVIE DSNXRSBC DSNWMSGS DSN@XAZP DSN6SPRM DSNTIPX
  DSNTXAZP DSNTIWPC DSNDSPRM DSNTIVMN DSNTIWMN DSNTIVAF DSNTIVIN
  DSNTIWMS DSNTIVMS DSNTIWAF DSNTIWIN
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RC10 PSY UI76575

     UP21/08/11 P F108

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.