Re: Really Frustrating -9628 Errors

On Thu, 2005-11-17 at 14:23 -0500, Adam Tauno Williams wrote: > We've written several Informix stored procedures that return MULTISET as > the type; the application than does a SELECT a,b,c FROM > TABLE({procedurename}({parameterlist...})). And these WORK!!! But only > for awhile, the next day, or after a couple of days suddenly they stop > working - and they haven't been changed. They simply start to bomb out > with a -9628 (type not found) error for any SQL statement like the > above. > We are using "IBM Informix Dynamic Server Version 10.00.UC1" on LINUX. > The engine itself is perfectly stable, but attempts to use SPLs that > deal with collection types fall apart after a few days. > An example of a procedure that bombs has an return type like - > CREATE FUNCTION p_get_equip_history(i_equip_id INT, i_event_count INT) > RETURNING MULTISET ( ROW (record_id INT, equipment INT, > entry_type INT, meter_reading INT, event_date DATETIME YEAR TO DAY, > workorder_number INT, workorder_segment INT, charge_labor FLOAT, > charge_parts FLOAT, charge_misc FLOAT, component_code CHAR(10), > modification_code CHAR(10), job_code CHAR(10), description CHAR(255)) > NOT NULL );> .... > Has anyone seen this? I've searched high-and-low but don't find much > information on -9628 except to indicate that we are doing something > wrong... BUT THE ROUTING WORKS,,, for awhile. A little debugging revealed this, which doesn't mean anything to me (again, this EXACT procedure worked for about 24 hours) - -------------------------------- start select cursor. select ROW(a.record_id, a.equipment, a.entry_type, a.meter_reading, a.event_date, a.workorder_number, a.workorder_segment, a.charge_labor, a.charge_parts, a.charge_misc, a.component_code, a.modification_code, a.job_code, b.description) from equipment_history as a, equipment_history_typer as b where (and (= a.equipment, i_equip_id), (= a.entry_type, b.entry_type_id)) order by a.event_datedesc exception : looking for handler SQL error = -9628 ISAM error = 0 error string = = ""exception : no appropriate handler -------------------------------- [demime 1.01d removed an attachment of type application/pgp-signature which had a name of signature.asc] sending to informix-list

Sounds like a bug i've seen before but 'should' have been fixed??? ... update stats will work but it only for xx hours and someother may fail. phone TS and have them look in their bugdb. wa afaicr is cranking up spl cache params PC_POOLSIZE, DS_POOLSIZE, DD_HASHSIZE,DD_HASHMAX, PC_HASHSIZE,DS_HASHSIZE, look at onstat -g cac for UDR Cache: Number of lists : 31 PC_POOLSIZE : 127 change one above and bounce and see if changed... am a bit out of it... also some of them have to be a prime number.. maybe it's doc'ed nowadays dono Superboer Adam Tauno Williams schreef: > On Thu, 2005-11-17 at 14:23 -0500, Adam Tauno Williams wrote: > > We've written several Informix stored procedures that return MULTISET as > > the type; the application than does a SELECT a,b,c FROM > > TABLE({procedurename}({parameterlist...})). And these WORK!!! But only > > for awhile, the next day, or after a couple of days suddenly they stop > > working - and they haven't been changed. They simply start to bomb out > > with a -9628 (type not found) error for any SQL statement like the > > above. > > We are using "IBM Informix Dynamic Server Version 10.00.UC1" on LINUX. > > The engine itself is perfectly stable, but attempts to use SPLs that > > deal with collection types fall apart after a few days. > > An example of a procedure that bombs has an return type like - > > CREATE FUNCTION p_get_equip_history(i_equip_id INT, i_event_count INT) > > RETURNING MULTISET ( ROW (record_id INT, equipment INT, > > entry_type INT, meter_reading INT, event_date DATETIME YEAR TO DAY, > > workorder_number INT, workorder_segment INT, charge_labor FLOAT, > > charge_parts FLOAT, charge_misc FLOAT, component_code CHAR(10), > > modification_code CHAR(10), job_code CHAR(10), description CHAR(255)) > > NOT NULL );> > .... > > Has anyone seen this? I've searched high-and-low but don't find much > > information on -9628 except to indicate that we are doing something > > wrong... BUT THE ROUTING WORKS,,, for awhile. > > A little debugging revealed this, which doesn't mean anything to me > (again, this EXACT procedure worked for about 24 hours) - > > -------------------------------- > start select cursor. > select ROW(a.record_id, a.equipment, a.entry_type, a.meter_reading, > a.event_date, a.workorder_number, a.workorder_segment, a.charge_labor, > a.charge_parts, a.charge_misc, a.component_code, a.modification_code, > a.job_code, b.description) > from equipment_history as a, equipment_history_typer as b > where (and (= a.equipment, i_equip_id), (= a.entry_type, > b.entry_type_id)) > order by a.event_datedesc > exception : looking for handler > SQL error = -9628 ISAM error = 0 error string = = ""> exception : no appropriate handler > -------------------------------- > > [demime 1.01d removed an attachment of type application/pgp-signature which had a name of signature.asc] > sending to informix-list

bug_number 130885 description 9628 TYPE NOT FOUND USING SPL WHEN ROUTINE/EXT TYPE CACHE IS HAMMERED ERROR IS STORED IN SYSPROCPLAN product_code ONLINE component_code CT dono have it checked; Superboer.