Re: SQL statements for physical and logical files -- MIDRANGE-L

Excellent listing of behavior of these things - you and I can have a chat about this at COMMON.

I know I made a statement about equivalence - my comments were pertinent to structure, what are the component objects of each. In that regard, they are very much alike - PFs and tables, etc.

Now as to behavior - that is controlled by the several attributes and methods these objects have - one can find in a DMPOBJ that there is a byte, perhaps, that is set to one value for a PF, to another for a table. Based on that attribute, which is an extension of the inherited data space object, the methods used are either different or behave differently.

So as is often the case in these threads, we speak on different planes and may seem to disagree, seems to me.

Your clear presentation is much appreciated.

C U in a few!
Vern

On 2/14/2017 12:10 AM, Birgitta Hauser wrote:

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

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.
2. SQL tables never have a key (contrary to DDS described physical files).
For SQL tables AND DDS described tables primary and unique key constraints
can be defined.
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.
When reading data from SQL tables there is no validation check because the
complete validation check was already done when the data was written.
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.
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)

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.

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.

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
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.

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

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.

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.

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.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
„Train people well enough so they can leave, treat them well enough so they
don't want to.“ (Richard Branson)

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Nathan Andelin
Sent: Dienstag, 14. Februar 2017 01:35
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: SQL statements for physical and logical files

I meant to say "In regards to data integrity" rather than "In regards to
code integrity".
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD