Skip to main content

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. 

Work with Active Jobs from SQL - IBM i

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'.


If you have any Suggestions or Feedback, Please leave a comment below or use Contact Form. 
Labels:

Comments

Post a Comment

Popular posts from this blog

All about READ in RPGLE & Why we use it with SETLL/SETGT?

READ READ is one of the most used Opcodes in RPGLE. As the name suggests main purpose of this Opcode is to read a record from Database file. What are the different READ Opcodes? To list, Below are the five Opcodes.  READ - Read a Record READC - Read Next Changed Record READE - Read Equal Key Record READP - Read Prior Record READPE - Read Prior Equal Record We will see more about each of these later in this article. Before that, We will see a bit about SETLL/SETGT .  SETLL (Set Lower Limit) SETLL accepts Key Fields or Relative Record Number (RRN) as Search Arguments and positions the file at the Corresponding Record (or Next Record if exact match isn't found).  SETGT (Set Greater Than) SETGT accepts Key Fields or Relative Record Number (RRN) as Search Arguments and positions the file at the Next Record (Greater Than the Key value). Syntax: SETLL SEARCH-ARGUMENTS/KEYFIELDS FILENAME SETGT  SEARCH-ARGUMENTS/KEYFIELDS FILENAME One of the below can be passed as Search Arguments. Key Fiel
4 comments
Read more

What we need to know about CHAIN (RPGLE) & How is it different from READ?

CHAIN READ & CHAIN, These are one of the most used (& useful) Opcodes by any RPG developer. These Opcodes are used to read a record from file. So, What's the difference between CHAIN & READ?   CHAIN operation retrieves a record based on the Key specified. It's more like Retrieving Random record from a Database file based on the Key fields.  READ operation reads the record currently pointed to from a Database file. There are multiple Opcodes that start with READ and all are used to read a record but with slight difference. We will see more about different Opcodes and How they are different from each other (and CHAIN) in another article. Few differences to note.  CHAIN requires Key fields to read a record where as READ would read the record currently pointed to (SETLL or SETGT are used to point a Record).  If there are multiple records with the same Key data, CHAIN would return the same record every time. READE can be used to read all the records with the specified Ke
Post a Comment
Read more

Extract a portion of a Date/Time/Timestamp in RPGLE - IBM i

%SUBDT Extracting Year, Month, Day, Hour, Minutes, Seconds or Milli seconds of a given Date/Time/Timestamp is required most of the times.  This can be extracted easily by using %SUBDT. BIF name looks more similar to %SUBST which is used to extract a portion of string by passing from and two positions of the original string. Instead, We would need to pass a value (i.e., Date, Time or Timestamp ) and Unit (i.e., *YEARS, *MONTHS, *DAYS, *HOURS, *MINUTES, *SECONDS or *MSECONDS) to %SUBDT.  Valid unit should be passed for the type of the value passed. Below are the valid values for each type. Date - *DAYS, *MONTHS, *YEARS Time - *HOURS, *MINUTES, *SECONDS Timestamp - *DAYS, *MONTHS, *YEARS, *HOURS, *MINUTES, *SECONDS, *MSECONDS Syntax: %SUBDT(value : unit { : digits { : decpos} }) Value and Unit are the mandatory arguments.  Digits and Decimal positions are optional and can only be used with *SECONDS for Timestamp. We can either pass the full form for the unit or use the short form. Below i
2 comments
Read more