Routine or Unix Script

monaz · Post by **monaz** » Tue Mar 31, 2009 2:33 pm

hi All,

There is a requirement in project is that we need to implement the following in two ways the same code as descriped

1.The job1 will give me a output file with two columns Id and Name

2. I need to use job1 file output to the job 2 as parameter( Id and Name)
What job2 will do is that it will have the SQL query where the parameter two will be used in the where clause.
And the output of this job will give Parameter of job1 and the required columns from SQL

So please suggest how to implement , i have got the solution from the experts need help on how to implement.

Or else let me know if any alternate solution is there to implement for this?

As i am new to datastage dont know how to implement the below task

A sequence will first call Job1. And then a DS routine is called, which will read the file, and call Job2 with two parameters. This routine will looop through the file, for each line it will call the job2. This solution can be little difficult and complicated.

Second approach could be through UNIX script.
After Job1 is completed, a UNIX script is triggered.
This script will read the file, and for each line, it will call Datastage Job2 with necessary parameter. Its possible to call Datastage Job from a UNIX script.

Please let me know how to trigger the unix script in the job.

So please suggest how to implement.

It will be really great full

DSguru2B · Post by **DSguru2B** » Tue Mar 31, 2009 2:39 pm

I like option two. A third option that you can consider is to call Job2 in your first job using DSJobRun() and pass the values as a job parameter.

chulett · Post by **chulett** » Tue Mar 31, 2009 2:58 pm

I don't see a problem with option 1, nor a need for any 'looping'. A routine can be used to retrieve the values and called twice in the Sequence, once for each value. Or a UserVariables stage could pull both values, then either could pass them as job parameters to Job2. Neither difficult nor complicated in my book.

ray.wurlod · Post by **ray.wurlod** » Tue Mar 31, 2009 4:10 pm

I'm sure I answered that recently in a different thread. Too busy to search for it at the moment. Solution involved use of Job1 user status area.

monaz · Post by **monaz** » Tue Mar 31, 2009 5:57 pm

chulett wrote:I don't see a problem with option 1, nor a need for any 'looping'. A routine can be used to retrieve the values and called twice in the Sequence, once for each value. Or a UserVariables stage could pu ...

thanks for your reply.

Can anyone guide me in steps how to follow

It will be really helpfull as i am new to datastage, so finding difficult to start off

chulett · Post by **chulett** » Tue Mar 31, 2009 9:36 pm

ray.wurlod wrote:I'm sure I answered that recently in a different thread. Too busy to search for it at the moment. Solution involved use of Job1 user status area.

viewtopic.php?t=126249

Also suggest you search the forums for "setuserstatus" a function that helps with leveraging the USERSTATUS area Ray mentions.