Lookup Failure Default Value

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

Post Reply
jackson.eyton
Premium Member
Premium Member
Posts: 145
Joined: Thu Oct 26, 2017 10:43 am

Lookup Failure Default Value

Post by jackson.eyton »

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!!
Last edited by jackson.eyton on Wed Jan 24, 2018 10:43 am, edited 1 time in total.
-Me
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
jackson.eyton
Premium Member
Premium Member
Posts: 145
Joined: Thu Oct 26, 2017 10:43 am

Post by jackson.eyton »

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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
dsguy1247
Premium Member
Premium Member
Posts: 33
Joined: Wed Feb 04, 2004 6:35 am
Contact:

Post by dsguy1247 »

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
jackson.eyton
Premium Member
Premium Member
Posts: 145
Joined: Thu Oct 26, 2017 10:43 am

Post by jackson.eyton »

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
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

The Modify stage lookup functions allow for a default value to be specified. Perhaps this is what you encountered during your training.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
jackson.eyton
Premium Member
Premium Member
Posts: 145
Joined: Thu Oct 26, 2017 10:43 am

Post by jackson.eyton »

Hmm that might be it, here is a pretty interesting article about that stage.
https://it.toolbox.com/blogs/vincentmcb ... ter-110807

It sounds like I could use this stage to handle those lookup values though. Thanks guys!
-Me
dsguy1247
Premium Member
Premium Member
Posts: 33
Joined: Wed Feb 04, 2004 6:35 am
Contact:

Post by dsguy1247 »

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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
Post Reply