By Chuck Luttor, IBM i Software Developer at Able-One Systems inc.
SQL has been a standard for relational database management and access since the 80’s across platforms, 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 2000’s 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.
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 DB advances which 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 aside I recently heard Frank Soltis state that almost all DB enhancements for the 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.
As an RPG programmer, I believe that it is not only desirable but necessary to replace both DDS and RPGLE op codes with SQL Data Definition Language (DDL) and SQL input/output statements. 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 RPGLE programs can help programmers be more productive. So the latter step should come first because they 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 the i’s Database so that this can be done without any recompiling of the existing RPGLE programs. DB management tools are available to automate the tedious job of creating SQL to update and reformat DB objects by providing “select the options” GUIs which provide both documentation/cross reference and promotion tools. DB administrators still not required. However, that topic is for another blog.
My Plan
It will be my task in this blog to demonstrate beyond any reasonable doubt that the average RPGLE developer can quickly become proficient in replacing RPGLE DB operation codes with their SQL equivalents when new programming is undertaken.
In each installment of this blog I will visit an op code or set of op codes in order to prove my contention. 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 RPGLE programmer will understand the examples even if they do not yet have access to V7R1 and/or full free-format.
CHAIN vs SELECT INTO
First up today is CHAIN. I remember this 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 RPGLE 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 its keyed disk access merits vs the merits of its SQL equivalent, SELECT INTO.
Part 1 - File Definition (see part1 in col. 1 at line 000105 of example 1)
Explicit definition of files is required by RPGLE 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 SELECT’s clauses. If CHAIN(N) and UNLOCK are also used then this is a wash as far as locking is concerned.
Part 2 - Data Definition (see part2 in col. 1 at line 000109 of example 1)
I, for one, always define my normalized records with an external data structure, that way in debug I can see the whole record with one eval command. But this is not necessary in RPGLE and is necessary in SQL if we are to conveniently access the row’s data. Otherwise, we must individually specify each column. Now if we have a 1000 column row which is quite possible in a VIEW or LF with extensive joins then it may well be to our program’s performance advantage to specify only the columns we want if they are few enough. Beyond a dozen columns or so, I say use a data structure, my time is valuable. Now such a VIEW or LF could save many CHAINs and SELECTs.
Although the logical purists among will argue that this is a requirement of SQL and not of RPGLE, I say that there is no meaningful program which does not require some debugging of input/output and therefore this DS is also a requirement in RPGLE. I say part2 is a push (betting term for a draw) between opponents.
Part 3 – Record or Row Access (see part3 in col. 1 at line 000121 of example 1)
We have arrived at the heart of the matter. Compare, if you will, the CHAIN statement at line 122 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.
|
Keyword |
Parameter(s) |
Observations |
|
select |
* |
I think we can all learn immediately that “select *” reads all the columns of a TABLE or VIEW and that we can specify individual fields separated by commas, just as easily. For example: “select vendno, vendname”. This is very similar to RPGLE 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 are distinguished from SQL variables/column names by putting a semi-colon in front of them. Not at all different however than an op code with a data structure as its result. But consider, 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 location rules. Then this would be taken as *LIBL/VENDFILE. We could specify LIBRARY/FILE also. We could opt for SQL rules but that is unlikely. |
|
where |
vendno = :vendno |
This is the KLIST 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. Many IF statements following the CHAIN can sometimes be replaced. For example, suppose there were many types of vendors and we needed to add transaction fields only for type ‘A’. Consider “select vendname into :transvname where vendno = :transvno and vendtype = ‘A’”. But keeping it simple the where clause is really the equivalent of an IF TRUE statement. |
|
with |
NC |
Simply, this means no commit control and no lock. It is the default but may not be what you want when concurrent access is an issue (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? Finally, omitting this clause will default the SQL statement to the commitment control and isolation level set previously. We will deal with isolation, row locking and commitment control in another installment as it is too extensive a topic to adequately discuss here. |
|
fetch |
first row only |
Always include this clause for compatibility with CHAIN’s behaviour for duplicates. You can omit this clause if you want to check that the SELECT returns one and only one row. The multiple rows returned error, SQLSTATE = ‘21000’ or SQLCODE=-811, assigns variables unpredictably. |
Part 4 – Exception Handling (see part4 in col. 1 at line 000134 of example 1)
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 RPGLE Reference Manual. They are all available on-line.
Replacing CHAIN with SELECT INTO
In conclusion, the learning required to replace the typical CHAIN is remarkably easy (except for isolation and that is not easy in any context) and I venture to suggest that within the first 20 SELECTs a considerable majority of programmers will have it in hand.
I have kept it simple by considering only the CHAIN equivalence. If we had considered all the other capabilities of a SELECT INTO statement this article would be both orders of magnitude longer and so confusing that no one would want to convert.
Stay tuned for our next blog post, when we discuss SETLL and READE vs DECLARE CURSOR and FETCH.
If your company needs assistance with deciding on an SQL vs no SQL approach, contact us today for a free consultation.
