9905 error with varchar in a multiset

On Dec 30, 3:01 pm, Michelle wrote:
> Hello,
>
>   On random occurrences when a database is first created or updated
> and a procedure containing a multiset with varchars is loaded I get
> the following error:
>
> 9905: [Internal] No extended type information for domain.
>
>   This is a random and seldom occurrence, but happens enough to cause
> a lot of trouble.  It's been seen in both Informix 9 and 10 and is not
> machine/platform specific.  Changing the varchars to chars seems to
> rectify the problem,   but it is a solution I cannot use.
>
>   Has anybody seen this before?  Any ideas?

I have seen similar bugs (-9905 and -9628) before, reported them and
IBM fixed them in some 11 release. The problem is that MULTISET
requires two extended data types: one for the ROW and one for the
MULTISET itself. Sometimes, the records of either one of them are
missing (or duplicated), and this causes stored procedures (and SQLs)
to fail. I had to manually clear the relevant records from the
extended data type sys tables (not something for the faint of heart!)
and restart the engine (otherwise these records might still be in the
cache) and then recreate the stored procedures (or SQLs). You might be
able to get away with a database restart and then recreate the stored
procedures - if you are lucky...

Happy New Year,

Zachi
 >> Stay informed about: 9905 error with varchar in a multiset 

sounds like an occasion of an oldie:

130885 9628 TYPE NOT FOUND USING SPL WHEN ROUTINE/EXT TYPE
CACHE IS HAMMERED ERROR IS STORED IN SYSPROCPLAN

up the values for PC_POOLSIZE, DS_POOLSIZE, DD_HASHSIZE,DD_HASHMAX,
PC_HASHSIZE,DS_HASHSIZE etc.

Superboer.







On 30 dec, 23:27, Zachi wrote:
> On Dec 30, 3:01 pm, Michelle wrote:
>
> > Hello,
>
> >   On random occurrences when a database is first created or updated
> > and a procedure containing a multiset with varchars is loaded I get
> > the following error:
>
> > 9905: [Internal] No extended type information for domain.
>
> >   This is a random and seldom occurrence, but happens enough to cause
> > a lot of trouble.  It's been seen in both Informix 9 and 10 and is not
> > machine/platform specific.  Changing the varchars to chars seems to
> > rectify the problem,   but it is a solution I cannot use.
>
> >   Has anybody seen this before?  Any ideas?
>
> I have seen similar bugs (-9905 and -9628) before, reported them and
> IBM fixed them in some 11 release. The problem is that MULTISET
> requires two extended data types: one for the ROW and one for the
> MULTISET itself. Sometimes, the records of either one of them are
> missing (or duplicated), and this causes stored procedures (and SQLs)
> to fail. I had to manually clear the relevant records from the
> extended data type sys tables (not something for the faint of heart!)
> and restart the engine (otherwise these records might still be in the
> cache) and then recreate the stored procedures (or SQLs). You might be
> able to get away with a database restart and then recreate the stored
> procedures - if you are lucky...
>
> Happy New Year,
>
> Zachi
 >> Stay informed about: 9905 error with varchar in a multiset