Lookup

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
Poovalingam
Participant
Posts: 111
Joined: Mon Nov 30, 2009 7:21 am
Location: Bangalore

Lookup

Post by Poovalingam »

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

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply