Re: sqlrpgle select statements

["Jim Franz" <jfranz@xxxxxxxxxxxx>]

My analysis is pretty crude. I am remote to the system, Ops Nav functions 
are not
allowed - telnet client only (thru vpn), so i have no access to it's tools. 
I am writing
a history record after each group of sql prepare/fetch trapping the sqlcod 
and sqlstt
with the actual statement, plus timestamp. Before I put the logical in the 
"from" each
file was fractions of a second, then always a 6 to 7 second delay for this 
one file.
File was not in use (which i don't think matters). Not a complex select, in 
fact all
the 6 files had the same selection (an order# and date range). All the other 
files had
a dds logical file with 1st key = order#, except this problem file.
So I created the logical for that file, and again the delay.....
So I changed select "from" to the new logical file and now delay is gone 
....
> From 6 second delay consistently, to zero delay consistently. That 6 second 
delay
for each order# was costing me hours of processing every time it ran.
May not fit the rules,,,but go figure...or is it possible that every once in 
a while
the classic query engine works better?
This is V5R3, ptf group is old 5207 (last June/July) and DB group same 
vintage.
We as developers can scream but operations (outsourced) refuses to keep even 
close to current.
Now - everything everyone has said makes sense, including the quote in the 
SQL at
Work book that using a logical "can" make a difference. There seem to be 
some instances where
the optimizer does quirky things. And since it is constantly being tinkered 
with, I'm not
sure my solution will last.
jim franz


----- Original Message ----- 
From: <qsrvbas@xxxxxxxxxxxx>
To: <rpg400-l@xxxxxxxxxxxx>
Sent: Wednesday, March 01, 2006 8:26 PM
Subject: RE: sqlrpgle select statements


> rpg400-l-request@xxxxxxxxxxxx wrote:
>
> >   3. AW: sqlrpgle select statements (HauserSSS)
> >
> > 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.
>
>
> Although I won't recommend specifying the LF name in a SELECT/FROM, this 
> does bring up the question of why in the world _some_ SELECTs will run in 
> a fraction of the time when an LF is named instead of the PF.
>
> I would expect that the answer would be found within DB2 group PTFs, but I 
> don't really know.
>
> Tom Liotta
>
> --
> Tom Liotta
> The PowerTech Group, Inc.
> 19426 68th Avenue South
> Kent, WA 98032
> Phone  253-872-7788 x313
> Fax    253-872-7904
> http://www.powertech.com
>
>
> __________________________________________________________________
> Switch to Netscape Internet Service.
> As low as $9.95 a month -- Sign up today at 
> http://isp.netscape.com/register
>
> Netscape. Just the Net You Need.
>
> New! Netscape Toolbar for Internet Explorer
> Search from anywhere on the Web and block those annoying pop-ups.
> Download now at http://channels.netscape.com/ns/search/install.jsp
> --
> 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.