IBM Support

IC85433: BATCH INSERTS CAUSING DUPLICATE ROWS WHEN USING NULLIDRA (REOPT=ALWAYS) VS. NULLIDR1 (REOPT=ONCE)

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Data might become corrupt when performing batch inserts into the
    DB2 database,
    resulting in duplicate data being inserted.
    This affects both Java and non-Java (DB2 Call Level Interface,
    .NET, ODBC)  applications.
    
    To Repro using Java:
    
    (1) Create the table
    db2 "CREATE TABLE db2inst1.t1 ( fieldone INTEGER NOT NULL,
    fieldtwo INTEGER NOT NULL )";
    db2 "bind db2clipk.bnd collection NULLIDRA"  /* This is
    REOPT=ALWAYS package */
    db2 "bind db2clipk.bnd collection NULLIDR1" /* This is
    REOPT=ONCE package */
    db2 connect reset;
    
    (2) Compile the Java test program with the following code and
    ensure
    currentPackageSet=NULLIDRA
     String
    URL="jdbc:db2://test.ibm.com:50000/SAMPLE:currentPackageSet=NULL
    IDRA;jdbcCollection=NULLIDRA;";
     Connection con = DriverManager.getConnection(URL, "db2inst1",
    "secret");
    
     String prepString="insert into db2inst1.t1(fieldone, fieldtwo)
    values(?, ?)";
    
     PreparedStatement pst = con.prepareStatement (prepString);
    
         for ( int i=0;i< 5;i++)
         {
             pst.setInt(1,i+1);
             pst.setInt(2,i+2);
             pst.addBatch();
         }
         pst.executeBatch();
         con.close();
    
    The following is the expected results from running the commands:
    
    $ db2 "select * from db2inst1.t1"
    
    FIELDONE    FIELDTWO
    ----------- -----------
              1           2
              2           3
              3           4
              4           5
              5           6
    
      5 record(s) selected.
    
    
    Instead of receiving these results, the data becomes corrupt,
    resulting in the same
    data being inserted in each row as shown:
    
    $ db2 "select * from db2inst1.t1"
    
    FIELDONE    FIELDTWO
    ----------- -----------
              1           2
              1           2
              1           2
              1           2
              1           2
    
      5 record(s) selected.
    

Local fix

  • Use currentPackageSet=NULLIDR1 (JDBC) or Reopt=3 (DB2 CLI)
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Apply DB2 version 9.7 Fixpack 7                              *
    ****************************************************************
    

Problem conclusion

  • First fixed in DB2 version 9.7 Fixpack 7.  This is a server side
    fix and should be applied to DB2 database server.
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC85433

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    YesSpecatt / Pervasive

  • Submitted date

    2012-07-19

  • Closed date

    2012-10-18

  • Last modified date

    2012-12-07

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

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

    IC85841 IC85842 IC85843

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC85433

Modified date: 07 December 2012