Ok, let me see if I can lay out the scenario descriptively enough.
We have a single sequence, designed to dynamically load hundreds of tables, that has the following Job Activities, in order:
0) Scheduler - passes along a DS project name, a job name and table name for the sequence to run.
Sequence comprised of:
1) Pre Row Count - executes a server job that tallies the number of rows initially on a table
2) Table Load* - executes a server or parallel job to load a table. Parallel or server simply depends on how the table was built. **contains a required job name that acts as a dummy**
3) Post Row Count - executes a server job that tallies the number of rows now on the table
4) Load Status Table - executes a server job that writes the table name and date to a table that simply documents when tables have been loaded
5) Compare Counts job* - executes a server job that compares the pre and post row count values for a large drop in row count and sends email if so.
Z) Exception Handler - sends an email if any of the above jobs don't run.
* - has notification activities to send an email if the status of the executed job isn't '1'.
Ok, so we copy the control language of this sequence and paste it in a new server job, but change the dummy 'load job' name in the control language with a variable so that any job name can be loaded to the sequence.
This all seemed to work fine for a few weeks until we added step '4) Load Status Table'. Now all tables are loading fine, but every once in a while we get an exception notification email and upon investigation the stated, from the scheduler, table loads fine, but the table name is _not_ on the status table. So, that recent step, 'step 4)' doesn't execute for a few tables. Again, the lion's share is fine.
Oh, all job activities are set to 'reset if required, then run'.
It has been hard to determine exactly what has happened, because the few jobs that send exception emails seem to have dropped off the job log list to review.
Are we having this issue because:
A) We should be copying the Job Control Language into a parallel job instead of a server job since we have both types being run from within the sequence.
B) Step 2 is working fine because only a specific job is being run, but Step 4 is bombing because there are multiple sequence executions attempting to load the status table at the same time.
C) We are totally off base in this design and should go in a different direction. (If this is the case, I'd love to hear it, but also interested in a solution that is inline with our current design.)
D) Other
Thanks!
Sequence Design Suggestion
Moderators: chulett, rschirm, roy
I'd lean towards B at first but there really isn't any way for us to know. If there is a possibility that the 'Load Status Table' job will be triggered simultaneously across your loads then make sure it is a multi-instance job and send something like the table name in as the invocation id.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers