Page 1 of 1
Assigning output of a query to a JOB parameter
Posted: Fri Jul 16, 2004 1:22 pm
by dwscblr
An oracle OCI9i stage returns a one row that contains only one column. I need to assign it to a job parameter. I tried doing
"JOBPARAM"=last_seq_num.
But the After tab expects a SQL.
I need to use the value returned by the OCI9 stage in a stage variable and increment it by 1 every time.
Posted: Fri Jul 16, 2004 1:57 pm
by kduke
What I would do is split this into several jobs. Output this to a hash file. Write a batch file to read it in and set the parameter when you start the next job.
Posted: Fri Jul 16, 2004 4:44 pm
by ray.wurlod
You can research here and discover that job parameter values are set when the job starts, and can not be changed subsequently.
Therefore you need to capture your initial value in a predecessor job, pick up that value in the job controller (which might be a job sequence), and set the parameter value in the next controlled job from the controller.
There are several techniques for accomplishing this.
setting a parameter from a hash file
Posted: Mon Jul 19, 2004 12:26 am
by peternolan9
ray.wurlod wrote:You can research here and discover that job parameter values are set when the job starts, and can not be changed subsequently.
Therefore you need to capture your initial value in a predecessor job, pick up that value in the job controller (which might be a job sequence), and set the parameter value in the next controlled job from the controller.
There are several techniques for accomplishing this.
Hi Ray,
I also need to do the same with my timestamps for extracting data from oracle...I'm considering doing it inside my C++ scheduler and DS job submission program because it gives me more control.....you know my DS basic skills are pretty low...can you share the code fragment of how to read a hash file and set a parm? I have parm setting code from a friend down there, but not hash file reading code....
Posted: Mon Jul 19, 2004 4:25 am
by roy
Hi,
a search here will get you the code you need, it was covered extensivly.
but IMHO, you can simply pop the value you need via user status and pass it along to the next job (in case you need something simple).
IHTH,
Posted: Mon Jul 19, 2004 5:01 am
by ray.wurlod
You need to know the key value for the row in the hashed file, and the field number in which the desired value resides. Let me take a shortcut by assigning these directly to variables. In the code example I'm going to read a variable from a hashed file and load that value into a parameter in an attached job. I'll also assume that DSAttachJob() has been invoked so that the job handle hJob has been successfully assigned.
Code: Select all
$IFNDEF JOBCONTROL.H
$INCLUDE DSINCLUDE JOBCONTROL.H
$ENDIF
Key = "Control"
CutoffDate = "2004-06-30"
* A "file variable" is like a "handle" and something like a C pointer.
Open "ControlHF" To ControlHF.fvar
On Error
Msg = 'Error opening hashed file "ControlHF". Status ' : Status() : '.'
Call DSLogWarn(Msg, "Setting Param")
End
Then
Read HFRecord From ControlHF.fvar, Key
Then
* hJob is a job handle assigned by a previous DSAttachJob()
ParamName = "CutoffDate" ; * the name of the parameter
ParamValue = CutoffDate ; * the value for the parameter
ErrCode = DSSetParam(hJob, ParamName, ParamValue)
* Non-zero error code indicates an error of some kind
If ErrCode <> DSJE.NOERROR
Then
Msg = 'Error setting "' : ParamName : '" parameter. Code ' : ErrCode
Call DSLogWarn(Msg, "Setting Param")
End ; * end of IF statement
End
Else
Msg = Quote(Key) : ' record not found in "ControlHF" hashed file.'
Call DSLogWarn(Msg, "Setting Param")
End ; * end of Read statement
Close ControlHF.fvar ; * no longer needed
End
Else
Msg = 'Unable to open hashed file "ControlHF". Status ' : Status() : '.'
Call DSLogWarn(Msg, "Setting Param")
End ; * end of Open statement