Update is not working correctly in Oracle stage

lsnovick · Post by **lsnovick** » Mon Aug 27, 2012 11:44 am

I cannot get my UPDATE to work when I use the Oracle Update/Insert OPTION. I have played around with the statement but cannot get it to update. It will reject all records for the update but works correctly for the insert. If I take constraints off my table, DataStage will insert each row again even though it is suppose to be an update. Is this a bug in DataStage PE 8.1? If so, is there a workaround?

I have searched the forum and have not found a solution. The question seems to be out there but not how people worked around this.

ArndW · Post by **ArndW** » Mon Aug 27, 2012 9:43 pm

It is unlikely that this is a bug.
Are you letting DataStage generate the insert and update statements? If so, have you correctly marked your key column(s)?

chandra.shekhar@tcs.com · Tue Aug 28, 2012 12:11 am

What error it is throwing ?

chulett · Post by **chulett** » Tue Aug 28, 2012 6:03 am

Realize that for these combo actions to work, the first action must fail for the second to be triggered. An update fails if it doesn't find a row to update. An insert fails when it already exists and it is covered by a unique constraint of some kind so it physically cannot be inserted into the database again.

I'm curious what the answer to Arnd's question is...

lsnovick · Post by **lsnovick** » Tue Aug 28, 2012 6:19 am

I have tried all the options with no resolution. I have now the update and insert going down different paths. It knows that it is an update. It goes down the path of an update (matched on the keys in the table to see it as an update.)

I have the update statement being generated by DataStage:

UPDATE #dMAP.dIWschema#.AC_AE_ZIP_XREF SET EMPL_ID = ORCHESTRATE.EMPL_ID, INSERT_DATE = ORCHESTRATE.INSERT_DATE WHERE (ZIP = ORCHESTRATE.ZIP)

I have it rejecting into a dataset. The error is just 1403.

chulett · Post by **chulett** » Tue Aug 28, 2012 6:23 am

ORA-01403: no data found

I would verify that your in-job zip code values do in fact match what is in the table. I really should rephrase that - figure out why they don't match since (at the moment) Oracle is telling you they don't.

lsnovick · Post by **lsnovick** » Tue Aug 28, 2012 9:25 am

Why does DataStage think it is an update? It is going down that path but when it does the "Update" Oracle thinks it is a problem? I put the contraint on the table, it gives me rejects. I take it off, it inserts duplicate keys.

I add two paths to my job IsNull and NotNull, it goes down the path of NotNull to do an update and that is where it just rejects.

chulett · Post by **chulett** » Tue Aug 28, 2012 9:35 am

You'd need to explain your job design at this point so we know exactly how it is set up. If you are still using a "combo" action, please specify which one you are using so we know the order it will try the actions.

lsnovick · Post by **lsnovick** » Tue Aug 28, 2012 9:42 am

I have a sequential stage coming in. I lookup to see if the row exists. Then I have it writing to an Oracle stage Update then insert (I have done it both ways without success.) I have auto-generated right now.

I have it writing to a dataset if the lookup was successful (bring in one field and said if it is not null, write to a dataset.) I look at the dataset, looks all good. Then I take the key, put it in TOAD, and it is there.

us1aslam1us · Post by **us1aslam1us** » Tue Aug 28, 2012 11:27 am

Check the data type for the ZIP column in the target table. Also verify what data you are feeding into this column? Is it integer, text, text with white spaces?

sAM