Restrict Batch Query Jobs to Specific Time Limits

 

Problem Statement:

How to restrict batch query jobs to a specified time limit so they are ended if they reach that limit?

Use the CPUTIME parameter of the Job CLASS to limit the time the batch job can execute.

The QQRYTIMLMT system value is used to prevent a query from starting if it's total time of execution exceeds the value estimated by the AS/400 Query Optimizer. If the estimate is incorrect for any reason and the job is permitted to execute this value has no effect on ending the job. The CPUTIME parameter of the Job CLASS is used by OS/400 as the maximum time limit that any executing job is allowed to run. If a job consists of multiple routing steps then each routing step is allowed to use this amount of CPU time.

Follow these steps to restrict batch query jobs to a maximum amount of CPU processing time.

1. Create a new CLASS using the following command and Specify the desired CPUTIME limit in the range of 1 to 9,999,999 milliseconds.

CRTCLS CLS(mylib/newcls) RUNPTY(20) TIMESLICE(500) CPUTIME(#######) 
CRTCLS CLS(mylib/newcls) RUNPTY(50) TIMESLICE(5000) PURGE(*NO) DFTWAIT(120) CPUTIME(#######)

Note: Except for the CPUTIME value, the first example will create a class that is a duplicate of the one normally used for query sessions (QGPL/QWCPCSUP). The second will create a class that is a duplicate of the QBATCH class in QSYS.

2. Identify a subsystem and job queue where you want the query jobs to run. Insure the job queue has a job queue entry in the subsystem.
   1. DSPSBSD library/sbs description Opt. 6 - Job queue entries. The SCTCP job queue in the subsystem where Showcase Strategy normally runs may be used. A job queue in the QBATCH subsystem may also be considered since the changes being made are to apply to batch queries only.
   2. Use ADDJOBQE if necessary to add the job queue to the subsystem description. ADDJOBQE SBSD(mylib/sbsd) JOBQ(mylib/newjobq) MAXACT(*NOMAX) SEQNBR(##) Maximum active jobs (MAXACT) may be set to a specific value.
      Note: The sequence number must be unique. The system processes all jobs from job queues with the lowest sequence numbers before moving to the next job queue entry until all jobs are processed or the subsystem maximum job limit is reached. This command can be executed while the subsystem is active.
       
3. Create a new job description that will use the selected job queue. This new job description will specify it's own unique routing data to match a routing entry that will added to the subsystem in the next step. The new CLASS will be associated with the job by means of this routing entry.
   Use DSPSBSD Opt. 7 - Routing Entries and review the Compare Values to insure the routing data selected does not exist in the subsystem.

CRTJOBD JOBD(mylib/newjobd) JOBQ(mylib/newjobq) RTGDTA('batch query')

Other parameters for the job description can be found by displaying job description SCTCPSRV in the Showcase server library. Copying the SCTCPSRV job description and then changing the job queue and routing data may be the easiest way to create the new job description.

4. To add a routing entry to the subsystem use the following command:

ADDRTGE SBSD(mylib/sbsd) SEQNBR(###) CMPVAL('batch query') PGM(QSYS/QCMD) CLS(mylib/newcls)

Notes:

• The SEQNBR must be a unique identifier so it may be necessary to use DSPSBSD and Opt. 7 - Routing Entries to locate an unused sequence number.
• The sequence number position is insignificant if all routing data is unique.
• Entries are searched in order so in cases of duplicate routing data only the first entry will be processed.
• This command can also be entered while the subsystem is active.

5. The final step to limiting Query batch jobs to set time limit involves using the Warehouse Manager Client to change the Query user profile(s) to use the new job description whenever they run batch queries
   1. Sign on to Warehouse Manager Client.
   2. Under Applications - right click on Query, select Resource Settings
   3. In the Resource Setting dialog add the user profile(s) if not already present. This can be individual or group profiles. If all users are to be limited then the *PUBLIC profile can be used.
   4. Right-click on the profile(s), select Edit and select the Batch tab.
   5. In the Batch Job Information section, specify the new job description and library. Be sure to click on Validate then OK and then you must click on the AS/400 icon to update the server. The batch query users should now run under the new job description which will direct them to the routing entry that has a CLASS with a CPUTIME limit set to the restricted value. If the batch query reaches that time limit the operating system on the AS/400 will terminate the job with a CPC1218 message.

Note: These changes do not affect queries submitted using Warehouse Builder.

Ref#: 1475998