Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.
Moderators: chulett , rschirm , roy
Developer9
Premium Member
Posts: 187 Joined: Thu Apr 14, 2011 5:10 pm
Post
by Developer9 » Wed Dec 04, 2013 12:00 am
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
Charter Member
Posts: 43085 Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO
Post
by chulett » Wed Dec 04, 2013 12:27 am
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...
-craig
"You can never have too many knives" -- Logan Nine Fingers
Developer9
Premium Member
Posts: 187 Joined: Thu Apr 14, 2011 5:10 pm
Post
by Developer9 » Wed Dec 04, 2013 1:44 am
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
Premium Member
Posts: 198 Joined: Fri Mar 09, 2007 3:51 am
Post
by MT » Wed Dec 04, 2013 8:59 am
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..
regards
Michael
Developer9
Premium Member
Posts: 187 Joined: Thu Apr 14, 2011 5:10 pm
Post
by Developer9 » Thu Dec 19, 2013 3:59 pm
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
Premium Member
Posts: 198 Joined: Fri Mar 09, 2007 3:51 am
Post
by MT » Fri Dec 20, 2013 1:22 am
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.
regards
Michael
Developer9
Premium Member
Posts: 187 Joined: Thu Apr 14, 2011 5:10 pm
Post
by Developer9 » Fri Dec 20, 2013 11:10 am
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