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



Joined: 30 Nov 2009
Posts: 111
Location: Bangalore
Points: 976

Post Posted: Mon Sep 25, 2017 3:20 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Server
OS: Unix
Hi,
In server job I would like to understand the difference between doing the lookup using colon or bind variable and doing without using colon or bind variables.

Job1:
Reference stage: Oracle Connector
Reference sql: Select ABC, DEF FROM XYZ;
Using ABC as Key column in the Oracle Key in Oracle Connetor and in the transformer stage input link column and connected the key column in the transformer stage.

Job2:
Reference stage: Oracle Connector
Reference Sql: Select ABC, DEF FROM XYZ where ABC = :1;

In Job 1 Datastage extract all the reference data into the server and do the joining where as in Job2 for every input row reference sql will be executed like parallel job sparse lookup. Is my understanding correct? Please advise.

Thanks & Regards,
Poovalingam.
chulett

Premium Poster


since January 2006

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

Joined: 12 Nov 2002
Posts: 42336
Location: Denver, CO
Points: 217386

Post Posted: Mon Sep 25, 2017 6:48 pm Reply with quote    Back to top    

Okay... it's been awhile but...

Have you actually implemented both of these and they behaved as noted or are these strictly theoretical questions / statements? Unless something has changed, last I knew only the UV and ODBC stages allowed multiple records to be returned from a Server lookup - and you had to specifically enable the option in the reference link called (if I recall correctly) "Enable multi-row result set".

Otherwise they always return a single record. For scenario 1 you have no control over which one gets picked. For scenario 2 it is controlled by the bound value of the key field on each row. And from what I remember, both behave like a sparse lookup... there's no "joining on the server" for either one. If you want things cached on the server and the lookups actually performed there, use a hashed file.

_________________
-craig

Watch out where the huskies go and don't you eat that yellow snow
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: 42336
Location: Denver, CO
Points: 217386

Post Posted: Mon Sep 25, 2017 6:49 pm Reply with quote    Back to top    

Now if this is new behavior in the Connector stages, I'll have to defer to others.

_________________
-craig

Watch out where the huskies go and don't you eat that yellow snow
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: 54089
Location: Sydney, Australia
Points: 293360

Post Posted: Mon Sep 25, 2017 10:10 pm Reply with quote    Back to top    

chulett wrote:
Now if this is new behavior in the Connector stages, I'll have to defer to others.

Not required. What you posted is still accurate unto version 11.5.0.2.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
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