Re: 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 <zklop...@xxxxxxxxx> wrote:
On Dec 30, 3:01 pm, Michelle <shannon_c...@xxxxxxxxxxx> 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

.

Relevant Pages

  • Re: Handling data relations in Gridview
    ... same functionality is to use stored procedures for the CRUD interface ... generation, and about the cache you mentioned, if the whole data is not ... Categories;select ProductID, ProductName, CategoryID from Products"; ...
    (microsoft.public.dotnet.framework.aspnet)
  • RE: procedure cache flush?
    ... Regards ... "arthur" wrote: ... > that clears the cache periodically, or did i just do my math wrong? ... > also, what objects, other than stored procedures, are in the procedure ...
    (microsoft.public.sqlserver.server)
  • Re: Max Number of Stored Procs in 1 Database (sql server 200)
    ... There is no seperate maximum for number of stored procedures, ... (if a stored procedure is executed, the execution plan is generated and ... cache size is limited). ...
    (microsoft.public.sqlserver.mseq)
  • RE: Is the query using the cache
    ... Use in your trace Events - Stored Procedures - SP:CacheInsert, SP:CacheHit, ... See BOL for more information. ... AMB ... > the cache. ...
    (microsoft.public.sqlserver.programming)