Check locks on IFS Objects from SQL - IBM i

IFS Object Locks

Identifying locks or references on IFS Objects become essential at times. This can be done by calling API QP0LROR (Retrieve Object References). 

The same information can be retrieved from SQL by using table function IFS_OBJECT_LOCK_INFO. 

This function accepts two parameters (Path name and Ignore errors). 

Path Name (PATH_NAME) - Path name for which the locks or references are to be retrieved. This could be a directory or stream file. This is a mandatory parameters. 

Ignore Errors (IGNORE_ERRORS) - YES or NO to be passed to determine what needs to be done when an error is encountered. This is an optional parameter and YES would be considered if no value passed. 

• YES (Default) - A warning is returned and no row would be returned incase of an error.  
• NO - An error is returned.

This function can be called to check the locks on a directory

Or, by passing a stream file. 

Optionally we can pass Ignore errors 'NO' if the error is to be returned in case of any errors (like Invalid directory or stream file). 

Below are the few important columns returned. 

• PATH_NAME - Full path name of an object.

• JOB_NAME - Job name holding the lock or reference. 

• RO_COUNT - Number of read only references for this job.

• WO_COUNT - Number of write only references for this job.

• RW_COUNT - Number of read and write references for this job. 

• XO_COUNT - Number of execute only access references for this job. 

There are many other columns returned by this function and full list of columns can be found here. 

*This function is only available since IBM i 7.3 TR7 and IBM i 7.4 TR1. 

 

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

Dictionary Merge & Update Operators in Python

Dictionaries

Dictionaries (dict) are used to store the data based on a key value. Unlike Lists and Tuples, data in the dictionaries isn't stored based on the index. And, data in the dictionaries are specified using curly brackets with the key and it's corresponding value ({key1 : value1, key2 : value2,...}. Click Here to see more about dictionaries. 

In this post, we will see more about the dictionary merge and update operators introduced in Python 3.9 along with the equivalent methods to do the same before 3.9.

 

Merge (|) Operator

Merge operator would merge the elements from both the dictionaries into a new dictionary. This wouldn't update either of these dictionaries. 

If there are any common keys between both the dictionaries only one would be present in the resulting dictionary as no duplicate keys are allowed in dictionaries.

In the above example, 

• Line - 5: merge (|) operator merges the elements from both the dictionaries (first_dictionary and second_dictionary) and adds the data to the result dictionary (new_dictionary).

      new_dictionary = first_dictionary | second_dictionary   

If there are duplicate keys in both the dictionaries, data from the second dictionary would be updated in the result dictionary.

Key value '3' has same value 'THREE', so we wouldn't know data from which dictionary the resulting data is from. Below example shows how the data is populated in the result dictionary.

This operator is same as using {**d1, **d2} for merging dictionaries. 

Let's have a look at how merging works for merging the keys alone and both keys and values. 

In the above example, 

• Line - 5: Using single asterisk before the dictionaries inside curly brackets would merge the keys from both the dictionaries and returns a set. 
• Line - 6: Using two asterisks before the dictionaries inside curly brackets would merge both the key and value and returns a dictionary. 

Update (|=) Operator

Update operator would append the elements from the dictionary on the right side to the dictionary on the left side.

By just using the merge operator, data in the dictionary can be updated as below. 

In the above example, 

• Line - 5: We are using merge operator to merge the data from the two dictionaries and assign the data to first_dictionary. 

Instead of using the merge operator, we can use update (|=) operator to do the same. 

In the above example, 

• Line - 5: We are using update (|=) operator to update the dictionary on the right side to the dictionary on the left side. And, data in the right side dictionary won't be updated. 

This is same as using dictionary update() method. 

These operators make the dictionary merge and update operations easier.

Hope the above info was useful in understanding Dictionary Merge and Update operators. 

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

Check Journaled Objects from SQL - IBM i

Journaled Objects

Journals play very important role on IBM i and are helpful to identify who has updated the data and/or to retrieve the data before the update or delete from the tables and so on. 

Journaled objects can be seen by using WRKJRNA (Work with Journal Attributes) CL command or by using API QjoRetrieveJournalInformation (Retrieve Journal Information). 

One other easier way to check the list of journaled objects is by using SQL view JOURNALED_OBJECTS. This view is present in QSYS2 and system name JRN_OBJS.

We can use this view to retrieve the objects attached to a particular journal or identify the journal associated with the particular object and so on. 

E.g.: 

Retrieve the objects attached to a journal. 

In the above query, 

• Line - 1: SELECT statement on JOURNALED_OBJECTS view. 
• Line - 2: Where condition on column JOURNAL_LIBRARY to select the records with the journal library passed. 
• Line - 3: condition on column JOURNAL_NAME to select the records with the journal name passed. 

Or, If we need to identify the journal attached to a specific object. 

In the above query,

• Line - 2: Where condition on column OBJECT_LIBRARY to select the records with objects present in the library passed. 
• Line - 3: condition on column OBJECT_NAME to select the records with the object name passed. 
• Line - 4: condition on column OBJECT_TYPE to select the records with the object type passed. 

These are just the two examples that I use most and we can query for the specific requirement from the different columns present in the view. 

Below are the full list of columns present in this view. 

• JOURNAL_LIBRARY - The name of the library that contains the journal. 

• JOURNAL_NAME - Then name of the journal.

• IASP_NUMBER - The number of ASP (Auxiliary Storage Pool) in which journal is present (1 for the system ASP).

• IASP_NAME - The name of IASP (Independent Auxiliary Storage Pool). Special value *SYSBAS for system ASP (ASP 1) and other basic ASPs (2 - 32).

• OBJECT_TYPE - Type of the object journaled. 
• *DIR - Directory
• *DTAARA - Dataarea
• *DTAQ - Data Queue
• *FILE - Database File
• *JRNRCV - Journal Receiver
• *LIB - Library
• *STMF -Stream File
• *SYMLNK - Symbolic Link

• OBJECT_LIBRARY - Name of the library in which the journaled object present. Contains null value for object types *DIR, *STMF or *SYMLNK.

• OBJECT_NAME - Name of the object journaled. Contains null value for object types *DIR, *STMF or *SYMLNK.

• FILE_TYPE - Type of the file that is journaled. Below are the valid file types and contains null value if the object journaled is not a file.
• PHYSICAL - Physical File
• LOGICAL - Logical File

• PATH_NAME - IFS path name for the IFS object journaled.  Contains null value if object types are not *DIR, *STMF or *SYMLNK.

• FILE_IDENTIFIER - Identifier associated with the IFS object. Contains null value if object types are not *DIR, *STMF or *SYMLNK.

• JOURNAL_IMAGES - Specifies the kind of images to be written to journal for this object. Below are the valid values and contains null value for journal receiver (Object type - *JRNRCV).
• *AFTER - Only after images are written to the journal.
• *BOTH - Both before and after images are written to the journal.

• OMIT_JOURNAL_ENTRY - Specifies the journal entries to be omitted from writing to journal for this object. Below are the valid values and contains null value for journal receiver (Object type - *JRNRCV).
• *NONE - No entries are omitted. 
• *OPNCLO - Open and close entries on the specified file members are omitted. 
• *OPNCLOSYN - Open, close and force entries on the specified objects are omitted. 

• INHERIT - Specifies whether new objects created within this journaled library (or directory) should inherit the journal state of the parent library (or directory). Below are the valid values and contains null value if object type is not *LIB or *DIR.
• *YES - New objects will inherit the journal state of the parent. 
• *NO - New objects will not inherit the journal state of the parent.

• REMOTE_JOURNAL_FILTER - Specifies whether the journal entries written for the objects that inherit the parent library (or directory) are eligible for remote journal filtering by object. Below are the valid values and contains null value if object type is *JRNRCV.
• *YES - Journal entries written are eligible for remote journal filtering. 
• *NO - Journal entries written aren't eligible for remote journal filtering.

*This function is only available since IBM i 7.3 TR9 and IBM i 7.4 TR3. 

 

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

Tuple Unpacking in Python

Tuple Unpacking

Tuple is a collections of data (of different data types) and are created by specifying the data (or variables) inside round brackets. We can also call this as Tuple packing. See more about working with tuples.

So, what is tuple unpacking? As the name suggests unpacking the data from a tuple into variables is called tuple unpacking.

Let's have a quick look at the creation of a tuple (packing) and unpacking of a tuple. 

In the above example, 

• Line - 2: Creating variable num_one to store a number (1).

• Line - 4: Creating variable char_one to store a string (ONE). 

• Line - 8: Creating a tuple by mentioning the variables created inside round brackets (num_one, char_one). This can also be done by directly providing the data inside round brackets (1, "ONE").

• Line - 11: Unpacking the tuple, unpacking the data in a tuple into different variables. First element in the tuple would be assigned to the first variable and second element in the tuple would be assigned to the second variable. 

• Lines - 13 & 14: Printing the data from the unpacked variables.

We have not used round brackets to do the tuple unpacking. But, this should work in the same way even if we specify the variables inside round brackets for unpacking the data from a tuple. 

In the above example, we are unpacking a tuple with two elements to two different variables. Similarly, we can do the same for any number of equal variables. 

What to do if there are more number of elements in the tuple than the variables? 

Tuple unpacking would fail with "ValueError: too many values to unpack". 

So, how to deal with these kind of issues? Well, there isn't straight forward answer to this and depends on why we are moving more number of elements (from tuple) to a less number of variables. 

If this is intended and group of tuple elements should be assigned to a single variable (as list), this can be done by using asterisk (*) before the variable. 

Let's take a look at different example to understand this better. 

In the above example, 

• Tuple has three elements.
• First element is the color. 
• Second and Third elements are the fruits in the specific color. 

So the requirement here is to create a variable called 'color' to hold the color from tuple and create a list called 'fruits' to hold the list of fruits from tuple. 

If we use the variable names color and fruits as it is, ValueError would be thrown. So, we are using asterisk (*) before 'fruits' which needs to store the fruits as a list. 

Result here would be, 

• Mapping of first variable to the first element (no asterisk for color, so would consider as one to one mapping). 
• Mapping of second variable (list) to the remaining elements in the tuple. 

There are few points to consider here.

• An empty list would be created if there is no corresponding data present in the tuple.     

• A list would be created with just one element if there is only one corresponding entry in the tuple. Unlike the the variable with the type of data (if asterisk is not used).

• A list would be created with multiple elements if there are multiple entries in the tuple. 

One thing to note here is a list variable doesn't necessarily be the second or last, this can be in any sequence. 

Let's take if we use asterisk before the color in the above example.

color would be created as a list with the first two (or more if present) elements and fruits (str) would be created with the last element. 

We can only have one starred expression in an assignment.

Let's take another example with three variables. 

In this example, 

• We are using three variables and given asterisk for the middle variable. 
• First and Last elements in the tuple are assigned to the first and last variables. 
• All other elements in the middle (from Tuple) are assigned as a list to the starred variable. 

Tuple unpacking would be helpful in many scenarios. We will have a look at how we can use tuple unpacking with dictionaries to unpack key and value from dictionary. 

Dictionary method items would return each key and value combination as a tuple. 

In the above example, for loop is repeated for every item (key and value) of a dictionary and the same would be returned as a tuple. 

Let's use tuple unpacking to unpack key and values.

Key and value are unpacked from tuple and can be used separately inside for loop. 

Hope the above info was useful in understanding Tuple unpacking in Python. 

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

Split the string in SQL - IBM i

Split String

Substring from a string can be extracted using %SUBST BIF in RPG, %SST in CL and SUBSTRING in SQL by passing from position and the numbers of characters to be extracted. 

But, if we need to split the string based on a specific delimiter and if we don't know the positions? One way to do this is by retrieving the position of a delimiter in the string using %SCAN BIF. This would require more logic to be written if the string needs to be split into multiple substrings. 

This has been made easier with the use of SPLIT table function in SQL. This function is present in SYSTOOLS library.

SPLIT table function accepts three parameters. 

• INPUT_LIST (Input List) - String(s) that needs to be split.

• DELIMITER (Delimiter) - String or character that needs to be considered as separator. Both Input List and Delimiter are mandatory parameters. 

• ESCAPE (Escape) - A character string of length '1' that is to be used as escape character. Delimiter followed by escape character would not be considered as separator. This is an optional parameter.

Let's have a quick look at the simple example to understand this better. 

E.g.:

Split the string into words (consider blank space as delimiter). 

In the above query, we are only passing the two mandatory parameters Input list and Delimiter. 

This table function returns the below two columns. 

• ORDINAL_POSITION (Ordinal Position) -  Position of the substring in the result. Starts from 1.
• ELEMENT (Element) - Substring that is split. 

Result of the above query would be as below. 

Above query has split all the words followed by space and returned as a separate row. 

In case if we need some of the delimiters to be skipped followed by a specific (escape) character, we can pass the optional parameter ESCAPE with the corresponding character. 

In the above example, 

• We are passing back slash (\) as escape character. 
• Table function would ignore the delimiter followed by the escape character and would not split the the string. 
• Escape character wouldn't present in the substring split. 

In both these examples, we are passing the string exclusively which may not always be the case if we need to use this function in the procedures or programs. 

We can use pass the columns from the other tables in query and specify the delimiter. 

Let's have a look at another simple example. 

In the above query,

• Line - 1: TESTTABLE is a table with just one character column (CHARFIELD). 

• Line - 2: Table function SPLIT from SYSTOOLS library. 

• Line - 3: First parameter INPUT_LIST for SPLIT table function. We are passing one column CHARFIELD from the table TESTTABLE. We are using TRIM so any leading or trailing blanks wouldn't be considered.

• Line - 4: Second parameter DELIMITER for SPLIT table function. We are passing blanks, so string would be split into words like in previous example.

• We aren't passing the third and optional parameter ESCAPE. This can be passed if required. 

Let's have a look at the result.

In the result, 

• First column CHARFIELD is from the table TESTTABLE, original data in the table before it is split. 
• Second column ORDINAL_POSITION returns the position of the substring. 
• Third Column ELEMENT returns the substring. 

There would be multiple rows of substrings for each row in the original table. 

*This function is only available since IBM i 7.3 TR6 and higher. 

 

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

Using if-else statements in List Comprehension

List Comprehension

List comprehension is one of the ways of creating list. List would be created as a result of some operation on a range, set or list based on some condition (optional). Click Here to see more about list comprehension. 

In this post, we will see how to use if - else statements in list comprehension. 

Let's have a quick look at using if statement alone before we see if - else statements. 

E.g.: 

Create a list of odd numbers less than 10. 

odd_numbers = [number for number in range(10) if number % 2 != 0]

In the above example, We are using if statement towards the end for identifying the odd numbers. 

We are only considering the odd numbers using the if statement and leaving the others. But if we have to to a different operation for even numbers and different operation for odd numbers, we should be using if-else statements. 

if-else statements in List Comprehension

So, How do we use else statement in list comprehension? Is it just adding else statement after if condition? 

Syntax wise it's a little different if we need to use both if and else statements. 

Let's have a look at a simple example to understand this better. 

E.g.: 

Create a list with square of even numbers between 0 and 9 and just add "ODD" for every odd number between 0 and 9.

This requires both if and else statements.

result_list = 

[number * number if number % 2 == 0 else "ODD" for number in range(10)]

Let's breakdown the above statement into four parts. 

number * number indicates the expression that needs to be run if the condition is true and the resulting value is to be added to the list. This can be any expression as per the requirement. 
if number % 2 == 0 indicates the required if condition.
else "ODD" contains both the else statement and the value that needs to be added if the condition mentioned is False. Value here can be any expression as required. For example, We can use number * number if we need to calculate the square of odd number.
for number in range(10) indicates the for loop over an iterable. 

So, syntax wise if - else statements in list comprehension would look like below.

[expression if condition else expression for element in iterable]

Key difference between using is if and if-else is the placement of these statements.
if statement is to be used at the end (i.e., after for) if no else statement is required. 
if - else statements are to be used at the beginning (i.e., before for) if else statement is required. 

One thing to note is, List comprehension looks to be the easiest way of creating list. But, this can get more complicated and difficult to read and understand when we use it with multiple if (and else) and for statements. 



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

Retrieve System Values from SQL - IBM i

System Values

System values contain the control information related to the IBM i OS or parts of OS. 

System values are used to control or define the way working environment is setup on the system. For example, setting up time zone, default system library list, number of jobs allowed and so on...

System values can be accessed either from IBM Navigator for i or by using DSPSYSVAL (Display System Value) command. And, the same can be accessed from a program either by using RTVSYSVAL (Retrieve System Value) CL command or QWCRSVAL (Retrieve System Values) API. 

One other way of accessing this information from SQL is by using view 'SYSTEM_VALUE_INFO' present in QSYS2 library (System name for this view is SYSVALINFO).

SYSTEM_VALUE_INFO view has got the below three columns.

• SYSTEM_VALUE_NAME - Name of the system value
• CURRENT_NUMERIC_VALUE - System value, if numeric. if not, contains NULL. 
• CURRENT_CHARACTER_VALUE - System value, if character. if not, contains NULL.

We can use these columns to query the system value we are looking for (like any other SELECT query). 

For example, let's have a look at the list of system values contain information related to date. 

In the above query, 

• We are using the SELECT query on view SYSTEM_VALUE_INFO. 
• WHERE condition on SYSTEM_VALUE_NAME column to check the values containing 'DAT'. 

All the values returned are in character format, so system value is returned in the column 'CURRENT_CHARACTER_VALUE' and column 'CURRENT_NUMERIC_VALUE' is NULL. 

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

If Condition in Python

If Condition

Conditions are nothing but statements used to check if a specific action is True or False. 

In this post, we will see how If condition works in Python and how to use if, elif (Else-If) and else statements. 

Syntax:

if some_condition:

    // code to be executed

elif another_condition:

    // code to be executed

elif another_condition_2:

    // code to be executed

.

.

.

else:

    // code to be executed

Let's breakdown the syntax of into three statements and see each of the statement in detail along with few examples.

• if
• elif
• else

if statement

If statement is used to verify if a specific condition is True and the block of code under the if statement would only be executed if the condition is True. 

if some_condition:

    // code to be executed

'some_condition' here needs to be replaced with the actual condition to be used in the program and 'code to be executed' needs to be replaced with what needs to be executed when the condition is satisfied. 

If the condition is not satisfied (In other words is False), code under the if condition would not be executed. 

E.g.: 

Check if a number is even and print "Even Number" if True. 

In the above example, 

• Line - 5: We are declaring the variable 'number' with the value 10 (even number).
• Line - 6: We are using if statement to check if the result of number divided by 2 (modulus) is zero.
• Here we are using Relational operator '==' to check if 'number % 2' is equal to '0'. Have a quick look at the different Relational operators.
• Line - 7: print statement to print "Even Number". This statement would only be executed if the number is even. 

If we are passing the number '11' instead of 10, modulus of 11 divided by 2 is '1' which is not equal to zero. 

In this case code under if condition is not executed and no output would be printed. else statement would be helpful in these cases. 

else statement

else statement is used when we need to execute set of statements when the condition of the if statement becomes False. 

if some_condition:

    // code to be executed

else:

    // code to be executed

code written under the else statement would only be executed if the condition on the if statement is False. 

E.g.:

Let's consider the same example of checking if the number is even. In the previous example, we aren't printing anything if the number passed is odd number.

In the above example, 

• Line - 6: We are declaring the variable 'number' with the value 11 (odd number).
• Line - 7: We are using if statement to check if the result of number divided by 2 (modulus) is zero.
• Here we are using Relational operator '==' to check if 'number % 2' (modulus) is equal to '0'. 
• Modulus of 10 divided by 2 is '1', so condition is False.
• Line - 8: print statement would not be executed as the condition is False. This statement would only be executed if the number is even.
• Line - 9: else statement indicate the code to be executed if the condition is False. Following code under else would be executed.
• Line - 10: print statement to print "Odd Number"

*Code under if or else would be identified based on indentation. 

This works perfectly fine if there is only one condition to be checked. However, If there are multiple conditions to be checked, we can use elif statement. 

elif statement

elif (Else If) statement is used when we have another (one or more) condition(s) to be verified before else statement if the condition on the if statement is false. We can have more than one elif statements. 

if some_condition:

    // code to be executed

elif another_condition:

    // code to be executed

elif another_condition_2:

    // code to be executed

.

.

.

else:

    // code to be executed

First elif statement would only be executed if the condition on the if statement is false, Second elif statement would only be executed if the condition on the previous elif statement is false and so on, else statement would only be executed if none of the if or elif statements are true.

Let's have a look at simple example to understand if-elif-else statements better. 

E.g.: 

Identify the color and print the name of color. 

In the above example, 

• Line - 9: We are declaring the variable 'color' with value 'Green'.

• Line - 11: if statement with condition on color to check if 'White'. 
• If True, code under the if statement would be executed (Line - 12). In this example result is False, so code under this statement would not be executed.
• If False, condition on the next elif statement would be checked. 

• Line - 14: elif statement with condition on color to check if 'Black'.
• If True, code under the elif statement would be executed (Line - 15). In this example result is False, so code under this statement would not be executed. 
• If False, condition on the next elif statement would be checked. 

• Line - 17: another elif statement with condition on color to check if 'Green'.
• If True, code under the elif statement would be executed (Line - 18). 
• If False, code under the else statement would be executed. In this example result is True, so code under else statement would not be executed. 

• Line - 20: else statement would only be executed if none of the above if and elif statements are True. 

Nested if statements

Nested if statements are having a if statement with in another if statement. 

There is no limit on how many levels of nested if statements can be used. But, it is advised not to use too many nested if statements to make the program simple and easy to understand. 

if some_condition:

    // code to be executed

    if nested_condition:

        // code to be executed

        .

        .

        .

    else:

        // code to be executed

    .

    .

    .

else:

    // code to be executed

Let's have a look at the simple example to understand this better. 

E.g.: 

Check if a number is between 0 and 9, If yes, check if the number is between 0 and 4.

In the above example,

• Line - 3: We are declaring the variable 'number' with value '4'. 

• Line - 6: if statement to check if the number is between 0 and 9. 
• range(10) would return the numbers from 0 to 9 (both inclusive).

• Line - 8: if statement with in the previous if statement (nested) to check if the number is between 0 and 4.
• This if statement would only be executed if the condition on previous if statement is True. 

• Line - 9: print statement would print "Number is between 0 and 4".
• This would only be executed  the condition on the if statement is True (Line - 8).

• Line - 11: else statement corresponding to the nested if statement.
• print statement to print "Number is between 5 and 9" (Line - 12) under else statement. This would only be executed if the condition on corresponding if statement is False.

• Line 14: else statement corresponding the original if statement.
• print statement to print "Number is greater than or equal to 10" (Line - 15) under else statement. This would be executed if the condition on corresponding if statement is False.

pass statement

pass statement works more like a placeholder where some code is mandatory by syntax and no logic needs to be executed. 

In the above example, No logic needs to be executed if the number is even. Or, In other words, it is not yet finalized on what to be done. 

In these cases, we can use pass statement as placeholder and continue with the program. 

This can be replaced later if required or leave it as is if required. 

Hope the above was a bit of help to understand the If conditions in Python. 

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

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

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