AW: sqlrpgle select statements

Hi,

Specifying a logical file in an SELECT-Statement will not force the query
optimizer to use the access path of the logical file. The query optimizer
only takes the field selections, join information and select/omit clauses
from the logical file. Then the Select-Statement gets rewritten with this
information and the physical file. After the query optimizer checks ALL
available access path (keyed logical files and SQL indexes) on the
appropriate physical files (tables) to find the optimal access path.
If the access path of the specified logical file is used, it's nothing more
than by hazard.

You should NEVER use a logical file in an SELECT-Statement!
The query dispatcher that decices which query engine will be used, will
reroute all SQL-Statements that use a logical file to the Classic Query
Engine (CQE). This rerouting will cost up to 10% of performance.

Further each SQL statement must be checked and optimized.
STRDBG will show if an new index is needed.
The iSeries Navigator tools (Visual explain) provide even better analysis.
In visual explain also an index advisor is integrated.

To get a optimal performance, you should read the following white paper:
"Indexing and statistics strategies for DB2 UDB for iSeries"
http://www-03.ibm.com/servers/enable/site/education/abstracts/indxng_abs.htm
l

"Query performance and query optimization" also provides a lot of tips how
to gain performance.
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/rzajq
/rzajqkickoff.htm


SQL is faster if it's optimized and a bunch of records can be read and
returned at time.
Native I/O is faster if only a single record gets read.

By the way, it would be better to create an SQL index instead of a keyed
logical files.
Because a DDS-described logical file has a page size of 8K besides a SQL
index has a page size of 64K.
An SQL-index can be specified in the F-Specs and used in RPG like any other
logical file.

Mit freundlichen Gruessen / Best regards

Birgitta

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les Brown)

-----Ursprungliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von
Matt.Haas@xxxxxxxxxxx
Gesendet: Mittwoch, 1. Marz 2006 21:18
An: rpg400-l@xxxxxxxxxxxx
Betreff: RE: sqlrpgle select statements


Jim,

You should not need to specify the logical file name in the query. The
query optimizer should be able to figure out the best logical to use.
I've run into cases where logicals that exactly matched the where and
order by clauses were not used. Compiling the programs with
ALWCPYDTA(*YES) took care of the problem.

Matt

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Jim Franz
Sent: Wednesday, March 01, 2006 3:09 PM
To: RPG400-L@xxxxxxxxxxxx
Subject: sqlrpgle select statements

I had an pgm running too long & found that one of 7 different
select statements was scanning the whole file. The fields in the
where clause were always the same, so i created a logical. Ran again
and was not any faster. Then remembered a comment in SQL at Work book
that
using the logical name in the select statement would work better, and it
did.

The other 6 files have an existing logical where
at least the first field in the where clause is the same as logical key,

so should i change the select statement to use the best logical name,
or leave them alone.
I'm not sure if to only specify a lf name when there is a problem or
always specify?

jim franz

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


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