SQL May Be Catching Up with DDS

Published: June 18, 2008

by Ted Holt

As enthusiastic as I am about SQL, I am not ready to abandon the native DB2-for-i interfaces. There are a few things that DDS and native I/O op codes do that SQL doesn't handle as well. However, in V6R1 IBM has eliminated one of the DDS advantages.

A logical file can do three tasks: it can define a record format to be a set of fields, it can define a data set to be a set of records, and it can create an access path over the data. SQL needs two commands. CREATE VIEW handles the first two tasks, and CREATE INDEX handles the last one. Until V6R1, that is.

Prior to V6R1, CREATE INDEX creates a logical file that contains all fields from the underlying physical file, similar to a logical file in which no field list is given, like the following:

A          R CUSTREC                   PFILE(COW00)
A          K COMPANY                               
A          K CUSTNBR                               

As of V6R1, you can create the index to have one of three field lists: all fields, the key fields only, or key fields plus other fields of your choosing. Specify your choice after giving a name to the record format.

The following SQL command creates an access path over physical file MyTable, using field SomeField as the key. All fields (columns) are included in the record format, which is named MyRec.

create index on MyTable (SomeField) 
   rcdfmt MyRec
   add all columns

Here is the equivalent DDS.

A          R MYREC                      PFILE(MYTABLE)
A          K SOMEFIELD

This SQL command includes only the key fields in the record format.

create index on MyTable (SomeField) 
   rcdfmt MyRec
   add keys only

Here is the equivalent DDS.

A          R MYREC                      PFILE(MYTABLE)
A            SOMEFIELD
A          K SOMEFIELD

And this create command includes the key fields and also fields OneField, TwoField, RedField, and BlueField.

create index on MyTable (SomeField) 
   rcdfmt MyRec
   add OneField, TwoField, RedField, BlueField

Here is the equivalent DDS.

A          R MYREC                      PFILE(MYTABLE)
A            SOMEFIELD
A            ONEFIELD
A            TWOFIELD
A            REDFIELD
A            BLUEFIELD
A          K SOMEFIELD

This new feature will not affect SQL applications. At present, you can't reference an index name in the FROM clause of a SELECT statement, and I doubt that's ever going to change. But native applications will be able to use a subset of fields, a logical-file feature that I have used to advantage many times.

As much as I like SQL, I don't think it's perfect, and I still see plenty of room for improvement. (For further thought, visit http://en.wikipedia.org/wiki/SQL. See Criticisms of SQL and Alternatives to SQL.) If only IBM would give me a way to add an access path to a view. Then I could CHAIN to (read randomly by key) an SQL view from an RPG program. I could use that feature today.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot