DB2 - Problem description
Problem IC85433 | Status: Closed |
BATCH INSERTS CAUSING DUPLICATE ROWS WHEN USING NULLIDRA (REOPT=ALWAYS) VS. NULLIDR1 (REOPT=ONCE) | |
product: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
Problem 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. | |
Problem Summary: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Apply DB2 version 9.7 Fixpack 7 * **************************************************************** | |
Local Fix: | |
Use currentPackageSet=NULLIDR1 (JDBC) or Reopt=3 (DB2 CLI) | |
available fix packs: | |
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows | |
Solution | |
First fixed in DB2 version 9.7 Fixpack 7. This is a server side fix and should be applied to DB2 database server. | |
Workaround | |
not known / see Local fix | |
BUG-Tracking | |
forerunner : APAR is sysrouted TO one or more of the following: IC85841 IC85842 IC85843 follow-up : | |
Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 19.07.2012 18.10.2012 07.12.2012 |
Problem solved at the following versions (IBM BugInfos) | |
9.7.FP7 | |
Problem solved according to the fixlist(s) of the following version(s) | |
9.7.0.7 |