home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Latest versionsfixlist
11.1.0.7 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

DB2 - Problem description

Problem IC77374 Status: Closed

DATA TYPE UNDER THE TABLE COLUMNS BECOMES NULL AFTER RENAMING THE COLUMN
USING THE ALTER TABLE STATEMENT.

product:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problem description:
Here is a simple example: 
 
--> CREATE TYPE "TAB_1_1"."T_UDST" 
 
     AS ( 
 
    "NAME"            VARCHAR(20), 
 
    "HEADCOUNT"               INTEGER 
 
     ) 
 
    MODE DB2SQL 
 
    REF USING INTEGER; 
 
 
 
--> CREATE TABLE "TAB_1_1"."TAB_REF_SCOPE" OF 
"TAB_1_1"."T_UDST"( REF IS "OID" USER GENERATED ); 
 
 
 
--> CREATE TABLE "TAB_1_1"."TAB_SCOPE_EQ" ("COL1" 
REF("TAB_1_1"."T_UDST")); 
 
 
 
 
--> ALTER TABLE "TAB_1_1"."TAB_SCOPE_EQ" ALTER COLUMN "COL1" ADD 
SCOPE "TAB_1_1"."TAB_REF_SCOPE"; 
 
 
db2 "select tabschema, tabname, colname, typename, 
target_typeschema, target_typename, scope_tabschema, 
scope_tabname from 
syscat.columns where tabschema = 'TAB_1_1' and tabname = 
'TAB_SCOPE_EQ'" 
 
TABSCHEMA 
 
TABNAME 
 
COLNAME 
 
TYPENAME 
 
TARGET_TYPESCHEMA 
 
TARGET_TYPENAME 
 
SCOPE_TABSCHEMA 
 
SCOPE_TABNAME 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
TAB_1_1 
 
TAB_SCOPE_EQ 
 
COL1 
 
REFERENCE 
 
TAB_1_1 
 
T_UDST 
 
TAB_1_1 
 
TAB_REF_SCOPE 
 
  1 record(s) selected. 
 
Once the column is renamed as below, the data types under the 
columns becomes NULL. 
 
 
--> ALTER TABLE "TAB_1_1"."TAB_SCOPE_EQ" RENAME COLUMN "COL1" TO 
"COL1NEW"; 
 
 db2 "select tabschema, tabname, colname, typename, 
target_typeschema, target_typename, scope_tabschema, 
scope_tabname from syscat.columns where tabschema = 'TAB_1_1' 
and tabname = 'TAB_SCOPE_EQ'" 
 
TABSCHEMA 
 
TABNAME 
 
COLNAME 
 
TYPENAME 
 
TARGET_TYPESCHEMA 
 
TARGET_TYPENAME 
 
SCOPE_TABSCHEMA 
 
SCOPE_TABNAME 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
---------------------------------------------------------------- 
TAB_1_1 
 
TAB_SCOPE_EQ 
 
COL1NEW 
 
REFERENCE 
 
- 
 
- 
 
- 
 
- 
 
  1 record(s) selected.
Problem Summary:
**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* Data type under the table columns becomes NULL after         * 
* Renaming the column using the ALTER TABLE statement.         * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fix Pack 5.                       * 
****************************************************************
Local Fix:
You can rename the column to the correct name using the ALTER 
TABLE statement.
available fix packs:
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
Solution
First fixed in DB2 Version 9.7 Fix Pack 5.
Workaround
not known / see Local fix
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

06.07.2011
14.12.2011
14.12.2011
Problem solved at the following versions (IBM BugInfos)
9.7.FP5
Problem solved according to the fixlist(s) of the following version(s)
9.7.0.5 FixList