looping jobs

[roshanearly]

What i need is a way to control my jobs in some sort of loop such that a new file is passed on every loop

my job is hitting the db and taking a file and processing it .what i need is to loop this thing in a sequence so that ..like if there are 10 files in the db ...the job can run 10 times and process all files one by one

the no of files in db is not fixed

kindly give an elaborate explanation as i am new to datastage

[chulett]

I don't know about an "elaborate" explanation but the Start Loop and End Loop stages do exactly what you need - allow activities (jobs, whatever) to be "looped" in a Sequence job and for them to be provided a different set of input values each iteration. And those input values are typically filenames. You just need a way to build a delimited list of filenames to pass to the Start Loop so it can feed them to the job parameter that the job uses one at a time.

Start your research by reading the Designer Client Guide pdf, it has a nice explanation of those stages.

Another option, if you don't actually need to process each file separately, is to use a wildcard pattern and process them all at once.

[prasannakumarkk]

Only few days back this was discussed. Guess the number of files are not more. Write the result of table execution in to a file comma seperated file. Within in the start and end loop acitivity, call the job for each file. Here is a solution provided by Ray & Craig
viewtopic.php?t=149071

[chulett]

Writing the list of files to a file is certainly not required but if that helps you work out the kinks then by all means do so.

[roshanearly]

Thanks for the reply everyone .i am using start loop activity and end loop activity for looping.

now the table in db from which i am taking the file has a column as "status" which i have to update from "No" to "Yes" for each row that is processed.kindly help me with this where to give the update so the it gets updated in the sequence.

Also how to give the end loop condition so that the loop stops once the status of all the files changes from no to yes(after processing)

[chulett]

Write a small Server job to update the status, I'm assuming passing in the filename will allow you to find the row again. Run it via another Job Activity stage inside the loop after your processing job.

The loop will end automatically when it runs out of things in its "list of things" and then move on to whatever stage comes after the End Loop.

[roshanearly]

Each row in my table has a unique id which i wish to use as a parameter to update the status in the table.
can i use this unique id as input in db2 stage as the key to write an update command .how??

if not pls tell me how to use this parameter and update my status

update table_name set status='yes' where unique id=(here i need the value)

[prasannakumarkk]

Before we write the query, wont the file name and unique id have one to one mapping. If not, how you will find the unique id?

[roshanearly]

yes the file and unique id have one to one mapping ...i am selecting one file which i am processing using other job in the sequence ....i wish to update status of the same file so i am using the unique id of that file

[prasannakumarkk]

OK from the sequencer pass the file name as paramter to a job where we have the connector stage. In the after/before sql call the update statement

update table_name set status='yes' where file_name = #pFileName#

[roshanearly]

the file names are same ...the only difference is this unique id

[chulett]

And? Pass into the job whatever you need to use to update the record. If that means the "unique id" then that becomes the job parameter. I would still suggest a small Server job if that's all it needs to do, you can build one with just a Transformer and a DB2 target that sends one row to the target with your job parameter in it and then let the stage do the update. Or you could leverage the command line interface for the database to do the update. A parallel job that just does that seems like a wee bit of overkill.

So what exactly is your sticking point right now? Which part do you need help with?

[roshanearly]

thanks chulett and others ...its done i am able to update it properly ....but now i am having problem in start loop activity .

what i need to do is run the sequence that number of times for which status is "NO".for this how can i give the end loop parameter ....

[ray.wurlod]

You need to test this within the loop, or construct a list based on the status before entering the loop.