DSXchange

Hi Folks,

I am currently investigating the possibility of generating OSH script (and whatever else is necessary for a job to run) as an alternative to building many (hundreds) of very similar ETL jobs through the GUI. I need to know what needs to be generated (eg OSH, score, etc) and how such code can be then run in a similar fashion to running a GUI-created job through Director.

I would apprecitate thoughts/advice from people who have attempted a similar exercise, on what the best approach would be and what alternatives exist.

Cheers,
Mick

Sure you can write OSH script. There is a manual called the Orchestrate Operators Guide that will help. But you've just wasted a lot of money on DataStage software that can do it for you from a GUI that is much easier to maintain.

You can leverage the Creation of Jobs from Templates, if you need to create many similar jobs.

How similar is "very similar"?

Like, "Basically the same job w/hundreds different
sources (non-matching schemas but a similar purpose)
that map to a single target schema?"

What drives your mappings, e.g. manually launched?
Batch schedule? Real-time?

It would be easier to speculate about the pros/cons of
different approaches if you told us a something about
the business problem(s) you're going to solve.

John G.

megamic wrote:Hi Folks,

I am currently investigating the possibility of generating OSH script (and whatever else is necessary for a job to run) as an alternative to building many (hundreds) of very similar ETL jobs through the GUI. I need to know what needs to be generated (eg OSH, score, etc) and how such code can be then run in a similar fashion to running a GUI-created job through Director.

I would apprecitate thoughts/advice from people who have attempted a similar exercise, on what the best approach would be and what alternatives exist.

Cheers,
Mick

[quote="jgreve"]How similar is "very similar"?

Similar as in the logic is indentical, the only difference being the schema definition in the initial step (which is a complex flat file).

The business problem is essentially reading in a series of flat files, performing some minor filters and transformations, and writing out to a dataset, which will be loaded into a database in a later job.

Any fields that are used in the steps are common accross all schemas, and other fields are simply mapped through. So the generated code is virutal identical save for inital schema.

I would be interested to know more about "Creation of Jobs from Templates" as mentioned, as using the GUI would be preferable to code-generating OSH directly.

Cheers
Mick

Create a job containing no table definitions ("metadata"). Save the job so that it gets a name. Go to the New menu and create a new template from that job. Once that's done, as many times as needed go back to the New menu and choose create job from template; name the job and load the requisite table definitions for that particular job.

megamic wrote:

jgreve wrote:How similar is "very similar"?

Similar as in the logic is indentical, the only difference being the schema definition in the initial step (which is a complex flat file).

The business problem is essentially reading in a series of flat files, performing some minor filters and transformations, and writing out to a dataset, which will be loaded into a database in a later job.

Any fields that are used in the steps are common accross all schemas, and other fields are simply mapped through. So the generated code is virutal identical save for inital schema.

I would be interested to know more about "Creation of Jobs from Templates" as mentioned, as using the GUI would be preferable to code-generating OSH directly.

Cheers
Mick

hi Mick,
Have you tried RPC (Runtime Column propagation) ? All your other fields that are mapped through should be propagated throught by turning on RPC so you don't have to explicit map columns across stages.

Chin

Yes, we have looked at RCP, but it doesnt seem to go well with transformers. There are certain field manipulations which have to be manually set for this to work.
Cheers

Not sure if my approach work for PX.

megamic wrote:Hi Folks,

I am currently investigating the possibility of generating OSH script (and whatever else is necessary for a job to run) as an alternative to building many (hundreds) of very similar ETL jobs through the GUI. I need to know what needs to be generated (eg OSH, score, etc) and how such code can be then run in a similar fashion to running a GUI-created job through Director.

I would apprecitate thoughts/advice from people who have attempted a similar exercise, on what the best approach would be and what alternatives exist.

Cheers,
Mick

Not sure if my approach works for PX.

But I had more than 50 sever jobs with similiar logic. So I exported one job as a dsx/xml flat file as a sample file, then wrote a shell script to produce the other 49 and then imported them back to DataStage repository. Any text editor could be used to search, replace and change the sample file to the right logic instead of shell scripts.

megamic wrote:Hi Folks,

I am currently investigating the possibility of generating OSH script (and whatever else is necessary for a job to run) as an alternative to building many (hundreds) of very similar ETL jobs through the GUI. I need to know what needs to be generated (eg OSH, score, etc) and how such code can be then run in a similar fashion to running a GUI-created job through Director.

I would apprecitate thoughts/advice from people who have attempted a similar exercise, on what the best approach would be and what alternatives exist.

Cheers,
Mick

Edward's option should work, untill the jobs are recompiled to create the executables.