Page 1 of 1
How to combine 2 datasets from a database
Posted: Thu Aug 17, 2006 3:30 pm
by seanc217
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
Posted: Thu Aug 17, 2006 3:42 pm
by narasimha
Do a 'UNION ALL' between Table 1 and Table 2
Posted: Thu Aug 17, 2006 3:46 pm
by seanc217
Thanks for the reply,
Is there a way for DataStage to do this kind of operation?
Or is utilizing the database the best way?
Posted: Thu Aug 17, 2006 3:51 pm
by narasimha
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?
Posted: Thu Aug 17, 2006 3:52 pm
by kris007
Look into the Funnel Stage.
Posted: Thu Aug 17, 2006 3:59 pm
by DSguru2B
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.
Posted: Thu Aug 17, 2006 4:02 pm
by meena
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..
Posted: Fri Aug 18, 2006 3:52 am
by prabu
narasimha wrote:Do a 'UNION ALL' between Table 1 and Table 2
You also should undertand that
will preserve the duplicates, say value 'A' is available in both datasets, your generated result set will have
will suppress duplicates
UNION ALL will make use of parallelism but UNION cannot.
Posted: Fri Aug 18, 2006 11:22 am
by devnull
prabu wrote:narasimha wrote:Do a 'UNION ALL' between Table 1 and Table 2
You also should undertand that
will preserve the duplicates, say value 'A' is available in both datasets, your generated result set will have
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.
Posted: Fri Aug 18, 2006 12:04 pm
by narasimha
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...
Posted: Fri Aug 18, 2006 12:10 pm
by prabu
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]
