Hi,
Birgitta says that if I use LFs I'm condemned to using CQE.
No, I said, you are condemned to using CQE if you SPECIFY a logical file in
a SQL-Statement.
You can have only DDS described physical and logical files (without
select/omit clauses) and your queries can be executed with the SQE.
On the other hand you also can use SQL DDL to create all tables, views and
indexes and DDS only for a few logical files (with select/omit clauses)
and your queries are executed by the CQE.
To avoid DDS for logical files with select/omit clauses, IBM introduced
derived indexes in release 6.1.
Derived indexes can be built over columns that do not exist in the based
tables (for examples Quantity*Price or Upper(MyName) or Year(MyDate)).
Additionally derived indexes contain where-clauses, much like select/omit
clauses.
A SQL index cannot be specified in a SQL statement, but a SQL index can be
used like any keyed DDS described logical file with native I/O.
That means you can specify an SQL index in RPG in the F-Specs and read it
with RPG OPCodes.
In this way almost every DDS described logical file can be replaced with SQL
DDL.
(The only thing you cannot do with a derived index is to join multiple
files).
BTW SQL indexes and views can be built over DDS described logical files.
On the other hand it is also possible to built DDS described logical files
over SQL defined tables.
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: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von David FOXWELL
Gesendet: Monday, 13. October 2008 17:34
An: RPG programming on the AS400 / iSeries
Betreff: RE: AW: SQL versus READ for sequential processing
Whoah up there.
Birgitta says that if I use LFs I'm condamned to useing CQE, and your saying
that even if I change to DDL there may be no difference?
How exactly do I use the database monitor in iNav?
-----Message d'origine-----
De : rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] De
la part de Vern Hamberg
Envoyé : samedi 11 octobre 2008 17:50
À : RPG programming on the AS400 / iSeries
Objet : Re: AW: SQL versus READ for sequential processing
You should run a database monitor to verify which engine ran you query.
You can do this from Navigator quite easily, from the SQL statement
processor. There will be information there about all manner of things,
including which engine. Read the help text to learn what you are looking at
and where to find the information.
Just because a table was created with DDL, there is no guarantee that SQE
will be used to run the query - the way a table is created is NOT one of the
criteria for which engine is used.
Regards
Vern
Åke Olsson wrote:
Not that in my example the table used for the SQL test was created with
SQL Create Table. It should have used the SQE.
It ran slowly anyway.
Med vänlig hälsning / Best regards
Åke Olsson
Pdb DataSystem AB
Box 433 SE 551 16 Jönköping Sweden visit: Brunnsgatan 11
phone: +46 (0)36 342976 mobile: +46 (0)705 482976 fax: +46 (0)36 34 29
29
ake.olsson@xxxxxx www.pdb.se
This e-mail and any attachments may contain confidential and
privileged information. If you are not the intended recipient, please
notify the sender immediately by return e-mail, delete this e-mail and
destroy any copies. Any dissemination or use of this information by a
person other than the intended recipient is unauthorized and may be
illegal.
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
midrange.com
