passing date parameter

[ds_developer]

OK, so you have 2 jobs. The purpose of job1 is to calculate a date, send it to job2 and run job2. Job1 has a string parameter called query_date and the format is YYYYMMDD. Job1 does not have any stages; all the code is in the Job Control section of the Job Properties. The default value of the query_date parameter is blank which causes the job control code to calculate a date (like sysdate or sysdate-1, you have to code this). When the query_date parameter is not blank, you use that date (usually some date in the past). In either case, the resulting date is held in a variable, say QueryDate.

Job2 is your real job (has the source query for DB2, does transformations and loads the target). It also has a query_date parameter. This parameter is used in your source query as you initially posted (you might have to reformat the date somehow).

Back in the job control section of job1, you add job2 (using Add Job) and multiple lines of code will be added. One of those lines will be a DSSetParam call for the query_date parameter (this is job2's query_date parameter). Replace the third value in this call with your QueryDate variable. This passes the date from job1 to job2 to be used in the source query of job2.

This design allows a normal run of job1 to send the typical (sysdate or sysdate-1) date to job2. If you need to run job2 for a specific date, run job1 with that date (like 20070401) in it's query_date parameter and it will be passed on to job2.

You'll have to do some coding, but this should give you a pretty good idea of what to do.
John

[Krazykoolrohit]

I added a Uservariable stage in the sequence and added a variable definition to be passed onto the job activity stage with the following derivation

Code: Select all

convert('/','-',Oconv(Date() - 7, "D4/YMD"))

that made it work.

[ray.wurlod]

You didn't need the Convert() function - you could have specified the "-" as the delimiter in the Oconv() function.

Code: Select all

Oconv(Date() - 7, "D-YMD[4,2,2]")