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

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.

 

A Better Solution: ESP Renewable Resources

 

A more elegant solution was to leverage ESP's RESOURCE functionality.

 

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. 

Using a renewable resource in this manner resulted in a much cleaner, more scalable, and easier-to-maintain solution for preventing DB2 table contention.