Re: Road Map to move a home grown application from RPG to DB2 SQL or to any other data base platform

DDS described files and SQL defined tables and indexes are NOT identical
even though they may be used in the same way.

No one said that they were precisely identical. In fact, Vern indicated
that there was a flag in the data object (i.e. PF), which indicated whether
it was created with SQL DDL or DDS. The point is that they are "essentially
identical", or identical in regards to essential elements.

The problem with drawing distinctions between PF and LF data objects,
whether they're created with DDS or SQL DDL, is that too many people
imagine wide differences. You hear people claim that DDS files are ISAM
files, or DDS files are FLAT files, while data objects that were created
with SQL DDL are true tables and indexes, as though there were relevant
differences.

Too many people believe that there are two separate file systems on IBM i,
namely the DDS file system, and the SQL file system. One really common myth
is that files created with SQL DDL perform better than files created with
DDS. Some members of the IBM DB2 for i Lab Services Team have nurtured that
myth.

1. physical files and SQL tables. There minor architectonical differences.
Just try to copy invalid numeric data from a flat file into a DDS described
physical file with CPYF *NOCHK. All data is copied into the DDS described
physical file
Now try to execute the same CPYF *NOCHK with an SQL defined table. The CPYF
fails and stops with the first invalid data.
When writing into a DDS described file there is no check whether the data
is
valid or not. The validation occurs as soon as the data is read.
When writing into an SQL defined table data is checked an invalid data is
rejected. But there is no check anymore when reading from the SQL described

Dan Cruikshank cited that example in the following article:

http://ibm.co/2j5Lgho

I found the example to be rather contrived because it expressly tries to
force bad data into PFs, and it relies on the behavior of the CPYF utility.
That type of data corruption went away after people began using externally
described files, as opposed to "flat files" and internal descriptions.

2. SQL Indexes can be specified in RPG within the F-Specs and used like any

keyed logical file. Bun an SQL index cannot be specified in SQL statement.

There you're referring to differences between the SQE and RLA rather than
relevant distinctions between PF and LF data objects.

An SQL index is always built over a single table or physical file. Within
logical files multiple tables can be joined and joined logical files
additionally can have a key.

There you're referring to differences between SQL DDL and DDS rather than
relevant distinctions between PF and LF data objects.

SQL views are always unkeyed, but can include everything that is possible

with an SELECT-Statement except they cannot include an ORDER BY clause.
SQL views can be specified within the F-Specs in RPG, but because they do
not have any keys, their use is only restricted.

There you're referring to and affirming my previous point that SQL views
can be accessed via RPG RLA.

BTW beginning with release 7.2. all data access including QUERY/400,

OPNQRYF, native I/O is executed with the SQL Query Engine.

In regards to QUERY/400 and OPNQRYF using the SQE, that's progress. I
remember the announcement.

In regards to native I/O using SQE, I'm not sure what you're referring to.

Note that Vern's point, and my point pertain to PF and Indexes being
essentially identical. SQL Views and Materialized Query Tables obviously
don't have precise counterparts under DDS.