Results 1 to 13 of 13

Thread: Sqlstate=23505

  1. #1
    Join Date
    Feb 2008
    Posts
    7

    Unanswered: Sqlstate=23505

    hi

    iam facing the following error

    Database driver error...
    Function Name : Execute
    SQL Stmt : INSERT INTO <table>(ROW_ID,MIN_IF_ROW_BATCH_NUM,MAX_IF_ROW_BAT CH_NUM,DATE_CREATED,IFB_FILE_NAME,STATUS) VALUES ( ?, ?, ?, ?, ?, ?)
    SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table from having duplicate rows for those columns. SQLSTATE=23505
    sqlstate = 23505.

    my targe tables are in DB2

    please help me to come out this problem
    Last edited by jalaja sompalle; 02-20-08 at 10:25.

  2. #2
    Join Date
    Feb 2008
    Posts
    7
    please give me the solunion for the error
    Database driver error...
    Function Name : Execute
    SQL Stmt : INSERT INTO <table>(ROW_ID,MIN_IF_ROW_BATCH_NUM,MAX_IF_ROW_B AT CH_NUM,DATE_CREATED,IFB_FILE_NAME,STATUS) VALUES ( ?, ?, ?, ?, ?, ?)
    SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table from having duplicate rows for those columns. SQLSTATE=23505
    sqlstate = 23505.

    my targe tables are in DB2
    Last edited by jalaja sompalle; 02-20-08 at 10:26.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    As the message says: the table has a unique constraint (primary keys are just unique constraints as well), and your insert tries to add a value in this column that already exists in another row -> the unique constraint is violated. So the solution is to fix your application and to not insert duplicates in columns with unique constraints.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Feb 2008
    Posts
    7

    informatica

    Hi stolze

    thanks and please let me know what i have to do see this error iam getting in my log file my target table is in DB2 .so Please tell me The procedure to pull out he duplicates and delete them.

    jalaja

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Hmmm?? You have a unique constraint on the table, so DB2 prevents you from inserting such duplicates. This is a basic relational concept.

    Regarding log files, you have to look at whatever your application provides. This is an application problem, not a DB2 problem. DB2 does not log failed statements. It only logs the real and successful changes to the database.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Feb 2008
    Posts
    7

    Sqlstate=23505

    Thanks Stolze

    My competency is datawarehousing.so while loading the data from source (Flatfile) To Target(DB2) .In this process the data is passed thru different transformaion and at last the final output is stored in target.so inlast stage only the error is throw that is loading the data in DB2.And my target table is defined by primary key.

    jalaja
    Last edited by jalaja sompalle; 02-21-08 at 02:29.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    In that case, there are two questions I have:
    (1) Is the unique constraint (PK) semantically correct in the first place? Warehouses often have unique constraints for dimension tables only and not for fact tables.
    (2) Are you sure that your ETL process does the right thing?

    In the end, it still comes down to your application (ETL being the application here) - either the database schema does not match the application's expectations, or the application screws up along the way.

    Also, since you are loading the data into DB2, you could use the LOAD command and write any violators for constraints to exception tables (see DB2 manual for the options on LOAD). That way, you could look specifically at the problematic rows and this may help you to figure out what's going wrong. If you can't use LOAD and stick to INSERT statements or the IMPORT command (which also just maps to INSERT), you have to have a look at your application.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Feb 2008
    Posts
    7
    Hi Stolze

    In my target table primery key constaint is there and it seems duplicate records are there in target able that why iam geting error and i want to know the process of removing duplicaes from DB2 tables

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Ok, my last attempt to explain this: You do not have duplicates in your table in DB2 but rather your application tries to insert a row for which a duplicate already exists in DB2. You have a problem in your application and not in DB2. Remove the duplicates in your application.
    Last edited by stolze; 02-21-08 at 03:37.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Feb 2008
    Posts
    7
    Hi Solze

    But if there is a error in my application then data is not get loaded .data is loaded properly but instead of showing sucessfull the session will be running still we stop it manually.and then when we check the error log file the above mentioned error is displayed

    Thanks
    jalaaj

  11. #11
    Join Date
    Feb 2008
    Posts
    7
    Hi Solze

    But if there is a error in my application then data is not get loaded .data is loaded properly but instead of showing sucessfull the session will be running still we stop it manually.and then when we check the error log file the above mentioned error is displayed

    Thanks
    jalaaj

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Many companies are off-shoring DBA jobs to completely untrained people like this poster from India because they think it is more cost effective. My advice is to not even respond to ridiculous threads like this one.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  13. #13
    Join Date
    Aug 2016
    Posts
    1
    Probably helps to know what the app was doing prior to getting the 23505 error. In my case, a program I maintained did an uncommitted delete prior to doing an insert with the same key. Either the delete needed to be committed or a commit level of *none should have been specified in the compile of the program.

Posting Permissions

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