DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
ssreeni3
Participant



Joined: 18 May 2012
Posts: 29

Points: 165

Post Posted: Wed Dec 06, 2017 4:32 am Reply with quote    Back to top    

DataStage® Release: 9x
Job Type: Parallel
OS: Unix
Hi,
I have 6 columns in Table.No primary keys.
But unique index is created on first 5 columns.

I tried to update the table by taking these 5 index columns as key columns.
2 index columns have null values.

I have taken Oracle connector for Update.
Job went fine with no warnings.But record is not updated.

Please help me to sort out this issue

Thanks in advance,
srini
ssreeni3
Participant



Joined: 18 May 2012
Posts: 29

Points: 165

Post Posted: Wed Dec 06, 2017 5:42 am Reply with quote    Back to top    

Hi,

additional information.
Reject error code:0
Reject error text :row not updated - update mode

Thanks,
Srini
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42283
Location: Denver, CO
Points: 217108

Post Posted: Wed Dec 06, 2017 7:46 am Reply with quote    Back to top    

Even though you have a unique index, you cannot have a PK over those fields because Primary Keys don't allow nullable fields. And you can't simply pass NULL to those fields and expect an equality chec ...

_________________
-craig

Watch out where the huskies go and don't you eat that yellow snow
Rate this response:  
Not yet rated
ssreeni3
Participant



Joined: 18 May 2012
Posts: 29

Points: 165

Post Posted: Thu Dec 07, 2017 12:47 am Reply with quote    Back to top    

Thanks Chulett.

Is there any alternate way for this scenario?

Thanks,
srini
Rate this response:  
Not yet rated
priyadarshikunal



Group memberships:
Premium Members

Joined: 01 Mar 2007
Posts: 1735
Location: Troy, MI
Points: 9315

Post Posted: Thu Dec 07, 2017 5:49 am Reply with quote    Back to top    

you should have a surrogate key for updates in such cases where you can replace the nulls with some value you are not expecting in the data such as 0, -9999 or ~, and then do a lookup and send records to update based on surrogate key you got from there.

_________________
Priyadarshi Kunal

Genius may have its limitations, but stupidity is not thus handicapped. Wink
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42283
Location: Denver, CO
Points: 217108

Post Posted: Thu Dec 07, 2017 8:11 am Reply with quote    Back to top    

Short answer: no. Anything you do that needs to use that composite index will involve NVL(). Nature of the beast.

_________________
-craig

Watch out where the huskies go and don't you eat that yellow snow
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours