Job set the own parameters

[eoyylo]

Hi,
I use the sequencer to run a job (named "JOB1") that need a parameter (named "PAR_XX") that is stored in a file.

PAR_XX is in the parametr's list of JOB1

I think to read the parameter in the job control code of JOB1 and use it in the same job.

When the job start, first of all execute the job control code and read the file but I don't understand how I can "tell" to JOB1 to use this parameter in a ORAOCI8 Plug-in.
How can i do it? Is it possible to do it?


In the ORAOCI8 i have the query
SELECT ID
FROM table
WHERE LAST_MODIFIED > TO_DATE('#PAR_XX#', 'YYYY-MM-DD HH24:MI:SS')

and in the job control code have:

OpenSeq KshPath:"/EBIS_params_21" To FileVar Else Call DSLogFatal('Errore nella lettura del parametro di filtro 21 per il job : H3GITmisODStoODSACCOUNTRev2', "JobControl")
ReadSeq val From FileVar Then PAR_XX = val

I tried to set the parameter with the next code in the job contol code but doesn't work

ErrCode3 = DSSetParam(DSJ.ME, "PAR_XX", PAR_XX)


An another little question: where can I find information about the DataStage language for routine?

Thanks in advance

Regards

Mario

[eoyylo]

Hi Ricardo,
Unfortunately I can't use this solution.



Is it possible to use a DataStage Routine in the "WHERE clause" of an ORAOCI8 Plug_in?

Thanks

Regards

[wnogalski]

Create a Routine that will extract the parameter from the file, put it before the job in the sequence, add a parameter to the job, and pass the routine's return value to the job.

[eoyylo]

Hi wnogalski,
sorry but I don't understand

Can you explain?

Mario

[kcbland]

Is it possible to use a DataStage Routine in the "WHERE clause" of an ORAOCI8 Plug_in?

No. Either a job parameter or you need to put the value into a work table and join to it.

[chulett]

What wnogalski is suggesting is you take your job control code and put it into a custom routine. Pass the value of the parameter that it looks up back out as the 'Ans'wer. Then, you can use it in the Sequencer job just before the Job Activity stage that needs it. Use a Routine Activity stage to run it and then, in the value of the parameter in the downstream job that needs it, put:

Code: Select all

RoutineStageName.$ReturnValue

Replace RoutineStageName with the actual name you use for the Routine Activity stage.

[wnogalski]

Yes - exactly. In the sequence add a routine before Your JOB1 and pass RoutineStageName.$ReturnValue as a parameter to Your job.

Here's the routine's code:

Code: Select all

OpenSeq Arg2:'\':Arg1 To FileVar 
      On Error Call DSLogWarn("Cannot open ":FileVar, MyRoutine)
      Then ReadSeq FileLine From FileVar 
           On Error Call DSLogWarn("Error from ":FileVar: " status=":Status(),"MyRoutine")
           Then 
              Ans = Trim(FileLine)
           End
CloseSeq FileVar

Arg1 - File name
Arg2 - File path

[battaliou]

I dont want to cause any derision here, but the code posted above will need to be modified as the datastage logger will have problems accessing the file variable FileVar (improper data type) You would probably be better off creating a new variable e.g. FileName = Arg2:'\':Arg1 and using that instead.

Also, I realise this is sample code, but MyRoutine on line 2 should be in quotes "MyRoutine"

Ultimately, getting back to the original query of using a sequencer to pass a parameter to "Job1" , I'm in favour of a more hierarchial approach where an basic server program eg Job1Call is used to extract the required values and then calls "Job1" This approach tends to look a lot cleaner in the job control, and allows a greater degree of error handling.

[wnogalski]

...FileVar (improper data type) You would probably be better off creating a new variable e.g. FileName = Arg2:'\':Arg1 and using that instead.

Also, I realise this is sample code, but MyRoutine on line 2 should be in quotes "MyRoutine"

Of course You're right