Way to implement queries btw two different source systems
Posted: Tue Jul 10, 2007 1:23 pm
Hi,
If I need to have a query between two different source systems (Oracle & say informix). Like
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
Could degrade the performance of extract
Are there any other ways you can suggest or which way should I use.
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.