incremental extraction

[dsscholar]

Hi all,

For incremental extraction from source,

i take the last run's date in a text file, which can be used as reference in lookup and i can pick up the records which have greater date than that from the source. If there is a table for keeping the last run's date value means, then there is no need for lookup i can take using that in the query itself. Is there any way i can do the logic with text file in the source itself. please advise.

Thanks,
Ram

[chulett]

Read the text file and pass the value in as a job parameter, then bind the parameter value into your source sql.

[dsscholar]

Hi chulett,

How to update the parameter file everytime. If we create a parameter set for this specifically, how to access it. Please explain the logic. Is the parameter set will be a text file. And in the value tab of the parameter set i have to give the initial value and update it everytime?. If its unix i can update using "> standard output syntax ". What in case of windows, should i create a new file and delete the existing one.

Thanks in advance

[ray.wurlod]

The > redirection operator is just as valid in Windows.

The approach is to use a DataStage job or routine or execute command activity to populate this file.

[Pacific007]

There is other option apart from provided by other readers. You can use the control table and that should be populated initially at every run of your jobs/sequence and in your table you can add a column update_date whose value should be passed from DS transform DS job start date. Use pre SQL in your jobs to check the records date with the update_date column because it is not yet updated. and at the end of your jobs update the control table with the update_date, so every time ur control table will have last_update date and current date apart from first run where u have to set initial default value and you can use those dates in pre and post SQL to get ur desired result.

[chulett]

Well, the other readers answered the question as asked.