DB2 Connector Error.. having duplicate values for the index

[Developer9]

Hi,

I have a job scenario,where in I am inserting data into a table( INSERT STATEMENT) also I am using DELETE statement as BEFORE SQL.

Getting the following error:

Code: Select all

DB2_XXXX: SQLExecute reported: SQLSTATE = 23505: Native Error Code = -803: Msg = [IBM][CLI Driver][DB2/AIX64] 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 "SCHEMA.TABLE" from having duplicate values for the index key.  SQLSTATE=23505

I verified the data writing to a test file and I found that there are no duplicates.

Please let me know some idea about resolving this issue

Thanks

[chulett]

The duplicates could be against existing data rather than constrained to the input data. Of course, you haven't clarified exactly what it is you are deleting in your before SQL...

[Developer9]

Hi Chulett,

The target table "SCHEMA.TABLE" has zero records.
Table Action selected as "Append"
Before SQL used=For Deletion of Existing Data

Also I used Remove Duplicate stage to remove the duplicates before writing to target table.

I hope this information is appropriate
Thanks for input

[MT]

Hi

well the message is quite clear - so let us check it in detail
You can use following SQL to figure out which constraint you are violating:

SELECT INDNAME, INDSCHEMA
FROM SYSCAT.INDEXES
WHERE IID = <index-id>
AND TABSCHEMA = 'SCHEMA'
AND TABNAME = 'TABLE'

In your case you have to use 1 als the <index-id> - that is returned by the original message.

I am pretty sure DB2 is right and maybe you have missed one duplicate..

[Developer9]

Hi,

I got the following results ..when I execute the above Query

Code: Select all

INDNAME=SQL131106162530000
INDSCHEMA=SYSIBM

Last week I was able to insert one record to the table with no issues but today I got the error when I re-run the job.

I am looking into this issue and will give more details

Thank you

[MT]

Hi Developer9,

Code: Select all

INDNAME=SQL131106162530000
INDSCHEMA=SYSIBM

It is a generated name so I assume you created the table with a primary key and this is the index DB2 automatically generates for it.

[Developer9]

Hi MT,

Yes.I created only Primary keys for this table. (with TSPACE 32K).

Table has 230 columns and defined with 9keys. But out of 230 columns I am updating only few columns

I will post if any other information

Thanks