error because of constraints

[dbatlanta]

Hi All,

I have a source in DB2, and my target is Informix. The tables have the same columns,datatype,PM keys.I have used a lookup to lookup the two unique columns from the target which has the composite PM key. And I am using a filter to filter only the new data based on the lookup. I did some testing and it seems to work to insert only the new rows but I also get this error. Is this because I have the PM keys in the target. Or is my logic right?

WRT_8229 Database errors occurred:
Informix Error -239 :Could not insert new row - duplicate value in a UNIQUE INDEX column.

 

[blom0344]

I have no Informix knowledge, but you have to ascertain 2 things:

If you use data-driven logic and plan to do only inserts, then:

1. Make sure that absolutely no rows are written to the target that will cause a duplicate compared with a key that ALREADY exists in the target.

2. The records to be inserted should also be unique in terms of PK's

You can use the lookup to verify that 1. will be okay, but you need to 'tune' your mapping to avoid 2. as well.

Other than that , your target may have additional unique indices that consists of other (or more) fields that the PK. This is quite common with OLTP type of databases.

If the DB2 and Informix tables are totally similar, the last cause may be important. Check with your DBA which indices exist on the informix table.

(On the other hand, why not use a more straightforward insert/else update strategy?)

Ties Blom