Union in Datastage Designer

[kduke]

Karthik

Load one table into DataStage and let it generate the SQL. Change it to user defined SQL and add the union.

Kim.

Kim Duke
DsWebMon - Monitor DataStage over the web
www.Duke-Consulting.com

[ray.wurlod]

If the tables are in the same data source, use user-defined SQL.
SELECT col1, col2, col3 FROM table1
UNION
SELECT colA, colB, colC FROM table2;
... with all the usual caveats about data type matches and so on.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

[datastagedummy]

Or you can load it into your own locsl universe table and do whatever you want with user-defined query.

[kduke]

Karthik

You still have a couple choices. You can create a dblink in Oracle so that the UNION works in Oracle. You can select one table post it. Select the next table and post it. Post it to a temp table or a hash file. Use this as your source.

Kim.

Kim Duke
DsWebMon - Monitor DataStage over the web
www.Duke-Consulting.com

[eoyylo]

Hi,
it is possible to load the data without user defined query but with 2 jobs (simpler to implement but difficult to manage) or 1 job (more difficult to implement but easier to manage )

first solution

job 1 -> load the data from remote host (table A) to local table in your host. You must set "truncate table then insert" upodate action

job 2 -> load the data from remote host (table B) to local table in your host. You must set "insert without clearing" upodate action

the problem is that you must run job 1 and AFTER job 2

second solution

job -> it is simplier to design the job (ignore the ".")

.......................................(2)
....................B--------> T -----> LOC_TAB
...................................^
...................................| (3)
........................(1).......|
..........A ---> T ----> LOC_TAB


where

A fisrt remote table
B second remote table
T trasformer
LOC_TAB local table in you host
(1) update action "Truncate table"
(2) update aciotion "insert without clearing"
(3) a query like "select sysdate from dual". It is necessery because the load of data from table B must be after the load from table A
and this link do it

Mario

[Teej]

I don't see why you can not use a funnel stage, and two input stages.

-T.J.

[eoyylo]

funnel stage?
which is the funnel stage? The merge stage is only for file, right?

Mario

[ray.wurlod]

This confusion is one of the reasons those new fields appear at the top when you post a new message. If the OP had signalled that server jobs were implied in the question, an answer pertinent to parallel jobs would have been less likely to have been given.