DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic is not resolved, but there is a WORKAROUND.
Author Message
jackson.eyton



Group memberships:
Premium Members

Joined: 26 Oct 2017
Posts: 133

Points: 2153

Post Posted: Wed Jan 24, 2018 9:51 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Windows
Hey everyone,
I wanted to ask a simple question, does anyone know where I would set the default value for a failed lookup that is set to continue? In a numeric lookup the default we get is a 0, we're hoping we can just change the default returned value as opposed to adding additional logic to look for 0s and change them. I'm open to any suggestions. Thanks in advance!!

_________________
-Me

Last edited by jackson.eyton on Wed Jan 24, 2018 10:43 am; edited 1 time in total
chulett

Premium Poster


since January 2006

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

Joined: 12 Nov 2002
Posts: 42836
Location: Denver, CO
Points: 220819

Post Posted: Wed Jan 24, 2018 10:20 am Reply with quote    Back to top    

So, actually for a Parallel job and not a Server job as marked yes?

_________________
-craig

Your brain is full of spiders, you've got garlic in your soul, Mr. Grinch!
I wouldn't touch you With a thirty-nine-and-a-half foot pole!
Rate this response:  
jackson.eyton



Group memberships:
Premium Members

Joined: 26 Oct 2017
Posts: 133

Points: 2153

Post Posted: Wed Jan 24, 2018 10:36 am Reply with quote    Back to top    

Correct, I put this in general as I was unsure if the configuration options to do this for a lookup would be parallel specific. My apologies.

_________________
-Me
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: 42836
Location: Denver, CO
Points: 220819

Post Posted: Wed Jan 24, 2018 2:03 pm Reply with quote    Back to top    

No worries, let me scootch you over to the correct forum...

_________________
-craig

Your brain is full of spiders, you've got garlic in your soul, Mr. Grinch!
I wouldn't touch you With a thirty-nine-and-a-half foot pole!
Rate this response:  
dsguy1247



Group memberships:
Premium Members

Joined: 04 Feb 2004
Posts: 33

Points: 276

Post Posted: Wed Jan 24, 2018 2:10 pm Reply with quote    Back to top    

I am not sure that you can change the default value. One option is that you could make the column coming from the reference link as nullable. In a subsequent transformer stage you can build some logic to replace the nulls with the default value of your choice. Alternatively, you can send the lookup failures to a reject link, and add specific values for the column in question and then funnel back to the main stream.

It all depends upon what is the overall goal.

Hope this helps,
Raj
Rate this response:  
jackson.eyton



Group memberships:
Premium Members

Joined: 26 Oct 2017
Posts: 133

Points: 2153

Post Posted: Thu Jan 25, 2018 12:00 pm Reply with quote    Back to top    

dsguy1247,
I kind of figured this was the route we'd have to go. I could have sworn setting a default lookup failure value was an option somewhere from the courses I took, but I wonder thats for string lookups only? In either case I believe you're correct and we'll be opting to send failed lookups to rejects and we'll set the value from a subsequent transformer from there.

_________________
-Me
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: 42836
Location: Denver, CO
Points: 220819

Post Posted: Thu Jan 25, 2018 12:16 pm Reply with quote    Back to top    

Well... the documentation for the stage does say:

•Choose an action from the Condition Not Met list. Possible actions are:
◦Continue. The fields from that link are set to NULL if the field is nullable, or to a default value if not. Continues processing any further lookups before sending the row to the output link

And for the OSH operator itself, it notes that:
Quote:
continue tells the operator to continue execution when a record of an input data set does not have a corresponding record in every input lookup table. The input record is transferred to the output data set along with the corresponding records from the lookup tables that matched. The fields in the output record corresponding to the lookup table(s) with no corresponding record are set to their default value or null if the field supports nulls.


But I don't see anything regarding how to set the "default value", so it may already be pre-determined based on data type. Perhaps ping support and ask them?

_________________
-craig

Your brain is full of spiders, you've got garlic in your soul, Mr. Grinch!
I wouldn't touch you With a thirty-nine-and-a-half foot pole!
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

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

Joined: 23 Oct 2002
Posts: 54431
Location: Sydney, Australia
Points: 295208

Post Posted: Sun Jan 28, 2018 7:11 pm Reply with quote    Back to top    

The Modify stage lookup functions allow for a default value to be specified. Perhaps this is what you encountered during your training.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne (especially seeking good business analysts)
Rate this response:  
Not yet rated
jackson.eyton



Group memberships:
Premium Members

Joined: 26 Oct 2017
Posts: 133

Points: 2153

Post Posted: Tue Jan 30, 2018 8:59 am Reply with quote    Back to top    

Hmm that might be it, here is a pretty interesting article about that stage.
https://it.toolbox.com/blogs/vincentmcburney/datastage-tutorial-how-to-become-a-modify-stage-zen-master-110807

It sounds like I could use this stage to handle those lookup values though. Thanks guys!

_________________
-Me
Rate this response:  
Not yet rated
dsguy1247



Group memberships:
Premium Members

Joined: 04 Feb 2004
Posts: 33

Points: 276

Post Posted: Wed Jan 31, 2018 1:25 pm Reply with quote    Back to top    

Jackson,

When I used to teach Datastage for IBM, I used to teach that modify stage is much less expensive than the transformer stage, hence when performance is critical, it is better to use modify stage. Then, during one of the debugging sessions with IBM tech support for a client, the support guy said that the in the latest versions, the CPU utilization was no longer a huge differentiator. I did not question him.

However, the drag and drop features of most of the stages, where you can drag the input columns to output, is not available in the modify stage, which makes it a little painful and prone to silly typo errors.

On the original post, I believe you have a work around, but I just wanted to share my two cents on my "favorite" modify stage BTW, the Vincent article was great and it makes a lot of sense. In fact, any article that quotes Ray Wurlod is just awesome.

Thanks,
Raj
Rate this response:  
chulett

Premium Poster


since January 2006

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

Joined: 12 Nov 2002
Posts: 42836
Location: Denver, CO
Points: 220819

Post Posted: Wed Jan 31, 2018 4:16 pm Reply with quote    Back to top    

dsguy1247 wrote:
the support guy said that the in the latest versions, the CPU utilization was no longer a huge differentiator. I did not question him.

Ray has made the same observation in the past as well. I also did not question him. Wink

_________________
-craig

Your brain is full of spiders, you've got garlic in your soul, Mr. Grinch!
I wouldn't touch you With a thirty-nine-and-a-half foot pole!
Rate this response:  
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