Using SQL on AS400, iSeries and IBM i systems

Structured Query Language

Some places are still avoiding SQL

Simple Example

If you’re one of these, you need to start looking seriously at SQL. SQL is playing a bigger role in both the definition and manipulation of the database. It is now very common to see embedded SQL. Have a look at the Run SQL Scripts feature of the Database function in iSeries Navigator.

DB2 SQL has a couple of quirks specific to that DataBase but on the whole, it is portable across platforms and easy to understand because that’s what everyone is doing.

I’m not the only supporter of SQL in IBM i RPGLE programs. If you are new to this concept here is a good resource:

“You’ve heard about embedded SQL and maybe you’ve tried embedded SQL, but you may be asking yourself, “Why would I use embedded SQL?” That’s the course that I have taken. Of course, when a new capability comes out, I am usually eager to start digging into it and figuring out how it works. And I have done that with SQL, but I couldn’t easily find a reason to justify its use until recently

Dynamic File Source

When I started developing with SQL, I immediately found a strong reason to use it: the dynamic usage of files within an RPG program without the need for F-specs. We had an existing program that was screaming for this capability because we were legally obligated to use physically separated files that would not be updated once they were archived out of the current year’s updateable file.

At the end of each year, we would copy the currently used file into a new physical file that would have the year included in the name. For example, the file for the current year would be named EMPLOYEE, and at the closing of the 2010 year, the data from 2010 would be copied into a newly created file named EMPL2010 and the current EMPLOYEE file would be cleared to prepare for the new data in 2011.

This caused the yearly process to also include program maintenance to add the newly created file to the F-specs and make any necessary changes to use the files within the historical programs.

So, to support this without the yearly maintenance, I rewrote the historical applications to use embedded SQL. The program was able to dynamically determine the file name within the application, and it was able to determine the appropriate file name because I implemented a naming convention for all the historical data.


Then I discovered EXTFILE. Using the EXTFILE keyword in your file specification allows you to specify the name of the file to be referenced at compile time, but it doesn’t specify the file until run time. I wrote an article awhile back, “Reusing a Single File Description on Multiple Files,” that shows you how to do this. So that alternative shot my justification for embedded SQL out of the water. I thought I was using SQL for something that RPG alone couldn’t do, and then I found out that wasn’t the case.

Dynamic Data Sorting

Even though embedded SQL lost the title for doing the impossible with RPG alone, I still found another good reason for using embedded SQL. You can specify the way that the files are sorted during run time.  And you can do this without the need to create a logical file. Not only that, you can also dynamically specify the way it is sorted within the program; so you’re eliminating multiple logicals.  With these capabilities, I put dynamic files back on the list for reasons that I would use it. And then there are unions and joins and grouping too!


The next thing I was looking for was performance. I thought there was a different mechanism that was automatically used when I coded in SQL, but that’s not true. You don’t get better performance simply by using SQL coding style. You get the performance increase in the way that you create the files, which I’ll discuss in an upcoming article.”