Bogus non-exclusive access creating a foreign key

Hi Family. I have a familiar-looking problem with a familiar looking error message. I created a table with primary an foreign key constraints in line. When I kept getting a message complaining of "Non-exclusive access" on one of the referenced tables I pulled out one of the constraints, built the table and figured I'd just add that FK constraint later. Here's my success story: alter table source_control add constraint (foreign key(source_num) references source constraint src_ctrl_src_fk)# ^ # 242: Could not open database table (informix.source). # 106: ISAM error: non-exclusive access. A check of sysmaster:syslocks puts the lie to this - there was nobody accessing that table. But, persistent curmudgeon I, I went into a transaction, locked the table in exclusive mode and then retried the above command. Of course, I got the same results. The last time I got this error(in a previous life) it was the result of a corrupted partition page or catalog; a bunch of tables with no entries in syscolumns. (I went looking for the right key words - I know the message looked familiar!) This is clearly not the case here - I can access the column names from the catalog and run dbschema against that table (source). The new table has no rows yet so there is no chance of constraint violation already existing. Besides, there is a reliably accurate message already in place to report *that* situation. And yes, I am running the commands as user informix (a practice I object to but that's another story.) Has anyone seen this type of situation? If it is a symptom of corruption (as I suspect) how can I isolate this corruption? Thanks. -- +---- Jacob Salomon -- Obligatory sesquipedalian obfuscation: ---------+ | An object of igneous, sedimentary or metamorphic mineral in combined | | states of elevated linear and rotational kinetic energy acquires no | | accumulation of bryophytic vegetation. | +----------------------------------------------------------------------+ Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

In article <7k6272$s54$1@nnrp1.deja.com>, Jacob Salomon <jakesalomon@my- deja.com> writes >Hi Family. > >I have a familiar-looking problem with a familiar looking error message. > >I created a table with primary an foreign key constraints in line. When >I kept getting a message complaining of "Non-exclusive access" on one >of the referenced tables I pulled out one of the constraints, built the >table and figured I'd just add that FK constraint later. > >Here's my success story: > >alter table source_control > add constraint (foreign key(source_num) > references source > constraint src_ctrl_src_fk)># ^ ># 242: Could not open database table (informix.source). ># 106: ISAM error: non-exclusive access. > >A check of sysmaster:syslocks puts the lie to this - there was nobody >accessing that table. But, persistent curmudgeon I, I went into a >transaction, locked the table in exclusive mode and then retried the >above command. Of course, I got the same results. > I've seen this when users have a cursor open on the table. (We use dirty read). And no, I cannot see a way to find which user it is!!! I vote for an onstat option to list prepare statements and prepared cursors stored in the engine (onstat -g ses <session id> only lists the last one..). >The last time I got this error(in a previous life) it was the result of >a corrupted partition page or catalog; a bunch of tables with no >entries in syscolumns. (I went looking for the right key words - I know >the message looked familiar!) This is clearly not the case here - I can >access the column names from the catalog and run dbschema against that >table (source). > >The new table has no rows yet so there is no chance of constraint >violation already existing. Besides, there is a reliably accurate >message already in place to report *that* situation. And yes, I am >running the commands as user informix (a practice I object to but >that's another story.) > >Has anyone seen this type of situation? If it is a symptom of >corruption (as I suspect) how can I isolate this corruption? > >Thanks. >-- >+---- Jacob Salomon -- Obligatory sesquipedalian obfuscation: ---------+ >| An object of igneous, sedimentary or metamorphic mineral in combined | >| states of elevated linear and rotational kinetic energy acquires no | >| accumulation of bryophytic vegetation. | >+----------------------------------------------------------------------+ > > >Sent via Deja.com http://www.deja.com/ >Share what you know. Learn what you don't. -- David Williams