Pages

Wednesday, March 31, 2021

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

IFS Object locks from SQL - IBM i

Or, by passing a stream file. 

IFS Objects locks from SQL - IBM i

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. 

Wednesday, March 24, 2021

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. 

Retrieve Objects attached to a journal - IBM i

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. 

Identify the journal attached to a object - IBM i

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. 

Wednesday, March 17, 2021

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

Split string into words in SQL - IBM i

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. 

Split string in SQL - IBM i

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. 

Split the string in SQL - IBM i

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. 
Split string in SQL - IBM i

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. 

Split string in SQL - IBM i

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.

Split string in SQL - IBM i

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. 

Wednesday, March 10, 2021

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. 

Retrieve System Values from SQL - IBM i

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. 

Tuesday, March 2, 2021

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. 

Popular Posts