IBM Support

IT19545: LOAD FROM CURSOR (LOCAL OR REMOTE DB) FROM SOURCE COLUMN CHAR(CODEUNITS32) MIGHT FAIL OR INSERT EXTRA TRAILING BYTES

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

 

APAR status

  • Closed as program error.

Error 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
    

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).
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to Db2 11.1 Mod 4 Fixpack 5 or higher                *
    ****************************************************************
    

Problem conclusion

  • First fixed in Db2 11.1 Mod 4 Fixpack 5
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT19545

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2017-03-06

  • Closed date

    2020-01-16

  • Last modified date

    2020-01-16

  • APAR is sysrouted FROM one or more of the following:

    IT16516

  • APAR is sysrouted TO one or more of the following:

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RB10 PSN

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 January 2020