Creating common datastage jobs

[dnat]

Hi,

We are doing similar processing with ETL jobs so wanted to know how we can make it configurable.

For ex: Job 1

1.Extract from an oracle table and write in in a fixed width file.

Here we would need to change the columns and use a user defined query for selection.

2. Extract from an oracle table and write in a delimited file

Here we would need to change the columns and use a user defined query for selection.

3. Read from a file and do upsert/insert or just update.

Here also the file columns would change and we might need to update a particular column.

-----------
For column differences i think we can use schema file..I havent worked with it, but will check. But can you define the keys differently for different jobs .

Need inputs.

Thanks!!

[dxk9]

You are going to use different jobs for the examples you mentioned right?

You can use differnt keys for different jobs and also within a single job.


Regards,
Divya

[dnat]

Yes, i have given three different scenarios.

For example Job1 needs to be processed for different sets of files with different keys

[kduke]

You want to create a job template then rip out the columns in your template and then replace them with source and target columns stored in a table.

Is this correct?

In version 8 there is a product called Fast Track but the template or standard job is IBMs. There are other ways to accomplish this but none are that simple. There is a way to use RCP and schema files to accomplish the changing of the columns. Still not a simple solution.

I have done this by writing programs which read the standard job's export file and replacing the columns in all the stages. Not a simple task. It may be worth the money though. Lets say you have 200 tables. Lets say it may take 3 days per developer per table. Lets say my generate can cut that down to 1 day. You would save 400 work days. Lets say it takes you 10 days to write the generator. You still save 390 days. I would say it would take you a lot more than 10 days but still might be worth it. Next the project leads all thought it was waste of time. They never gave me credit for cutting the development time in half. Some of the developers complained it forced all the jobs to look and act the same. I think was a huge benefit. Every time we changed our standard job then I had to spend a couple hours changing the generator. So the leads thought all I ever did was work on the generator. I still produced as many jobs as anybody on the team.

When you do something this complex not everbody will be thrilled. Nor will they understand your grasp of the whole development process.

[chulett]

Search here and research in the docs RCP and schema files.

Kim, nobody but us old-timers would be able to build a flux capacitor, err... I mean job generator from scratch. Besides, even when you do nobody but another Grognard would really appreciate it.

[kduke]

Thanks Craig for the laugh.

My point is that if you think outside the box sometimes it appears you are not doing your job.

I do think anybody can do this. It is not that difficult to do. Just a lot of trial and error. It is only complex in that it is hard to explain and hard to justify.

[ray.wurlod]

In any case the old paper capacitors leak after a certain age.