SQL To The Rescue

April 10, 2017 Bob Cozzi

Since the early 1990s, developers have been using SQL embedded in RPG as a way to retrieve just the right data for the task at hand. Certainly it was at least a decade before it became commonplace to see SQL embedded in RPG, but now, 15 years later, it is in fact the go-to method for data access, or at least you can see it from here.

I was an early adopter of SQL, but an off-again, on-again user of embedded SQL. Originally, I felt the first format RPG with quasi-free format SQL (and all those plus signs to continue it) felt wrong. Besides, back then hardware favored native file I/O versus SQL. Today, both issues have become extinct and no longer are even viable debate points. With free-format RPG and free-format embedded SQL, you get a wonderful tool that allows developers to accomplish any task they are skilled enough to perform.

About six years ago, we were asked by a client to help recover from a total loss of their system. Everything had been destroyed in a major fire, from the shop floor to the Power5+ server they had been using to run the business. All backups were also lost due to a failure in the on-site tape storage system. The fire-proof safe wasn’t shut at the time of the fire, so all backup tapes were also lost.

Fortunately, they had other locations also running IBM i on Power, and while the then IT support person didn’t keep the system current or even in sync, we at least were able to recover some of the core applications to get them back up and running by the time their facility was rebuilt.

You might now be wondering “what does embedded SQL have to do with recovery?” Good question. To get the client’s reporting systems and other major applications refreshed with current technology so they could hit the ground running, I made the decision to use the latest IBM i innovations in RPG, SQL, and the Web. I wanted to bring this company into the 21st century. It had been one of those post-System/36 “one person” shops that was running a combination of RPG II and RPG III on IBM i, but compiling it with the CRTBNDRPG command and thus thought they were using RPG IV. Obviously, they were not. Since I won’t touch RPG II anymore, and have a certain negative reaction when I still see RPG III in user’s code, I decided the first step was to do a mass conversion to RPG IV using the CVTRPGSRC (Convert RPG Source) command that has been on the system for more than 20 years. It converts RPG II and RPG III to RPG IV syntax. It’s up to you to adjust any subtleties, but for the most part, it just works.

Once I had a good RPG IV base, I could enhance each app as needed using embedded SQL to select the right data for the situation. I avoided their OPNQRYF commands whenever possible, opting to use embedded SQL instead.

But then I hit a mountain. This client has a long history, and during that history it accrued a lot of reports and inquiries. Most were what we once called “80-80 listings,” but some had a little customization, sorting, and selecting. In fact, we found the old FMTDATA (Sort) being used all over the place. Now, I haven’t used SORT since the 1970s, having moved to System/38 in 1981 and never looked back. There was no way I was going to using SORT or move the SORT specs to OPNQRYF. Since we were looking at hundreds of reports with SORT, we needed a better option.

Back in our offices, we had just started working on an SQL processing tool. We weren’t sure what it would become, but wanted to create a new version of our very popular SQL Lite for AS/400 product we had written back in the early 1990s. This new product was being written in C and C++ for performance and accessibility to low-level interfaces on the IBM i system. We wanted something new and something fresh and that could work with today’s constantly changing technology interfaces.

The results of that research and development produced SQL iQuery, and Version 1 became available about six months after we became aware of our client’s SORT catalog. It meant we could simple run an SQL SELECT statement to get a report that contained the fields in the sequence the client needed and with only the records they wanted. We moved the technology from this:

1. CL Program
2. SORT Specs Source Member
3. FMTDTA command
4. Custom RPG Report program

To this:

RUNiQRY 'SELECT a,b,c, order by c' OUTPUT(*PRINT)

We reduced the IT assets that needed to be produced and maintained in order to generate a simple report, to a one-line CL command, or more accurately, a single SQL statement.

We also did one more thing: We installed IBM Transform Services 5770-TS1 (*BASE and option 1) to allow users to automatically generate PDF files instead of just legacy printer SPOOLED files.

The IT developer time needed to produce reports (and there were literally hundreds of them) went from using the CL program, SORT/FMTDTA, and custom RPG method that took about a day per each report with cloning, to about 45 minutes with SQL iQuery. In one day the IT developer could create several reports for the client – six to 12 reports on average – and provide a consistent result.

Challenges Of Success

One of the challenges we faced in the wake of this successful development cycle was how to provide the future IT developer with the ability to modify the reports (as needs change) and how to manage those CL statements.

In our legacy SQL Lite product, we had implemented a method to store SQL statements within a source file member, and run those statements. It was an easy decision to introduce that feature into SQL iQuery, and using our new C / C++ engine, it turned out to be easy to implement as well.

Basically, the RUNiQRY command would run SQL statements via its SQL parameter. By allowing source file members to be processed, we needed to add new parameters and change the default for the existing SQL parameter.

• SQL new default is SQL(*SRCFILE)
• SRCMBR – New parameter to accept the name of the source member contain SQL statements
• SRCFILE – New parameter to accept the SQL source file name that holds the member name.

With this simple change, we enabled the RUNiQRY command so that it could run SQL statements right out of a source file member. Yes, I said “statements” plural. Any number of SQL statements may be run from within a source member. Similar to, but not the same as, the RUNSQLSTM (Run SQL Statements) CL command included on the system. That command, however, does not support the use of the SQL SELECT statement, but most other SQL statements such as CREATE, UPDATE, INSERT, DROP may be specified.

SQL iQuery initially supported running any SQL statements as a batch process. It basically ran each statement, one after the other.

Having this capability provided use with the tool we needed to catalog the growing number of SQL SELECT statements being used to create reports. We would create a member in QSQLSRC, give it a reasonable name, and assign a text description to it that described the report being produced. For example, the Daily Sales Register might be named DAILYSALE and its text description would have been “Daily Sales Register Report.”

To run this report, the user simply enters:

RUNiQRY SRCMBR(DAILYSALE) OUTPUT(*PRINT)

It is much easier to put something like this on a Menu rather than embed the entire SQL SELECT statement on the command itself. Plus, you get the added advantage of being able to tweak the SELECT statement external to the RUNiQRY command itself. Any developer will tell you, this is better.

Better Is The Enemy Of Good

During my career, I had the pleasure and privilege of working with some of the best people that IBM Rochester and Toronto had to offer. During a short retirement period in the late 1990s, I did a few projects with NASA’s Jet Propulsion Lab in Pasadena, California. The one I most enjoyed was doing field test work in Italy on the Mars Rover’s Alpha Gamma X-Ray Spectrometer, which was subsequently used on those first Mars rovers during that same period. As a lifelong “space freak,” it may have been the highlight of my career.

During that period, one of the managers told me something that stuck with me. Perhaps today it is a common phrase that people use, but back then it was new to me. He said, “Better is the Enemy of Good.” You can always make things better, but to finish something, you have make it good enough.

We applied this philosophy to the way we designed and implemented SQL iQuery. First we made it good enough to run any SQL statement from within CL or the command line using the RUNiQRY command. Next, we improved it by adding support to run SQL out of source members.

In our third phase (Version 3, naturally) we added output support components. That is, we added the ability to direct the output of the SQL SELECT statement to a growing number of output media. Originally, we included the usual suspects: Display, Print, Outfile, CSV, PDF, and TEXT. But we have since added EXCEL, XML, HTML, and JSON among others.

Something Magical Happened

While creating the routine to process source members, we realized we could implement something wonderful: a scripting language. That is we found we could create a real SQL programming language that could be both subtle and out of the programmer’s way, and powerful enough to handle anything needed, even web applications.

While there are always challenges with writing a programming language from standards to syntax, we choose to simply implement an SQL and RPG style syntax that existing RPG developers would understand “in 10 minutes” while SQL DBA/DBE’s would be able to easily comprehend as well.

When we shipped SQL iQuery Version 4 on March 1, the upgrade included the new SilQ (pronounced Silk) programming language. It is also called “iQuery Script,” but don’t worry about it being another language to learn. You already know it if you know RPG or SQL or both. In fact, you can avoid it entirely since it is merely an extension to running SQL from source members, and it doesn’t matter if you don’t use it. But it is powerful and allows IT developers to get things done now without writing a customized RPG program just to process an SQL statement. And when you need to receive and send data between an IBM i system and the web, or your own web pages, it is ready and works well with that.

In fact, for the client where this all started, we created an entire storefront website using SQL iQuery – no custom RPG at all is involved in this, no CGIDEV, no PHP, no COZTOOLS CGILIB, just SQL iQuery. The web interface is written in SQL using standard JavaScript with some jQuery JavaScript library features utilized for each of use, but SQL iQuery works with everybody’s web because it can send the results of your SQL SELECT statements as JSON directly to the web.

You can check out our client’s website here if you’re interested.

The advantage we have now with SQL iQuery and our customer’s disaster recovery is that all those SQL reports we created a few years back, we can now deliver to users as EXCEL or PDF files via email. No longer do you need to add on this conduit or that transfer app to transfer something. Just email it. In some cases we also simply redirect the reports to the web. We have the capability to use SQL iQuery and push the results out as HTML (such as a scrolling table) or a set of JSON data nodes; whatever works for you.

Today, SQL iQuery is helping our disaster recovery client utilize their Power7+ running IBM i V7R2 and it really is taking advantage of some of the coolest technology in the world. In fact, our client is so satisfied with the kind of applications they can get today versus the legacy SORTS and RPG II code, they’ve decided to upgrade their entire set of 7 IBM Power 7 servers to Power8 running V7R3 over the next 10 months. SQL iQuery helped make that happen.