Reusing Stage Variable in the Job Control

endyl13 · Post by **endyl13** » Tue Jan 21, 2003 6:11 am

Dear all,

How can I use a stage variable in a job control?

In my case, I need to write some files, and the number of files will come from doing a 'select count(*) from table'.
I designed my 2 Jobs, the first one is to get the count and run the second job based on that count.
In the transformer, I pass the value of the count(*) into Stage_Var1, and I reuse Stage_Var1 in the Job Control.
But it failed. It seems like the Stage_Var1 in the Transformer doesn't linked to the Job Control.

Can anyone help me with this?

Thanks in advance...
Endy

chulett · Post by **chulett** » Tue Jan 21, 2003 7:51 am

The two jobs run separately and therefor one doesn't have a clue about the other's Stage Variables. You need some way to pass that information from the first to the second job and then use it as a Job Parameter in the second one. You'll find this discussed here recently, but the simplest way (IMHO) to do this is to use DSSetUserStatus in the first job and DSGetJobInfo in your Job Control afterwards to retrieve it. Then use DSSetParam to assign the value to the second job.

-craig

endyl13 · Post by **endyl13** » Tue Jan 21, 2003 9:16 am

Hi Craig,

I am very new with DataStage Basic, and a bit confused with the way it works.
In Job1, I put OCI Stage => Transform Stage => Seq File stage
The Output of OCI stage is the "Select Count(*) from Table", I put it into the StageVar and then to the Seq File.
I want to re-use the StageVar in the Job Control so that the value of that StageVar can be passed as Job2 parameter. But Failed, it makes me don't understand the logic of DS Basic.

Now, based on your advice, where should I put the DSSetUserStatus command? Is it inside the Job1 Job Control before running the Job2?
Where should I put DSGetJobInfo? Is it also in the same Job1 Job Control?

Can you explain me more about this?

Btw, The Last attempt I made was to made 2 jobs, 1 to read from OCI and 1 to write to Seq File, and with 1 Control Job. I also try to use Var1 = DSGetStageInfo(DSJ.ME,"transformer",DSJ. STAGEINROWNUM) but the result always come with NULL. Here the complete code in JOb Control from my last attempt:

* Setup Read1, run it, wait for it to finish, and test for success
hJob2 = DSAttachJob("Read1", DSJ.ERRFATAL)
If NOT(hJob2) Then
Call DSLogFatal("Job Attach Failed: Read1", "JobControl")
Abort
End
ErrCode = DSRunJob(hJob2, DSJ.RUNNORMAL)
ErrCode = DSWaitForJob(hJob2)
Status = DSGetJobInfo(hJob2, DSJ.JOBSTATUS)
If Status = DSJS.RUNFAILED Then
* Fatal Error - No Return
Call DSLogFatal("Job Failed: Read1", "JobControl")
End

hJob3 = DSAttachJob("Read1", DSJ.ERRFATAL)
Var1 = DSGetStageInfo(hjob3,"TransformPIXBlob",DSJ.STAGEINROWNUM)
FOR X=1 TO Var1

* Setup Write1, run it, wait for it to finish, and test for success
hJob1 = DSAttachJob("Write1", DSJ.ERRFATAL)
If NOT(hJob1) Then
Call DSLogFatal("Job Attach Failed: Write1", "JobControl")
Abort
End
ErrCode = DSSetParam(hJob1, "FileName", X:".ND")
ErrCode = DSRunJob(hJob1, DSJ.RUNNORMAL)
ErrCode = DSWaitForJob(hJob1)
Status = DSGetJobInfo(hJob1, DSJ.JOBSTATUS)
If Status = DSJS.RUNFAILED Then
* Fatal Error - No Return
Call DSLogFatal("Job Failed: Write1", "JobControl")
End

NEXT X

Thanks in advance
Endy

Regards

ND

ray.wurlod · Post by **ray.wurlod** » Tue Jan 21, 2003 4:00 pm

Rather that trying to do it in BASIC, why not just have the result of the count written to a one row, two column hashed file? The key is any constant value, say "X", while the other field contains the result of the count.
In the job that needs a count, just perform a reference lookup against the hashed file with a key expression that returns "X".

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518