Looping in Sequence with Startloop Actvy and Endloop Actvy

[John Daniel]

Below is the scenario which i want to achieve by means of the Sequencer.
Sequencer calls the job that basically extracts the data from the table from certain period of time. For example, when the job is run for the first time given a parameter of start date (eg., 2005/01/01) till current date.(ie.,2009/11/16).
Today when am running the sequence,it should be
i.e,
Parameter Values :
#pStart# : '2005/01/01' ; #pEnd# : Current Date (2009/11/16)
select * from TblA where Date>=#pStart# and Date<=#pEnd#

When am running a month later (i.e. 2009/12/16), it should take the start date as the previous run's Current date and End date as the day's CurrentDate automatically.
i.e,
#pStart# : '2009/11/16'; #pEnd# : Current Date (2009/12/16)
select * from TblA where Date>=#pStart# and Date<=#pEnd#

and goes like that for subsequent runs.
Can anyone suggest a solution for this which can continue the load with just the beginning start Date.('2005/01/01' in this case)

How to achive this scenario Please help me in this.

Help would be Appreciated

[boxtoby]

Hi John,

It sounds to me like you wish to run a job where the end date is today and the start date is the end date from the previous run.

I would store the end from each run in a table. Then when the job runs next time you can retrieve that value using a job which reads the table and writes it out to a flat file. Your sequence can then execute a unix cat command to retrieve the date from the flat file and pass it as the start date parameter to the job.

For the initial run you will need to populate the table manually.

From what you describe it doesn't sound like you need a start/end loop activity, unless I have missed something.

Hope that helps,

Regards,
Bob.

[vishal_rastogi]

HI
you can create one table A in which you can put these dates and then your job design will become

job to extract from the table A for date and putting that into a file -----> read that file through a routine or unix command -----> pass these parameters taken from the file into the the main job ----> upadte the table A for the dates

[priyadarshikunal]

The answer will differ in case you have DataStage 8.5 as all the arithmetic calculations you would do in a seq can be done in a simple job within transformer using loop variable.

The logic as follows:

1. first get the number of days from database or file where you have stored the last run date lets say it as NUMDAYS.

2. Get CounterVAR=Ceil(NUMDAYS/15)

3. put counter var in a numeric loop in Start loop activity as max with step 1.

4. now all you have to do is to fetch the LastRunDate again from database add 1 sec in database query or in datastage, put it as start date and +15 in end date.

5. and keep updating the database last run time with end time after end of every run.

6. Don't forget to check the end date time with sysdate/CurrentTimestamp and if its greater then subtract 30 mins from sysdate and put it as end date.

If you have 8.5 then point 1-4 and 6 can be done in transformer for making a list of start and end dates. and loop activity will then run in a list loop mode generated earlier.

enjoy,