Reg combining the data from 3 oracle tables

madhonrougeuri · Post by **madhonrougeuri** » Wed Feb 09, 2005 4:29 pm

Hi All,
Iam new to DS.I have a server job in which I need to combine 3 different oracle tables into one Oracle Target db.How do achieve this.any pointers plz..
Thanks a lot

ray.wurlod · Post by **ray.wurlod** » Wed Feb 09, 2005 4:45 pm

Welcome aboard! :D

Are all these tables in the same database? If so, and no transformation or checking is required, bypass DataStage and do the whole thing within Oracle.

Code: Select all

INSERT INTO table4(column_list)
SELECT columns FROM table1, table2, table3 WHERE join_criteria ;

Otherwise, use the same SELECT statement to feed data into DataStage (that is, perform the join in Oracle), and load the fourth table from DataStage.

If the tables are in different databases, load the two probe tables into DataStage hashed files, remembering only to load rows and columns that are actually needed for the task, and perform standard reference lookups against these.

madhonrougeuri · Post by **madhonrougeuri** » Thu Feb 10, 2005 12:04 pm

Thanks Ray..
but the joining doesnt help due to the large amount of data.
Can I do it something like this..
oci1--Tr1--Target1
oci2--Tr2--Target2
oci3--Tr3--Target3
Any help?
Thanks.

ketfos · Post by **ketfos** » Thu Feb 10, 2005 12:08 pm

Hi,
yes, you can load data into three respective target tables through a transformer using OCI stage as mentioned by you.

Or

In the input OCI stage , you can define user defined SQL as input. You can have a join condition here before you reach transformer.

Ketfos

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Feb 10, 2005 12:09 pm

If you can dissect the full source data into multiple chunks, you can make use of multiple instance option in DataStage and achieve as you mentioned.

Also you can try to break the source using some partition and supply it in the 'where clause' of the SQL that Ray provided.

Eg. insert into table (column list)
select column_list from table where condition AND partition_logic

where partition_logic can be used to break down the data segments into smaller bits - preferably on the way you have partitioned your db (table)

madhonrougeuri · Post by **madhonrougeuri** » Thu Feb 10, 2005 12:15 pm

Thanks,I will try it and get back if any doubts.

amsh76 · Post by **amsh76** » Thu Feb 10, 2005 12:22 pm

Yep as mentioned by Sainath and Ray..you can divide your input in smaller chunks. And if you can achieve this through parameters..then Multiple Instance will be the best option for you.

madhonrougeuri · Post by **madhonrougeuri** » Thu Feb 10, 2005 3:07 pm

Can I do it in the same way even if all the tables source and target are in the same oracle database??

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Feb 10, 2005 3:28 pm

You can use both the DataStage and SQL query option when they are in the same db. But you need to make sure that there is enought rollback segment space to utilize this functionality.

Also if you are creating the result into a target flat file (which can also be a named pipe), you can use sql loader to load into partitions and have options like disabled index, direct load etc.

ray.wurlod · Post by **ray.wurlod** » Thu Feb 10, 2005 3:37 pm

The database ends up doing the same amount of work - maybe more - than if you'd done the original joins!

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Feb 10, 2005 3:40 pm

...but it will avoid going through multiple partitions and scan the whole db. Also the load will be specific to some partitions enabling the direct load functionality and hence the ability to bypass indexes and rollbck segments.