Passing read rows from one job activity to other in seq

Simar · Post by **Simar** » Fri Mar 01, 2013 10:15 pm

Hi All,

I am reading from a table (can be one or multiple rows) and the row/s have job name and required job parameters. Based on my read I need to trigger the those different or same multi-instance data load jobs parallely with two common multi-instance jobs (one before data load and one after data load) with different parms as they will be different in all rows.

I have tried to do following:

Seq 1, calling a server job reading from table and setting the output to UserStatus variable. And then it triggers a seq 2 which have a three parallel jobs as I need and it takes parameter from the UserStatus variable. But issue I am facing is that it only records the last row read from the table not all the rows.

I have tried to put it in a loop but still it only reads last row. Is there any way of doing this without writing to sequential file and then reading it.

Responses will be highly appreciated. Thanks in advance...!

chulett · Post by **chulett** » Fri Mar 01, 2013 11:26 pm

UserStatus only holds a single 'record' or element. Are you able to use UtilityRunJob for this? Meaning rather than the Server job writing the name of the job to run out it actually runs it instead.

Simar · Post by **Simar** » Sat Mar 02, 2013 6:16 am

Hi Chulett,

Thanks for quick reply. Though I am not able to see your full reply (because I am not a premium user and can't be a as I work occasionally on DS).

I tried to use the UtilityRunJob in the server job itself and then triggering my following jobs but it doesn't trigger them parallely it does them in sequential order. It starts first for first row and then wait for it to finish and then start the second for second row.

Thanks...!

chulett · Post by **chulett** » Sat Mar 02, 2013 8:00 am

Yes, it does... that why I posed it more as a question. I thought that might be ok since you are considering a looping sequence. And you didn't miss anything significant so I unmarked it.

Simar · Post by **Simar** » Sat Mar 02, 2013 12:15 pm

Hi Craig,

So, if I understood correctly you are saying the loop will also run these jobs sequentially, I wasn't able to test that part because I was always getting last row and it was triggering same job 3 times.

And then (if I can ask) what would be the best way to implement my requirements which are as following:

- Read from table (can be 1 or multiple rows and each row will have job name and certain parms)
- Trigger those jobs in parallel which will be in a sequence as they have a common before and after part. (this sequence will be a multiinstance one which will call same first and last job (with a different provocation id) and the middle will be a data load job with certain parms which were read from the table.

Thanks in advance...!

Simar · Post by **Simar** » Mon Mar 04, 2013 4:42 pm

Hi Chulett,

I am trying to implement this by storing data in a file and reading it with execute command and then using dsrunjob command in a loop for all rows with waiting for status.

But, I am not sure on how to read multiple rows and parse them to run in a loop. Any help will be appreciated.

Thanks in advance...!

ray.wurlod · Post by **ray.wurlod** » Mon Mar 04, 2013 6:34 pm

Execute your command and pipe the result through tr to convert the line terminators into commas. Optionally pipe through sed or awk to remove the empty "line" at the end, or use a user variable to strip this.

chulett · Post by **chulett** » Mon Mar 04, 2013 11:49 pm

"Parse them to run in a loop" means what? Are we talking about a Sequence job with the Start and End Loop stages? Or are we talking about a fully scripted command line solution? Since you mentioned the Execute Command stage I'm assuming the former but not sure what you mean by "dsrunjob"... the Job Activity stage? Or something else.

Simar · Post by **Simar** » Tue Mar 05, 2013 8:34 am

Thanks Ray and Craig for your comments.

Ray: Though I was only able to part of your post but I think I understand what you are saying is to have all those rows converted to a big column and then loop through it. In that case my next question would be, that will it scale in the sense that if I get let's say 100 rows and each row is around 500 chars and then my one col (having all rows) would be of 100*500 + 99 (for comma)= 50099 chars, so, would that stage and next stage in sequence job would be able to parse it.

Craig: I am talking about start and end loop stages in sequence job. To start jobs in parallel (based on num of rows I get), I am trying to use DSJOB command in execute command stage rather job activity in the loop because it waits for one to end and then triggers the other.

Thanks again...!

chulett · Post by **chulett** » Tue Mar 05, 2013 8:59 am

Some random thoughts...

I have no idea what the maximum size of the "list of things" can be for the Start Loop stage to iterate through.

Do you really want to be running up to 100 PX jobs at the same time?

That methodology won't allow you to monitor the outcome of the job runs.

Simar · Post by **Simar** » Tue Mar 05, 2013 10:19 am

Hi Craig,

No, we won't be running 100 jobs, right now we will be setting the higher limit to 15 and in future we can vary it based on resource and utilization factors.

So, if that's the case, I can try with the Ray's approach and see if I get what I am looking for.

Thanks...!

ray.wurlod · Post by **ray.wurlod** » Tue Mar 05, 2013 1:20 pm

The only limit on the length of a string in server jobs (and a sequence is a server job) is how much memory you have available to store it in. Certainly 50KiB won't be a problem.

Simar · Post by **Simar** » Tue Mar 05, 2013 5:42 pm

Thanks a ton Craig and Ray. I have been able to achieve what I wanted.

I have a sequence which has a job activity and this activity reads the rows from table and then these rows are stored in a file. Then I use Execute command to read this file (as pointed by Ray) and then I have a start loop to loop through the list of contents in the file and in the loop, I have a Execute command to trigger DSJOB - run to trigger the next sequence job which will do my pre, load and post step.

Sequence 1: Job Activity --> Execute Command --> Start Loop --> Execute Command (triggers Sequence 2 with different jobs) --> End Loop

Thanks again...! You guys are Gurus.

prasannakumarkk · Post by **prasannakumarkk** » Wed Mar 06, 2013 12:18 am

Simar,

Sequence 1: Job Activity --> Execute Command --> Start Loop --> Execute Command (triggers Sequence 2 with different jobs) --> End Loop

Are you able to run the jobs in parallel. I guess inside a loop, a activity cant start until the previous counter got completed. In the execute command activity within loop did you give wait option for dsjob command?

Simar · Post by **Simar** » Wed Mar 06, 2013 11:18 am

Hi Prasanna,

I am sure it won't be parallel enough to trigger all the jobs at same time but it triggers as it loops through and doesn't wait for the first one to fail or succeed.

and, I have not given the wait option in DSJOB command.

Thanks...!