Pass value to parameter

[le thuong]

Hi All,



How can I do the following:



- we need to load data from an oracle source

- in this source we have a timestamp that indicates when the row was last updated/inserted

- We want to get only the rows where timestamp > lastruntime



I see a few options:

Storing lastruntime in a sql table and when running the job the next day
- fetching the lastruntime from the sql table and storing it in a parameter
- after this, executing the qry against our source like 'select * from A where timestamp > #PS_Parameters.Lastruntime#'
- Problem: How to assign a value from a resultset to a parameter?
Another approach would be to use a datastage macro. I know there is a macro like DS.Jobstarttime.
- Problem: Is there also a macro that stores lastruntime?
Yet another approach would be storing lastruntime in a sql table and when running the job the next day
- fetching the lastruntime from the sql table and storing it in a variable
- after this, executing the qry against our source like 'select * from A where timestamp > $lastruntime'
- Problem: How to store the value from a resultset to a variable?
Thanks in advance,

[chulett]

We store that date/timestamp in another table, one dedicated to storing those "last extract dates" and then simply join the appropriate record from that table to our source query.

Otherwise, as you've noted, you need the 'overhead' of a process to fetch and store it somewhere accessible externally, like a flat file or the USERSTATUS area of a Server job. Then job control / a Sequence file could fetch that stashed value and pass it as a job parameter to the job that needs it.

[JRodriguez]

Thuong

You are in the right track. We do it a bit different, we use an audit timestamp field in the target table. and use those values to determine and extracts the delta from the source.

Essentially:

- Use a sequence job, a server job, and the sourcing job
- With the server job, extract the maximum timestamp from the target and pass the timestamp value out using the job's status area
- In the parallel job, next job in the sequence, use a parameter for the timestamp that you would use in your 'select * from A where timestamp > MaxTargetTimeStamp'

Notice that the first time the target timestamp will be null, and you would need to default the value - using nvl function- to any timestamp that meet the condition
- We have an environment variable "LowDate" that we default to the company's founding date-