DSXchange

Hi All,

How can i achieve Oracle connect by functionality in DataStage

regards
madhukar

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

This, I assume:

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

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

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

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.

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.

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.

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.

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