DB2 - Problem description
Problem IT19545 | Status: Closed |
LOAD FROM CURSOR (LOCAL OR REMOTE DB) FROM SOURCE COLUMN CHAR(CODEUNITS32) MIGHT FAIL OR INSERT EXTRA TRAILING BYTES | |
product: | |
DB2 FOR LUW / DB2FORLUW / B10 - DB2 | |
Problem description: | |
A load from cursor operation, when source data is from column of type CHAR(n CODEUNITS32), might try to insert extra trailing bytes into target table column. This affects both regular cursor (source and target tables are in the same database), and load remote fetch (the DECLARE CURSOR statement has a DATABASE clause; source and target tables are in different databases). There are 2 related failure symptoms: (1) in load remote fetch, a common use case is a migration scenario where source and target tables / columns are identical (but in different databases). The erroneous extra trailing bytes might not fit into identical target column (or even slightly larger target column), causing the row to be rejected with SQL3229W reason code 3. A sample scenario: db2 connect to test1 db2 "CREATE TABLE T (PROJECT_TYPE CHAR(1 CODEUNITS32) NOT NULL) ORGANIZE BY ROW" db2 "insert into T values ('A')" db2 terminate db2 connect to test2 db2 "CREATE TABLE T (PROJECT_TYPE CHAR(1 CODEUNITS32) NOT NULL) ORGANIZE BY ROW" db2 "DECLARE C1 CURSOR DATABASE TEST1 USER myuserid USING mypasswd FOR SELECT * FROM T WITH UR" db2 "LOAD FROM C1 OF CURSOR REPLACE INTO T" the load rejects the row with SQL3229W The field value in row "F0-1" and column "1" is invalid. The row was rejected. Reason code: "3". (2) if the target table column is large enough, then the load is successful but the column now contains erroneous extra trailing bytes, which are spaces (hex 0x20) if regular cursor load, and nulls (hex 0x00) if remote fetch load. A sample scenario: db2 connect to test db2 "CREATE TABLE TAB1 (C CHAR(2 CODEUNITS32))" db2 "insert into TAB1 values ('A')" db2 "select hex(C) from TAB1" ==> 0x'4120' db2 "CREATE TABLE TAB2 (C VARCHAR(10))" db2 "DECLARE C CURSOR FOR SELECT * FROM TAB1" db2 "LOAD FROM C OF CURSOR REPLACE INTO TAB2" ==> successful db2 "select hex(C) from TAB2" ==> should be 0x'4120', but got 0x'4120202020202020' i.e. extra spaces at the end ==> if this is a load remote fetch (i.e. DECLARE CURSOR statement has DATABASE clause; TAB1 is from a remote database) then we would see 0x'4120000000000000' i.e. extra nulls at the end | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to Db2 11.1 Mod 4 Fixpack 5 or higher * **************************************************************** | |
Local Fix: | |
In the declare cursor statement, cast the CHAR(n CODEUNITS32) source data column into equivalent VARCHAR i.e. VARCHAR(n CODEUNITS32). Do not do CURSOR LOAD - use intermediate flat files (ex. IXF). | |
Solution | |
Workaround | |
not known / see Local fix | |
BUG-Tracking | |
forerunner : IT16516 follow-up : | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 06.03.2017 16.01.2020 16.01.2020 |
Problem solved at the following versions (IBM BugInfos) | |
Problem solved according to the fixlist(s) of the following version(s) |