CHAIN vs SELECT INTO

Keyword	Parameter(s)	Observations
select	*	“SELECT *” reads all the columns of a table or view. We can just as easily specify individual fields separated by commas. For example: “select vendno, vendname“. This is very similar to RPG, where by default we read all fields and must specify the input record fields if we wish to read a subset.
into	:vendds	Host program variables (referenceable to your RPG program) are distinguished from SQL variables/column names by putting a colon in front of them. This is similar to an op code with a data structure as its result. If you are accessing a master record in order to place some field(s) into a transaction record if and only if the master file row with the specified key exists, then you could do this “select vendname into :transvname where vendno = :transvno“.
from	VENDFILE	Usually we would use the IBM i object naming rules. Then this would be taken as *LIBL/VENDFILE. We could specify LIBRARY/FILE also. We could also opt for SQL naming rules using a period as in LIBRARY.FILE.
where	vendno = :vendno	This is the key list, or as in our example CHAIN, the key parameter. A bit of extra keying but surely no effort to learn. Again this where clause can be very powerful, and often replaces many IF statements that would follow the CHAIN. For example suppose there were many types of vendors and we needed to add transaction fields only for type ‘A’. We would specify “select vendname into :transvname where vendno = :transvno and vendtype = ‘A'”. We should also note that the SQL select will work even without an appropriate index by scanning the table rows.
with	NC	This means no commit control and no lock. It is the default behavior, but it may not be what you want when concurrent access is an issue (i. e., you may not want to read uncommitted rows). Each SQL statement can have its own locking and isolation parameters. If you want exclusive locking you would code “with RR use and keep exclusive locks”. Then you would need to issue an UPDATE, DELETE or INSERT followed by COMMIT or a ROLLBACK. Isn’t it time to use commitment control anyway? Omitting this clause will default the SQL statement to the commitment control and isolation level set previously.
fetch	first row only	Always include this clause for compatibility with CHAIN’s behavior for duplicates. Without the fetch first row only clause and if more than one row satisfies the select, SQL will return the multiple rows returned error, SQLSTATE = ‘21000’ or SQLCODE = -811, and the assignment of variables occurs unpredictably. An order by clause may be required to ensure that the correct row is the first one selected.

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.