DSXchange

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

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

[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

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

[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

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

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

[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

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

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

HTWH.

Regards
Elavenil

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

[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

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.

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.