One generic job to extract data from all tables

[wahi80]

Hi,
I'm getting back to DataStage after a couple of years, so I apologize first in case the question below has been answered earlier. I tried searching but did not get the answer.

I have 130 Tables in my ODS schema. Data from all these tables needs to be extracted everyday by using last_update_dt column. Everyone in my team is suggesting that they would write 130 jobs to extract data from each table. But I think it can be done in one generic job since all columns need to be extracted from each table.

I had done something similar 5 years back, something with RCP column..but age is playing tricks with memory. I had something like a for loop passing all table names to this one multi instance job.

Someone please tell me it can be done in one job rather than 130 jobs.


-Regards
Wah

[jerome_rajan]

I posted an answer here for a generic file to table process. The flow for a table to table process shouldn't be too different.

[ray.wurlod]

I, too, have done this in the past, and I'm sure at least the basic ideas exist on DSXchange.

On another occasion I used the content of the system tables to create SELECT and INSERT statements "on the fly" for each table.

And don't forget that Modify specifications can be completely parameterized.

[wahi80]

Hi,

I was able to get all columns of all required tables.
What if I just had to make a transformation to one column across all the tables I extracted.

e.g: Create_Dt needed to be Sysdate is all files that were created by the generic job

Can this be done?

Regards
Wah

[ray.wurlod]

Yes.

Does Create_Dt come from source or is it being generated in your job?

In the former case use a Transformer or Modify stage, in the latter case use a Transformer or Column Generator stage.