Hi,
Did some more testing and find out that it create temporary access path
when I select to output to file.
It does not create temporary access path when output to screen. Strange.
Not at all! You only have to know that there is a different optimization
goal.
When executing a SQL statement interactively with STRSQL or iSeries
Navigator's Run an SQL Script or STRQM it is optimized to return the first
block of the result set as fast as possible (optimization goal = FIRST I/O).
On the other hand when doing an output into a file, it will be optimized to
execute the whole query as fast as possible (optimization goal = ALL I/O).
It's like comparing a bicycle with an airplane.
If you have to overcome a distance of 100 meters, by using the bicycle
you'll reach the goal faster than by using the airplain.
On the other hand if you have to overcome a distance of 100 miles, taking
the airplane makes more sense.
In this way, interactive SQL may use an index access while by writing the
result into a file a table scan will be prefered.
BTW it may be even result in a different behaviour by moving from CQE to
SQE. The creation of a temporary access path (index) is a very expensive
operation, because several table scans must be executed (finding the
distinct keys building the bitmap...). It's the CQE that prefers building a
temporary index, while SQE will use a (single) table scan instead.
But even with SQE under release V5R4 maintained temporary indexes, that can
be used by all jobs and stays existent until the last access plan that uses
this temporary index will disappear from the SQE plan cache.
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-bounces@xxxxxxxxxxxx
[
mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Lim Hock-Chai
Gesendet: Friday, May 11, 2007 17:12
An: Midrange Systems Technical Discussion
Betreff: RE: STRQMQRY and Interactive SQL
Did some more testing and find out that it create temporary access path when
I select to output to file. It does not create temporary access path when
output to screen. Strange.
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[
mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Lim Hock-Chai
Sent: Friday, May 11, 2007 9:41 AM
To: Midrange Systems Technical Discussion
Subject: STRQMQRY and Interactive SQL
I've a sql statement that when I run it in interactive sql, it does not try
to create temporary index file. When I run it using STRQMQRY, it try to
create temporary index. Anybody know why?
--
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.
midrange.com
