Bob Cozzi
-
Guru: The Transition From Modern RPG IV To Modern SQL
October 30, 2023 Bob Cozzi
Seven or eight years ago I was working with a client who needed more contemporary reporting than provided by the legacy Query/400 product. Initially I looked at Db2 Web Query and realized the documentation was sparse and the tutorials for most things, even “getting started” were virtually non-existent. It was also about four times more expensive than I felt it was worth to a P05 or P10 client. I mentioned the documentation issue to the folks at IBM and they agreed. Some of it was eventually resolved, but not enough in my opinion to get the masses to replace Query/400 …
Read more -
Guru: Search Source Code For Strings Using SQL
October 9, 2023 Bob Cozzi
The IBM-supplied Find String Using PDM (FNDSTRPDM) command and PDM option 25 are how most developers scan for a simple value in a list of source file members. But what if you need more? For example, what if you want to find something complex, such as the field named “CUST” but not the field named “CUSTNAME” “CUSTOMER” or “CUSTNO”? Go ahead, I’ll wait?
While I do use FNDSTRPDM all the time, I now use the SQL READSRC (Read Source File Member) Table function more often.
READSRC SQL UDTF
The READSRC SQL Table function or UDTF (User-defined table function) uses an …
Read more -
Guru: Generating XML Using SQL – The Easy Way
September 18, 2023 Bob Cozzi
There are many verbose methods of generating XML. You can:
- Write your own RPG code
- Using a free or third-party API
- Use SQL iQuery OUTPUT(*XML) option
- Use the built-in SQL XML functions such as XMLELEMENT
I’m sure there are others, but these seem to be the most popular.
For years I had been using the XMLELEMENT approach; a rather verbose set of XML function built into Db2 for i SQL. In fact, SQL iQuery’s OUTPUT(*XML) is based on that feature. It simply regenerates your SQL statement using XMLELEMENT statements for each output column/field name.
But if you’re using something IBM …
Read more -
Guru: Retrieving The Long And Short Object Name
August 14, 2023 Bob Cozzi
Many releases ago, IBM i received “Long SQL Names” for files and libraries. These new longer names (up to 128 characters) were well received by SQL enthusiasts, but largely ignored by the mainstream IBM i developer. As each version of IBM i emerged, more and more shops experienced one or more objects with a longer-than-10-character name.
Recently I created a file named BOAT_TRAFFIC. This name is clearly longer than 10 characters. I used SQL DDL (the CREATE or REPLACE TABLE statement) to create the file. Using SQL DDL is the only real way to create an object with a long …
Read more -
Guru: Object Usage Statistics
July 10, 2023 Bob Cozzi
A long time ago in a system far, far away, IBM added the Date Last Used to the Object Description for all objects on the system. The term “last used” means different things to different object types; for *FILE objects it means the file was opened or its description was changed, for *PGM objects it means the program was called, for other objects it generally means what you might think it means (viewed, retrieved/read, updated, etc.) The only exceptions are Device Descriptions which get updated when a *DEVD changes from “Vary On Pending” to some other status.
The Date Last …
Read more -
Guru: Binding Directory Entries
June 5, 2023 Bob Cozzi
I assume you’ve heard about *BNDDIR (Binding Directory) objects introduced circa 1994 with OS/400 V3R2. The infamous QC2LE binding directory is used by a huge number of RPG applications to access C runtime and unblocked MI functions such as system cvthc, cpybytes, and matmatr. You have probably seen RPG IV source code with the BNDDIR(‘QC2LE’) keyword on the header specification.
I was one of the first developers outside of IBM to use Binding Directories for my own code. When I go back and look at my own RPG IV code created prior to mid-2007, well over 90 percent of it …
Read more -
Guru: Find Unused Objects On IBM i Using SQL
May 15, 2023 Bob Cozzi
I have a client that uses SQL iQuery for nearly all “Report” type applications since SQL iQuery Version 2. They asked if they could create a report that listed all the unused objects in their user libraries. They also wanted the option to output to Excel-compatible format if desired.
Since SQL iQuery allows you to output the results of your SELECT statements to any of its supported formats, Excel-compatible is given.
Note: the code for this article is avaialble on GitHub at this link.
There were a couple of approaches. I could use our other product, SQL Tools OBJECT_LIST …
Read more -
Guru: Copy From Stream File FMTOPT(*NOCHK)
April 13, 2020 Bob Cozzi
Using SQL frequently, as I do, I tend to look for solutions to problems that can be resolved using the database language (i.e., SQL). Sometimes I have to invent a missing piece of the puzzle. Often that entails writing a User-Defined Function (UDF) for use in SQL, but sometimes a CL command is the better choice.
Recently I had a situation where what the client calls an “EDI File” was being downloaded using sftp on the IBM server running i. This file is created at a vendor that runs IBM mainframes (an actual mainframe by the way) and the file …
Read more -
Guru: Copy OUTQ To PDF
February 3, 2020 Bob Cozzi
A long time ago I created a CL command named Copy from OUTQ (CPYOUTQ). This command allowed you to selectively copy spooled files from one OUTQ to either another OUTQ or to the IFS as a PDF or text file. My customers use it all the time for monthly archiving of spooled files and redistribution of output. You may have it on your own system.
Being one of the handful of original advocates for the so called “Openness APIs” for IBM OS/400 (now IBM i), I quickly embraced the system APIs and have used them extensively throughout the decades. One …
Read more -
The (More) Modern RPG Language
December 16, 2019 Bob Cozzi
Back in 1988, I wrote what became the book on RPG III. Then in 1996, I published the RPG IV version and updated it again circa 2000. But in the years that followed, RPG IV became mostly stale; a tweak here and there, but nothing too spectacular.
In recent years, a wave of RPG IV enhances has been revealed, most notably free-format was completed and helped propel RPG IV, once again into a truly modern language. Although the measure of “modern” for RPG IV seems to lean toward how much free-format syntax is supported; which is ironic considering COBOL and …
Read more