Multiple Range lookup conditions in Same Lookup Stage

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
Mike
Premium Member
Premium Member
Posts: 1021
Joined: Sun Mar 03, 2002 6:01 pm
Location: Tampa, FL

Post by Mike »

One lookup stage can only designate 1 reference link for multiple rows so you can only do range lookups on whatever reference link is designated. You can do two range lookups against 1 reference link. Since you want to do range lookups on 2 different reference links, you will have to use 2 lookup stages.

Mike
sohasaid
Premium Member
Premium Member
Posts: 115
Joined: Tue May 20, 2008 3:02 am
Location: Cairo, Egypt

Post by sohasaid »

Mike wrote:Since you want to do range lookups on 2 different reference links, you will have to use 2 lookup stages.
Thanks Mike for the clear response, it really helped. In my case I use range lookups heavily (up to seven range lookups per job) along with hash file sets as reference stage for better performance.

Would it impact the performance if I used a separate lookup for each reference link (in this case seven lookup stages)? If yes, what factors should I take into consideration to avoid such an impact.

Thanks.
Mike
Premium Member
Premium Member
Posts: 1021
Joined: Sun Mar 03, 2002 6:01 pm
Location: Tampa, FL

Post by Mike »

I doubt that you'd see any noticeable performance difference between a pipeline of 7 lookup stages and a single lookup stage with 7 reference links.

The important thing is you will get 7 of 7 correct results instead of 1 of 7.

It would be an interesting exercise to compare the job score between the 2 designs to see if operator combination combines any of the lookups.

Mike
sohasaid
Premium Member
Premium Member
Posts: 115
Joined: Tue May 20, 2008 3:02 am
Location: Cairo, Egypt

Post by sohasaid »

Mike wrote:It would be an interesting exercise to compare the job score between the 2 designs to see if operator combination combines any of the lookups.
Agree. I will make the exercise and post the results. I will mark post as resolved for now.

Thanks for your support
Post Reply