How to combine 2 datasets from a database

seanc217 · Post by **seanc217** » Thu Aug 17, 2006 3:30 pm

I have 2 datasets in an Oracle database that I want to combine. So for example let's say table 1 has the following values:

A
B
C

and table 2 has the following values:

D
E
F

I want the result dataset to be

A
B
C
D
E
F

What is the best way of going about this?

Thanks

narasimha · Post by **narasimha** » Thu Aug 17, 2006 3:42 pm

Do a 'UNION ALL' between Table 1 and Table 2

seanc217 · Post by **seanc217** » Thu Aug 17, 2006 3:46 pm

Thanks for the reply,

Is there a way for DataStage to do this kind of operation?
Or is utilizing the database the best way?

narasimha · Post by **narasimha** » Thu Aug 17, 2006 3:51 pm

It is best if you do it on the database side. It is always the fastest method.

There are multiple ways of doing it in datastage, but do you want to go that path?

kris007 · Post by **kris007** » Thu Aug 17, 2006 3:52 pm

Look into the Funnel Stage.

DSguru2B · Post by **DSguru2B** » Thu Aug 17, 2006 3:59 pm

Union All is the best way as advised by others. Its a simple sql that you need to write and get it done. Are the two tables in different databases? If yes then use the funnel stage. You can also create two files and combine them at the OS level.

meena · Post by **meena** » Thu Aug 17, 2006 4:02 pm

Yes,it is best to do in oracle database directly instead of going to other stage..When we are designing a job we consider the performance of the job..I prefer to do it on database side..

prabu · Post by **prabu** » Fri Aug 18, 2006 3:52 am

narasimha wrote:Do a 'UNION ALL' between Table 1 and Table 2

You also should undertand that

Code: Select all

UNION ALL

will preserve the duplicates, say value 'A' is available in both datasets, your generated result set will have

Code: Select all

A
A

Code: Select all

 UNION

will suppress duplicates

UNION ALL will make use of parallelism but UNION cannot.

devnull · Post by **devnull** » Fri Aug 18, 2006 11:22 am

prabu wrote:
narasimha wrote:Do a 'UNION ALL' between Table 1 and Table 2
You also should undertand that
Code: Select all
UNION ALL 
will preserve the duplicates, say value 'A' is available in both datasets, your generated result set will have
Code: Select all
A
A
Code: Select all
 UNION 
will suppress duplicates

UNION ALL will make use of parallelism but UNION cannot.

And if that is the case, then the Funnel with a Sort is your best option.

narasimha · Post by **narasimha** » Fri Aug 18, 2006 12:04 pm

devnull wrote: And if that is the case, then the Funnel with a Sort is your best option.

May I know why you are suggesting this? May be I am missing something...

prabu · Post by **prabu** » Fri Aug 18, 2006 12:10 pm

narasimha wrote:
devnull wrote: And if that is the case, then the Funnel with a Sort is your best option.
May I know why you are suggesting this? May be I am missing something...

only reason i can think of is , you may get a satisfaction of using more stages in your job [if you think for some more time, you may also 'feel' remove duplicate stage is required]