New SQL procedure to end multiple jobs

As part of the latest Technology Refresh, IBM i 7.5 TR5 and IBM i 7.4 TR11, comes a new SQL procedure easily end multiple jobs in one statement. This procedure is called END_JOBS, and it is found in the SYSTOOLS library.

This procedure has ten parameters, most of them will be familiar you if you know the End Job command, ENDJOB. The parameters are:

• JOB_NAME_FILTER:  The unqualified job name, what I called the "short job name". If not given the default is *ALL.
• CURRENT_USER_LIST_FILTER:  Up to ten user profiles, separated by a comma. The default is for all users.
• SUBSYSTEM_LIST_FILTER:  Up to 25 subsystem names, separated by a comma. If not given them all subsystems are considered
• END_OPTION:  Like the OPTION parameter in the ENDJOB command. The allowed values are CONTROLLED or IMMEDIATE. Controlled is the default.
• END_CONTROLLED_DELAY:  Like the DELAY parameter of ENDJOB. If the END_OPTION in controlled then this is the number of seconds it waits before it ends the job.
• DELETE_SPOOLED_FILES:  Like the SPLFILE parameter of ENDJOB. NO do not delete the spool files there were generated by the job. YES do delete all the spool files are deleted. Default is No.
• MAXIMUM_JOBLOG_ENTRIES:  Like the LOGLMT parameter of ENDJOB. *NOMAX all messages are written to the joblog. *SAME joblog's logging limit does not change. Or a number for the maximum number of messages written to the joblog, zero indicates that no job log should be generated. Default is Same.
• JOB_END_MAXIMUM:  The maximum number of jobs to end. The default is that there is not maximum.
• JOB_END_ITERATION_COUNT:  The maximum jobs to end in a single iteration. Default is 100.
• JOB_END_ITERATION_DELAY:  The number of seconds to delay between end job iterations. Default is 60 seconds.

Before I do anything I need some jobs that I can end. It just so happens I have some in the QBATCH subsystem. I can list them using the ACTIVE_JOB_INFO SQL table function:

SELECT JOB_NAME FROM TABLE(QSYS2.ACTIVE_JOB_INFO( SUBSYSTEM_LIST_FILTER => 'QBATCH'))

Which returns:

JOB_NAME -------------------- 126562/QSYS/QBATCH 128663/SIMON/TESTJOB 128664/SIMON/TESTJOB 128665/SIMON/TESTJOB 128666/SIMON/TESTJOB 128667/SIMON/TESTJOB

If I just wanted to end one job I would use the ENDJOB command:

End Job (ENDJOB) Type choices, press Enter. Job name . . . . . . . . . . . . JOB User . . . . . . . . . . . . . Number . . . . . . . . . . . . How to end . . . . . . . . . . . OPTION *CNTRLD Controlled end delay time . . . DELAY 30 Delete spooled files . . . . . . SPLFILE *NO Maximum log entries . . . . . . LOGLMT *SAME Additional interactive jobs . . ADLINTJOBS *NONE

The disadvantage of the command is I can only end one job at a time:

ENDJOB JOB(128673/SIMON/TESTJOB) OPTION(*IMMED) LOGLMT(0)

Previously, it would be quicker to use the following SQL statement:

01 SELECT JOB_NAME, 02 QSYS2.QCMDEXC('ENDJOB JOB(' || JOB_NAME || ') OPTION(*IMMED) LOGLMT(0)') 03 FROM TABLE(QSYS2.ACTIVE_JOB_INFO( 04 SUBSYSTEM_LIST_FILTER => 'QBATCH', 05 CURRENT_USER_LIST_FILTER => 'SIMON'))

Line 1: I want to show the job name in the results.

Line 2: Here I am using the QCMDEXC scalar function to execute the ENDJOB command, using the job name column from ACTIVE_JOB_INFO.

Lines 4 and 5: I only want to end the jobs in the QBATCH subsystem that are mine.

When I execute the statement the following results are displayed:

JOB_NAME 00002 --------------------- ------ 128663/RPGPGM/TESTJOB 1 128664/RPGPGM/TESTJOB 1 128665/RPGPGM/TESTJOB 1 128666/RPGPGM/TESTJOB 1 128667/RPGPGM/TESTJOB 1

The "1" in the second columns denotes that the command in the QCMDEXC scalar function completed successfully, in other words the job was ended.

The END_JOBS gives me a simpler interface to end jobs. If I want to end all jobs on the system called "TESTJOB" I would use the following statement:

01 CALL SYSTOOLS.END_JOBS( 02 JOB_NAME_FILTER => 'TESTJOB', 03 END_OPTION => 'IMMEDIATE', 04 MAXIMUM_JOBLOG_ENTRIES => 0)

The above will end all jobs where the job name contains "TESTJOB" regardless of which subsystem the job is in, and user profile is running the job. No information is returned regardless of whether I end any jobs or not.

I have to admit the above statement scares me little as there is a danger I end more jobs than I expected to, as there may be other jobs with the same name in different subsystems.

Using the statement below I can search any jobs "TESTJOB" regardless of subsystem:

01 SELECT SUBSYSTEM,JOB_NAME 02 FROM TABLE(QSYS2.ACTIVE_JOB_INFO( 03 JOB_NAME_FILTER => 'TESTJOB'))

Which returns:

SUBSYSTEM JOB_NAME --------- -------------------- QBATCH 128860/SIMON/TESTJOB QBATCH 128861/SIMON/TESTJOB QBATCH 128862/SIMON/TESTJOB QBATCH 128863/SIMON/TESTJOB QBATCH 128864/SIMON/TESTJOB QSPL 128865/SIMON/TESTJOB

Notice that the last result is in the QSPL subsystem, all the others are in QBATCH.

I changed the previous END_JOBS statement to be:

01 CALL SYSTOOLS.END_JOBS( 02 JOB_NAME_FILTER => 'TESTJOB', 03 SUBSYSTEM_LIST_FILTER => 'QBATCH', 04 END_OPTION => 'IMMEDIATE', 05 MAXIMUM_JOBLOG_ENTRIES => 0)

Line 3: I added a selection to only end the jobs in the QBATCH subsystem.

After executing the above END_JOBS statement I executed the previous ACTIVE_JOB_INFO and it returned:

SUBSYSTEM JOB_NAME --------- -------------------- QSPL 128865/SIMON/TESTJOB

All the TESTJOBS in QBATCH have been ended.

Here is another scenario: I have jobs with the same job name, in the same subsystem, but belonging to different users. Executing the previous ACTIVE_JOB_INFO statements I have returned:

SUBSYSTEM JOB_NAME --------- -------------------- QBATCH 128880/SIMON1/TESTJOB QBATCH 128885/SIMON/TESTJOB QBATCH 128886/SIMON/TESTJOB QBATCH 128887/SIMON/TESTJOB QBATCH 128888/SIMON/TESTJOB QBATCH 128889/SIMON/TESTJOB

Notice that the first job belongs to the user SIMON1, the others all belong to SIMON.

Another change to the END_JOBS statement:

01 CALL SYSTOOLS.END_JOBS( 02 JOB_NAME_FILTER => 'TESTJOB', 03 SUBSYSTEM_LIST_FILTER => 'QBATCH', 04 CURRENT_USER_LIST_FILTER => 'SIMON', 05 END_OPTION => 'IMMEDIATE', 06 MAXIMUM_JOBLOG_ENTRIES => 0)

Line 4: This is how I select that only the jobs belonging to the user profile "SIMON".

I executed the END_JOBS statement, and then ran the ACTIVE_JOB_INFO statement again to give me the following results:

SUBSYSTEM JOB_NAME --------- -------------------- QBATCH 128880/SIMON1/TESTJOB

The job belonging to SIMON1 was not ended.

I can end jobs for more than one user profile using the CURRENT_USER_LIST_FILTER parameter.

These are the active jobs in QBATCH:

SUBSYSTEM JOB_NAME --------- --------------------- QBATCH 128880/SIMON1/TESTJOB QBATCH 128896/SIMON/TESTJOB QBATCH 128897/SIMON/TESTJOB QBATCH 128898/SIMON/TESTJOB QBATCH 128899/SIMON/TESTJOB QBATCH 128900/SIMON/TESTJOB

I can then modify my last END_JOB statement:

01 CALL SYSTOOLS.END_JOBS( 02 JOB_NAME_FILTER => 'TESTJOB', 03 SUBSYSTEM_LIST_FILTER => 'QBATCH', 04 CURRENT_USER_LIST_FILTER => 'SIMON,SIMON1', 05 END_OPTION => 'IMMEDIATE', 06 MAXIMUM_JOBLOG_ENTRIES => 0)

When executed all the jobs for both users were ended:

SUBSYSTEM JOB_NAME --------- ---------------------

I do like this. I can see myself using it when a number of jobs all need to be ended at once quickly.

 

You can learn more about the END_JOBS SQL procedure from the IBM website here.

 

This article was written for IBM i 7.5 TR5 and 7.4 TR11.