Re: sqlrpgle select statements -- RPG400-L

My analysis is pretty crude. I am remote to the system, Ops Nav functionsare notallowed - telnet client only (thru vpn), so i have no access to it's tools.I am writinga history record after each group of sql prepare/fetch trapping the sqlcodand sqlsttwith the actual statement, plus timestamp. Before I put the logical in the"from" eachfile was fractions of a second, then always a 6 to 7 second delay for thisone file.File was not in use (which i don't think matters). Not a complex select, infact allthe 6 files had the same selection (an order# and date range). All the otherfiles 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 ina while

the classic query engine works better?

This is V5R3, ptf group is old 5207 (last June/July) and DB group samevintage.We as developers can scream but operations (outsourced) refuses to keep evenclose to current.Now - everything everyone has said makes sense, including the quote in theSQL atWork book that using a logical "can" make a difference. There seem to besome instances wherethe optimizer does quirky things. And since it is constantly being tinkeredwith, 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, thisdoes bring up the question of why in the world _some_ SELECTs will run ina 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 Idon'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 athttp://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.