Paremetrize the columns in a DB stage.

Minhajuddin · Post by **Minhajuddin** » Wed May 30, 2007 10:23 am

Hi there,

Can you guys Please help me with this scenario.

                         DRS_Stage
                                |
                                |
                                |
                                |
                                |
                                V
Sequential_File ----->JOIN_Stage---------->Sequential_File

I have ten parallel jobs which have the same setup as the one shown above.

Here the input source file, the schema of the input file, the output file, the columns from the DB stage all are same.

The only difference in them is in the key which is used to join. In the ten jobs I use ten different columns to join the data.

I was thinking of implementing it using a single job and parametrizing the key column name in the join stage. Is that possible?. And if we can parametrize, Can we use multiple instances of job?
Or do I need to create copies of the job and change the key column names in all the jobs.......

I really appreciate your help.

Thank you :D

DSguru2B · Post by **DSguru2B** » Wed May 30, 2007 10:40 am

Sorry buddy. You cannot parametrize column names. No method that I know of. You will have to explicity define column names and keys.

Minhajuddin · Post by **Minhajuddin** » Wed May 30, 2007 10:50 am

Is there any way we can do this using routines or Buildops or any other options

DSguru2B · Post by **DSguru2B** » Wed May 30, 2007 10:54 am

I doubt it. If you are really eager to get rid of 10 jobs and keep one then you can get away with it by maintaining two jobs.
Job1 will load your source file into a temp table.
Job2 will take in user defined sql that joins the tables and spits out the result to a flat file. You can parametrize the user defined sql.

Minhajuddin · Post by **Minhajuddin** » Wed May 30, 2007 11:01 am

Can you please elaborate on how to go about with job2.

How can I write a SQL Query which is paremetrizeble.

Do you mean I can create a job parameter say "SQL" and put just "SQL" in the User defined SQL box of the DRS stage?

Thanks for the help.

DSguru2B · Post by **DSguru2B** » Wed May 30, 2007 11:19 am

Yes. SQL will be surrounded by hashes ofcourse telling the engine that it is a parameter.

Minhajuddin · Post by **Minhajuddin** » Wed May 30, 2007 11:23 am

Thanks for all the Help DSguru2B :D

ray.wurlod · Post by **ray.wurlod** » Wed May 30, 2007 7:00 pm

Try relying upon Runtime Column Propagation. You will, of course, need to specify the join key, so that may limit your flexibility somewhat.