Crystal/Informix error
Crystal Reports failed when adding an Informix table, with the ODBC error "Invalid distribution format found for nunique" (Informix error -750, bad data distributions in sysdistrib). Art Kagel suggested checking onstat -g ses for the underlying SQL/ISAM errors, and noted catalog tables aren't covered by normal update statistics and that in-place upgrades can leave stale distributions. The fix: update statistics low for table sysindices drop distributions only, then update statistics high on it, and ideally drop and rebuild all distributions. The poster confirmed this resolved the problem; the root cause was never pinned down with IBM support.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Ran into a strange problem. We're trying to use Crystal Reports as a reporting tool for a customer. We can connect to the database and get a list of tables but when adding the table to the report we get an error reading "Query Engine Error: 'HY000:[Informix][Informix ODBC Driver][Informix]Invalid distribution format found for nunique'". I've looked around and the only reference to "nunique" I've found is the nunique column on the sysindexes table. I've updated stats everywhere. I'm not sure what else invalid distribution format found could mean. sysindexes needs rebuilt? That should be updated when you update stats shouldn't it? As I said if need be I can post the query to look at too. Any help would be greatly appreciated. Thanks, Nate
If you look at the onstat -g ses <sid> output for the Crystal Reports
session after it gets that ODBC error, you should see the Informix SQL and
ISAM level errors which will give you a better idea of what's happened.
Apparently Crystal is not displaying the extended error text from the
engine.
Art
Art S. Kagel, Principal Consultant
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
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 Tue, Nov 12, 2013 at 3:39 PM, NATE HICKS
<nathaniel.hicks@trnswrks.com>wrote:
> Ran into a strange problem. We're trying to use Crystal Reports as a
> reporting
> tool for a customer. We can connect to the database and get a list of
> tables
> but when adding the table to the report we get an error reading "Query
> Engine
> Error: 'HY000:[Informix][Informix ODBC Driver][Informix]Invalid
> distribution
> format found for nunique'". I've looked around and the only reference to
> "nunique" I've found is the nunique column on the sysindexes table. I've
> updated stats everywhere. I'm not sure what else invalid distribution
> format
> found could mean. sysindexes needs rebuilt? That should be updated when you
> update stats shouldn't it? As I said if need be I can post the query to
> look
> at too. Any help would be greatly appreciated.
>
> Thanks,
>
> Nate
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c23fbc30172804eb00fda3
OK, did some digging. This error corresponds to Informix error -750 which
indicates bad data distributions in sysdistrib. As noted, it is pointing
to a system catalog table column which is not normally processed when you
update statistics, you have to specifically mention the catalog table. Didyou recently upgrade your instance in-place? If you do that you have to
drop all data distributions and recreate them all from scratch.
Do the following:
update statistics low for table sysindices drop distributions only;
update statistics high for table sysindices;
See if that resolves the issue for the nunique column. If it does, I would
STRONGLY recommend that you drop all distributions on all tables (best to
just DELETE FROM sysdistrib; in all databases except sysmaster, sysadmin,
and sysusers) then recreate your distributions as you normally do (ie run
dostats or whatever scripts you run daily). You can get dostats to do all
of this for you with:
dostats -m --drop-distributions --force-run -d '*'
Art
Art S. Kagel, Principal Consultant
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
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 Tue, Nov 12, 2013 at 3:49 PM, Art Kagel <art.kagel@gmail.com> wrote:
> If you look at the onstat -g ses <sid> output for the Crystal Reports
> session after it gets that ODBC error, you should see the Informix SQL and
> ISAM level errors which will give you a better idea of what's happened.
> Apparently Crystal is not displaying the extended error text from the
> engine.
>
> Art
>
>
> Art S. Kagel, Principal Consultant
>
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> 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 Tue, Nov 12, 2013 at 3:39 PM, NATE HICKS
<nathaniel.hicks@trnswrks.com>wrote:
>
>> Ran into a strange problem. We're trying to use Crystal Reports as a
>> reporting
>> tool for a customer. We can connect to the database and get a list of
>> tables
>> but when adding the table to the report we get an error reading "Query
>> Engine
>> Error: 'HY000:[Informix][Informix ODBC Driver][Informix]Invalid
>> distribution
>> format found for nunique'". I've looked around and the only reference to
>> "nunique" I've found is the nunique column on the sysindexes table. I've
>> updated stats everywhere. I'm not sure what else invalid distribution
>> format
>> found could mean. sysindexes needs rebuilt? That should be updated when
>> you
>> update stats shouldn't it? As I said if need be I can post the query to
>> look
>> at too. Any help would be greatly appreciated.
>>
>> Thanks,
>>
>> Nate
>>
>>
>>
>>
*******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>
>
--001a11c23fbcaab63a04eb011ea0
Art saves the day again! Thanks it worked. I wasn't doing medium I was doing low stats before when doing this. I'll attribute it to in-place upgrades. Thanks again
In-place upgrades??? Next you'll be blaming the former DBA. LOL Glad you got it resolved. Art is a great asset to have on here. Dan -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of NATE HICKS Sent: Wednesday, November 13, 2013 9:28 AM To: ids@iiug.org Subject: Re: Crystal/Informix error [31939] Art saves the day again! Thanks it worked. I wasn't doing medium I was doing low stats before when doing this. I'll attribute it to in-place upgrades. Thanks again ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Hi,
I can see you've already got a resolution to this.
Did you manage to work out the mechanism by which the corruption might have
got there or raise a support case with IBM about it?
I only ask because we saw a similar kind of thing several months ago.
I am not sure if your case is the same but it has similar characteristics. We
could even restore the corruption from onbar and had a logical log entry
corresponding to the writing of the corrupt value to disk. However, IBM
support were unable to ascertain the cause.
Ben.
Our issue was just apparently goofy stats. No support case with IBM just the amazing user group ;)