Re: SQL statements for physical and logical files

I do not want to restart discussion about DDS and SQL DDL, but fact is:

Even facts may be misleading if they are given more relevance than they
merit.

1. DDS described physical files and SQL tables are NOT identical, but can
be
used in composition with SQL AND native I/O in the same way.

I would question the relevance of this, and would suggest that it is hardly
relevant. They attributes which ARE identical far outweigh the ones which
aren't.

2. SQL tables never have a key (contrary to DDS described physical files).

One could view that as a functional weakness of DDL.

For SQL tables AND DDS described tables primary and unique key constraints
can be defined.

That's a relevant point about DDS and DDL equivalence.

3. When data is written into SQL tables it is checked whether it is valid or

not, when writing data into DDS described tables there is only a minor
check.

The gate metaphor which I shared previously is applicable here. The
validity check prevents non-numeric values to be inserted into numeric
fields, but that breaks code which some shops have lived with for years.

When reading data from SQL tables there is no validation check because the
complete validation check was already done when the data was written.

That "sounds" relevant from a performance perspective. But it's very
misleading.

When reading from DDS described tables, data is checked because there was

only a partial check when writing the data.
In this way reading from SQL tables is faster than reading from DDS tables,
while writing into SQL tables is slower.

The alleged performance improvement has proven to be false.

If we compare how many reads and how many writes we perform ... working with

SQL tables IS FASTER (even if we talk only about nano-seconds)

See the above. The performance of READ and WRITE operations is essentially
the same for tables defined via DDS and DDL. Sometimes it's marginally
faster for DDS defined tables. The alleged validity check on READS does not
degrade performance.

4. DDS described logical files (independent whether keyed or unkeyed) can be

used in an SQL-Statement. The SQL statement will be rewritten based on the
underlying physical file/SQL table and on the DDS description.
Key Information is ignored because the query optimizer decides if and which
access path (key constraints, logical files, indexes) will be used.

That function point in the SQE appears to favor DDS.

5. SQL Views can be used in SQL-Statements (which is a really good idea for
masking complexity and for moving business logic into the database).
SQL Views can also be specified within the F-Specs in RPG, but because
Views
are always unkeyed, their use in composition with native I/O is only
restricted.

Good point.

6. SQL Indexes cannot be specified in an SQL statement because the query

optimizer decides which access path is used.
But SQL indexes can be used in composition with native I/O like any keyed
DDS described logical file. Data can be read, inserted, updated and deleted
using an SQL index in composition with native I/O

A neutral statement.

7. The enhancements in the index definition (derived and sparse indexes)
made in release 6.1. could at first only be used from native I/O. It took
several releases and technology refreshes until SQL itself could profit
from
those new index definition.
SQL indexes are much more powerful than DDS described logical files are.
SQL indexes support almost everything that is possible with DDS with the
exception of JOINs and multi-format logical files.

Regarding the generalization about SQL indexes exceeding the power of DDS
indexes, that doesn't make sense to me. Perhaps an example could clarify
the point.

10. SQL Views and Indexes can be built over DDS described physical files.
DDS described logical files can be created over SQL defined tables.

That speaks to the equivalence of IBM i database files, whether created
with DDS or DDL.

11. DDS tables can be easily converted into SQL tables. The SQL statement
must be generated first with reverse engineering (either IBM i Navigator or
the next ACS version or the stored procedure GENERATE_SQL()).
The CREATE TABLE Statement must be checked and if OK, it can run as CREATE
OR REPLACE TABLE statement.
Data and dependent Objects (such as logical files, views, triggers) are
preserved.
Since the format is not changed, it is not even necessary to recompile the
RPG and Cobol programs.

Again, the fact that DDS and DDL can both be generated from the same
object, support the idea of equivalence.

12. Keyed DDS described logical files (even with SELECT/OMIT clauses) can be

easily converted into SQL indexes. The SQL code can be generated with
Reverse Engineering and either the Option "Create Index instead of View" or
"Create additional index".
The original logical files must be deleted and after the SQL script for
creating the indexes must be executed.
Since the format is not changed either, there is no need to recompile the
programs with native I/O.

Same as above.

13. Why you should use SQL DDL instead of DDS? DDS is stabilized since
release V5R3M0 and there were already enhancements in release V5R1M0 that
are NOT included in DDS.

The IBM i DB team frequently expresses frustration over the lack of
adoption of so call enhancements which often face competition from
alternatives. I prefer storing stream files in the IFS rather than BLOB or
CLOB fields, for example.

My frustration is with seeing the resources that are consumed in efforts to
mass convert DDS to DDL, relative to the benefits received (which are often
overstated, don't materialize, and often create unexpected problems).