RE: SQL delete based on aggregate function?

In recent releases there are 2 optimizers. The classic, older one, is not very 
good at some of this stuff and results in this kind of performance. The newer 
one CAN do a better job and is where most of the optimization improvement is 
happening. Now to figure out which one was used, you need to us the database 
monitor. You can use STRDBMON on a green screen. Or use a Navigator monitor - 
these run DBMON for you. I don't know whether they report which optimizer is 
used. I forget which column in which row type has this info - QQ014 in a 1000 
record, maybe?

Now there are certain kinds of statements that will not use the new optimizer. 
There is some kind of info APAR out there that explains some of this - details 
are not with me - maybe go to www.iseries.ibm.com/db2 and dig around.

HTH
Vern


-------------- Original message -------------- 

> >The SQL optimizer recognizes that the subquery never changes... 
> 
> That _should_ be the case. The inner select is a non-correlated subquery 
> and as such should NOT be rerun every time. However, the performance I 
> get when I do run such a query seems to indicate otherwise, at least on 
> a V5R2 box. 
> 
> As a test I tried the following statement on a 270 w/5.2 doing nothing 
> else: 
> 
> select sum(latitude) from zip where state in (select state from zip 
> group by state having count(*) > 1000) 
> 
> Which should return a sum of the latitudes (a useless value, but forces 
> sql to read each record in the resultset). Zip has just over 800,000 
> rows (Canadian zips too) 
> 
> As far as I can tell that is a NON-Correlated subquery. However, I tried 
> running the query through visual explain and while I killed the query 
> before it ended, I did see this in the joblog: 
> 
> Message ID . . . . . . : CPI4325 
> Message . . . . : Temporary result file built for query. 
> Cause . . . . . : A temporary result file was created to contain the 
> results 
> of the query for reason code 11. This process took 0 minutes and 1.3 
> ... 
> 11 - The query optimizer creates a run-time temporary file in order t 
> implement certain correlated group by queries. 
> 
> FWIW, I ran the same query on SQLServer and got the answer in 3 seconds. 
> 
> 
> -Walden 
> 
> 
> ------------ 
> Walden H Leverich III 
> President & CEO 
> Tech Software 
> (516) 627-3800 x11 
> WaldenL@xxxxxxxxxxxxxxx 
> http://www.TechSoftInc.com 
> 
> Quiquid latine dictum sit altum viditur. 
> (Whatever is said in Latin seems profound.) 
> 
> 
> -- 
> 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. 
>