appending data from 2 different soruces to a single table
Moderators: chulett, rschirm, roy
appending data from 2 different soruces to a single table
Hi,
subject:
appending data from 2 different sources to a single table.
I have 2 source systems
source_1:oracle database
cust_id cust_name cust_city cust_zip
101 XXX City_1 1234
102 YYY City_2 5432
103 ZZZ City_1 1234
104 PPP City_1 1234
source_2: flatfile.txt
cust_id,cust_name,cust_city,cust_zip
105,XXXX,City_1,1234
106,YYYY,City_2,5432
107,ZZZZ,City_1,1234
108,PPPP,City_1,1234
in both sources, the primary key is cust_id
I need to have the data from 2 sources to a oracle table.
output in oracle table should look as:
cust_id cust_name cust_city cust_zip
101 XXX City_1 1234
102 YYY City_2 5432
103 ZZZ City_1 1234
104 PPP City_1 1234
105 XXXX City_1 1234
106 YYYY City_2 5432
107 ZZZZ City_1 1234
108 PPPP City_1 1234
regards
sunshine
subject:
appending data from 2 different sources to a single table.
I have 2 source systems
source_1:oracle database
cust_id cust_name cust_city cust_zip
101 XXX City_1 1234
102 YYY City_2 5432
103 ZZZ City_1 1234
104 PPP City_1 1234
source_2: flatfile.txt
cust_id,cust_name,cust_city,cust_zip
105,XXXX,City_1,1234
106,YYYY,City_2,5432
107,ZZZZ,City_1,1234
108,PPPP,City_1,1234
in both sources, the primary key is cust_id
I need to have the data from 2 sources to a oracle table.
output in oracle table should look as:
cust_id cust_name cust_city cust_zip
101 XXX City_1 1234
102 YYY City_2 5432
103 ZZZ City_1 1234
104 PPP City_1 1234
105 XXXX City_1 1234
106 YYYY City_2 5432
107 ZZZZ City_1 1234
108 PPPP City_1 1234
regards
sunshine
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
[quote="ray.wurlod"]Two separate streams into the same Oracle table. If the keys form disjoint sets, these streams can run in parallel.[/quote]
hi Ray,
as i am novice, can you explain me in detail.
currently i am practising using server jobs..
Two separate streams into the same Oracle table. --> Yes
If the keys form disjoint sets, these streams can run in parallel --> ??
i mean which stages we need to use..
can you be more specific..
Thanks for your reply..
regards
sunshine
hi Ray,
as i am novice, can you explain me in detail.
currently i am practising using server jobs..
Two separate streams into the same Oracle table. --> Yes
If the keys form disjoint sets, these streams can run in parallel --> ??
i mean which stages we need to use..
can you be more specific..
Thanks for your reply..
regards
sunshine
[quote="ray.wurlod"]Two separate streams into the same Oracle table. If the keys form disjoint sets, these streams can run in parallel.[/quote]
Thanks for the suggestion, it worked,got one more doubt, hope u will clear it. I took two instances of oracle, is it possible to have only one instance of the target?
Thanking you
Thanks for the suggestion, it worked,got one more doubt, hope u will clear it. I took two instances of oracle, is it possible to have only one instance of the target?
Thanking you
-
- Premium Member
- Posts: 209
- Joined: Fri Jan 09, 2004 1:14 pm
- Location: Toronto, Canada
- Contact:
Sunshine,
From your example above, it appears that your data streams have the same format. If this is true, use a collector stage to bring the two sources together and feed into your OCI.
Could look something like:
Seq ---> Xfm \
\
Collector --> XFM --> OCI
/
OCI ---> Xfm /
You wouldn't even need the three XFM, I put them there in the event that some work needs to be done to make the streams the same and that something needs to be done to manipulate data before it goes into the DB.
HTH
Rob Wierdsma
From your example above, it appears that your data streams have the same format. If this is true, use a collector stage to bring the two sources together and feed into your OCI.
Could look something like:
Seq ---> Xfm \
\
Collector --> XFM --> OCI
/
OCI ---> Xfm /
You wouldn't even need the three XFM, I put them there in the event that some work needs to be done to make the streams the same and that something needs to be done to manipulate data before it goes into the DB.
HTH
Rob Wierdsma
Rob Wierdsma
Toronto, Canada
bartonbishop.com
Toronto, Canada
bartonbishop.com
[quote="rwierdsm"]Sunshine,
From your example above, it appears that your data streams have the same format. If this is true, use a collector stage to bring the two sources together and feed into your OCI.
Could look something like:
Seq ---> Xfm \
\
Collector --> XFM --> OCI
/
OCI ---> Xfm /
You wouldn't even need the three XFM, I put them there in the event that some work needs to be done to make the streams the same and that something needs to be done to manipulate data before it goes into the DB.
HTH
Rob Wierdsma[/quote]
but i am getting error:
Link collector Doesnot support in-process active-to-active inputs or outputs![Sad :(](./images/smilies/icon_sad.gif)
Looking forward for your reply,
regards
sunshine
From your example above, it appears that your data streams have the same format. If this is true, use a collector stage to bring the two sources together and feed into your OCI.
Could look something like:
Seq ---> Xfm \
\
Collector --> XFM --> OCI
/
OCI ---> Xfm /
You wouldn't even need the three XFM, I put them there in the event that some work needs to be done to make the streams the same and that something needs to be done to manipulate data before it goes into the DB.
HTH
Rob Wierdsma[/quote]
but i am getting error:
Link collector Doesnot support in-process active-to-active inputs or outputs
![Sad :(](./images/smilies/icon_sad.gif)
Looking forward for your reply,
regards
sunshine
[quote="rwierdsm"]Sunshine,
From your example above, it appears that your data streams have the same format. If this is true, use a collector stage to bring the two sources together and feed into your OCI.
Could look something like:
Seq ---> Xfm \
\
Collector --> XFM --> OCI
/
OCI ---> Xfm /
You wouldn't even need the three XFM, I put them there in the event that some work needs to be done to make the streams the same and that something needs to be done to manipulate data before it goes into the DB.
HTH
Rob Wierdsma[/quote]
Thanks for your reply..
i need to set the option in JOb proeprties: in performance tab, set inprocess.
From your example above, it appears that your data streams have the same format. If this is true, use a collector stage to bring the two sources together and feed into your OCI.
Could look something like:
Seq ---> Xfm \
\
Collector --> XFM --> OCI
/
OCI ---> Xfm /
You wouldn't even need the three XFM, I put them there in the event that some work needs to be done to make the streams the same and that something needs to be done to manipulate data before it goes into the DB.
HTH
Rob Wierdsma[/quote]
Thanks for your reply..
i need to set the option in JOb proeprties: in performance tab, set inprocess.
[quote="elavenil"]My memory works correctly, the inter process option should be set in order to get rid of the error occurred.
HTWH.
Regards
Elavenil[/quote]
if you have some time,can you look into this issue..![Smile :)](./images/smilies/icon_smile.gif)
viewtopic.php?t=98611
HTWH.
Regards
Elavenil[/quote]
if you have some time,can you look into this issue..
![Smile :)](./images/smilies/icon_smile.gif)
viewtopic.php?t=98611
You could reply to Arnds post for the above link.sunshine wrote:if you have some time,can you look into this issue..elavenil wrote:My memory works correctly, the inter process option should be set in order to get rid of the error occurred.
HTWH.
Regards
Elavenil
viewtopic.php?t=98611
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
It looks like we have gone done the route of complexity when Ray offered a very simple solution to this requirement. Create two jobs where both of them insert into your target table. If the jobs do not contain the same data you can run the jobs at the same time which should perform better than using the collector and the IPC stage.ray.wurlod wrote:Two separate streams into the same Oracle table. If the keys form disjoint sets, these streams can run in parallel.
Keith Williams
keith@peacefieldinc.com
keith@peacefieldinc.com