PI25837: RTRIM RETURNS SQLCODE171 WHEN THE INPUT IS AN INVALID MIXED STRING

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• RTRIM RETURNS SQLCODE171 WHEN THE INPUT IS AN INVALID MIXED
  STRING. This is the DB2 v11 apar for PI20314.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 11 for z/OS users of EBCDIC mixed    *
  *                 data and the RTRIM, LTRIM or STRIP scalar    *
  *                 functions.                                   *
  ****************************************************************
  * PROBLEM DESCRIPTION: After migrating to DB2 for z/OS Version *
  *                      11, the scalar functions RTRIM, LTRIM   *
  *                      and STRIP may return an SQLCODE -171 if *
  *                      the input expression is an invalid      *
  *                      EBCDIC mixed data string.  In Version   *
  *                      9, some invalid EBCDIC mixed data       *
  *                      strings were tolerated.                 *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  DB2 Version 10 provided extended functionality for the LTRIM,
  RTRIM and STRIP functions.  With these extensions, the
  following behaviors changed :
  
  1. If the input argument to LTRIM or RTRIM is an invalid
  EBCDIC mixed data string, Version 10 and 11 may return an
  SQLCODE -171. For this format, Version 9 did not return an
  SQLCODE. For example,
  
  Column C1 contains X'40400F0F4040' in Table T1.
  (two consecutive shift-in control characters (X'0F') is illegal)
  
  SELECT RTRIM(C1) FROM T1;
     V10 and V11 returns -171 sqlcode
     V9 returns X'40400F0F'
  
  
  2. In DB2 Version 10 and 11, a shift-out control character
  (X'0E') followed by a shift-in control character (X'0F') is
  removed when trimming blanks. In DB2 Version 9, this combination
  of control characters is not removed. For example:
  
  Column C1 contains X'40400E0F4040' in Table T1.
  
  SELECT RTRIM(C1) FROM T1;
     V10 and V11 returns the empty string
     V9 returns X'40400E0F'
  

Problem conclusion

• APAR  PI20314 provided relief on Version 10 for this
  incompatible change for EBCDIC mixed data input to the RTRIM,
  LTRIM and STRIP functions.  To continue that relief on
  Version 11, this APAR will :
  
  1. Document the incompatible changes in the "Application and SQL
  release incompatiblities for migration" section of the manual.
  
  2. Add a new value V9_TRIM for the BIF_COMPATIBILITY system
  parameter to provide a system-wide switch to get the version 9
  behavior for RTRIM, LTRIM and STRIP. Setting BIF_COMPATIBILITY
  to V9_TRIM would also include the Version 9 behavior indicated
  by V9_DECIMAL_VARCHAR.  This apar does not change the default
  value of BIF_COMPATIBILITY, which is CURRENT for new
  installations, and the DB2 Version 10 setting for systems
  migrating to DB2 Version 11.  The V9_TRIM value only applies
  when the data is EBCDIC.
  
  3. Write IFCID 366 or 376 trace records when the Version 9
  RTRIM, LTRIM or STRIP are being used.  A new value "10" will
  be used for the function type field QW0366FN.
  
  4. Add LTRIM, RTRIM and STRIP to the SYSCOMPAT_V9 and SYSCURRENT
  schemas to provide a way for a specific SQL statement
  or application to choose between the Version 9 and Version 11
  behavior.  The schema names must be in front of SYSIBM in the
  path to be effective.  The schemas and zparm values are used
  during function resolution to determine which LTRIM, RTRIM
  or STRIP function will be invoked (Version 9 or Version 11).
  
  Packages bound before Version 10 will continue to get the
  Version 9 behavior on migration to Version 11. When a package
  is bound on Version 11 after applying this apar, the
  BIF_COMPATIBILITY parameter or current path will be used to
  determine the behavior of the LTRIM, RTRIM or STRIP functions.
  
  Materialized query tables and indexes on expression created
  before Version 10 that reference these functions will need
  to be dropped and recreated with the BIF_COMPATIBILITY
  parameter or current path value set appropriately to get the
  Version 9 behavior. If a reference statement has a different
  behavior that is specified by the BIF_COMPATIBILITY parameter
  or a different current path, the materialized query table
  or expression based index is not used.
  
  Views and inline SQL functions use the behavior of the SQL
  statement that references the object. It is possible for
  references to the same view or function in different
  applications to get different behavior for these functions.
  
  
  Additional keywords : SQLMIGRATION SQLRTRIM SQLLTRIM SQLSTRIP
  

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:

  UI24215

Modules/Macros

•    DSNDQW05 DSNTIPX  DSNWZIFB DSNXGSFN DSNXOFN2
  DSNXORFN DSNXOXEX DSNXRDBC DSNXRDB9 DSNXRME9 DSNXRMXE DSNXRSBC
  DSNXRSB9 DSN6SPRM
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI24215

     UP15/01/20 P F501

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.