Pages

Tuesday, January 26, 2021

RPG's new Built-in Function %LIST and New Operation Code FOR-EACH - IBM i

RPGLE

RPG is the primary programming language for development on IBM i. And, IBM continues to provide enhancements to RPG. 

In this post, we will see couple of recent additions to RPG. 
  • Built-in Function %LIST
  • Operation Code FOR-EACH
Click Here to read my previous post on new BIF %RANGE and Operator IN. 

%LIST

New Built-in Function %LIST provides an easy way to populate the data into an array. %LIST accepts list of values and returns a temporary array.

%LIST can be directly used where arrays are allowed. This provides an option to developer to use %LIST instead of having to create an array where ever possible. There are some exceptions to this, %LIST cannot be used with SORTA (Sort an Array), %LOOKUP and %SUBARR. 

Syntax:

%LIST(item1 : item2 : item3 : ...) 

Below are some of the important points to note while working with %LIST. 
  • Minimum of one operand to be passed. 
  • All Operands must be of same type (like, %LIST with only numeric values or %LIST with only Character values. Not both the types at the same time). 
  • Arrays or Data structures cannot be passed as operands.
  • If assigning data to an array, Defined array should be of same data type as the data passed. 
%LIST can be used in the new FOR-EACH loop or in IF conditions using new Operator IN. 

FOR-EACH (For Each)

New Operation code FOR-EACH provides an easier way to iterate through an array, Subarray (using %SUBARR) or temporary array (using %LIST).

FOR-EACH Opcode begins a loop and ENDFOR to end the loop. 

Syntax:

FOR-EACH(H) item IN Array(or %SUBARR or %LIST)

Below are some of the important points to note while working with FOR-EACH.
  • Extender 'H' can be used to half adjust the numeric values.
  • First Operand cannot be an array. 
  • Data type of the first operand should match with the data type of an array, sub array or list. 
  • First operand can be a data structure. But, second operand should be related to the first operand by using LIKEDS.
We will see an example using both %LIST and FOR-EACH to understand better. 

New BIF %LIST and Opcode FOR-EACH in RPGLE - IBM i

In the above example, 
  • Line - 8: %LIST with the list of colours (character) returns a temporary array and assigns the data to the array already created. 
  • Line - 13: FOR-EACH Opcode would loop through the array 'wFavoriteColours' (second operand). Loop is repeated for each element in the array and data can be accessed using 'wColour' (first operand) inside the loop. Variable used in the first operand needs to be defined already. 
  • Line - 17: FOR-EACH Opcode would loop through the list of values provided using %LIST (with out having to define array). 
  • Line - 22: FOR-EACH Opcode would loop through the list of numeric values provided using %LIST. 
  • Line - 28: FOR-EACH Opcode would loop through the part of the array using %SUBARR. 
We are only using arrays in the above example. Below is a simple example showing how FOR-EACH loop can be used with Data structures. 

FOR-EACH loop with Data structures in RPGLE - IBM i


Hope the above info was a bit of help to understand %LIST and FOR-EACH better. 


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

Tuesday, January 19, 2021

RPG's new Built-In Function %RANGE and new Operator IN - IBM i

RPGLE

Keeping the debate aside on whether the RPG is a dead programming language, Most of the recent surveys showed RPG is the primary program language for development on IBM i.

And, IBM continues to provide enhancements to RPG. In this post, we will see couple of recent additions to RPG. 
  • Built-in Function %RANGE
  • Operator IN

%RANGE

In SQL, we often use BETWEEN - AND in WHERE condition to check if a value is present in a particular range (specified using BETWEEN - AND). 

%RANGE BIF (Built-In Function) works in a similar way and used to check if a value is present in a particular range. This can be used to compare the data of any data type (and data we are comparing should be of same data type as the data mentioned in the RANGE)

Syntax:

%RANGE(lower-limit : upper-limit) ;

%RANGE accepts two arguments - Lower limit and Upper limit. Both Lower limit and Upper limit would be considered. 

%RANGE doesn't return any value and should always be used with operator 'IN'.

IN

IN operator is used to check if a value is present in the list of values provided. The list here can be, 
  • An array.
  • Range of values specified in %RANGE.
  • List of values specified using % LIST.
IN operator returns '1' if the value provided is present in the list and '0' if the value isn't present. 

Syntax:

x IN y

This can be used with in IF condition or DOW loop.

We will see an example using both %RANGE and IN to understand better. 

RPG's new BIF %RANGE and new Operator IN

In the above example, 
  • Line - 6: 'IN' operator is used to verify if Packed decimal value 'wNumber' is present in the range between 1 and 15. 
  • Line - 10: 'IN' operator is used to verify if Character value 'wString' is present in the range between 'AA' and 'AB'. 
    • Character value cannot be compared by specifying decimal values in %RANGE and vice-versa.
  • Line - 14: 'IN' operator is used to check the condition on DOW loop. 

Hope the above info was a bit of help to understand %RANGE and IN better. 


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

Tuesday, January 12, 2021

Display Journal from SQL - IBM i

Display Journal

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. 

Data from the journals can be retrieved by using DSPJRN command. One other way of retrieving the data from journals is by using SQL table function DISPLAY_JOURNAL. Data returned from this function is similar to the data returned by DSPJRN command. 

We will see few examples to see how DISPLAY_JOURNAL works. 

Display Journal on IBM i


In the above example, 
  • We are passing two mandatory parameters Journal Name and Library.
    • 'JOURNAL_NAME' - Name of the Journal.
    • 'JOURNAL_LIBRARY' - Name of the Library Journal is present in.
  • Below are the few columns we are retrieving from Journal. This function returns much more data and full list can be found in this link on IBM Knowledge center. 
    • 'ENTRY_TIMESTAMP' - Time when the Journal entry was captured (E.g.: Time when the data was written, updated or deleted).
    • 'JOURNAL_ENTRY_TYPE' - Returns the type of Journal entry.
    • 'COUNT_OR_RRN' - Returns the RRN of the data in the Physical file.
    • 'OBJECT' - Returns the Object details. Object Name and Library Name (and Member Name for files).
    • 'OBJECT_TYPE' - Returns the Object type. 
    • 'ENTRY_DATA' - Data Captured in the Journal. This field of type 'BLOB' (Binary Large OBject).
Display Journal on IBM i

Looking at the above result, 'ENTRY_DATA' is displayed as HEX values. This needs to be converted to the required data type. 

In the above example, we are only passing Journal Name and Library as the parameters and query returns all the data that is present in the current journal receiver. This is fine because I am using test journal. 

However, this becomes extremely difficult in any real applications as there would be many objects added to the Journal and query may take large time to process. It is best to pass the as much information as possible in the parameters so that query can return the specific results and can run faster. 

In the below example, we will pass the specific file name and journal entry type as parameters and cast the 'ENTRY_DATA' to return the data as character.

Display Journal on IBM i


In this example, we are converting the Entry data to character format and passing parameters to select the data for a specific file, journal entry types with in specified time range. 

  • Converting (CAST) the ENTRY_DATA from BLOB to character (VARCHAR) format.
    • In the above query, we are using 'CAST' two times to convert the data to character format. 
    • With just one CAST statement "CAST(ENTRY_DATA AS VARCHAR(50) CCSID 37) AS JOURNAL_DATA", Character conversion between CCSID 65535 and CCSID 37 not valid.
    • So, we are first converting the data as character and using another CAST to change the CCSID.

  • We are passing more number of parameters here (compared to the previous example) so that results can be specific. Below are some of the additional parameters we are passing.
    • OBJECT_NAME - Name of the object we are retrieving the data for.
    • OBJECT_LIBRARY - Name of the library object is present in. Special values '*LIBL', '*CURLIB' can be specified as well.
    • OBJECT_OBJTYPE - Type of the object passed (like *FILE, *DTAQ, *DTAARA or *LIB).  
    • OBJECT_MEMBER - Name of the member, this is only required for Object Type '*FILE'. Special values like '*ALL', '*FIRST' or '*NONE' can be specified as well. 
    • JOURNAL_ENTRY_TYPES - Journal entry types we are retrieving the data for. Multiple entries can be specified by separating them with space.
    • STARTING_RECEIVER_NAME - Name of the starting journal receiver name. Special values like '*CURRENT', '*CURCHAIN' or '*CURAVLCHN' can be specified as well. If no value is specified '*CURRENT' is considered by default. 
    • STARTING_TIMESTAMP & ENDING_TIMESTAMP - Starting and Ending timestamps to retrieve the data from Journal.
    • There are many other parameters that can be specified to narrow down the results.
Display Journal on IBM i

Above example is perfectly suited for a table with just one character field. We will see another example for a table with decimal field. 

Display Journal on IBM i

In this query, we are converting the data for each field separately by taking the substring using CAST & INTERPRET*.
  • In this example, we are retrieving the data for a table with two character fields (Order Number & Customer Name) and one decimal field (Order Value).
  • Lines 4 - 8: CAST function to convert the substring of BLOB to character and INTERPRET function to make sure the query will return the data in the required format for both the character fields.
  • Lines 10 - 11: CAST function to convert the substring of BLOB to character and INTERPRET function to interpret the data as packed decimal value from the character value.
    • Packed decimal field (5, 2) would only have the buffer length of 3 digits. So, we are taking the substring of 3 digits and converting it as character. 
    • Interpret function would convert the 3 digits as the packed decimal value of (5, 2). 
Display Journal on IBM i

*INTERPRET function is only available since IBM i 7.3 (TR8) and IBM i 7.3 (TR2). For the previous versions we can continue to use CAST. But, CAST wouldn't convert the data as packed decimal.

Display Journal on IBM i

This would display the decimal value as character only (suffixed with 'F') and doesn't show the period (.) to differentiate decimal values. 

Display Journal on IBM i


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

Wednesday, January 6, 2021

Read data in Data Area from SQL - IBM i

Data Area (DTAARA)

Data areas have been very much useful for sharing the data with in the job and across multiple jobs on IBM i. 

There have been many ways for reading the data from Data Area based on the place it is being read (like CL, RPGLE, SQL, Command Line etc...)

In this post, we will see how to read data in Data Area from SQL using function DATA_AREA_INFO. Results returned by this function are similar to the values returned by RTVDTAARA (Retrieve Data Area) CL command and QWCRDTAA (Retrieve Data Area) API.

Let's have a look at how DATA_AREA_INFO works with an example. 

DATA_AREA_INFO

In the above query,
  • 'DATA_AREA_NAME' is the only parameter we are passing and is the only mandatory parameter. Data area passed will be considered from the library list. If not present in library list and error will be thrown. This parameter accepts some special values like,
    • *LDA - Local Data Area
    • *GDA - Group Data Area
    • *PDA - Program Initialization Parameter Data Area
DATA_AREA_INFO

In the above result, 
  • Column 'DATA_AREA_LIBRARY' returns the library in which data area is present in.
  • Column 'DATA_AREA_NAME' returns the name of the data area. 
  • Column 'DATA_AREA_TYPE' returns the type of data stored in the data area (*CHAR, *DEC or *LGL).
  • Column 'LENGTH' returns the length of the data area. 
  • Column 'DECIMAL_POSITIONS' returns the number of decimal positions (this is only valid for Decimal data areas).
  • Column 'DATA_AREA_VALUE' returns the data stored in the data area. 
  • One other column which isn't in the above result is 'DATA_AREA_BINARY_VALUE', this returns the data stored in the data area as a binary value.
In the above query, we are just passing Data Area name and the data area present in the library list would be retuned by default. We can pass the library name explicitly to read the data from the data area in specific library. 

DATA_AREA_INFO

In the above example, we are passing library name and using Decimal data area. This should now return the number of decimal positions.

DATA_AREA_INFO

One other parameter this function accepts is 'IGNORE_ERRORS'. This parameter accepts two values.
  • 'YES' - Error is ignored and the partial data is returned. Only Data Area name (and Library Name if specified) and other columns would be null. 
  • 'NO' - An error is returned. This the default value. 
Above function would only work with one data area, If we need to retrieve the same data for multiple data areas at once from SQL, 'DATA_AREA_INFO' view would be useful. 

DATA_AREA_INFO view

This query would return the data from all the data areas present in the library passed. 

DATA_AREA_INFO view

Columns returned are almost same DATA_AREA_INFO function and below are the only additional columns in this view. 
  • 'SQL_SEQUENCE' - 'YES' if the data area is defined as SQL Sequence and 'NO' if not.
  • 'TEXT_DESCRIPTION' - Description of the data area.

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

Saturday, January 2, 2021

Search for a string in IFS using SQL - IBM i

Search for a string in IFS

IFS (Integrated File System) Stream files are major part of any (or most of) IBM i applications. 

Sometimes it becomes essential to scan through the IFS directory to identify the stream files with particular string. 

We can do this from SQL by writing a simple function using functions* IFS_OBJECT_STATISTICS (to retrieve the stream files present in directory) and IFS_READ (to read through the stream file and query for a specific string). Click on each of these functions to know more. 

Let's have a look at how new function (SCAN_IFS_FOR_STRING) returns the search results. 

Scan IFS directory for a string

Function 'SCAN_IFS_FOR_STRING' accepts two parameters.
  1. Input IFS Directory - IFS Directory that is to be scanned. Please note that all the Sub directories would be scanned as well.
  2. Scan String - String that is to be scanned for.
This query should return the stream files that contains the scan string in the specified Input directory. 

Scan for a string in IFS using SQL

This function returns three columns.
  • STMF_NAME - Stream file name (with full path) 
  • LINE_NUMBER - Line number with in stream file where the string is found. 
  • LINE - Line from the stream file where the the string is found. 
Below is the source for the function 'SCAN_IFS_FOR_STRING', same can be found in the Git Hub Repository

Scan for a string in IFS using SQL Functions - IBM i

Below is the brief description on what the above function would do.
  • Lines 5 - 7: Create (or Replace) function with two Input parameters (Input IFS directory & Scan string).
  • Lines 9 - 11: Format of the data to be returned. Scan results would be returned in three columns (Stream file name, Line number and Line).
  • Lines 19-22: Create (or Replace) temporary table with the three columns to be returned and to clear the data from the temporary table if the table already exist.
  • Lines 27-30: Retrieve the list of stream files present in the Input directory using 'IFS_OBJECT_STATISTICS' by passing Input directory and Object type as '*ALLSTMF' and to repeat the loop for all the files present.
  • Lines 34-38: Read stream files using function IFS_READ and select the records with only matching records by specifying where condition on LINE. Using UPPER() for both LINE and Scan string would convert the string to upper case for comparison and returns the matching records.
  • Line 43: Return the contents of temporary table.

*SQL Functions IFS_OBJECT_STATISTICS and IFS_READ are only supported since IBM i 7.3 (TR9) and 7.4 (TR3).

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

Popular Posts