Limit running number of jobs in a batch/ folder.

[Puneet Kalra]

Our ETL process, named ETL_PROCESS_DAILY_STG, comprises 40 jobs that operate independently, allowing all of them to run simultaneously at a scheduled time. However, running all 40 jobs concurrently poses a significant load on our database. To address this, we aim to limit the concurrent execution to a maximum of 10 jobs within a batch or folder. Could you please provide a concise example illustrating how we can implement this restriction?.

PS: We have tried doing small POC for this by creating a named resource of type System and assigning the requested number to 2 (for POC) and assigning this resource under resources in the batch, however this solution is not working.

If you could provide small example that would be great.

[Ronald Jeninga]

Hi,

basically you are on the right track.

It is all about the visibility of the resource, and I think this is where you go wrong.

If you define a "defined resource" within a batch, this resource is visible for all jobs below that batch.

If you now submit a bunch of those batches, all jobs will be able to allocate a resource because the batch provides it. Hence all jobs will run concurrently.

What you need is a single instance of the resource that is visible to all jobs.

The easiest is to create the resource in scope GLOBAL. No matter which jobserver executes a job, the job will try to allocate from the resource in GLOBAL.

Each job that participates needs to request the resource. In my example I've created a footprint and use that. (A footprint is basically a predefined set of system resource requirements).

Now to my example:

/* 1 */ 

create or alter named resource RESOURCE.'LOADCONTROL' 

with 

     group = 'ADMIN', 

     usage = CATEGORY;

 /* 2 */ 

create or alter named resource RESOURCE.'LOADCONTROL'.'DBPROCESS' 

with 

     group = 'ADMIN', 

     usage = SYSTEM;

 /* 3 */ 

create or alter footprint 'DBPROCESS' 

with 

     resources = ( RESOURCE.'LOADCONTROL'.'DBPROCESS' NOKEEP amount = 1 );

 /* 4 */ 

create or alter folder SYSTEM.'LOAD_CONTROL' 

with 

     group = 'ADMIN';

 /* 5 */ 

create or alter job definition SYSTEM.'LOAD_CONTROL'.'JOB1' 

with 

     environment = 'SERVER@LOCALHOST', 

     errlog = '${JOBID}.log' NOTRUNC, 

     footprint = 'DBPROCESS', 

     group = 'ADMIN',

     logfile = '${JOBID}.log' NOTRUNC,

     NOMASTER, 

     profile = 'STANDARD', 

     run program = 'sleep 30',

     type = JOB, 

     workdir = none; 

 /* 6 */ 

create or alter job definition SYSTEM.'LOAD_CONTROL'.'JOB2' 

with 

     environment = 'SERVER@LOCALHOST', 

     errlog = '${JOBID}.log' NOTRUNC, 

     footprint = 'DBPROCESS', 

     group = 'ADMIN',

     logfile = '${JOBID}.log' NOTRUNC,

     NOMASTER, 

     profile = 'STANDARD', 

     run program = 'sleep 30',

     type = JOB, 

     workdir = none; 

/* 7 */ 

create or alter job definition SYSTEM.'LOAD_CONTROL'.'JOB3' 

with 

     environment = 'SERVER@LOCALHOST', 

     errlog = '${JOBID}.log' NOTRUNC, 

     footprint = 'DBPROCESS', 

     group = 'ADMIN',

     logfile = '${JOBID}.log' NOTRUNC,

     NOMASTER, 

     profile = 'STANDARD', 

     run program = 'sleep 30',

     type = JOB, 

     workdir = none; 

/* 8 */ 

create or alter job definition SYSTEM.'LOAD_CONTROL'.'JOB4' 

with 

     environment = 'SERVER@LOCALHOST', 

     errlog = '${JOBID}.log' NOTRUNC, 

     footprint = 'DBPROCESS', 

     group = 'ADMIN',

     logfile = '${JOBID}.log' NOTRUNC,

     NOMASTER, 

     profile = 'STANDARD', 

     run program = 'sleep 30',

     type = JOB, 

     workdir = none; 

/* 9 */ 

create or alter job definition SYSTEM.'LOAD_CONTROL'.'LOAD_CONTROL' 

with 

     children = none, 

     group = 'ADMIN', 

     MASTER, 

     profile = 'STANDARD', 

     NOSUSPEND, 

     type = BATCH;

/* 10 */ 

alter job definition SYSTEM.'LOAD_CONTROL'.'LOAD_CONTROL' 

add or alter children = ( 

     SYSTEM.'LOAD_CONTROL'.'JOB1' 

     alias = none 

     static , 

     SYSTEM.'LOAD_CONTROL'.'JOB2'

     alias = none 

     static , 

     SYSTEM.'LOAD_CONTROL'.'JOB3' 

     alias = none

    static, 

    SYSTEM.'LOAD_CONTROL'.'JOB4' 

     alias = none

    static  );

/* 11 */

create or alter resource RESOURCE.'LOADCONTROL'.'DBPROCESS' in GLOBAL 

with 

     amount = 2, 

     group = 'ADMIN', 

     online, 

     requestable amount = 1;

This example is a batch with 4 children. The maximum number of jobs that can run concurrently is limited to 2.

And you'll notice that only 2 jobs will run at the same time, even if you submit multiple batches.

You can change the amount of the resource while jobs are running and you'll find that if you increase the amount, more jobs will be started, and if you decrease the amount, jobs will continue to run but no new jobs will be started for a while.

You can even see that the free amount of the resource can get negative and first after it gets positive new jobs will be started.

The amount is just an abstract number. You could also have an amount of 20 and have your jobs allocate 10 at a time.

HTH, but feel free to ask if you have more questions.

Ronald