DSXchange

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.

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.

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

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.