As an iSeries developer, you know that SQL is now IBM's sole strategic database definition and access language for DB2 for iSeries. Overall, SQL has substantial advantages over the "traditional" approach of DDS for data definition and HLL built-in I/O operations. SQL is an industry-standard language and is supported by many tools and training resources. Most important, the new DB2 support in OS/400 favors SQL for performance, and SQL offers a wide range of functions unavailable with DDS or HLL I/O.

Of course, some traditional functions are unavailable in SQL (e.g., multimember files), and for some types of database access, traditional I/O is still faster than SQL. Without getting into details, the net situation is that you should generally use SQL where possible to define new database objects and for database access in new applications, keeping an eye out for some exceptional cases where traditional approaches might still be warranted.

The more complex question is how do you begin using SQL with an existing database that has many DDS-defined files and many applications that use HLL I/O? This article provides a concise, fast-paced look at "coexistence" issues that can arise when you use traditional and SQL approaches in the same site.

Preliminaries

When you compare SQL to traditional approaches, it's important to understand that SQL includes both a data definition language (DDL — e.g., the Create Table statement) and a data manipulation language (DML — e.g., the Update statement). SQL also includes what are sometimes referred to as data control statements (e.g., Grant and Revoke), as well as some general-purpose programming statements to define the body of stored procedures, user-defined functions, and triggers.

In contrast, the traditional approaches use several distinct languages — including DDS and HLLs (e.g., RPG IV, ILE Cobol) — that include I/O extensions to support database files, and CL commands, such as those that Figure 1 lists.

Because both traditional and SQL facilities are built on many of the same core OS/400 capabilities, you can also selectively mix and match SQL/400 and traditional techniques. For example, you can use Create Table to create a database table and then use the GrtObjAut (Grant Object Authority) command to control access.

When you consider using SQL, it helps to be aware of functions available with the traditional approach for which no complete SQL alternative exists. The lists in Figure 2a can help you avoid pitfalls and better plan for coexistence. For comparison, Figure 2b offers a list of SQL functions unavailable with traditional database approaches.

Whether a particular item is significant depends, of course, on your application requirements. But overall, I'd say field reference files are the major missing piece in SQL data definition. SQL user-defined types provide some ability to reuse definitions, but they're not quite equivalent to the DDS feature, which is a valuable development (not runtime) aid. Tools, such as iSeries Navigator, that provide a one-time shortcut to define a new SQL column (field) based on an existing column are inadequate, because changes to the referenced column can't be automatically propagated to other columns that were based on the referenced column.

A reasonable alternative when you use SQL is to use a database-design and SQL-code­generation tool that lets you maintain definitions, including a "dictionary" of common column "types" dynamically linked to table definitions. The problem with this solution, of course, is that the tool must also support the full SQL for iSeries syntax.

Another alternative is to use an open-source or homegrown preprocessor that can read SQL table definitions that use a column "type" (e.g., $Currency) and output SQL code with the appropriate column characteristics substituted for the column type. This isn't a hard tool to create, but of course, it would be preferable if IBM solved the problem in a more systematic way. IBM, like it or not, should recognize that the lack of a substitute for field reference files is one of the most significant obstacles to wider adoption of SQL DDL on the iSeries.

Note that the fact that I included access path items in the second list in Figure 2a doesn't mean that SQL can't retrieve rows using select/omit logic or in sequence by absolute value. SQL can access data in these various ways, but the SQL Create Index statement doesn't let you create an index object (i.e., an access path) that has these properties. (Also, see "SQL Access of 'Cloned' Tables and Multimember Files," below, for additional considerations.)

Figure 3 recaps the comparable traditional and SQL database objects. Figure 4a provides a "crib sheet" for developers who are familiar with traditional database interfaces and want to know which techniques to use in SQL. This isn't an exhaustive list. I excluded many of the obvious comparable operations, such as the fact that the SQL Insert statement is comparable to an RPG IV or Cobol Write statement. I tried to list items that, in my experience, programmers frequently ask about. Figure 4b offers a list of traditional alternatives for selected SQL database techniques.

Basic SQL and Traditional Coexistence

Now let's take a closer look at exactly how SQL and traditional approaches can coexist. This question has two sides:

• How can you use SQL with traditional physical and logical files?
• How can you use traditional interfaces with SQL tables, views, and indexes?

I cover each in turn. First, let's look at SQL statements that you can use with traditional database objects, including DDS-defined physical and logical files. Figure 5a lists important SQL DDL statements and the respective database objects that you can reference with them.

In general, SQL DML statements can't access multiformat logical files or DDM files. The Declare Cursor, Delete, Insert, Select Into, Update, and Values statements can all access a program-described or externally described physical file or a single-format logical file. With the OvrDbF (Override with Database File) command, you can also redirect these statements to a different file or member. The Lock Table statement can allocate a program-described or externally described physical file.

Taking up the other side of the issue, in Figure 5b, I've listed important traditional functions and the respective SQL objects that you can reference with them.

As far as data manipulation goes, HLL I/O statements and the OpnQryF (Open Query File) command can reference an SQL table, view, or index. But note that SQL views can be accessed only in arrival sequence because views never have an associated keyed access path.

Also, an HLL program can't access any SQL object that has a column declared as a large object (LOB) type (i.e., BLOB, character large object — CLOB — or double-byte character large object — DBCLOB), a user-defined data type (UDT), or a DataLink. To access a base table or view that has one or more of these column types, you can create a view (over the restricted table or view) to exclude or cast the unsupported column types.

You can use the AlcObj (Allocate Object) and DlcObj (Deallocate Object) commands on tables, views, and indexes. You can also use AlcObj and DlcObj on the corresponding OS/400 objects for most other SQL objects (e.g., the program that's created by a Create Trigger statement).

As you can see, much flexibility exists for using either traditional features or SQL statements to define, control, and manipulate database objects of either type. The most significant runtime "mismatches" between SQL and traditional database approaches fall in three areas:

• SQL doesn't support access of multiformat logical files at all. This might be a concern for old legacy files; however, multiformat logical files were never a good iSeries (or AS/400 or S/38) technique, so getting rid of them and using a better SQL alternative is a good idea.
• SQL doesn't natively support multimember files, although both the SQL Alias statement and the CL OvrDbF command provide workable solutions. If you want to replace a multimember physical file with SQL objects, you generally need one SQL table for each existing member. You must modify your existing code's OvrDbF commands to use the ToFile parameter to redirect to the new tables (files), instead of using the Mbr parameter to redirect to specific members.
• SQL has no direct equivalent to keyed logical files.

The last item is by far the most pervasive and challenging concern that I've seen for organizations that want to move from DDS to SQL. You simply can't replace some keyed logical files with SQL alternatives without having to replace the HLL I/O that accesses the file. I look more closely at this concern in the next section.

Dealing with Keyed Logical Files

Keep in mind that you can use DDS to create keyed logical files over SQL tables, so converting a physical file to an SQL table and creating SQL indexes for the access paths that SQL and traditional I/O share is a viable solution. For more details about the advantages and required steps to exploit this tactic, see "Performance Comparison of DDS-Defined Files and SQL-Defined Files," May 2005, article ID 20067, and "Replacing a DDS Physical File with an SQL Table," May 2005, article ID 20057.

When you want to replace logical files with SQL objects, however, you have to look closely at how candidate logical files are defined. Figure 6 lists different characteristics of logical files and the typical requirements to replace them with SQL objects. Note that all the concerns arise from access by traditional HLL I/O. If you're replacing all programs that access a logical file at the same time that you're replacing the file, you can simply replace the file with an SQL view and use SQL DML to access it.

For the rare instances in which a logical file has no key or select/omit logic and spans only one physical file member, you can generally replace the logical file with an equivalent SQL view and, in your HLL programs, simply treat the view the same as the original logical file (see row A in Figure 6).

Even if the logical file has select/omit logic, as long as it has no key fields and spans only one physical file member (row B), you can use an equivalent SQL view. Be aware, however, that row selection for SQL views is always dynamic, whereas it's common for logical files to have static selection using an associated select/omit access path.

A much more common type of logical file than either of the preceding cases is one that shares the physical file's underlying record format (i.e., the logical file DDS doesn't have any explicit field definitions) and that simply defines a keyed access path (row C). When such a file spans a single physical file member and has no select/omit logic (as is commonly the case), a clever solution is simply to replace the logical file with an SQL index defined over the SQL table that replaces the original physical file. The index should, of course, have the same key specifications as the logical file. Your HLL programs can then simply open the SQL index as if it were a keyed logical file (which, in fact, it is, under the covers).

Unfortunately for sites wanting to migrate their database objects to SQL, many logical files don't fall into any of the preceding categories. For keyed files that have field definitions but no select/omit logic and that span only a single physical file member, some programs might be able to use an SQL index, as I described previously, as long as the program can easily be adapted to simply read the entire record format.

For all the other cases, the only solution is to replace HLL I/O with SQL DML. This, of course, might be a substantial undertaking. Most sites that I've worked with simply leave these logical files in place. (As explained in "Replacing a DDS Physical File with an SQL Table," you might want or need to re-create these logical files after replacing the underlying physical file with an SQL table.)

SQL Is the Future

SQL should be the ultimate goal for iSeries organizations that want to exploit the full functionality and performance of the iSeries database. Nevertheless, in many practical situations, an organization can't move all the database files and HLL applications to SQL in one fell swoop. The information in this article can help you plan the most effective approach to transitioning, including taking advantage of SQL and traditional database coexistence along the way.

Paul Conte is a senior technical editor for iSeries NEWS.