IC95046: THE DBMS_LOB.COMPARE FUNCTION AND DBMS_LOB.READ PROCEDURE DO NOT PROCESS CLOBS CORRECTLY IF MULTI-BYTE CHARACTERS ARE PRESENT

DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows

APAR status

• Closed as program error.

Error description

• The dbms_lob.compare function incorrectly expects the unit of
  measurement for the offset_1 and offset_2 parameters to be read
  in terms of the number of bytes, regardless of the type of data
  passed to the lob_1 and lob_2 parameters.  Instead, the
  dbms_lob.compare function should use number of characters as the
  unit of measurement for these parameters in order to correctly
  handle multi-byte character sets.
  
  For example, the following call to the dbms_lob.compare function
  should compare the 2nd character of each string and return -1
  because "a" is not equal to "b".  However, it return 0 because
  it is comparing the second byte of the the double-byte character
  "ﾁ" to the second byte of the character "ﾁ".
  db2 "values(dbms_lob.compare(clob('ﾁa'), clob('ﾁb'), 1, 2, 2))"
  
  Similarly, the following command should fail with SQL0138N (A
  numeric argument of a built-in string function is out of range.
  SQLSTATE=22011), but it returns -1 instead.
  db2 "values(dbms_lob.compare(clob('ﾁa'), clob('ﾁb'), 1, 3, 3))"
  
  The problem can only be hit when the database codepage supports
  multi-byte characters.
  
  The dbms_lob.read procedure has the same problem with the
  "offset" and "amount" parameters.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * All users                                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to DB2 version 10.1.0.4.                             *
  ****************************************************************
  

Problem conclusion

• The problem is first fixed in DB2 version 10.1.0.4.
  

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:

  IC97269 IC97288 IT00542

Fix information

• Fixed component name

  DB2 FOR LUW

• Fixed component ID

  DB2FORLUW

Applicable component levels

• RA10 PSN

     UP