The SQL iQuery for #IBMi Story
Replacing IBM Query/400 queries was our goal for SQL iQuery back in 2016. The IBM i licensed program was sunset and decades of user-based queries were being used with a now obsolete interface. The journey to SQL iQuery was started with a simple goal: Allow any standard SQL statement to be run via a CL command interface, including the SELECT statement.
Since my company had created the SQL Lite product for AS/400 back in the early 1990s, our experience with that product provided a good starting point. But we quickly realized that we needed to find a way to extend this new SQL query tool by providing a way to create output options (such as CSV, JSON, XML, and spreadsheet formats) without any work required by the User.
This meant we needed to alter the way the new product processed SQL. The original SQL Lite product used embedded SQL in a high-level language (RPG IV mostly) to run SQL statements using EXECUTE IMMED, while the SELECT statement was processed via the RUNQMQRY CL command internally. Ironically there are no fewer than four "competitive" or copy-cat 3rd-party apps for IBM i that still use this technique today. This design clearly would not scale for our new goal of write-once, output anything. So we looked elsewhere.
We decided to use the Microsoft ODBC interface APIs known as SQL CLI (Call-level Interface). The CLI APIs were ported to IBM i years ago but were largely unused by non-IBM software. They are standardized, so the Microsoft documentation filled any gaps in the IBM docs. Fortunately the IBM docs only fell short in a few areas, so it was pretty complete.
The goal of the new design was to process an SQL statement and if applicable, create an SQL result set in a standard format so that our output processor could use it to produce a variety of output formats.
Since Query/400 has effectively 3 output formats (Display, Printer, File) these were the first output formats we created.
The CL command we built was pulled from our SQL Lite product, and was named RUNSQL. Yes, we used RUNSQL for over 25 years before IBM hijacked the name for their benign and IMHO pointless CL command. But that's another story... Here's what it originally looked like:
Type command, press Enter. ?
===> RUNSQL 'SELECT * FROM qiws.qcustcdt'
The output was pretty much as you might expect (hopefully LinkedIn doesn't mess up the format):
IQRYVIEW?? QIWS.QCUSTCDT?????????????????????????????????? iQuery for IBM i?????????????????????????? 3 JAN 2023?????? COZZISYS??
Estimated Rows:?? 12????????????????????????????????????????? Result Set????????????????????????????? Data width:????? 101????? ?
Position to line:???????????????????????????????????????????????????????????????????????????????????? Shift to column:????????? ?
??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ?
??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ?
?CUSNUM? LSTNAM??? INIT? STREET???????? CITY??? STATE?? ZIPCOD? CDTLMT? CHGCOD??? BALDUE??? CDTDUE????????????????????????????? ?
?938472? Henning?? G K?? 4859 Elm Ave?? Dallas? TX?????? 75217??? 5000?????? 3???? 37.00?????? .00????????????????????????????? ?
?839283? Jones???? B D?? 21B NW 135 St? Clay??? NY?????? 13041???? 400?????? 1??? 100.00?????? .00????????????????????????????? ?
?392859? Vine????? S S?? PO Box 79????? Broton? VT??????? 5046???? 700?????? 1??? 439.00?????? .00????????????????????????????? ?
?938485? Johnson?? J A?? 3 Alpine Way?? Helen?? GA?????? 30545??? 9999?????? 2?? 3987.50???? 33.50????????????????????????????? ?
?397267? Tyron???? W E?? 13 Myrtle Dr?? Hector? NY?????? 14841??? 1000?????? 1?????? .00?????? .00????????????????????????????? ?
?389572? Stevens?? K L?? 208 Snow Pass? Denver? CO?????? 80226???? 400?????? 1???? 58.75????? 1.50????????????????????????????? ?
?846283? Alison??? J S?? 787 Lake Dr??? Isle??? MN?????? 56342??? 5000?????? 3???? 10.00?????? .00????????????????????????????? ?
?475938? Doe?????? J W?? 59 Archer Rd?? Sutter? CA?????? 95685???? 700?????? 2??? 250.00??? 100.00????????????????????????????? ?
?693829? Thomas??? A N?? 3 Dove Circle? Casper? WY?????? 82609??? 9999?????? 2?????? .00?????? .00????????????????????????????? ?
?593029? Williams? E D?? 485 SE 2 Ave?? Dallas? TX?????? 75218???? 200?????? 1???? 25.00?????? .00????????????????????????????? ?
?192837? Lee?????? F L?? 5963 Oak St??? Hector? NY?????? 14841???? 700?????? 2??? 489.50?????? .50????????????????????????????? ?
?583990? Abraham?? M T?? 392 Mill St??? Isle??? MN?????? 56342??? 9999?????? 3??? 500.00?????? .00????????????????????????????? ?
??????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ?
?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? Bottom
F3=Exit?? F2=Save?? F4=Field List?? F6=Print?? F7=Left?? F8=Right?? F12=Cancel? F14=SQL Stmt? F21=Command line?? F22=Debug Info??
?
We opted to default to *DS4 mode since statistically nobody configures *DS3 mode screens in ACS anymore. Turns out, our customer/users loved that choice.
The second output format was *PRINT since Query/400 is used mostly for printed output. The CL command to vary the output target was simply the addition of an OUTPUT parameter:
Type command, press Enter. ?
===> RUNSQL 'SELECT * FROM qiws.qcustcdt' OUTPUT(*PRINT)
As you'd expect, adding the OUTPUT(*PRINT) parameter reroutes the output to the printer. Here's a look at the printed resultSet:
Pretty much the same as the display version, but now it includes the "12 records listed." footer, as expected.
Lastly we had to create an OUTPUT(*FILE) option. To do that we use classified technology. Here's the CL command to do that:
Type command, press Enter. ?
===> RUNSQL 'SELECT * FROM qiws.qcustcdt' OUTPUT(*FILE) OUTFILE(QGPL/MYCUSTS)
The OUTFILE parameter identifies the database file you want to create. If you don't specify it, a default name is produced. As it turns out, if you can process a SELECT statement, IBM i developer rarely actually need OUTFILE support. Without SELECT statement support, OUTFILE capability is critical. So while you may see other blogs using OUTFILE support or a similar technique in their examples, it is only due to their proactive refusal to use SQL iQuery which fully supports the SELECT statement, weird, I know.
SQL iQuery was Born
Once we had those 3 output formats working properly we released the product as "SQL Query File". The original name "Query File" was intended to associate the name of this product with Query/400 so customers would realize it was a replacement for Query/400. Apparently they didn't get the memo or were happy with Query/400 as we sold only 4 copies at the time.
After that disappointing launch, I decided to go on safari in South Africa and think about it. During that trip, I really hoped to see Rhinos in the wild. To my delight, upon arrival at sunset we had about 6 of them blocking our access to the lodge. It was awesome!
While at the lodge that week, I asked for a walking safari with a guide. The staffer replied with "I'll query the staff and see who might be available."
Yes, a South African Safari Lodge staffer unknowingly triggered the name "iQuery" when he said "I'll query the staff...".
Upon returning to Chicago, I implemented a product rename and we repurposed the product as what we now call "SQL iQuery" for IBM i. The Command name was switched to RUNiQRY ("Run iQuery") as this point, since IBM had screwed on the RUNSQL command at this point. We also implemented two additional formats that became 2 of the 3 most used formats we support: CSV and JSON
I had written a CPYTOCSV CL command about a decade earlier for my COZZI TOOLS (COZTOOLS) product. CPYTOCSV itself was born out of the fact that the IBM-supplied CPYTOIMPF was arguably the worst command ever created by IBM. The performance was horrible back then. CPYTOCSV ran 40 to 50 times faster and produced clearer results. Of course since then, IBM has copied the way we do CSV by rewriting it using the same SQL CLI APIs and C/C++ code. However our customers say iQuery is far and away faster at creating CSV results than CPYTOIMPF, even today.
But I digress. I rewrote the CSV output for iQuery using C and C++ and it works and performs extremely well. The 2nd format was JSON, and while nobody offered JSON output on IBM i at that time, after attended a Conference in San Jose with the original author of "AJAX" (the web technology) and discussing the future of XML vs JSON with Jessie, he suggested that JSON would dominate over XML, and he was right. I opted to implement JSON even before XML or Excel.
After shipping CSV and JSON output, I had a phone conversion with ITJungle where the editor was astonished that I went with JSON over XML. It was the right decision and today it is still the best IBM i JSON output engine available IMHO.
At this point we had OUTPUT(* | *FILE | *PRINT | *CSV | *JSON) and it was time to relaunch the product as SQL iQuery with the RUNiQRY command.
Today, SQL iQuery's OUTPUT capabilities include:
In addition, we also have a Web component that reads CGI input from HTML Forms and passes it to our powerful SQL iQuery Scripts processor. This is the only way I create web apps for IBM i today.
The Excel support was an interesting part. There was no native binary Workbook (XLSX) library that could be ported to the IBM i platform. So we decided to write our own from scratch, using published interface. That meant we need to use the Open Spreadsheet format, which is now called SpreadSheetML. It is an XML-based file that contains spreadsheet content. Microsoft Excel reads it seamlessly, however after about 2016 MS changed Excel to generate a message upon opening our iQuery .XLS file. That message is just a warning and can be ignored without issue. LibraOffice and other Open Office packages all natively support the .XLS files we produce with iQuery.
领英推荐
Since most customers who had Excel in mind were using CSV to do that, they often overlook the fact that we can produce much nicer formatted Excel-compatible files than legacy CSV processes.
With an article in ITJungle, some LinkedIn posts, and a few IBM i-centric website/blog entries, we launched SQL iQuery and waited for the orders to flood-in. We are still waiting on that flood of orders.
Prompting with SQL iQuery
A customer needed the ability to perform runtime prompting of the end-users for their SQL iQuery scripts. It turned out to be a security thing related to the U.S. Government during COVID. Using DSM (look it up) we quickly build a set of iQuery script commands to create end-user prompts and pass data to and from those prompts. (Note: iQuery script is a source member with SQL statements in it, similar to a batch file, that run in sequence.) With that prompter language they were able to prompt the end-users for things like a date range, a customer number, really anything at all. And it looks "a million times better" than the prompts in Query/400 runtime prompting.
Where we are
Over the next 2 years we sold nearly 12 copies of SQL iQuery. The subsequent 2 years we sold an additional 6 copies; today we have nearly 45 customers. As a reference, the original SQL Lite product, back in the 1990s sold nearly 700 licenses over a 3 year period.
It was at this point, seeing fewer than 4 dozen customers, that I felt the IBM i market was fading away.
Today I've moved SQL iQuery to maintenance mode--fixing any reported issues, pro-actively going in and looking for code optimization opportunities, and refining the user experience. I'm also creating a set of documents ("books") on using SQL iQuery that'll start being released in early Feb 2023.
We have a few customers that run "everything" using SQL iQuery (that's a good thing). Many/most customers want reports, so if they occasionally contact me to help with that, I only use SQL iQuery to produce reports, today. Why? Because as soon as I show them a Report they typically ask "Can I get that in Excel?" and the answer when it is created with SQL iQuery is always "yes". Turns out most of the customers use SQL iQuery for just a few project-types, such as XLS or CSV output, while others tend to replace Query/400 with it... you can retrieve the Query/400 source and run it directly in iQuery, unaltered. But even better, you can enhance it using SQL and then redirect the output to Excel, JSON, CSV or whatever you need... which we all know you can't do with Query/400.
SQL Tools Emerges
When V7R2 came out IBM started publishing what they then called "SQL Services" which have subsequently been rebranded "IBM Services". With that same release they opened up the SQL Function interface to allow advanced developers an easily interface to creating SQL functions with non-RPG languages, such as C and C++. So I started researching that technology and realized I could create SQL functions that would call IBM i API interfaces and return the results as an SQL resultSet. I felt this was an awesome discovery!
Few know that in addition to helping IBM design RPG IV, I was one of the original consultants/advocates for opening up the OS/400 operating system (now called IBM i) by creating APIs for the platform. Originally we only had QCMDEXC and QTBXLATE; to open up the system they initially created the Openness Includes and corresponding APIs, such as QUSCRTUS and the DTAQ APIs. Today we have well over 1000 native IBM i APIs not to mention myriad PASE (Unix) APIs IBM has ported over; and let's not forget the SQL CLI APIs.
I would not want to replicate what IBM created with the SQL Services, since there would be no point, rather, I would supplement it--for example, the RTVMBD Table function I wrote is unique and is the fastest way to get a member's description into RPG using SQL. Well, it is the only way using SQL to do that.
The very first function I created was RTVOBJD (Retrieve Object Description). I had decided to use CL command names as the SQL Function names where possible to make the transition from CL commands to SQL functions easier. That RTVOBJD function is documented at this link:
The next function was the OBJECT_LIST Table function. This function was basically similar to the OBJECT_STATISTICS function IBM created but worked the way I desired, and it included an Object Name input parameter to help with performance. I do use OBJECT_STATISTICS for a few things, and IBM has (at my request) added the OBJECT_NAME parameter to it for better results, but I often prefer my own OBJECT_LIST for day-to-day tasks, particularly on earlier releases of IBM i.
After I got those two functions working well, I used them as a design template and started down a road of high-productivity: over the next few weeks I created MBR_LIST, LIBRARY_LIST, SPOOLED_LIST, MNUOPT_LIST, JOB_LIST, IFS_LIST, MSGQ_LIST, PDFMAP_LIST, LOCK_LIST, NETS_LIST, and so on. For the RTV APIs, I built RTVCMDD, RTVDEVSTS, RTVDTAARA, RTVJOBA, RTVGRPPRF, RTVJOBA, RTVJOBD, RTVJRNINFO, RTVLIBD, RTVLASTSPLF, RTVMSGD, RTVNETA, RTVSYSVAL, RTVOBJD, RTVUSRPRF, RTVUSRSPC, etc.
These functions work great, provide a way to extract the information easily using embedded SQL (no more /COPY's and long parameter lists!) and according to the customers we do have, are very stable.
Today we have over 100 SQL functions in what is now called SQL Tools. Most IBM i APIs that RPG and CL programmers use regularly have been ported to SQL Functions in SQL Tools. Virtually none of those RTV functions are replicated in the "IBM Services" catalog.
Initially we offered these SQL functions with SQL iQuery for free but even with that new functionality SQL iQuery sales stalled. So, we decided to break-off those SQL functions into a stand-alone product named SQL Tools. They didn't require iQuery to work, so it was a no-brainer.
Initially we offer SQL Tools for a small (under $500/US) license fee. We figured we'd sell tons of them, so the lower price was warranted. But, you guessed it, nobody purchased SQL Tools. Zero sales. Over time we added more and more functions to the product and made sure that every function we introduced worked across all IBM i versions from V7R2 through the latest release, and they do. And this is a critical point: when we create an SQL function in SQL Tools, unlike the IBM Services feature which appear on V7R4 or R5 and do not get ported back to the N-2 releases, we build for V7R2 and later so can rely on not being left behind. If you have a multiple version environment you don't have to think about whether these SQL Tools work "here" but not "there". Don't worry, they just work.
Over the 2 years since we introduced SQL Tools as a stand-alone product, we have grown to over 100 functions. But at that original price point the feedback we got was "it should be free like IBM SQL functions are free" and "the price is too low, so people think it isn't any good".
First, IBM SQL functions are bundled in the cost of the operating system and SWMA fees. So you are paying big money for those functions. After all, can you name 10 new features in V7R5 that aren't SQL functions? It seems that's where all the budget is going, from where I sit.
Over the years we have had a dozen or so customers license SQL Tools; they love it and can't live without it. Their System Admins particularly enjoy using SQL Tools inside of IBM ACS. You do not need to use SQL iQuery to take advantage of SQL Tools. They can be run in any SQL processor, iQuery, ACS, STRSQL, and as emebedded SQL in RPG IV. We've heard there are some additional 3rd party SQL processors, SQL Tools may be used in those as well.
For about a year, we've heard great things from our customers about the one particular SQL Tools View that we include. It is named QSYSERR and it scans QHST for hardware and software errors... they love it! Recently we enhanced SQL Tools by creating a new QIssues View that incorporates most issues people saw with QSYSERR ands all the other stuff you want to know about, including: MSGW, Cache battery, DASD space, DISK failures, hardware failures, tape issues, and so on. That View alone (which wraps several of our SQL Tools Functions) is well worth the price of admission, IMHO.
Since there is no way to market to the IBM i customer base any more (no News/400, Midrange, Q38, etc. with a large subscriber bases) our only option for successfully marketing an awesome product like SQL iQuery or SQL Tools is to partner with IBM directly, and for end-user word-of-mouth. Sadly with in-person events at an all-time low, word-of-mouth isn't likely.
Sadly, there are no processes/programs in place to effectively partner with IBM on IBM i software marketing. Sure, they have a "marketplace" of sorts, but that's more of a billboard than an app store. I have proposed that IBM ship SQL Tools with IBM i, as a chargeable feature/licensed product, and I further suggested that they contact with me to create API-driven SQL functions/procedures while they focus on stuff I can't create—stuff that requires access to internals that isn't exposed to end-developers. But they don't seem interested... yet. I am leaving that offer on the table in case they have a change of heart. (IBM, call me!)
So for now, the future means that the nearly 4 dozen or so SQL iQuery customers and the dozen or so SQL Tools customers will continue to have a great resource/advantage over their competition, while everyone else misses out.
PUB400.com
The good folks over at Pub400 (a free cloud-based IBM i system) allow all their 30k users to utilize both SQL iQuery and SQL Tools for free. We gave Pub400 a site-license for both products so any user of their service can rely on SQL iQuery and SQL Tools being available to them on that server.
For now, you can order SQL iQuery and SQL Tools from my website. I would if I were you but I'm jaded. But for those who read this article and then go on with life as if they didn't, you're contributing to the foundering of this IBM i ship by not engaging. Go ask the boss if you can order SQL iQuery or SQL Tools all they can do is say "no" but maybe they'll say "yes".
That's all there is to it.
IBM i on Power System Administrator. 2024 - Awarded IBM i Advocate, Contributor, Influencer and IBM i Ready badges.
2 年Great explanation! Thank you so much.