DSXchange

Has anyone been able to use DSSETPARAM from within a job? I want to set the value of a job parameter based on the result of a data transformation within a transform stage. The parameter will be used as a job output file name. For example, if a job performs a lookup on an Oracle table, and the value it retrieves needs to used in the name of the output file. Can this be done using a parameter. I would like to do this from within a job, and avoid using external job scripting and job control.

Thanks

I don't think you can use DSSETPARAM within the job. I think they are one of the first things that are executed when the job is started.

Look on of Ray's postings where he mentioned the Order of execution of variables in the job.

butlerhd wrote:Has anyone been able to use DSSETPARAM from within a job?

You can't, period. You can only affect the value of a Job Parameter before the job starts, once it does it is cast in stone.

However, that doesn't mean you can't do what you want to do - but you will need to break this into two jobs. Use the first to get the value that you need and then pass it to a second job that does the actual work as a Job Parameter to be used as the output filename. A simple Sequence job can automate the passing of the parameter. One way to skin that particular cat, anyway.

Thanks for the swift reply, but I am still unclear how to set the Parameter Value in the first of the two proposed jobs. If I break the job into two, and the first job derives a value( from a lookup or data transformation), how do I put that value into a job parameter or job sequencer parameter that can then be available to the second job?

Thanks

IMHO, the simplest way is to leverage the USERSTATUS area. There have seen several discussions here on the subject which a search should turn up. It will flesh out what I've written below.

I've built a custom routine to call the DSSetUserStatus function from inside a Transform to do exactly the kind of thing we are discussing. This information in essence gets 'parked' and any subsequent job can reference it. Then, when you link the two jobs via the Sequence job, set the value of the second job's parameter to the User Status value from the previous job. It should be available via the "Insert Parameter Value / External Parameter Helper" as the StageName.$UserStatus choice from the stage that runs the first job.

If you wish to stay within a single job, you might consider a fixed output file name and use an after-job routine call to execute a script to do the database fetch and copy/move the file under the variable name.

Craig

I would use a hash file or a sequential file. It is very easy to write the result to either of these adn read it back in the batch job controlling the next job. These are a lot easier to explain to someone not fimliar with DataStage. They are more conventional storage methods. Keep it simple. I think these more eloborate methods are confusing and may not work from release to release.