Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2011
    Posts
    1

    Unanswered: DB2 duplicate rows error code

    Hello.

    When I want to insert a duplicate row in a table from a console/tool (e.g.: DB Visualizer) I get the following error message:

    [Error Code: -803, SQL State: 23505] AN INSERTED OR UPDATED VALUE IS INVALID BECAUSE INDEX IN INDEX SPACE IDXR1PXX CONSTRAINS COLUMNS OF THE TABLE SO NO TWO ROWS CAN CONTAIN DUPLICATE VALUES IN THOSE COLUMNS. RID OF EXISTING ROW IS X'0000000301'.. SQLCODE=-803, SQLSTATE=23505, DRIVER=3.57.82
    which is OK.

    When I want to run the same query from a Java application using PreparedStatement.executeBatch() (batch because it could insert more than one row at a time) I get a different error message:
    com.ibm.db2.jcc.am.wn: [jcc][t4][102][10040][3.57.82] Batch failure. The batch was submitted, but at least one exception occurred on an individual member of the batch.
    Use getNextException() to retrieve the exceptions for specific batched elements. ERRORCODE=-4228, SQLSTATE=null
    com.ibm.db2.jcc.am.co: A NON-ATOMIC INSERT STATEMENT ATTEMPTED TO PROCESS MULTIPLE ROWS OF DATA, BUT ERRORS OCCURRED

    and the error code is -4228

    Why this difference? Did I do something wrong?

    I use DB2 version 9 on z/OS (DSN09015) and the DB2 driver version is 3.57.82

    I intentionally want to insert a row that already exists in the table and I expect to have the -803 error code returned, but instead, in the Java application, I get a different error code: -4228

    The Java application behaves as expected on Oracle and MSSQL databases and there it returns the error codes that are expected in case of duplicated row error ( 2601 for MSSQL and 1 for Oracle). The problem is on a DB2 database where it should return -803 but instead it returns -4228.

    Any help would be appreciated!

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Sounds like you either have to use an ATOMIC statement, or Use getNextException() to retrieve the exceptions for specific batched elements. If there is only one SQL statement that can insert multiple rows being executed, then maybe you don't have use PreparedStatement.executeBatch() ?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •