sysdate-1 Date as Job Parameter

[ririr]

I need to pass sysdate -1 as default value to one of the job parameters to all of our datastage jobs. I tried to use Oconv(@DATE, "D-YMD[4,2,2]") in the default value and it throws an error: "Must have a valid date(not including time)"..

Any help is appreciated!

Thanks

[roy]

Hi,
several thing:
1. check what did the Oconv() produce as a givven date in your case (it can return an empty string or NULL).
2. check what is the expected date format from the target DB.

the method you implemented works fine you just need to debug it.

no DS avail now but did you try Oconv(( @DATE ) , "D-YMD[4,2,2]") ?

Good Luck,

[chulett]

the method you implemented works fine you just need to debug it.

Sorta. Yes, the methodology can be made to work, but you can't use expressions in parameter default values.

You need some sort of Job Control wrapper that computes the data and then assigns the resultant value to the parameter. Or build a master Seqeuncer job that does something similiar.

Since you need to use this in 'all of your jobs', how are they being scheduled or run now?

[ririr]

We are in test mode. Eventually all of jobs will be scheduled.

On your comment

"sort of Job Control wrapper that computes the data and then assigns the resultant value to the parameter"

can you please be more specific.

Also, how is the parameter default value different in jobs and sequencers.

Thanks

[chulett]

I understand that all of the jobs will be scheduled, my question is how? Do you have access to an enterprise scehduler? Will you be using 'dsjob' from the command line or cron via Director? Are you planning on using Sequencer jobs at all? Those kinds of things would drive different answers to your question.

When I talked about 'job control', I was talking about hand-coded 'jobs' that are used to setup, run and monitor other jobs - or a series of other jobs, much like Sequencer jobs but more powerful. As part of the code, you would use what you posted to get a value for sysdate - 1 and then use DSSetParam to assign that value to the appropriate parameter before running the job. If you check your online help index for 'DS', you'll see there are a number of DSGet and DSSet functions that are used for this very purpose. You could probably take one of the function names - like DSSetParam - and search through the forum to turn up code snippets that show it being used.

FYI, there are ways to accomplish this in a Sequencer job as well. As an example, a routine could be written to compute your date. Each sequencer could run that via the Routine Activity stage and then use the StageName.$ReturnValue from it to populate any downstream jobs that needed it as their parameter value.

How are parameter default values different in jobs and sequencers? They're not, really.

[roy]

Hi,
Guess I didn't quite get the whole picture for some reason .
(I implemented such solutions to many a time before )

so let us know what method/way you'll use to run theese jobs and answers will shurly follow.