RE: Query works fine in IDS 10 but crashes 11.....
While we are no closer to having an answer, IBM support has returned at least one bit of light: "Im now being told that Informix tech support says that they are getting calls from other folks on 11.5 with this problem, and are trying to recreate it in house." So it appears that this *could* possibly be a genuine bug. I will pass along more info as it comes to light. Jonathon Wyza CX & CBORD System Administrator CX Programmer/Analyst Administrative Computing Bethel College (574)-257-3381 AIM: Iamwyza jonathon.wyza@bethelcollege.edu ============================== SLES 11x64 & IDS 11.50.FC6 Dont document the problem, fix it. Atli Björgvin Oddsson ________________________________________ From: ids-bounces@iiug.org [ids-bounces@iiug.org] on behalf of Wyza, Jonathon [wyzaj@bethelcollege.edu] Sent: Thursday, August 12, 2010 4:37 PM To: ids@iiug.org Subject: RE: Query works fine in IDS 10 but crashes 11..... [20912] Thanks, I've bundled up the schemas/unloads (minus identifying information of course)/queries and sent them on to our support personnel at our vendor who will then open a call with IBM for us. I'll relay the final results when I have them. Jonathon Wyza CX & CBORD System Administrator CX Programmer/Analyst Administrative Computing Bethel College (574)-257-3381 AIM: Iamwyza jonathon.wyza@bethelcollege.edu ============================== SLES 11x64 & IDS 11.50.FC6 "Don't document the problem, fix it." - Atli Björgvin Oddsson -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Thursday, August 12, 2010 4:04 PM To: ids@iiug.org Subject: Re: Query works fine in IDS 10 but crashes 11..... [20910] Dbschema will only do one or all tables. With myschema (included in utils2_ak) you can specify MATCHES wildcards to the '-t' option so myschema -d mydatabase -t 'mo*' to list all tables starting with "mo". Nothing easier than that available I'm afraid. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Aug 12, 2010 at 3:35 PM, Wyza, Jonathon <wyzaj@bethelcollege.edu>wrote: > Uhm, is there an easy way to dump the schema of a set of tables? > > Jonathon Wyza > CX & CBORD System Administrator > CX Programmer/Analyst > Administrative Computing > Bethel College > (574)-257-3381 > AIM: Iamwyza > jonathon.wyza@bethelcollege.edu > ============================== > SLES 11x64 & IDS 11.50.FC6 > > "Don't document the problem, fix it." > - Atli Björgvin Oddsson > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Art Kagel > Sent: Thursday, August 12, 2010 3:27 PM > To: ids@iiug.org > Subject: Re: Query works fine in IDS 10 but crashes 11..... [20908] > > Not out of the realm of possibility, bugs are prolific little > critters. Can you package up the SQL, a schema, and either sample data > or at least relative row counts and open a support case with IBM? It > would be good to know that they are working on a fix for complex > queries like this one, especially for you in case the next one you run > into can't be broken down so easily. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors > (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own > opinions and do not reflect on my employer, Advanced DataTools, the > IIUG, nor any other organization with which I am associated either > explicitly, implicitly, or by inference. Neither do those opinions > reflect those of other individuals affiliated with any entity with > which I am affiliated nor those of the entities themselves. > > On Thu, Aug 12, 2010 at 3:03 PM, Wyza, Jonathon > <wyzaj@bethelcollege.edu>wrote: > > > Art, > > Your information about the way the optimizer is quite enlightening > > and useful for understanding why one query will run faster than > > another. Sadly, setting the optimization to low didn't help at all. > > I left it run for about an > hour > > before going back and killing Informix. I'm guessing there must be > > some > bug > > in > > 11.5 that handles this level type of situation differently (or wrongly). > In > > any case I can break the query down into segments (of maybe 10 > > joins) as temporary tables and then tie them together later in the query. > > > > Jonathon Wyza > > CX & CBORD System Administrator > > CX Programmer/Analyst > > Administrative Computing > > Bethel College > > (574)-257-3381 > > AIM: Iamwyza > > jonathon.wyza@bethelcollege.edu > > ============================== > > SLES 11x64 & IDS 11.50.FC6 > > > > "Don't document the problem, fix it." > > - Atli Björgvin Oddsson > > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf > > Of > Art > > Kagel > > Sent: Thursday, August 12, 2010 2:05 PM > > To: ids@iiug.org > > Subject: Re: Query works fine in IDS 10 but crashes 11..... [20903] > > > > Oh! SET OPTIMIZATION HIGH/LOW; has nothing to do with the number of > > rows > in > > the tables. In order to determine a query plan under HIGH, the > > optimizer has to select the best table to select for the first table > > to query. It does this by calculating the costs of selecting each of > > the 32 tables in your query as the first table to examine. For each > > of the 32 selections for the first table it then has to examine the > > costs of choosing each table but one as the second table. For each > > of these 32*31 options it then calculates the costs of choosing each > > table but two for the third table to join, etc. > > So, the number of calculations it has to perform is the number of > > tables > in > > the query factorial which is a MASSIVE number of calculations. For > > just > 27 > > tables the value is: > > > > 10,888,869,450,418,352,160,768,000,000 > > > > For 32 tables the number of calculations explodes to: > > > > 263,130,836,933,693,530,167,218,012,160,000,000 > > > > 24,165,120 times larger! According to my calculations, if these > > plans are processed one per CPU cycle on a 3.3GHZ machine it will > > take > > 839,352,209,821,375,589 days or 2,298,014,816,718,847 years to > > complete, > or > > longer than the universe has existed already by two orders of magnitude. > > > > Under LOW optimization, the optimizer only examines one layer or > > branch > of > > possible query plans. So once it has selected the best first table, > > it > only >
Continued follow-up from IBM: My developer contact has identified the likely defect fix that points to our query hang problem. In all likelihood the cause is referenced by APAR IC65300 The short description says : "INFINITE LOOP OCCURS IN OPFINDLNKEG WITH A LARGE NUMBER OF OUTER JOINS" This problem apparently does not exist in 11.50.FC7. ... Now I get to wait on our software vendor to decide if they'll support FC7 or not. Weeeee. Jonathon Wyza CX & CBORD System Administrator CX Programmer/Analyst Administrative Computing Bethel College (574)-257-3381 AIM: Iamwyza jonathon.wyza@bethelcollege.edu ============================== SLES 11x64 & IDS 11.50.FC6 "Don't document the problem, fix it." - Atli Björgvin Oddsson -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Wyza, Jonathon Sent: Wednesday, September 01, 2010 6:55 PM To: ids@iiug.org Subject: RE: Query works fine in IDS 10 but crashes 11..... [21113] While we are no closer to having an answer, IBM support has returned at least one bit of light: "I'm now being told that Informix tech support says that they are getting calls from other folks on 11.5 with this problem, and are trying to recreate it in house." So it appears that this *could* possibly be a genuine bug. I will pass along more info as it comes to light. Jonathon Wyza CX & CBORD System Administrator CX Programmer/Analyst Administrative Computing Bethel College (574)-257-3381 AIM: Iamwyza jonathon.wyza@bethelcollege.edu ============================== SLES 11x64 & IDS 11.50.FC6 "Don't document the problem, fix it." - Atli Björgvin Oddsson ________________________________________ From: ids-bounces@iiug.org [ids-bounces@iiug.org] on behalf of Wyza, Jonathon [wyzaj@bethelcollege.edu] Sent: Thursday, August 12, 2010 4:37 PM To: ids@iiug.org Subject: RE: Query works fine in IDS 10 but crashes 11..... [20912] Thanks, I've bundled up the schemas/unloads (minus identifying information of course)/queries and sent them on to our support personnel at our vendor who will then open a call with IBM for us. I'll relay the final results when I have them. Jonathon Wyza CX & CBORD System Administrator CX Programmer/Analyst Administrative Computing Bethel College (574)-257-3381 AIM: Iamwyza jonathon.wyza@bethelcollege.edu ============================== SLES 11x64 & IDS 11.50.FC6 "Don't document the problem, fix it." - Atli Björgvin Oddsson -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Thursday, August 12, 2010 4:04 PM To: ids@iiug.org Subject: Re: Query works fine in IDS 10 but crashes 11..... [20910] Dbschema will only do one or all tables. With myschema (included in utils2_ak) you can specify MATCHES wildcards to the '-t' option so myschema -d mydatabase -t 'mo*' to list all tables starting with "mo". Nothing easier than that available I'm afraid. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Aug 12, 2010 at 3:35 PM, Wyza, Jonathon <wyzaj@bethelcollege.edu>wrote: > Uhm, is there an easy way to dump the schema of a set of tables? > > Jonathon Wyza > CX & CBORD System Administrator > CX Programmer/Analyst > Administrative Computing > Bethel College > (574)-257-3381 > AIM: Iamwyza > jonathon.wyza@bethelcollege.edu > ============================== > SLES 11x64 & IDS 11.50.FC6 > > "Don't document the problem, fix it." > - Atli Björgvin Oddsson > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Art Kagel > Sent: Thursday, August 12, 2010 3:27 PM > To: ids@iiug.org > Subject: Re: Query works fine in IDS 10 but crashes 11..... [20908] > > Not out of the realm of possibility, bugs are prolific little > critters. Can you package up the SQL, a schema, and either sample data > or at least relative row counts and open a support case with IBM? It > would be good to know that they are working on a fix for complex > queries like this one, especially for you in case the next one you run > into can't be broken down so easily. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors > (art@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own > opinions and do not reflect on my employer, Advanced DataTools, the > IIUG, nor any other organization with which I am associated either > explicitly, implicitly, or by inference. Neither do those opinions > reflect those of other individuals affiliated with any entity with > which I am affiliated nor those of the entities themselves. > > On Thu, Aug 12, 2010 at 3:03 PM, Wyza, Jonathon > <wyzaj@bethelcollege.edu>wrote: > > > Art, > > Your information about the way the optimizer is quite enlightening > > and useful for understanding why one query will run faster than > > another. Sadly, setting the optimization to low didn't help at all. > > I left it run for about an > hour > > before going back and killing Informix. I'm guessing there must be > > some > bug > > in > > 11.5 that handles this level type of situation differently (or wrongly). > In > > any case I can break the query down into segments (of maybe 10 > > joins) as temporary tables and then tie them together later in the query. > > > > Jonathon Wyza > > CX & CBORD System Administrator > > CX Programmer/Analyst > > Administrative Computing > > Bethel College > > (574)-257-3381 > > AIM: Iamwyza > > jonathon.wyza@bethelcollege.edu > > ============================== > > SLES 11x64 & IDS 11.50.FC6 > > > > "Don't document the problem, fix it." > > - Atli Björgvin Oddsson > > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf > > Of > Art > > Kagel > > Sent: Thursday, August 12, 2010 2:05 PM > > To: ids@iiug.org > > Subject: Re: Query works fine in IDS 10 but crashes 11..... [20903] > > > > Oh! SET OPTIMIZATION HIGH/LOW; has nothing to do with the number of > > rows > in > > the tables. In order to determine a query plan under HIGH, the > > optimizer has to select the best table to select for the first table > > to query. It does this by calculating the costs of selecting each of > > the 32 tables in your query as the first table to examine. For each > > of the 32 selections for the first table it then has to examine the > > costs of choosing each tabl