During our month-end batch processing cycle, we had nearly
50 batch jobs that updated the same row in a DB2 table.
Individually, these jobs completed within seconds. However,
when multiple jobs ran concurrently, they frequently encountered table contention because they were attempting to update the same row simultaneously.
As a result, some jobs would abend.
Every month-end cycle, we typically experienced 3 to 5 job
abends due to this contention. Since the month-end processing window was
already tight, every abend introduced delays in the month end cycle and
increased operational effort for reruns.
The Initial Approach: Mutual Exclusion with NOTWITH
To prevent these jobs from running at the same time, we
initially considered modifying the schedule so that each job was mutually
exclusive with every other job.
ESP provides the NOTWITH statement for defining mutually
exclusive jobs. This seemed like a viable solution, but implementation quickly
became cumbersome.
For example:
JOB JOB1
RUN WORKDAYS
REL JOBXXX
NOTWITH(JOB2, JOB3, JOB4, ... JOB50)
ENDJOB
JOB JOB2
RUN WORKDAYS
REL JOBYYY
NOTWITH(JOB1, JOB3, JOB4, ... JOB50)
ENDJOB
With nearly 50 jobs involved, each job needed to reference
the other 49 jobs in its NOTWITH list. Maintaining such a configuration would
have been difficult, error-prone, and far from elegant.
A Better Solution: ESP Renewable Resource
Instead of managing a large network of mutual exclusions, we
leveraged ESP's RESOURCE functionality.
The RESOURCE statement allows jobs to request resources
before submission. We defined a virtual renewable resource called MY_TABLE
with a maximum count of 1.
This effectively represented our DB2 table as a shared
resource that only one job could access at a time.
Each job was updated as follows:
JOB JOB1
RUN WORKDAYS
REL JOBXXX
RESOURCE (1,MY_TABLE)
ENDJOB
JOB JOB2
RUN WORKDAYS
REL JOBYYY
RESOURCE (1,MY_TABLE)
ENDJOB
...
JOB JOB50
RUN WORKDAYS
REL JOBZZZ
RESOURCE (1,MY_TABLE)
ENDJOB
How It Works
When ESP submits a job that requires a renewable resource,
it temporarily allocates the requested resource from the available resource
pool.
Since MY_TABLE was defined with a count of 1, only one job
could obtain the resource at any given time.
RUN WORKDAYS
REL JOBXXX
NOTWITH(JOB2, JOB3, JOB4, ... JOB50)
ENDJOB
RUN WORKDAYS
REL JOBYYY
NOTWITH(JOB1, JOB3, JOB4, ... JOB50)
ENDJOB
RUN WORKDAYS
REL JOBXXX
RESOURCE (1,MY_TABLE)
ENDJOB
RUN WORKDAYS
REL JOBYYY
RESOURCE (1,MY_TABLE)
ENDJOB
RUN WORKDAYS
REL JOBZZZ
RESOURCE (1,MY_TABLE)
ENDJOB
- If the resource is available, the job starts immediately.
- If the resource is already in use, ESP holds the job until the resource becomes available.
- Once the job completes, regardless of whether it ends successfully or abends, the resource is automatically returned to the pool.
- Eliminated table update contention between batch jobs.
- Removed the need for complex and difficult-to-maintain NOTWITH definitions.
- Simplified scheduling logic significantly.
- Reduced month-end job abends caused by concurrent updates.
