Latest versionsfixlist
11.1.0.6 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26
Question in the chat LiveZilla Live Help

DB2 - Problem description

Problem IC63747 Status: Closed

SELECT DISTINCT WITH LANGUAGE AWARE COLLATION SEQUENCE WITH EO S1
ATTRIBUTE MAY RETURN WRONG RESULTS

product:
DB2 FOR LUW / DB2FORLUW / 950 - DB2
Problem description:
In a unicode database which was created with a language aware 
collation sequence, like "UCA500R1_LROOT_AN_CX_EO_FX_HX_NX_S1" 
a SELECT DISTINCT may return wrong results. 
 
The problem is that despite attributes "EO S1" are specified, 
which should respect case sensitivity, a string may be sorted as 
a duplicate and therefore may be missing from the result set. 
 
This problem happens only if the case difference is after the 
7th character. 
 
For example suppose 5 rows containing the column values: 
 
Achter de Hoven 
Achter de Hoven 
Achter De Hoven 
Achter de Hoven 
Achter de Hovenstraat 
 
Then a "select distinct(column) ..." returns just: 
 
Achter de Hoven 
Achter de Hovenstraat 
 
which is wrong because "Achter De Hoven" is missing. The case 
sensitivity was not respected.
Problem Summary:
**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* In a unicode database which was created with a language      * 
* aware collation sequence, like                               * 
* "UCA500R1_LROOT_AN_CX_EO_FX_HX_NX_S1"                        * 
* a SELECT DISTINCT may return wrong results.                  * 
*                                                              * 
* The problem is that despite attributes "EO S1" are           * 
* specified, which should respect case sensitivity, a string   * 
* may be                                                       * 
* sorted as a duplicate and therefore may be missing from the  * 
* result                                                       * 
* set.                                                         * 
*                                                              * 
* This problem happens only if the case difference is after    * 
* the 7th character.                                           * 
*                                                              * 
* For example suppose 5 rows containing the column values:     * 
*                                                              * 
* Achter de Hoven                                              * 
*                                                              * 
* Achter de Hoven                                              * 
*                                                              * 
* Achter De Hoven                                              * 
*                                                              * 
* Achter de Hoven                                              * 
*                                                              * 
* Achter de Hovenstraat                                        * 
*                                                              * 
*                                                              * 
*                                                              * 
* Then a "select distinct(column) ..." returns just:           * 
*                                                              * 
* Achter de Hoven                                              * 
*                                                              * 
* Achter de Hovenstraat                                        * 
*                                                              * 
* which is wrong because "Achter De Hoven" is missing. The     * 
* case sensitivity was not respected.                          * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 UDB Version 9.5 FixPak 6                      * 
****************************************************************
Local Fix:
Either: 
 
Creating an index on the column allows select distinct to return 
the correct result. Verify that optimizer chooses the index. 
 
Or: 
 
Avoid a BINSORT used by DB2 internally by setting registry 
variable: 
 
db2set DB2_BINSORT=off
available fix packs:
DB2 Version 9.5 Fix Pack 6a for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.5 Fix Pack 10 for Linux, UNIX, and Windows

Solution
First fixed in DB2 UDB Version 9.5 FixPak 6
Workaround
see LOCAL FIX.
BUG-Tracking
forerunner  : APAR is sysrouted TO one or more of the following: IC63837 
follow-up : 
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
15.10.2009
01.09.2010
01.09.2010
Problem solved at the following versions (IBM BugInfos)
9.5.
Problem solved according to the fixlist(s) of the following version(s)
This site uses cookies to make it easier for us to provide you with our services. By using our site you agree to the use of cookies.