RE: Error -710 from stored procedure

Heinz, I think that the most simple workaround is to replace SELECT ur_benkrz_to_bennr(imandant,iben) INTO ben FROM systables WHERE tabid=1 with LET ben = ur_benkrz_to_bennr(imandant,iben) This should make the initial procedure independent on 'informix'.systables. Actually, the entire story about '-710' is much more interesting, and, I believe, deserves a vary broad discussion. The problem originally comes from a fact, that Informix doesn't create query plans at runtime. Instead, it uses pre-calculated query plans stored in 'sysprocplan'. To track situations, when the query plan for a stored procedure should be re-optimized, Informix stores dependency list. This dependency list lists all tables, that procedure is referencing, with their versions. Each table in the database has it's version stored in 'systables'. 'Version' is integer, but, in fact, in consists of two parts. Lower two bytes, minor version, is modified when 'update statistics' is executed for the table. Major version of the table changes, when any major 'alter table' operation is made on the table itself (including create/drop indexes!!!), or on any other table this table references directly. When only minor version of the table is changed, 'set optimization low' in a session execution the SQL sometimes (not always...) helps to prevents the necessity to re-optimize the SQL's is a stored procedure. When major version of the table is changed, 'set optimization low' in a session executing SQL's or SPL's against this table doesn't help. The '-710' error becomes inevitable. Interesting enough, that, very often, after 'alter table' is executed, '-710' error appears again even if a session, that is trying to execute the stored procedure, reconnects to the database. In that case, '-710' goes away only after Informix restarts. Sometimes, the following workaround helps: it is necessary to do some minimal 'update statistics' (like 'update statistics medium for table ... distributions only') for the altered table itself and for all tables referencing it - in the same transaction as 'alter table'. I came to that conclusion experimentally. It looks like there is a bug in Informix SPL optimizer, that causes Informix to incorrectly process major/minor table version number changes when table is altered. -Alexey > -----Original Message----- > From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] On > > Hi all, > > IDS 7.31 UD4 > SCO Unix 3.2 5.0.6 > > Sometimes when we execute a stored procedure it fails with the error: > > -710, Table (informix.systables) has been dropped,altered or renamed. > The stored procedure makes a > SELECT ur_benkrz_to_bennr(imandant,iben) INTO ben > FROM systables WHERE tabid=1; > > The stored procedure ur_benkrz_to_bennr only makes an SELECT INTO. No > modifications on any table. > > At execution time of the stored procedure Systables was not dropped,altered > or renamed. > I altered an other table (drop (primary) key, create (primary) key) in the > database, but such an action don't alter the > systables (i am right?). > > What can be the issue of such an error? > > Any workaround? > > Thanks in advance for any answer. > (excuse my poor school-english) > > Heinz > sending to informix-list sending to informix-list