Creating access paths (DDS described logical files or even better SQL
indexes) is always a good idea when working with large files in composition
with SQL.
While DDS is an outdated technology you should intent to create SQL indexes
instead, which are much more powerful.
... even though indexes cannot be specified in an SQL statement, they can be
used with native I/O like any keyed logical file.
Specifying a DDS described logical file in a SQL statement is not a good
idea (and never was).
The SQL optimizer will first analyze the DDS description of the logical
file, take the selected columns, join information, select omit/clauses (NOT
the key information) and rewrites the SQL statement based on this
information.
After optimization starts at that time the optimizer does not know that
there was a logical file with a specific key defined.
The query optimizer estimates (all) existing access path (SQL indexes and
logical files), based on the joins, where conditions, group by conditions
(and sometimes even order by clause) and interviews the statistics manager
(which provides information about the data composition). If up to ~15% of
the data of the table are selected the optimizer may use an existing access
path.
If the specified DDS described logical file is taken it is nothing else than
by hazard. (In this way if you need the rows to be returned in a specific
sequence, you need to add an order by clause).
... and even worse before release 7.1 TR 7 or 8 DDS analysis could only be
performed by the CQE optimizer, so the queries could not profit from the
newer SQE.
There is a nice whitepaper about indexing and statitisc strategies written
by Kent Milligan and Mike Cain, which should be learned "by heart".
Here is the link:
https://www-304.ibm.com/partnerworld/wps/servlet/ContentHandler/stg_ast_sys_
wp_db2_i_indexing_methods_strategies
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!"
-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Booth Martin
Gesendet: Thursday, 03.9 2015 22:15
An: Midrange Systems Technical Discussion
Betreff: SQL sort
I am fairly new with SQL so please excuse what are most likely very simple
questions.
I am looking to understand the pros and cons with SQL.
On large files with data that will be used in various applications and in
various sort-by schemes, are logical files a help?
If so, does one reference the physical file and then SQL finds the best
logical, or does it help to specify a specific logical file?
What is the right way to collect a set of records equal to SFLPAGE?
So far, I believe my biggest problem is that I haven't yet figured out how
to ask the right questions.
midrange.com
