utility run job: get return values

stivazzi · Post by **stivazzi** » Fri Oct 06, 2006 12:49 am

Hi to everybody...
i have a problem: how can i get return values from utility run job??
i am calling it from a transformer stage, where can i get back the values? and... they are in an array... how to get single values from return array?

thanks
Andrea

ArndW · Post by **ArndW** » Fri Oct 06, 2006 1:41 am

Andrea,
the utility run job is a function which will return an integer as the value - this is the return code of the job started. What sort of return values in an array are you looking for?

stivazzi · Post by **stivazzi** » Fri Oct 06, 2006 1:44 am

in help guideline i see "output type" = array...

my called job should be a select which returns 2 or more values... my wish is to pass those values to the original job

thanks
Andrea

ArndW · Post by **ArndW** » Fri Oct 06, 2006 1:53 am

I wish I had the docs in front of me, but I suspect they state a "dynamic array" which is quite different from an array. Calling another job from a transform stage using this routine is probably not the best solution for your problem, particularly since it is quite slow (several seconds per row at best) and is not designed to return values.

If you could explain what you are trying to achieve perhaps someone here might be able to come up with a better approach for you to use.

stivazzi · Post by **stivazzi** » Fri Oct 06, 2006 1:56 am

i want to call a job2 passing a parameter...
job2 is just a select on db, returning 2 values...
these 2 values should be passed to job1 (the caller)....

hope it's clear...

thanks
Andrea

ArndW · Post by **ArndW** » Fri Oct 06, 2006 2:06 am

If you need different value for each input row then using a direct link to lookups in the job itself might be the best thing. If the 2 values are needed only one time per job then I would retrieve them in a sequence called before the job and pass them in as parameters.

ray.wurlod · Post by **ray.wurlod** » Fri Oct 06, 2006 2:33 am

I don't have access to DataStage right now, but I am fairly sure that there is another function to decode the return value from UtilityRunJob.

Can you search your routines for it? It's probably in the same category as UtilityRunJob.

ArndW · Post by **ArndW** » Fri Oct 06, 2006 2:45 am

Ray - that would mean explicitly setting the user return from the job to return the values from the 2 rows; this doesn't seem to be a viable method.

ray.wurlod · Post by **ray.wurlod** » Fri Oct 06, 2006 2:47 am

From memory I recall that a variable in COMMON is used to store the dynamic array, and the other function decodes it from there. But, as stated, I'm not anywhere where there's DataStage; it's Friday night and I'm going out just as soon as I've finished my DSXchange chores.

chulett · Post by **chulett** » Fri Oct 06, 2006 6:38 am

stivazzi wrote:i want to call a job2 passing a parameter...
job2 is just a select on db, returning 2 values...

You might want to explain how in the heck this 'job' is returning anything like two values from a database. All you get back from a DataStage job run is a status.

Once you explain exactly what it is you are doing, you may be able to get more coherent suggestions when people come back to do more chores.

stivazzi · Post by **stivazzi** » Fri Oct 06, 2006 6:44 am

my idea was job1 -> utilityRunJob('job2', 'param1',0,0)

in job2 i have an oracle stage, with a select like 'select A, B from table1 where c = param1'... this 2 values (A, B) need to be passed to job1.... it will also be 1record!
in job1 i will have my data + A +B...

i cannot be more clear

thanks
Andrea

stivazzi · Post by **stivazzi** » Fri Oct 06, 2006 6:45 am

my idea was job1 -> utilityRunJob('job2', 'param1',0,0)

in job2 i have an oracle stage, with a select like 'select A, B from table1 where c = param1'... this 2 values (A, B) need to be passed to job1.... it will also be 1record!
in job1 i will have my data + A +B...

i cannot be more clear

thanks
Andrea

ArndW · Post by **ArndW** » Fri Oct 06, 2006 7:04 am

Stivazzi,

you have now had 3 people comment that this is not the way DS works and that it cannot be done this way. I don't know how much clearer the responses can be

UtilityRunJob is a function whose effect (return value) is a dynamic array which contains information about the job's run status and the stages/links in that job. You can look at the source of the routine (I just did) and see how the output is put together. The output does not contain any values retrieved in the job called.

chulett · Post by **chulett** » Fri Oct 06, 2006 7:10 am

You need to rethink your approach. Why are you calling jobs from a Transform? Explaining your overall goal with these jobs would go a long way towards helping us help you figure out the right way to do this.

Typically, the first job would be run by a Sequence job and the results of the query would be stashed somewhere - flat file, user status area, etc. Then something picks up those values and passes them as parameters to the second job when it is run by the Sequence job. The mechanism of the 'pick up' process varies based on how it was landed.

Please give us an idea of what exactly it is you are trying to accomplish.

stivazzi · Post by **stivazzi** » Fri Oct 06, 2006 7:26 am

i am processing data from an xml file...
before spreading these data in tables, i need to pick up the values of a tag and, with this values as parameter, pick up 2 other values from a table... these 2 tables need to be inserted in tables together with other data from xml file...

don't ask me why..... boss asked!

thanks
Andrea