How We Eliminated DB2 table Contention Across 50 Batch Jobs Using ESP Renewable Resources

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.

• 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.

 
In essence, each job borrows the resource for the duration of its execution, ensuring exclusive access to the shared table while preventing contention.
 
The Results
 
This approach delivered several benefits:

• 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.