Multiple Instances and record looping

[DawnBAscUK]

Hello,

Quite a simple question but been trawling the boards trying to figure out the best way to deal with Multiple Instances of a datastage job.
To explain:
We have a sequential file as our source and what we want to do is pass one row to the job and allow it to finish then pass the next row to a new instance of the job. I believe this can be done in 7.5 with looping but we are constrained to using 7.1. Does anyone have an example of how to do this using batch logic or perhaps I am over looking something very obvious?

Kind Regards,

Dawn

[kcbland]

You want a single row to process in a job, then run the job in its entirety for next row, and so on?

Not only is that slow, you are going to find that just running a job that does nothing can take 3-4 seconds. This is because not only does it have startup overhead, wrapup includes purging its log and other things. For each row to be a complete run of a job is really a bad design.

I assume you have BEFORE/AFTER job/transformer statements, clearing of tables, etc, that require a single run for a single row. Based on that premise, you will probably need to build a custom job control Batch job to cycle the transformation job, the only trick being to produce a one row source file from the many row source file. I'm sure you could use a Batch job to open the source file and loop thru it, overwriting your one row source file and calling the transformation job, and repeating this until all rows are processed.

[kduke]

I basically do this in my EtlStats driver job. You feed the sequence name to the driver. It looks up the dependent jobs in that sequence. It feeds these to the job that gets row counts for each job in that sequence. The job name is also the instance id.

In this scenario it does make sense otherwise I agree with Ken.

[DawnBAscUK]

Hello,

We actually solved this by creating a job parameter as a batch number batching the records into groups. The using a bit of Job control creating a loop to pick up each of the batches.
The reason for this design was a client designed webservice which could only process 1 row of data per soap message. In the short amount of time we had I could not think of any other way to split out each row (within the same job) into X amount of XML messages and process those through the WS transformer. Thinking about it... perhaps the Row splitter would have been a better way to tackle this. Any other advice is appreciated.

Thank you for your valueable advice.

Kind Regards,

Dawn