Update is not working correctly in Oracle stage
Moderators: chulett, rschirm, roy
Update is not working correctly in Oracle stage
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.
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.
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)?
Are you letting DataStage generate the insert and update statements? If so, have you correctly marked your key column(s)?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 353
- Joined: Mon Jan 17, 2011 5:03 am
- Location: Mumbai, India
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...
I'm curious what the answer to Arnd's question is...
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
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.
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.
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.
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.
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA