Dynmically Creating Job Parameter

[1stpoint]

I have a DataStage job with 2 Parameters: Year 2004 and Period 01

In the Job Control of the job I have a sentence that says:

Code: Select all

PriorPeriod=PeriodAdd(Year:Period, -1)

In my first stage (Oracle) I would then reference the Parameters as:

WHERE Period Beween '#PriorPeriod#' and '#Year#'||'#Period'

This is not working. Is there a way to dynamically create a Job Paramter that I can use in the Job?

[chucksmith]

Are you getting a SQL error, or just the wrong data?

Do you have a trailing # before your last single quote?

[1stpoint]

Actually the Job is aborting without any error.
I modified the job control slightly:

Code: Select all

DEFFUN PeriodAdd(A, B) Calling "DSU.PeriodAdd"
PriorPeriod=PeriodAdd(Year:Period, -1)

I also added a Job Parameter PriorPeriod (String) because I wanted to use it in a transformer to show if it was being calculated correctly (200312).

The Job compiles but when it runs I get: Job ProjectsLaborHist aborted.

[chulett]

Tell us exactly what you mean when you say:

In the Job Control of the job I have a sentence

You can basically do what you are trying to do, I suspect you are trying to do it in the wrong place.

[1stpoint]

Yes, I do have a trailing #. The correct format of my where clause is:

WHERE Year||Period Beween '#PriorPeriod#' and '#Year#'||'#Period#'

[kduke]

Ray has explained this before. I am not sure I remember all the details why but you cannot change a parameter value in job control. I think the reason was the connection was already established to all the dabases in the stages before job control was executed.

You have to create a batch job and feed the parameters into the job when it is started.

[ray.wurlod]

You can do whatever you like with variables in the controlling job.

When you attach the controlled job and set its parameter values using DSSetParam, these values remain constant for the entire run of the controlled job.

You must pass the parameter values to the controlled job. If you're working for me, you must also check that the calls to DSSetParam were successful. You can quickly check what parameter values were passed to the controlled job by inspecting the status detail of the controlled job in Director, or at the "job started" event in the log.

So, in summary, make sure you pass values to the parameters PriorPeriod, Year and Period to the controlled job, so that it can use references to these parameters in the WHERE clause of the SQL in the stage that connects to Oracle.

If the controlled job still aborts, try resetting it (NOT re-compiling it) and see whether there's an event logged called "from previous run". If there is it will contain additional diagnostic information. There may be something completely unrelated to SQL causing the job to abort (for example, trying to run the job immediately after an error can result in spurious "unable to resolve service name" errors).

[chulett]

My suspicion is that this code is in the Job Control tab of a Server Job, not out in some separate job control - or at least that is what I was attempting to ascertain.

The other solution is to compute the prior period inside the actual Oracle query. I've just done exactly that, establishing a query that checks a 'rolling six month window' using similar parameters and the Add_Months function in Oracle. Actually, the number of months to check is a job parameter which is currently set to 6.