If I need to have a query between two different source systems (Oracle & say informix). Like
Code: Select all
select SourceSystemA.TableA.*
from SourceSystemA.TableA
inner join SourceSystemB.TableB
on SourceSystemA.TableA.FieldA = SourceSystemB.TableB.FieldB
Note : We are not selecting any fields from SourceSystemB. It is just for filtering records extracted from SourceSystemA
How can I do that ........
a) One way is to dump data (through datastage) from SourceSystem B to A, in a newly created table and use SQL accordingly (We are not allowed to create any table on the source, so this option is not there for us).
b) Extract all the data from SourceA to Sequential file and then filter out using hashed file (obtained through SourceB) -> We might end of pulling lot of data than required (I need to do calculations on that).
c) Extract values from SourceB and pass them as list of values as a parameter to SourceA, and change SQL as below
Code: Select all
select SourceSystemA.TableA.*
from SourceSystemA.TableA
where SourceSystemA.TableA.FieldA in (#ParameterList#)
Are there any other ways you can suggest or which way should I use.