Using SELECT tab with Unidata stage

[smoffa]

I have a job where I have a Unidata input stage. I would like to use a select clause to get only the new records from after the last time that I ran the job.

So the SELECT is like "PROCESS_DATE GT xx/xx/xx" (where xx/xx/xx is the last time I ran the job).

The problem is I need to retrieve/store the LastRunDate in a record.

Is there a way read the LastRunDate record and pass it to the SELECT clause and then write the new LastRunDate out after the job is finished?

Thanks,

Steve

[chucksmith]

I usually create a parameter table with four columns: ParameterName, DateValue, NumberValue, and StringValue.

Store your last run date in this table.

In Universe and Unidata, create an I-type (calculated) column, perhaps using the TRANS (or is it TRAN) function, in your input table to retrieve the last run date from the parameter table. Now both dates are visible in your primary input record, and you can replace the xx/xx/xx in your select clause with the new I-type column name.

Once your job finishes, update the last run date row in the parameter table, and your are ready for the next run.

This construct works equally well with SQL tables, skipping the calculated column step, and just joining with your parameter table.

A good side effect of this technique is that your process is protected from problems associated with missing a run. I call this a "self-healing process".

[smoffa]

Chuck,

I don't quite understand your method. My Unidata file is remote to my DataStage server. The TRANS I-type will retrieve a record from the remote server. I don't want to write to the remote Unidata server. I want to keep the LastRunDate local to the DataStage server.