appending data from 2 different soruces to a single table

sunshine · Post by **sunshine** » Mon Feb 20, 2006 7:20 pm

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

ray.wurlod · Post by **ray.wurlod** » Mon Feb 20, 2006 7:44 pm

Two separate streams into the same Oracle table. If the keys form disjoint sets, these streams can run in parallel.

sunshine · Post by **sunshine** » Mon Feb 20, 2006 7:55 pm

[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

sunshine · Post by **sunshine** » Mon Feb 20, 2006 7:56 pm

[quote="ray.wurlod"]Two separate streams into the same Oracle table. If the keys form disjoint sets, these streams can run in parallel.[/quote]

in both sources, cust_ID (first column) is primary key..

sunshine · Post by **sunshine** » Mon Feb 20, 2006 8:40 pm

[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

rwierdsm · Post by **rwierdsm** » Mon Feb 20, 2006 9:23 pm

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

elavenil · Post by **elavenil** » Mon Feb 20, 2006 9:48 pm

Make sure that the same customer id does not exist in both streams otherwise primary key violation would occur while loading this data into a table.

Regards
Elavenil

sunshine · Post by **sunshine** » Mon Feb 20, 2006 10:25 pm

[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

Looking forward for your reply,

regards
sunshine

sunshine · Post by **sunshine** » Mon Feb 20, 2006 11:04 pm

[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.

elavenil · Post by **elavenil** » Mon Feb 20, 2006 11:10 pm

My memory works correctly, the inter process option should be set in order to get rid of the error occurred.

HTWH.

Regards
Elavenil

sunshine · Post by **sunshine** » Mon Feb 20, 2006 11:11 pm

[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]

great job, Elavenil.

Thanks you very much..

sunshine · Post by **sunshine** » Mon Feb 20, 2006 11:14 pm

[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..

viewtopic.php?t=98611

kumar_s · Post by **kumar_s** » Tue Feb 21, 2006 3:51 am

sunshine wrote:
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
if you have some time,can you look into this issue..

viewtopic.php?t=98611

You could reply to Arnds post for the above link.

kwwilliams · Post by **kwwilliams** » Tue Feb 21, 2006 7:11 am

ray.wurlod wrote:Two separate streams into the same Oracle table. If the keys form disjoint sets, these streams can run in parallel.

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.