PI40667: SQLCODE170 AND SQLCODE171 WHEN USING SUBSTRING FUNCTION AND ITS FIRST ARGUMENT IS BINARY DATA TYPE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• SQLCODE170 AND SQLCODE171 WHEN USING SUBSTRING FUNCTION AND ITS
  FIRST ARGUMENT IS BINARY DATA TYPE.
  (This will be the enabling apar. Apar PI28192 will be the
  pre-conditioning apar).
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 10 and DB2 11 for z/OS users of the  *
  *                 SUBSTRING built-in function.                 *
  ****************************************************************
  * PROBLEM DESCRIPTION: The SUBSTRING function is to return a   *
  *                      substring of a string.                  *
  *                                                              *
  *                      An SQLCODE170 or SQLCODE171 can be      *
  *                      returned when using the SUBSTRING       *
  *                      built-in function when its first        *
  *                      argument is a binary (or varbinary)     *
  *                      data type or binary expression.         *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  This apar PI40667 is the enabling apar. Apar PI28192 is the
  pre-conditioning apar and should already be applied.
  
  An SQLCODE170 or SQLCODE171 can be returned when using the
  SUBSTRING built-in function when its first argument is a binary
  or varbinary data type or binary expression.
  
  The following queries (using the SUBSTR and SUBSTRING functions)
  which should return the same result, helps illustrate the
  problem for the SUBSTRING function with a binary input argument.
  
  
    SELECT HEX(SUBSTRING(BINARY('ABD', 3), 1))
          FROM SYSIBM.SYSDUMMY1;
  
  This example returns an SQLCODE170 from DSNXOBFF.
  
  
    SELECT HEX(SUBSTR(BINARY('ABD', 3), 1))
          FROM SYSIBM.SYSDUMMY1;
  
  This example returns the expected result
  
     +--------+
   1_| C1C2C4 |
     +--------+
  
  However, the first query returns an SQLCODE instead of the
  expected or correct result.
  
  This apar will help provide the missing support for the
  SUBSTRING built-in function having a binary input so that it
  functions similarly to the SUBSTR built-in function.
  
  This apar will deliver the bindtime support and enable the new
  function and requires the pre-conditioning apar PI28192 to have
  previously been applied.
  

Problem conclusion

• The code is modified in DB2 to provide the functionality for
  the SUBSTRING function to support a binary input argument.
  
  This apar serves as the enabling or bind time support to be
  applied after apar PI28192 which provides the pre-conditioning
  support. This apar will enable the new functionality.
  
  
  
  Additionally, the following publication changes are being made
  to the DB2 10 and 11 for z/OS SQL Reference along with apar
  PI40667 for the BINARY SUBSTRING built-in function.
  
  The description for BINARY SUBSTRING has changed. Here is the
  new text.
  ----------------------------------------------------------------
  
  binary-expression
   An expression that specifies the string from which the result
   is derived. The string must be a built-in binary string. The
   result of the function is a binary string. A substring of
   binary-expression is zero or more contiguous units of
   binary-expression.
  
  
  
  start
   An expression that specifies the position within
   binary-expression to be the first byte of the result. It must
   be an integer value. Start can be negative or zero. (The length
   attribute of a varying-length string is its maximum length.)
   A value of 1 indicates that the first byte of the substring is
   the byte unit of binary-expression.
  
  
  
  length
   An expression that specifies the length of the resulting
   substring.
  
  
   If binary-expression is a fixed-length string, omission of
   length is an implicit specification of
  
        LENGTH(binary-expression) - start + 1
  
   from the start position to the last position of
   binary-expression.
  
  
   If binary-expression is a varying length string, omission of
   length is an implicit specification of zero or
  
        CHARACTER_LENGTH(binary-expression) - start + 1,
  
   whichever is greater. If the resulting length is zero, the
   result is an empty string.
  
  
  
   If specified, length must be a value of an integer data type.
   The value must be greater than or equal to 0. If a value
   greater than n is specified, where n is the length attribute
   of binary-expression - start + 1, then n is used as the length
   of the resulting substring.
  
  ----------------------------------------------------------------
  
  Also, a link is being provided for a "rigorous description" of
  the actual length and result (which has been changed slightly).
  
  
  Please note that information about the BINARY SUBSTRING changes
  will be included in the Information Management Software for z/OS
  Solutions Information Center
  (http://publib.boulder.ibm.com/infocenter/imzic) at a later
  date.
  
  
  
  Additional Keywords: SQLCODE170 SQLCODE171 SQLSUBSTRING
                       SQLSUBSTR SQLBINARY SQLVARBINARY
  

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:

  UI32169 UI32170

Modules/Macros

• DSNTIAM  DSNXEBR  DSNXGRTM DSNXGSFN DSNXOBFA
  DSNXOBFF DSNXOBF3 DSNXOBF4 DSNXOFN2
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RA10 PSY UI32169

     UP15/11/04 P F511

• RB10 PSY UI32170

     UP15/11/04 P F511

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.