CHAIN vs SELECT INTO
August 2, 2016 Chuck Luttor
The average RPG developer can quickly become proficient in replacing RPG database operation codes with their SQL equivalents when undertaking new programming. In each installment of this series, I will visit an op code or set of op codes in order to prove my contention. First up today is CHAIN.
I remember the CHAIN op code from System/3 Model 6 and Model 10 disk days. (Yes, I have been around for a long, long time.) It has been used extensively by every RPG programmer since then. It is the basic op code for random access. In the “old days” it was used extensively to access disk records by relative record number as well as by key. Probably no longer. Let us discuss the merits of keyed disk access via CHAIN vs the merits of the SQL equivalent, SELECT INTO.
Part 1 – File Definition
Explicit definition of files is required by RPG and not required by SQL. In fact, each SQL SELECT INTO can specify its own lock and isolation parameters, which we will review briefly in the clauses of the SELECT statement. If CHAIN(N) and UNLOCK are also used, then this is a wash as far as locking is concerned.
Part 2 – Data Definition
I always define my normalized records with an external data structure so that in debug I can see the whole record with one eval command. But this is not necessary in RPG, and it is necessary in SQL only if we are to conveniently access the row’s data. Otherwise, we must individually specify each column. If we have a 1000-column row, which is quite possible in a view or logical file with extensive joins, then specifying only the columns we want may provide a performance advantage. Beyond a dozen columns or so, I prefer to use a data structure, as my time is valuable. Such a view or logical file could save many CHAINs and SELECTs. Although the logical purists among us will argue that this is a requirement of SQL and not of RPG, I contend that there is no meaningful program that does not require some debugging of input/output, and therefore this data structure is also a requirement in RPG. Part 2 is a push (betting term for a draw) between opponents.
Part 3 – Record or Row Access
We have arrived at the heart of the matter. Compare, if you will, the CHAIN statement at line 124 with the SELECT INTO statement stating at line 127. Clearly the SELECT is more complex and for the unfamiliar will require some learning. Now also consider the effort that is required with CHAIN to accomplish the same things that this SELECT statement is capable of. We will consider each row as shown below.
Part 4 – Exception Handling
For the straightforward found or not found condition, is there really anything to choose from? SQLCODE will be something other than zero for any conditions other than row found and all data transfer successful. SQL does, however, give the programmer access to a wide range of warnings, as shown above.
Exception handling will be another day’s topic. There will be no substitute for familiarizing oneself with the SQL Reference Manual and the SQL Messages and Codes Manual in the same way as the RPG Reference Manual. They are all available online.
Replacing CHAIN With SELECT INTO
I used the IBM i, which was handy for me to generate and test my examples. It is at V7R1 with the latest Technology Refresh level, and my example code is fully free-format. I believe that every RPG programmer will understand the examples, even if they do not yet have access to V7R1 and/or full free-format.
Learning to replace the typical CHAIN is remarkably easy (except for isolation, and that is not easy in any context). I venture to suggest that within the first 20 SELECTs, a considerable majority of programmers will have it in hand.
Chuck Luttor is an RPGILE and SQL programming consultant practicing in the IBM i space for many years in the Toronto and southern Ontario, Canada region. He has extensive expertise in securities brokerage accounting and accounting in general. Major Canadian banks and brokerage houses have been amongst his clients. His main area of interest now is the migration from DDS-defined databases to SQL data definition language defined databases accessed using RPG on the IBM i.
To SQL Or Not To SQL? That Is The Question That Faces Today’s RPG Programmer
SQL has been a standard for relational database management and access across platforms since the 80s, and it has been offered for IBM i for almost 20 years. However it is still not in use by many IBM i shops. This is because DB2/400 was originally released with DDS and it wasn’t until the early 2000s that SQL started to perform better on IBM i.
In the past 15 years, IBM has invested heavily in SQL on IBM i and is incorporating all new advances in the database into SQL. I recently heard Frank Soltis state that almost all database enhancements for the IBM i were made to SQL and that with only a few exceptions, DDS has not been enhanced since 2000. I will take his word for it.
On top of that, IBM provided the Generate Data Definition Language (QSQGNDDL) API to generate the SQL data definition language statements from DDS years ago and there are many complimentary tools available to make this easy.
So why are so many shops still using DDS? After all, how can we as IBM i developers hold our heads high and claim that we are doing the best jobs possible if we are missing out on the last 15 years of database advances that IBM has incorporated into SQL for i? We can be much more productive by using all the great additional capabilities that today’s SQL provides.
As an RPG programmer, I believe that it is not only desirable but necessary to replace both DDS and RPG database access op codes with SQL Data Definition Language (DDL) and SQL input/output statements, at least within new programs. However, they are separate steps within the SQL project. Which is best done first?
DDL redefinition of the existing database in and of itself is useful only for some hardware performance gains. SQL I/O in RPG programs can help programmers be more productive. The latter step should come first because programmers are much more valuable and costly than the hardware. And that step can be accomplished by the programmers themselves without anything more than management’s agreement and a measure of initiative. How many IT projects are that low cost and high return?
To achieve the next step, DDL database redefinition, IBM has kindly arranged DB2 for i so that this can be done without any recompiling of the existing RPG programs. Database management tools are available to automate the tedious job of creating SQL to update and reformat database objects by providing “select the options” GUIs, which provide both documentation/cross reference and promotion tools. Database administrators still not required. However, that topic is for another day.
Send your questions or comments for Chuck to Ted Holt via the IT Jungle Contact page.