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 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
> > looks at the best second table choice give that one choice for the
> > first table. That means it only has to examine SUM(1..32) query
> > plans or 561 plans.
> >
> > It may be some combination of insufficient distributions and HIGH
> > optimization that's appearing to hang the server.
> >
> > 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 1:29 PM, Wyza, Jonathon
> > <wyzaj@bethelcollege.edu>wrote:
> >
> > > Thanks,
> > > Set optimization low isn't helping (that I can tell, it's been
> > > running for about 3 minutes now, and when you consider that its
> > > only joining against 59 rows that seems excessive). I'm guessing
> > > dostats is located
> at
> > iiug?
> > > (along, I
> > > hope, with a tutorial/detailed readme)
> > >
> > > 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 1:28 PM
> > > To: ids@iiug.org
> > > Subject: Re: Query works fine in IDS 10 but crashes 11.....
> > > [20898]
> > >
> > > Personally, I don't think that AUS goes far enough and I depend on
> > > my own dostats utility for this. I would try it and see if that
> > > makes a difference.
> > > That said, see my other post about SET OPTIMIZATION LOW;
> > >
> > > 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 1:05 PM, Wyza, Jonathon
> > > <wyzaj@bethelcollege.edu>wrote:
> > >
> > > > It really wasn't an "upgrade", it was a fresh install. The steps
> > > > I took
> > > > we're:
> > > >
> > > > Old install (ids 10): dbexport -o /tmp cars <transferred the
> > > > /tmp/cars.exp files to new box> New install (ids 11.5): dbimport
> > > > cars -d dbs1 -i /tmp
> > > >
> > > > AUS Evaluation Ran (OAT confirms this) AUS Refresh Ran (OAT
> > > > confirms
> > > > this)
> > > >
> > > > 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 12:10 PM
> > > > To: ids@iiug.org
> > > > Subject: Re: Query works fine in IDS 10 but crashes 11.....
> > > > [20888]
> > > >
> > > > Did you drop all distributions after the upgrade and recreate
> > > > them from scratch - also recompiled all stored procedures?
> > > >
> > > > 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 10:19 AM, Wyza, Jonathon
> > > > <wyzaj@bethelcollege.edu>wrote:
> > > >
> > > > > I thought that at first, but according to AUS Evaluator, the
> > > > > only tables that need statistics updated are tables with less
> > > > > than 100 rows.
> > > > >
> > > > > 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 Obnoxio The Clown
> > > > > Sent: Thursday, August 12, 2010 10:18 AM
> > > > > To: ids@iiug.org
> > > > > Subject: Re: Query works fine in IDS 10 but crashes 11.....
> > > > > [20872]
> > > > >
> > > > > Wyza, Jonathon wrote:
> > > > > > We have a query that has 4 inner joins and 27 outer joins (I
> > > > > > know, it's a
> > > > > lot,
> > > > > > but that's how it is) . On our IDS 10 machine the query runs
> > > > > > with no trouble and very quick (IE we know it doesn't have a
> > > > > > Cartesian product). If we run
> > > > > it
> > > > > > on our IDS 11.5 machine (with the exact same data) then it
> > > > > > seizes the
> > > > > engine.
> > > > > > Killing dbaccess or sacego will not release the session.
> > > > > > Doing an onmode -z <sessid> will not work (it just hangs).
> > > > > > Shutting down the engine won't work (it just hangs). In the
> > > > > > end the only way to get the engine back is to kill
> > > > > the
> > > > > > oninit process.
> > > > > >
> > > > > > I've never seen anything like this that would totally
> > > > > > consume informix with
> > > > > a
> > > > > > query. Thoughts? (doing an onstat several times while it is
> > > > > > running after you've killed the originating process shows no
> > > > > > reads/writes, only cpu usage)
> > > > >
> > > > > UPDATE STATISTICS?
> > > > >
> > > > > --
> > > > > Cheers,
> > > > > Obnoxio The Clown
> > > > >
> > > > > http://obotheclown.blogspot.com I will now proceed to pleasure
> > > > > myself with this fish.
> > > > >
> > > > > --
> > > > > This message has been scanned for viruses and dangerous
> > > > > content by OpenProtect(http://www.openprotect.com), and is
> > > > > believed to be
> > clean.
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
> >
>
>
>
*******************************************************************************
> > > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > > >
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
> >
>
>
>
*******************************************************************************
> > > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > > >
> > > > >
> > > >
> > > > --0016367fb02deba1a0048da2976e
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
> >
>
>
>
*******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
> >
>
>
>
*******************************************************************************
> > > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > > >
> > > >
> > >
> > > --002215975ff2d5e377048da3ae98
> > >
> > >
> > >
> > >
> >
> >
> >
>
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> > >
> > >
> >
> >
> >
>
>
>
*******************************************************************************
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --0016e644c2b20c6672048da4333c
> >
> >
> >
> >
>
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
>
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001636e1ef4431998f048da55954
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001636e1ef44ac5572048da5dea1
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
