DB2 FOR LUW / DB2FORLUW / A50 - DB2

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

****************************************************************
* USERS AFFECTED:                                              *
* all                                                          *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Use local fix, or upgrade to the Db2 11.1 level that has the *
* 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).

not known / see Local fix

forerunner  : 
follow-up : IT19545 

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

08.08.2016
04.01.2019
04.01.2019