Mike Larsen is a Director of Information Technology at Auburn Pharmaceutical and an associate of Central Park Data Systems and has been working with IBM i systems for over 20 years. He specializes in RPG, CL, and SQL and recently has been working with PHP and Python. Current projects have given Mike the opportunity to work with generating and parsing XML and JSON from SQL and consuming SOAP and REST web services. Although his main area of expertise is on IBM i, Mike has a passion for learning other languages and how he can integrate other platforms with IBM i.
November 27, 2023 Mike Larsen
While working on a project recently, I needed to retrieve attributes of an item. While that sounds like a simple task, there was a twist. While reviewing the contents of the item master table, I noticed there could be multiple rows for the same item and each row was active and valid from a business perspective.
I won’t get into the business end of it, but my goal was to return the most recent iteration of the item for further processing in the program. After doing some research and trying some potential solutions, I came across the SQL partition by …Read more
June 19, 2023 Mike Larsen
I use SQL almost every day at my job. It may be just a query in ACS, or it may be embedded SQL in an RPG program. A few days ago, I needed to compare the contents of one IFS directory to another. Combining a few different table functions, I was able to develop a nice solution.
Note: The code for this article can be downloaded here.
In this example, I have a directory that has five text files in it. I have a second directory that has three text files in it, and they are the same documents …Read more
October 24, 2022 Mike Larsen
Recently, I was working with an employee file where first and last name were concatenated with a comma separator. A requirement of the project was to parse the name and populate a different table where first and last name are separate columns. I won’t address the design of the employee file, but I will show how I completed the request.
In Figure 1, I use the position string function via SQL to extract the first name.
Figure 1. Extract first name
In this example, I use the trim, substring, and posstr functions to position to the first …Read more
August 15, 2022 Mike Larsen
In the first part of this series, I showed how to replace characters in a string using SQL and regular expressions. This time, I’ll show other regular expressions that are available for us to use. The regular expression functions I’ll show are REGEXP_COUNT and REGEXP_LIKE and the examples come from production programs that I’ve recently implemented.
In the first example (Figure 1), I want to get a count of the number of occurrences of a pattern within a string. To make this more interesting, I’m searching for two different patterns within a sting. To accomplish this objective, I used …Read more
July 25, 2022 Mike Larsen
In the first part of this series, I will show how to replace characters in a string using SQL and regular expressions. I know I can replace characters using RPG or SQL, and they work great, but there are some cases where I find using regular expressions is a bit easier.
For the examples I’ll show, I’m using the REGEXP_REPLACE function. REGEXP_REPLACE allows me to search for a particular value within a string and replace it with another value.
In the first example (Figure 1), I want to search a string that has both letters and digits and I want …Read more
April 11, 2022 Mike Larsen
I use Access Client Solutions (ACS) daily to help with different tasks. I’m usually running SQL scripts or working with the IFS, but recently I was asked if there was a way to debug SQL stored procedures using ACS. More specifically, they were looking for a way that a “non-IBM i” person can debug SQL stored procedures on IBM i.
After browsing the various menus in Run SQL Scripts in ACS, I found a system debugger. I had seen the system debugger before, but never took the time to explore it further. With this new request in mind, I decided …Read more
July 27, 2020 Mike Larsen
I’m often asked to create reports for my business partners. The format of the report depends on who made the request. If the financial team made the request, I may produce an Excel file. The CEO may want to report quarterly sales to their shareholders. In that case, I may produce a PDF using the company letterhead.
In a prior article, I showed how to create Excel files using Db2 tables. Now I’m going to show how to create PDF documents using Db2 tables.
This story contains code, which you can download here.
I always start by making …Read more
April 27, 2020 Mike Larsen
In Calling RPG Programs From Python, Part 1, I showed how to call an RPG program from a Python script passing single string parameters as input and output. In the second part of this series, I’ll make the process a little more robust by passing a data structure to the RPG program.
The first part of the Python script (shown in the first piece of code below) imports the itoolkit modules and assigns them to local names. Next, I set up the first command that adds a library list entry to where the RPG object resides.
from itoolkit import… Read more
March 30, 2020 Mike Larsen
In a prior article, I showed how to pass parameters to a Python script and execute the script from an RPG program. Based on feedback and my own curiosity, I wanted to see how I could pass parameters to an RPG program and call it from Python. After a bit of research, I found the Python interface itoolkit.
itoolkit is an open source project provided by IBM as an interface to the XMLSERVICE toolkit, which allows us to call RPG programs, service programs, CL programs, and PASE Shell commands. itoolkit can be installed using an SSH terminal with …Read more
September 23, 2019 Mike Larsen
One of my recent projects required me to parse JSON returned from a web service. On the surface this sounded like a pretty easy task, but I quickly ran into a challenge. The JSON being retuned didn’t have a top-level element, and since I wanted to load the JSON into a data structure, my program couldn’t handle it.
After some searching, I found that some of my options included changing the code in the parser (JSONPARSE) or using a totally different parser altogether. While both of these are viable options, I decided to take a slightly different route.
Before I …Read more