Mike Larsen
-
Guru: String Manipulation Using SQL
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 -
Guru: Regular Expressions, Part 2
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 -
Guru: Regular Expressions, Part 1
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 -
Guru: Debugging SQL Stored Procedures With ACS
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 -
Guru: Creating PDF Documents With Python
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 -
Guru: Calling RPG Programs From Python, Part 2
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 -
Guru: Calling RPG Programs From Python, Part 1
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 -
Guru: Parsing JSON With DATA-INTO And A Twist
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 -
Guru: Passing Parameters To Python
August 5, 2019 Mike Larsen
Python scripts offer great benefits to developers, whether used standalone or in conjunction with RPG programs. As I’ve been exploring Python and recently I had a need to execute a Python script from an RPG program. In addition to executing the script, I also wanted to pass it parameters.
One of the benefits of passing parameters is to give us the ability to soft-code programs or scripts. Soft-coding makes programs more flexible and re-usable, and helps to reduce maintenance.
To illustrate how to do this, I’ve coded a very simple RPG program and an even simpler Python script. I’ll start …
Read more -
Guru: Finding Large Files With Python
July 15, 2019 Mike Larsen
It’s always a good idea to purge files that aren’t needed any longer. Chances are that you already have procedures in place to purge data from Db2 files and tables, but what about files that reside in the IFS? Do you have a good solution for keeping the IFS clean?
Perhaps you have old order files stored in the IFS. If you work for a large company, these types of files can accumulate quickly. I’ve written processes in the past to remove files from the IFS using RPG, but I’d like to offer an alternative. I’m going to show how …
Read more