𝗣𝗿𝗲𝘃𝗲𝗻𝘁𝗶𝗻𝗴 𝗗𝗕𝟮 𝗧𝗮𝗯𝗹𝗲 𝗖𝗼𝗻𝘁𝗲𝗻𝘁𝗶𝗼𝗻 𝗕𝗲𝘁𝘄𝗲𝗲𝗻 𝗦𝗘𝗟𝗘𝗖𝗧 𝗚𝗿𝗼𝘂𝗽𝘀 𝗮𝗻𝗱 𝗨𝗽𝗱𝗮𝘁𝗲 𝗝𝗼𝗯𝘀 𝗶𝗻 𝗘𝗦𝗣

We encountered a batch processing scenario where a group of jobs (let's call them the "SELECT Group") performed read-only SELECT operations against a DB2 table. These jobs were allowed to run concurrently with each other, but they could not run at the same time as a specific update job (JOBX) that modified the same table, as doing so could result in table contention and job abend.

Initial Approach: Mutual Exclusion with NOTWITH

ESP provides the NOTWITH statement to define mutually exclusive jobs. One option was to configure every job in the SELECT Group with NOTWITH(JOBX) so they would never run concurrently with JOBX.

 

JOB JOB1

    RUN WORKDAYS

    NOTWITH(JOBX)

ENDJOB

 

JOB JOB2

    RUN WORKDAYS

    NOTWITH(JOBX)

ENDJOB

 

JOB JOB3

    RUN WORKDAYS

    NOTWITH(JOBX)

ENDJOB

 

...

...

JOB JOB99

    RUN WORKDAYS

    NOTWITH(JOBX)

ENDJOB

 

To make the exclusion fully bidirectional, JOBX would also need to specify all SELECT jobs in its NOTWITH list:

 

JOB JOBX

    RUN WORKDAYS

    NOTWITH(JOB1,JOB2,JOB3,...JOB99)

ENDJOB

 

While this approach works, it becomes difficult to maintain as the number of jobs grows.

 

Solution with ESP Renewable Resources

 

The RESOURCE statement allows jobs to reserve and release resources before execution. We defined a virtual renewable resource called MY_TABLE with a maximum count of 99.

 

The SELECT Group jobs each required 1 unit of MY_TABLE before they could run. Once a job completed, the resource unit was automatically returned to the pool.

 

JOB JOB1

    RUN WORKDAYS

    RESOURCE (1,MY_TABLE)

ENDJOB

 

JOB JOB2

    RUN WORKDAYS

    RESOURCE (1,MY_TABLE)

ENDJOB

 

JOB JOB3

    RUN WORKDAYS

    RESOURCE (1,MY_TABLE)

ENDJOB

 

...

...

JOB JOB99

    RUN WORKDAYS

    RESOURCE (1,MY_TABLE)

ENDJOB

 

To ensure exclusive access, JOBX was configured to reserve all 99 units of the resource:

 

JOB JOBX

    RUN WORKDAYS

    RESOURCE (99,MY_TABLE)

ENDJOB

 

When JOBX starts, it consumes the entire MY_TABLE resource pool, preventing any SELECT Group jobs from obtaining the single resource unit they require. Conversely, if one or more SELECT jobs are running and holding resource units, JOBX cannot acquire all 99 units and must wait.

 

This approach effectively creates a synchronization mechanism where:

• Multiple SELECT jobs can run concurrently.
• JOBX runs exclusively.
• No SELECT job can run while JOBX is running.
• No lengthy NOTWITH statements are required. 

Final Solution 

Another elegant way to handle this requirement is by using ESP ENQUEUE, which allows a job to request either shared or exclusive access to a resource without requiring the resource to be predefined. ESP automatically prevents jobs with conflicting enqueue requests from running simultaneously, similar to how z/OS enqueues operate.

In our case, we defined a logical resource named MY_TABLE. All jobs in the SELECT Group requested this resource in SHARED mode, allowing them to run concurrently with one another while accessing the table.

JOB JOB1

    RUN WORKDAYS

    ENQUEUE NAME(MY_TABLE) SHARED

ENDJOB

 

JOB JOB2

    RUN WORKDAYS

    ENQUEUE NAME(MY_TABLE) SHARED

ENDJOB

 

JOB JOB3

    RUN WORKDAYS

    ENQUEUE NAME(MY_TABLE) SHARED

ENDJOB

 

...

...

JOB JOB99

    RUN WORKDAYS

    ENQUEUE NAME(MY_TABLE) SHARED

ENDJOB

 

The Update Job (JOBX) was configured to request EXCLUSIVE ownership of the same resource:

JOB JOBX

    RUN WORKDAYS

    ENQUEUE NAME(MY_TABLE) EXCLUSIVE

ENDJOB

With this configuration:

• Multiple SELECT Group jobs can run simultaneously because they all hold a SHARED enqueue on MY_TABLE.
• JOBX cannot start while any SELECT job is running because it requires EXCLUSIVE access.
• Likewise, no SELECT job can start while JOBX holds the exclusive enqueue.
• The solution is simple, scalable, and requires minimal maintenance as SELECT jobs are added or removed.

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.