Assigning output of a query to a JOB parameter

[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.

[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.

[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.

[peternolan9]

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....

[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,

[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