Retrieve Active Jobs info from SQL - IBM i

 

Work with Active Jobs

Work with Active Jobs (WRKACTJOB) is one most used and helpful commands on IBM i. This would help us to see what are the jobs running, status of the jobs, CPU percentage used and many more details. 

We can retrieve the same information from SQL by using table function ACTIVE_JOB_INFO. We can either retrieve information for all the jobs or retrieve a set of jobs by using optional parameters. 

With no parameters passed, ACTIVE_JOB_INFO returns all the active jobs running on the system. 

SELECT * FROM TABLE(ACTIVE_JOB_INFO()) ; 

We can make of the parameters to filter the jobs. Let's have a look at the jobs running for a specific user. 

In the above example, we are only selecting few columns and there are many other columns that we can get using this table function.

• Line - 1: Column 'JOB_NAME' returns the job name (job_number/user_name/job_name). 
• Line - 2: Column 'SUBSYSTEM' returns the name of subsystem the job is running in. 
• Line - 3: Column 'SUBSYSTEM_LIBRARY_NAME' returns the name of library in which subsystem is present. 
• Line - 4: Column 'AUTHORIZATION_NAME' returns the user profile name under which initial thread is running at this time. 
• Line - 5: Column 'JOB_TYPE' returns the type of job (i.e., Interactive, Batch or Pre-start etc). Below are the full list of job types. 
• ASJ - Autostart job.
• BCH - Batch job.
• BCI - Batch immediate job.
• EVK - Started by a procedure start request. 
• INT - Interactive job.
• M35 - Advanced 36 server job. 
• MRT - Multiple requester terminal.
• PDJ - Print driver job.
• PJ - Pre-start job.
• RDR - Spool reader job.
• SBS - Subsystem monitor job.
• SYS - System job.
• WTR - Spool writer job.
• Line - 6: Column 'FUNCTION' returns the last high level function initiated by initial thread. Below are some of the valid values. 
• CMDENT - Command entry display is being used. 
• COMMIT - Initial thread of the job is performing a commit operation. 
• DLTSPF - Spooled file is being deleted. 
• DUMP - Dump is in process. 
• JOBLOG - Job log is being produced.
• PASSTHRU - The job is a pass-through job. 
• ROLLBACK - Initial thread of the job is performing a roll back operation. 
• Line - 7: Column 'JOB_STATUS' returns the current status of the job. Below are some of the valid values. 
• CMNW - Waiting for the completion of an I/O operation to a communications device. 
• DEQW - Waiting for completion of a dequeue operation. 
• DLYW - Job is waiting due to the Delay Job (DLYJOB) command. 
• DSPW - Waiting for input from workstation display. 
• END - The job has been ended with the *IMMED option, or it's delay time has ended with *CNTRLD option. 
• HLD - Job is being held. 
• LCKW - Job waiting for a lock. 
• MSGW - Waiting for a message from MSGQ. 
• Line - 8: Column 'ELAPSED_CPU_PERCENTAGE' returns the CPU percentage used by the job.

These are just few of the columns returned by this table function. Full list of columns returned can be found here. 

• Line - 10: We are passing the optional parameter 'CURRENT_USER_LIST_FILTER'. List of up to 10 user profile names can be passed by separating with comma (,). If this parameter is passed, function would only return the list of jobs for the user profiles passed. If this parameter is not passed, passed as blanks or null value, function would return the jobs for all the users in system. 

There are few more optional parameters that can be passed. 

SUBSYSTEM_LIST_FILTER - List of up to 25 subsystem names can be passed by separating with comma(,). If this parameter is passed, function would only return the jobs running in these subsystems. 

If this parameter is not passed, passes as blanks or null value, function would return the jobs running all subsystems in the system. 

JOB_NAME_FILTER - Name of the job (unqualified) to be passed. If this parameter is passed, function would only return the jobs running with the name passed. 

Apart from the name, Below are the special values that can be specified. 

'*' - Information for the current job is to be retrieved. 

'*ALL' - Information for all jobs to be retrieved. 

'*CURRENT' - Information for all jobs with the current job name is to be retrieved. 

'*SBS' - Information for all active subsystem monitors is returned. 

'*SYS' - Information for all active system jobs is to be returned. Parameter  SUBSYSTEM_LIST_FILTER must not be passed when using this value. 

If this parameter is not passed, passed as blanks or null value, function would return the information for all the jobs running. 

DETAILED_INFO - Determines the level of information to be returned. Below are the valid values. 

'ALL' - Information for all the columns is to be returned.

'NONE' (default) - Only general information is returned for active jobs. All the columns before JOB_DESCRIPTION_LIBRARY column. Full list of columns can be found here. 

'QTEMP' - QTEMP_SIZE (Amount of storage used by objects in QTEMP) column is returned in additional to general information.

RESET_STATISTICS - YES or NO to be passed to either reset statistics or not. This is similar to 'F10=Restart statistics' in WRKACTJOB command. 

YES - If Yes is specified, statistics are reset and time that the query is used is taken as baseline. 

NO (Default) - If No is specified, statistics are not reset unless SUBSYSTEM_LIST_FILTER or JOB_NAME_FILTER are different from previous run. 

Changing the filter values will always cause the statistics to be reset irrespective of the value passed.  

In the above example, we are using 'ORDER BY' clause (Line - 12) to return the results in the descending order of Elapsed CPU Percentage. 

Similarly, We can use this function to retrieve the jobs as per the needs using the optional parameters and Where condition on the columns returned. 

Couple of examples to mention are, 

• Retrieve the list of jobs in MSGW across the system. This can be done by using JOB_STATUS column in the Where condition. 
• Retrieve the list of jobs running under a specific Job queue.This can be done by using JOB_QUEUE column in Where condition. JOB_QUEUE information would only be returned with DETAILED_INFO parameter 'ALL'.