how to make a join server jobs

jagadish9 · Post by **jagadish9** » Thu Jul 12, 2007 5:59 pm

Hi
i have to join 2 table table to derive a new filed in 1 table..

is there any other way to do join(with out using any odbc stage)

ray.wurlod · Post by **ray.wurlod** » Thu Jul 12, 2007 8:33 pm

Welcome aboard.

You are going to need some kind of database stage to get the data out of the tables, so I am presuming you are using ODBC stage for this.

If both tables are in the same database, best practice is to perform the join in SQL, particularly if it can be aided by indexes in the database.

You can certainly do the join without doing it in the database - now you need two ODBC stages, one streaming the left input and one performing lookups into a Transformer stage from the right input. Set your output link constraints to pass or not pass left input rows when the lookup fails - the first is a left outer join, the second is an inner join.

Note that this approach must select every row from the left input (except as constrained in a WHERE clause). It may end up processing far more rows in DataStage than performing the join in the database.

ray.wurlod · Post by **ray.wurlod** » Thu Jul 12, 2007 8:35 pm

Premium membership costs only a few cents per day, and give full access to replies by premium posters. 100% of this revenue is directed to helping to pay for the bandwidth and hosting charges incurred by DSXchange.