Speaking The SQL Lingua Franca On IBM i

June 3, 2019 Timothy Prickett Morgan

No matter what the job is, we all start out somewhere that is pretty far from being an expert and we depend on our elders and mentors to help us learn all the tricks and get good at the work.

So it is with the nearly ubiquitous database query language, Structured Query Language, or SQL for short. It started out in the head of IBMer Ted Codd back in 1969, which was coincidentally when the System/3 minicomputer launched and its successor many generations later, the System/38 in 1978, was the first IBM system and the first system in the world to implement the SQL language – although Oracle is generally given credit for being first, in 1979, to market with a relational database and SQL language, ahead of IBM’s SQL/DS database for mainframes, which came out in 1981.

While the AS/400 and its successors have always supported SQL queries against databases as well as the native row-based access that for many applications used less compute and therefore ran faster, in recent years SQL use in applications on the IBM i platform has been on the rise. There is more than enough spare computing oomph in a modern Power8 or Power9 server hosting IBM i databases to run SQL and not worry about melting the computer. To get a sense of what is going on with IBM i and SQL, we recently had a chat with Simon Hutchinson, who is the author of the popular RPGPGM.com blog and who also happens to be an expert in SQL on IBM i as well as an IBM Midranger with over three decades of experience who is currently the IT manager at a large manufacturing company running the Infor XA ERP suite. Hutchinson can’t be more precise about his day job, and it doesn’t matter for our purposes. Suffice it to say, as many of you already know, Hutchinson knows his stuff. Which will become immediately obvious.

Timothy Prickett Morgan: What are the what’s the biggest misconceptions that people have about using SQL with the IBM i platform?

Simon Hutchinson: I tend to think a lot of it is not misconception. I think a lot of it is people being ignorant of how to use SQL, and a lot of people are scared of using it. I certainly have found that a lot of people listen to their managers say “goodness gracious, don’t use that. It’s too complicated, no one will understand what it means. We can do everything perfectly well just using RPG.” I have had the experience of people telling me that they’ve used SQL and their superiors have gone in and rewritten their programs behind their backs to remove all SQL statements from it.

TPM: To be fair, I mean there was a time when you certainly didn’t want to let SQL loose with, for instance, complicated table joins on a production machine that was doing transaction processing. It would dim the lights.

Simon Hutchinson: I go back 31 years in the IBM midrange. I am actually pre-AS/400, since I was a System/36 guy. The AS/400s came out in 1988 and I have been using them since 1989 at various companies across the United States and the United Kingdom. I spent a long period as a freelancer in various industries and it has been interesting to see how much stuff has changed. But too many people get comfortable just doing things the same old way they have always done things. And to me, part of the problem with the IBM i and AS/400 platform is you can sort of do things the way you were doing them in 1988 and it works. It works, you can do exactly all the same things and you get results. Too many places are not looking to get the most bang for the buck out of them machine and they’re just happy that it works.

TPM: They leave a lot of features on the table if they don’t use them, that’s for sure. Which is why I think it is ludicrous when people talk about not wanting to focus on feeds and speeds and features in a system. This is how you make money, so it matters.

Simon Hutchinson: It is a great shame to think of all that IBM i shops could do. I think since IBM i 7.1 and up, it’s really been an exciting time — all the features that IBM has added, all the open source languages, all the RDi tools — it has made it an operating system that’s second to none. And it’s a shame to see so many people missing out on that.

TPM: What do you think the penetration of SQL usage is in the IBM i base at this point? There are shops, I presume, that are just doing SQL only at this point, but for the rest of them I know that most of them have the SQL tools on the machines, but are they really using it as a development language or just for selected queries?

Simon Hutchinson: All of my information is anecdotal on this, but there are a few companies that do everything, 100 percent, with SQL tables and indexes and views and they have gotten rid of all of their DDS. I would say the majority of the IBM i users do not have that luxury. If your company has spent a fortune to buy a large ERP application, you’re not gonna throw that out to replace all of its DDS files with SQL tables, views, indexes, etc. So a lot of people are just trying to work out can they use to the SQL on the Db2 functionality with the existing structure they have. And of course the answer is: Yes they can do this. I do that every day with my work. We are a big Infor XA user. I do all my file I/O, and probably about 80 percent of my work, using SQL these days. It works very well for that.

But you are right. In the old days, when I first ran across SQL – I think it was OS/400 Version 2.1 – you could kick off at SQL statements and it would take the system down to its knees. I remember being shouted out by various managers for doing that. But certainly things have got a lot better and that’s another misconception to do with a lot of the old rules that IBM told you that you had to do to get maximum efficiency no longer apply. As long as they don’t hurt you, you can certainly do things a lot better in a more modern way. I remember being told by IBM if you don’t have a logical file built on the key sequence you want, build one. Of course, they are saying today don’t do that. let it run and check the index advisor and if you find that advises you to build that index, build it, and if not, just let it be.

TPM: But they didn’t have that index advisor in the past.

Simon Hutchinson: Certainly not. I think with IBM i, there’s a layer that is essentially the same as the AS/400, but I think a lot of what happens under the hood is very different. With the move to the Power Systems, I would not be surprised to find out that the SQL calls are actually calls to Unix-type APIs, which will work a lot better on the RISC chipsets that are inside the machine, But again, that’s just my opinion. I don’t know if that is official.

But I have noticed things at work. We have large amounts of data and I’ve noticed that just running version 7.2, I can see a noticeable difference in inputting data using a SQL versus the old way of doing things like this.

TPM: What do you what do you think the difference is? What kind of performance difference are we talking about?

Simon Hutchinson: We’ve got some pretty horrendous old code and I’ve actually been fortunate enough to rework some of it. It was written in RPG III, heaven forbid, and now has a more modern approach. You can see things take a matter of not even a second compared to 10 seconds to 15 seconds to load screens and things like this. With most of the work being done using SQL views, etc.

TPM: That’s pretty good. Do you have any hesitancy with SQL? Are there places where you still have to worry, where you have to think twice about it?

Simon Hutchinson: I think that like most things, you need to be sensible. I’m sure anybody can write an SQL statement that can take a system down to its knees. But I think nowadays with all the controls of the Db2 engine that you’d have to do something pretty extraordinary. I’ve certainly joined some enormous tables together and done summarization on them and not crashed the system. I’m sure there must be something – I know there used to be talk of there being a query governor within the operating system. I’m sure Db2 for i is using something to stop me from doing that.

TPM: They wouldn’t want to take the machine down. Obviously, in a modern world connected to everyone through the Internet and wanting instant response time, this would be very, very bad.

Simon Hutchinson: Next to my office, on my right side, is the person who does everything with Windows Server, I hear his fraught exclamations when the SQL Server goes down. I have never even had that happen with Db2 on the IBM i, but he seems to reboot every other day.

TPM: Well good for him. It hurts.

Simon Hutchinson: Oh it does. It does. The company actually looked at migrating all kinds of applications off IBM i and onto SQL Server, and after doing various tests on which I was fortunately involved with two, we managed to prove that this would be an enormous mistake. There’s no way we could serve data we were doing now using that platform. When you have almost a thousand users, that makes that enormous difference.

TPM: Do you think that more and more people are going to be migrants migrating to SQL? I assume they will. There are all kinds of modernization efforts underway and tools to support this. SQL is a standard and it is very tough to beat. If you look at some of the survey data I’ve seen there is a very large number of people that claim to be using SQL. But I always wondered if they are just doing some queries or are they really using it as a development language. There could be two, or four, or six different classes of SQL users, with various gradations, for all I know. What does it look like?

Simon Hutchinson: In my experience, I found that we have all kind of ranges. There are small group that I know which do 100 percent and do everything in SQL. They have very little native database access, and all of their databases have been rewritten to be truly Db2 schemas as opposed to libraries and so on. And then all the way on the other side, there are companies that are still dipping their toes into the SQL world. They are the ones I hear a lot from and they ask me a lot of questions: What is the most efficient way of doing this? What do we need to be aware of? I don’t think there is a business case yet for companies to go ahead and spend a fortune to replace the ERP applications they have that use DDS files with SQL. I recommend to everybody, if you are writing new stuff, new programs with new ways of doing things, to incorporate as much as SQL into your programs, tables, whatever as possible. I’m sure as time goes on and in later releases we’re going to see some things going even faster and faster and having that much more flexibility using SQL.

TPM: How hard is it to teach the old RPG dog the new SQL tricks?

Simon Hutchinson: As I was saying before, that’s actually in some ways one of the problems we have with this platform. You can write code in 1988 and it works. And I think there are lots of people still doing that. It’s still a battle. I think that is being fought by companies to move away from writing RPG III to even RPG IV let alone for things like RPG free format. I spend a lot of time explaining to people that moving away from RPG III and RPG IV and ILE, I have certainly mind blown a lot of people getting their heads around that. Then once you’ve got that concept in place, now move on to replacing your database I/O with SQL functions.

TPM: It sounds like a very large undertaking for people, but do the benefits outweigh the effort?

Simon Hutchinson: SQL is something with which I think baby steps are really worthwhile. Ask yourself this: What is truly modernization? Yes, we can all say a truly modern application has a web front end and has a Db2-style backend. But I think modernization can mean many different things for many different people. Certain people may be modernizing their code from RPG III to RPG IV. It may be taking it from there and putting SQL in the database access, and if not for that then certainly building views and indexes to help make things run faster. I tend to think that modernization is one of these wonderful brushes that I think people tar many things with and a lot of people feel guilty they’re not doing things as cool as they hear of in the press or that other people are doing. But the point is to start.

Editor’s Note: On June 20 at 2 p.m. Eastern, Simon Hutchinson will be hosting a webinar along with New Generation Software on using SQL Views to simplify queries and reporting on the Db2 for i database. You can register at this link. Hutchinson is not a user of NGS software, but one who is keenly aware of the value of SQL views and, as it turns out, so is NGS, which sells analytics tools that ride atop Db2 for i. Here is how Langston explains the situation, and I am quoting him directly from email because he said it better than I can:

Ever since companies began storing data in databases, enterprising business people have wanted to see and analyze that data. The problem has always been that the people who know what they need to see and analyze rarely understand the layout of the enterprise database. The people who know the layout of the database rarely have the depth of business and industry knowledge needed to present the data in the most useful ways.

After all these years, we still routinely talk to companies where the business people say they love all of the functions and output formats we offer, but what good are they because they don’t understand their database. The folks in IT are too busy with other projects and don’t understand what the users need to do with the data anyway, so they just use programs or run SQL scripts to transfer raw data extracts and Excel files to the business folks, and hope they don’t ask for more.

Some products in the market attempt to attack the challenge of database complexity through a metadata layer or catalog. But an application-specific metadata catalog is by definition, application-specific. You put time into defining the metadata, but it’s only helpful within that application. 

Starting in the 1990s, some companies built data warehouses to overcome this problem. Copy, extract, cleanse, aggregate, summarize, transfer, and voila, you can have a database simple enough for even your sales and marketing folks to understand. Of course, those data warehouses were expensive to build, labor-intensive to maintain, and at best, a representation of history – not helpful for real-time decision making or forward-looking analysis.

This is where SQL Views can play a great role. A staff member who knows the database can create SQL Views that perform complex table joins and other data manipulation functions. His/her work ends there. Business users can build queries over these SQL Views. They don’t need to know, see, or understand what clever things the SQL View is doing to the data each time their query runs. It should run fast too because hopefully the person creating the SQL View took the time to optimize for performance. Suddenly, a complex, real-time enterprise database looks like a simple, flat file to the business user. You give a logistics manager, controller, marketing analyst, etc. data that way and they can hit high speeds as they add their personal touches to turn the data into a report, visualization or dashboard. And you might discover you can use those same SQL Views in other applications that need to access the same data.

SQL Views have been available for several IBM i OS releases, but most shops are still unaware or just beginning to grasp their potential.

So perhaps listening in to Hutchinson’s webinar is a good place to start if you are an SQL newbie.