Notes

Merging data with SQL MERGE

There have been times, many times, when I have needed to update the one or more columns in a table with values retrieved from another. A traditional approach to this is to use an UPDATE with subselects but there is a better way: The MERGE statement updates a target (a Read more…

By Paul, ago
Notes

Better date conversion with timestamp_format

Handling dates and times on the IBM i can often be a bit of a challenge. Obviously, if you are working with a modern application that uses the Date, Time and Timestamp data types everything is nice and straightforward. However, there are plenty of old applications that use eight, seven Read more…

By Paul, ago
Notes

Using EXTFILE to override files within RPG

So here’s the situation: Five warehouses populating five sets of files (same filenames, different libraries) and I have an RPGLE program that needs to read through each of these to accumulate dispatch information. The traditional way of doing this would involve writing a CL program to OVRDBF to each file Read more…

By Paul, ago
Notes

Adding variables to ad-hoc SQL queries with CL

Last month I mentioned using REXX to build and execute ad-hoc SQL queries. For the sake of completeness, here is the same solution implemented in CL using the RUNSQL command. The program does exactly the same as the previously describe REXX script — extracts all records flagged with today’s date Read more…

By Paul, ago
Notes

Using RGZPFM to sort physical files

It’s rare that you need to sort a database file (or table), but I have encountered a circumstance in which a sorted file had to be delivered. Being naturally lazy, I started looking around for the least-work method of achieving this and ended up looking at the RGZPFM command. This Read more…

By Paul, ago
Notes

Debugging RPG Programs in Batch

If you spend any time developing RPG applications you will find that, sooner or later, you will need to debug a program that runs in batch. There are a few steps that you need to take in order to do this and, because my memory is terrible, I’m putting them Read more…

By Paul, ago
Notes

Service Programs and Call Stack APIs

I’m a big fan of service programs. From a maintainability point of view, encapsulated procedures are great. And exported procedures, which mean you only need to develop any piece of functionality once, are even better. However, I now find myself in the position of having to start creating a set Read more…

By Paul, ago