DB2 sequence implementation in datastage

abhik05 · Post by **abhik05** » Thu Jun 07, 2012 8:01 am

I have one db2 main query which needs to be run for day 0 to the day calculated by following db2 query(select (days(last_day(CURRENT_DATE)) - days(to_date('20120101','YYYYMMDD'))) from SYSIBM.SYSDUMMY1).

day value is passed as a parameter to the main query.

I have the one job to run the main query with this parameter as job parameter.

How I can run this job for parameter values staring from 0 (day 0) to value (calculated day from db2 query)?

Please suggest.

chulett · Post by **chulett** » Thu Jun 07, 2012 8:07 am

If what you need is to run the job multiple times for a range of parameter values, a Sequence job with the Loop stages should work. You'd have to either build a delimited list of those values or from a starting point compute the next value for a set number of iterations.

bicap · Post by **bicap** » Thu Jun 07, 2012 8:22 am

Hi Craig
Thanks for the reply.

As per my requirement I dont know the upto which the loop has to be run as this is to be calculated run time by the query (select (days(last_day(CURRENT_DATE)) - days(to_date('20120101','YYYYMMDD'))) from SYSIBM.SYSDUMMY1). Aslo we need to pass the same to the main job also.

How I can do this?

PhilHibbs · Post by **PhilHibbs** » Tue Jun 12, 2012 10:26 am

bicap wrote:As per my requirement I dont know the upto which the loop has to be run as this is to be calculated run time by the query (select (days(last_day(CURRENT_DATE)) - days(to_date('20120101','YYYYMMDD'))) from SYSIBM.SYSDUMMY1). Aslo we need to pass the same to the main job also.

How I can do this?

Write a Server Routine to call the DB2 CLP with the query, and pick up the results into an Activity Variable which is then used as the loop limit.

*Edit* - although, why do you need to use DB2 to calculate the number of days? Don't you trust the DataStage server's system date?