Oracle connect by functionality in DataStage

madhukar · Post by **madhukar** » Wed Sep 19, 2007 7:59 am

Hi All,

How can i achieve Oracle connect by functionality in DataStage

regards
madhukar

ArndW · Post by **ArndW** » Wed Sep 19, 2007 4:29 pm

The PX Documentation contains several chapters on connecting PX and Oracle via different methods on different platforms. What specifically are you asking?

chulett · Post by **chulett** » Wed Sep 19, 2007 4:49 pm

This, I assume:

http://www.psoug.org/reference/connectby.html

How to replicate this Oracle functionality strictly in a DataStage job.

ArndW · Post by **ArndW** » Wed Sep 19, 2007 4:55 pm

Craig - thanks, I was so much in DataStage mode that I assumed the "connect" meant database connectivity. My bad.

kduke · Post by **kduke** » Wed Sep 19, 2007 8:34 pm

We did it in DataStage by running a job over and over until it selected 0 rows. What a mess. It is sort like drilling down one level at a time.

chulett · Post by **chulett** » Wed Sep 19, 2007 10:14 pm

Exactly. So, why the 'need' to replicate this in DataStage when your database can do it for you? And if a database isn't involved - get it involved. Load your hierarchical data into a work table (or tables) and source from there.

Yuan_Edward · Post by **Yuan_Edward** » Wed Sep 19, 2007 10:22 pm

Do you guys think a parallel routine could achieve this? I will have a similiar requirement in our project.

kduke wrote:We did it in DataStage by running a job over and over until it selected 0 rows. What a mess. It is sort like drilling down one level at a time.

Yuan_Edward · Post by **Yuan_Edward** » Wed Sep 19, 2007 10:31 pm

Well, in our project, the only reason that we want to do it in DataStage could be, I guess, we want to utilize the parallel processing capability of DataStage to handle huge volume of data.

Database like Oracle does have parallel processing ability but it is not as flexible as DS.

chulett wrote:Exactly. So, why the 'need' to replicate this in DataStage when your database can do it for you? And if a database isn't involved - get it involved. Load your hierarchical data into a work table (or tables) and source from there.

madhukar · Post by **madhukar** » Thu Sep 20, 2007 12:44 pm

Actually, we need to achieve during our transformation part where data will be either in datasets or output of a processing stage.

We wanted to avoid putting into a table, do connect by and reprocess again for transformation

Regards,
Madhukar