Don't u just luv her?
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: Tuesday, 14 February 2017 7:11 PM
To: 'Midrange Systems Technical Discussion'
Subject: [IE] RE: SQL statements for physical and logical files
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
--
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
############################################################## This correspondence is for the named person's use only. It may contain confidential or legally privileged information, or both. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this correspondence in error, please immediately delete it from your system and notify the sender. You must not disclose, copy or rely on any part of this correspondence if you are not the intended recipient. Any views expressed in this message are those of the individual sender, except where the sender expressly, and with authority, states them to be the views of Veda. If you need assistance, please contact Veda :- Australia
http://www.veda.com.au/contact-us New Zealand
http://www.veda.co.nz/contact-veda ##############################################################
midrange.com
