|
Problem IC73819
|
Status: Closed |
LOAD from IXF file into Oracle-compatible DB may fail to insert data, if input has NOT NULL column that has a 0-byte string |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
When performing LOAD operation into an Oracle-compatible
database (i.e. a database created with
DB2_COMPATIBILITY_VECTOR=ORA) from an IXF file, if DB2 first
loads a string with length=0, then it will treat all the
following strings as NULL values no matter if the string length
is zero or not. This problem will happen when the source table
column type is defined with NOT NULL (i.e. is not nullable), but
the target table column type is nullable.
This problem does not occur if we use IMPORT instead of LOAD.
It also does not occur if we use LOAD and DEL file instead of
LOAD and IXF file.
This problem can be reproduced in the following steps:
On source DB:
db2 "create db loaddb"
db2 "create table t1 (c1 varchar(10) not null )" -- c1 defined
with NOT NULL
db2 "insert into t1 values('')" -- insert '' before 'a'
db2 "insert into t1 values('a')"
db2 "export to t1.ixf of ixf select * from t1"
On target DB:
db2set DB2_COMPATIBILITY_VECTOR=ORA
db2 terminate
db2 "create db loaddb"
db2 "create table t1(c1 varchar(10))" -- on target, we define
the column c1 with NULL
db2 "load from t1.ixf of ixf replace into t1 "
$ db2 "select hex(c1) from t1"
1
--------------------
-
- << we lost 'a' here
2 record(s) selected. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* When performing LOAD operation into an Oracle-compatible *
* database (i.e. a database created with *
* DB2_COMPATIBILITY_VECTOR=ORA) from an IXF file, if DB2 first *
* loads a string with length=0, then it will treat all the *
* following strings as NULL values no matter if the string *
* length is zero or not. This problem will happen when the *
* source table column type is defined with NOT NULL (i.e. is *
* not nullable), but the target table column type is nullable. *
* *
* This problem does not occur if we use IMPORT instead of *
* LOAD. It also does not occur if we use LOAD and DEL file *
* instead of LOAD and IXF file. *
* *
* This problem can be reproduced in the following steps: *
* *
* *
* On source DB: *
* *
* db2 "create db loaddb" *
* *
* db2 "create table t1 (c1 varchar(10) not null )" -- c1 *
* defined *
* with NOT NULL *
* *
* db2 "insert into t1 values('')" -- insert '' before 'a' *
* *
* db2 "insert into t1 values('a')" *
* *
* db2 "export to t1.ixf of ixf select * from t1" *
* *
* *
* On target DB: *
* *
* db2set DB2_COMPATIBILITY_VECTOR=ORA *
* *
* db2 terminate *
* *
* db2 "create db loaddb" *
* *
* db2 "create table t1(c1 varchar(10))" -- on target, we *
* define *
* the column c1 with NULL *
* *
* db2 "load from t1.ixf of ixf replace into t1 " *
* *
* $ db2 "select hex(c1) from t1" *
* *
* 1 *
* -------------------- *
* - *
* - << we lost 'a' here *
* *
* 2 record(s) selected. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to db2 version 9.7 fix pack 4. *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
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 |
The fix will be included in db2 version 9.7 fix pack 4. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 11.01.2011
04.05.2011
04.05.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP4
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.4
|
 |
