Pass value from table to job parameter

[jerome_rajan]

Hi,

We have a framework that controls the operational metadata, scheduling and other housekeeping activities. The framework records the last successful completion time for every job into an audit table.

We have a job(say jb_test) that reads data from a voluminous staging table on a weekly basis. Due to the huge volume, we are looking at an incremental extract using a query that looks something like

Code: Select all

select * from xyz where tran_date>last run date

My question is, how do we inherit the last run date value that is stored in the audit table into a job parameter so we can use it in the query?

[chulett]

One thought - use a sub-query into the audit table. Can you do something along the lines of:

Code: Select all

select * from xyz where tran_date > (select last_run_date from audit_table where ???)

Do you have a way to join to the proper audit record for the job in question? The downside of that is you wouldn't have an audit trail of exactly what value was actually used.

Seems to me you could also use a Server job to fetch the last run date and store it in its USERSTATUS area, then a Sequence could easily pass that to the parameter in your incremental extract job. You could also establish a values file for this and update the value in the file in a similar fashion then simply run the job in question.

[prasannakumarkk]

If the framework is able to insert a record in to audit table, it must be able to select a record from audit table. So you can make the selection in framework and since the framework is doing the scheduling you can pass the extracted run date as paramter to the script that invokes the job.

In case both audit and staging table are in same database, adapt Craig approach.

[jerome_rajan]

Thank you! :D