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 IC72044 Status: Closed

Db2look dependency on locale settings might introduce wrong values on mimic
information

product:
DB2 FOR LUW / DB2FORLUW / 950 - DB2
Problem description:
Given the following table: 
 
CREATE TABLE "TEST"."TAB1"  ( 
                  "FIELDTYPE" CHAR(1) FOR BIT DATA ) 
 
 
db2 " select hex(HIGH2KEY) from sysstat.columns  WHERE COLNAME = 
'FIELDTYPE' AND TABNAME = 'TAB1' AND TABSCHEMA = 'TEST' " 
27E827 
 
The db2look output of 
 
db2look -d tlook -m -l -a -e -z TEST -t TAB1 -o tlook.out 
 
Depending on the locale settings, can show in the mimic 
information: 
 
 
UPDATE SYSSTAT.COLUMNS 
SET COLCARD=36, 
    NUMNULLS=0, 
    SUB_COUNT=-1, 
    SUB_DELIM_LENGTH=-1, 
    AVGCOLLENCHAR=-1, 
    HIGH2KEY=X'1A',  <<< This is incorrect and we are 
introducing incorrect values if we replay this ddl 
    LOW2KEY=X'14', 
    AVGCOLLEN=2 
WHERE COLNAME = 'FIELDTYPE' AND TABNAME = 'TAB1' AND TABSCHEMA = 
'TEST'; 
 
or 
 
UPDATE SYSSTAT.COLUMNS 
SET COLCARD=36, 
    NUMNULLS=0, 
    SUB_COUNT=-1, 
    SUB_DELIM_LENGTH=-1, 
    AVGCOLLENCHAR=-1, 
    HIGH2KEY='',  In here we have the character representation 
of 0xE28 which is a e with a tilde <<< this is incorrect and if 
we replay this ddl it will actually fail 
    LOW2KEY=X'14', 
    AVGCOLLEN=2 
WHERE COLNAME = 'FIELDTYPE' AND TABNAME = 'TAB1' AND TABSCHEMA = 
'TEST'; 
 
Depending on the locale settings 
 
 
The correct value should be: 
 
UPDATE SYSSTAT.COLUMNS 
SET COLCARD=36, 
    NUMNULLS=0, 
    SUB_COUNT=-1, 
    SUB_DELIM_LENGTH=-1, 
    AVGCOLLENCHAR=-1, 
    HIGH2KEY=X'E8', 
    LOW2KEY=X'14', 
    AVGCOLLEN=2 
WHERE COLNAME = 'FIELDTYPE' AND TABNAME = 'TAB1' AND TABSCHEMA = 
'TEST '
Problem Summary:
**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* Db2look dependency on locale settings might introduce wrong  * 
* values on mimic information                                  * 
*                                                              * 
* Given the following table:                                   * 
*                                                              * 
*                                                              * 
*                                                              * 
* CREATE TABLE "TEST"."TAB1"  (                                * 
*                                                              * 
*                   "FIELDTYPE" CHAR(1) FOR BIT DATA )         * 
*                                                              * 
*                                                              * 
*                                                              * 
*                                                              * 
*                                                              * 
* db2 " select hex(HIGH2KEY) from sysstat.columns  WHERE       * 
* COLNAME =                                                    * 
* 'FIELDTYPE' AND TABNAME = 'TAB1' AND TABSCHEMA = 'TEST' "    * 
*                                                              * 
* 27E827                                                       * 
*                                                              * 
*                                                              * 
*                                                              * 
* The db2look output of                                        * 
*                                                              * 
*                                                              * 
*                                                              * 
* db2look -d tlook -m -l -a -e -z TEST -t TAB1 -o tlook.out    * 
*                                                              * 
*                                                              * 
*                                                              * 
* Depending on the locale settings, can show in the mimic      * 
*                                                              * 
* information:                                                 * 
*                                                              * 
*                                                              * 
*                                                              * 
*                                                              * 
*                                                              * 
* UPDATE SYSSTAT.COLUMNS                                       * 
*                                                              * 
* SET COLCARD=36,                                              * 
*                                                              * 
*     NUMNULLS=0,                                              * 
*                                                              * 
*     SUB_COUNT=-1,                                            * 
*                                                              * 
*     SUB_DELIM_LENGTH=-1,                                     * 
*                                                              * 
*     AVGCOLLENCHAR=-1,                                        * 
*                                                              * 
*     HIGH2KEY=X'1A',  <<< This is incorrect and we are        * 
*                                                              * 
* introducing incorrect values if we replay this ddl           * 
*                                                              * 
*     LOW2KEY=X'14',                                           * 
*                                                              * 
*     AVGCOLLEN=2                                              * 
*                                                              * 
* WHERE COLNAME = 'FIELDTYPE' AND TABNAME = 'TAB1' AND         * 
* TABSCHEMA =                                                  * 
* 'TEST';                                                      * 
*                                                              * 
*                                                              * 
*                                                              * 
* or                                                           * 
*                                                              * 
*                                                              * 
*                                                              * 
* UPDATE SYSSTAT.COLUMNS                                       * 
*                                                              * 
* SET COLCARD=36,                                              * 
*                                                              * 
*     NUMNULLS=0,                                              * 
*                                                              * 
*     SUB_COUNT=-1,                                            * 
*                                                              * 
*     SUB_DELIM_LENGTH=-1,                                     * 
*                                                              * 
*     AVGCOLLENCHAR=-1,                                        * 
*                                                              * 
*     HIGH2KEY='',  In here we have the character              * 
* representation                                               * 
* of 0xE28 which is a e with a tilde <<< this is incorrect and * 
* if                                                           * 
* we replay this ddl it will actually fail                     * 
*                                                              * 
*     LOW2KEY=X'14',                                           * 
*                                                              * 
*     AVGCOLLEN=2                                              * 
*                                                              * 
* WHERE COLNAME = 'FIELDTYPE' AND TABNAME = 'TAB1' AND         * 
* TABSCHEMA =                                                  * 
* 'TEST';                                                      * 
*                                                              * 
*                                                              * 
*                                                              * 
* Depending on the locale settings                             * 
*                                                              * 
*                                                              * 
*                                                              * 
*                                                              * 
*                                                              * 
* The correct value should be:                                 * 
*                                                              * 
*                                                              * 
*                                                              * 
* UPDATE SYSSTAT.COLUMNS                                       * 
*                                                              * 
* SET COLCARD=36,                                              * 
*                                                              * 
*     NUMNULLS=0,                                              * 
*                                                              * 
*     SUB_COUNT=-1,                                            * 
*                                                              * 
*     SUB_DELIM_LENGTH=-1,                                     * 
*                                                              * 
*     AVGCOLLENCHAR=-1,                                        * 
*                                                              * 
*     HIGH2KEY=X'E8',                                          * 
*                                                              * 
*     LOW2KEY=X'14',                                           * 
*                                                              * 
*     AVGCOLLEN=2                                              * 
*                                                              * 
* WHERE COLNAME = 'FIELDTYPE' AND TABNAME = 'TAB1' AND         * 
* TABSCHEMA =                                                  * 
* 'TEST '                                                      * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.5 and Fix Pack 8                    * 
****************************************************************
Local Fix:
Check settings of LC_CTYPE as db2look is using isalnum calls.
available fix packs:
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
Problem was first fixed in DB2 Version 9.5 and Fix Pack 8
Workaround
not known / see Local fix
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :
20.10.2010
27.06.2011
27.06.2011
Problem solved at the following versions (IBM BugInfos)
9.5.
Problem solved according to the fixlist(s) of the following version(s)
9.5.0.8 FixList
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.