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

DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows

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 users                                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to DB2 version 10.1.0.2.                             *
  ****************************************************************
  

Problem conclusion

• The problem is first fixed in DB2 version 10.1.0.2.
  

Temporary fix

Comments

APAR Information

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

  IC85433

• 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

• RA10 PSN

     UP