FC8 Optimizer Bug etc..

Doug Fossmeyer wrote: > First, thanks in advance for any help you may provide. > > We have a vendor supplied 4gl process that has doubled in time since an upgrade to IDS 9.4fc8, tools7.32, sdk2.81 respectively. I have narrowed the issue down to several sql's in the 4gl. Below is the sqexplain on the first sql problem. While we don't normally amend our vendor's sql in this case we are willing to make the attempt. I have optcompind set to 0, pdq off, buffers at 900000, shm at 360488. I added update stats high for cdhtmp after the inserts are done and have been running the dostats utility. I have included the sqexplain, temp table info, oncheck -pt on the large table. > It doesn't look like you are doing ansi but... There is one really annoying optimiser bug in FC8 which is affecting my upgrade. IC50165: THE OPTIMIZER CHOOSES THE WRONG INDEX FOR QUERIES WITH AN ANSI JOIN http://www-1.ibm.com/support/entdocview.wss?rs=0&context=SSGU5D&context=SSHMMC&context=SSGU8G&context=SSGKNY&context=SSGU5Y&context=SSCRW7&context=SSGHZP&context=SSVT2J&q1=optimizer&q2=join&uid=swg1IC50165&loc=en_US&cs=utf-8&lang&NotUpdateReferer= Otherwise, look at the 10uc5 open bugs, perhaps fc8 ones as well. (uc5 code seems to make it into fc8). http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.relnotes.doc/uc5E/10.00.UC5E.lnxbugs.htm