It's interesting that something like Open Query File (OPNQRYF) has become somewhat of an icon under OS/400. Back in the days of the System/38 (that was the predecessor to the AS/400 and iSeries for those of you whose world began with AS/400), we lived with "just" logical files and RPG. Then, IBM decided to put a command interface (CL command) around the dynamic database component. The underlying code was being used to "test" the database, according to the author of OPNQRYF.

The author, George Timms of IBM Rochester, did a fantastic job of providing the System/38--and subsequently the AS/400 and iSeries--programmers with an invaluable tool. I remember seeing him receive several standing ovations at COMMON the year IBM announced the OPNQRYF command. Effectively, OPNQRYF killed the sort utility on this platform.

But something else was going on parallel with OPNQRYF. A Midwestern company was busy creating the first implementation of SQL under OS/400 (called CPF at the time). Interestingly, that SQL product, SEQUEL from Advanced Systems Concepts (ASC) of Schaumburg, Illinois, actually used the same underlying code as IBM's OPNQRYF.

Certainly, OPNQRYF followed the traditional CL syntax convention and has been enhanced over the years to provide even more functionality. But SQL was and is a multi-platform standard. A few years later, IBM shipped SQL for the System/38, and it has been a mainstream application development tool ever since.

SQL did not become an overnight success. Unlike OPNQRYF, SQL took years to get a foothold in the OS/400 market. With SQL, you could generate reports, update files, delete records, create files, order files and so forth, but you could not use IBM's version of SQL like OPNQRYF. So, OPNQRYF became the de facto standard.

But the ASC version of SQL included a little-known command: Open SQL File (OPNSQLF). In short, this command eliminated the need to learn the complex syntax of OPNQRYF by allowing you to learn industry-standard SQL and put it to work in your CL programs, effectively replacing OPNQRYF. Since both tools use the same underlying engine, there was little, if any, difference in performance.

Today, tens of thousands of AS/400 and iSeries shops use OPNSQLF with ASC's Sequel product. That's the good news. The bad news is there are probably hundreds of thousands of shops that use OPNQRYF file. Why is that bad news? Well, we're getting a new generation of programmers entering our market, and they will not be familiar with all those OPNQRYF statements. The new programmers tend to be familiar with SQL syntax, which means that the applications taking advantage of OPNSQLF or SQL in general will cause fewer issues for these new developers than the applications that use OPNQRYF.

The problem with the OPNQRYF command is the syntax. It is too complex. Sure, the prompter helps, but I find very few developers (although there are some) who would actually attempt to modify an existing OPNQRYF command. They don't touch them for fear of breaking the code. The interesting thing is, OS/400 developers originally thought SQL was too complex and thus avoided it in favor of logical views and OPNQRYF. Today logical views are still wonderful, but we really need to consider moving away from OPNQRYF--it just won't be maintainable in the future.

Replacing Everything with SQL

A couple of years ago, I had the privilege of helping design a database from scratch. I provided the design requirements for the database. The database administrator (DBA) created the files. When I asked to see the DDS for the files, the DBA said, "There isn't any." Needless to say, I was flabbergasted until he said that he created the files using a design tool that generated OS/400-compatible SQL statements. These design tools (and there are several) allow you to create a database using a graphical entity relationship modeling tool such as ERwin from Computer Associates. Then, you create the database relationships by connecting the tables (i.e., files) via their key fields, and then you specify the field properties. When you're ready, you press the Build-on-host button and--voila--your files are created. Physical files are created as SQL tables; logical files are SQL views.

As an OS/400 developer, the thing I initially didn't like was the fact that the DDS was not generated. I've always felt that DDS was easy to read, and modifying things like default values, column headings, and attributes are easiest with DDS. On the other hand, you can review the generated SQL CREATE TABLE and CREATE VIEW statements, which contain similar information. So now my thinking is that perhaps it's time to stop using DDS for database definitions.

Eliminating DDS

SQL provides the ability to create any type of database file. As mentioned, the SQL CREATE TABLE command is used to create a physical file. The syntax for CREATE TABLE is straightforward. But the cool thing is that you can also use SQL to change a physical file's definition. The SQL ALTER TABLE command allows you to actually change the attributes of a given column (field) in the database, while ALTER VIEW allows you to change the attributes of a logical view.

Today, there is little reason, shy of legacy habits, to continue to use DDS at all for database definitions. Perhaps the one thing DDS has over SQL--and this is a huge issue--is field referencing. SQL doesn't really have field referencing, but if you use an entity relationship tool, you can obtain similar functionality. I typically use field referencing for large, new projects, but of late I've not really liked looking at DDS and seeing a bunch of REFFLD keywords when I really wanted to see the data. That's why I wrote the Work with File Field Description (WRKFFD) command. That command displays a list of fields and their attributes for any database file. I will be posting the source for the WRKFFD command in the forum associated with this article in a few weeks.

Here's an example of DDS and the corresponding SQL statements needed to create the same file:

This same DDS would then need the CRTPF command to create the database file. In SQL, the data definition and creation statements are one in the same, as follows:

In order to set the COLHDG keyword another SQL statement (COMMENT ON) would need to be used. To insert the column headings, the following SQL statement would be used:

Again, if you use a tool like Operation Navigator or ERwin, the tool generates these statements for you so that you key the data definitions in only once.

Call to Action

What I recommend is that you go out and buy A Guide to SQL Standard by C.J. Date. It contains just about everything you need to know about SQL. Then, I would strongly suggest that you move away from using OPNQRYF for any more development. If necessary, visit the ASC Web site and find out more about the SEQUEL product and the OPNSQLF command.

Then, I would consider using SQL to create your future database definitions. Try out one of the entity relationship tools, but be aware that the good ones are very expensive, into several thousand dollars for one PC license. But if you know SQL, you can simply type in the SQL syntax or use the prompter in IBM's SQL/400 product (whatever its name is this month) or use the one in SEQUEL. Both are very capable.