RE: Bogus non-exclusive access creating a foreign key

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_000_01BEB79D.56E8A5A0 Content-Type: text/plain; charset="iso-8859-1" You can get that error when someone is accessing the table in a DIRTY READ isolation mode. No locks will exist. Attached is a shell script which will identify if anyone has the table open. You may want to modify it to provide additional information. Good luck, Rick Bernstein -----Original Message----- From: Jacob Salomon [mailto:jakesalomon@my-deja.com] Sent: Tuesday, June 15, 1999 10:24 To: informix-list@iiug.org Subject: 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. ------_=_NextPart_000_01BEB79D.56E8A5A0 Content-Type: application/octet-stream; name="lock_test.sh" Content-Disposition: attachment; filename="lock_test.sh" #!/usr/bin/ksh #========================================================================== # Script: /dw/bin/lock_test.sh # # PURPOSE: Wait until requested table is not in-use by other processes # # USAGE: lock_test.sh arg1 arg2 # # where: arg1 = Database name (sales, quality, etc) # arg2 = Table name #=========================================================================== if [[ $# -lt 2 ]] # Two arguments are required then print "\\n Usage: `basename $0` <database-name> <table-name>\\n" exit 16 fi # Determine part number(s) in hex VAR=`dbaccess $1 2>/dev/null <<- EOF! OUTPUT TO PIPE 'tail ' WITHOUT HEADINGS SELECT hex(partnum) FROM sysmaster:systabnames WHERE tabname = '$2' AND dbsname = '$1'; EOF!` if [[ -n $VAR ]] # Was a value found? then # .. Yes TABNOS=`echo $VAR | tr A-F a-f` # Table numbers in lower case print "Partnum(s) for table ${1}:${2} is $TABNOS" else # .. No print -u2 "\\nError: Table ${1}:${2} not found\\n" exit 12 fi for TABNO in $TABNOS do # Loop until number of processes for this table is 1 integer COUNTER=0 # Initialize loop counter integer EXITCODE=999 # Initialize exit code from awk EXITCODE=`onstat -g opn | awk \\ ' BEGIN { CNT=0 } (NF > 5 && $6 == "'${TABNO}'") { CNT=CNT+1 } END { print CNT } '` print "\\n There are $EXITCODE open connections to table $2 \\n" done exit 0 ------_=_NextPart_000_01BEB79D.56E8A5A0--

Summary of problem: In my original post, I described this problem: 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. In article <7k736i$iof$1@news.xmission.com>, "Bernstein, Rick" <rbernste@alarismed.com> responded: > You can get that error when someone is accessing the table in a > DIRTY READ isolation mode. No locks will exist. > Attached is a shell-script which will identify if anyone has the table > open. You may want to modify it to provide additional information Thanks, Rick. I have my own script for this purpose. As it happens, however, I tried both dirty read and repeatable read. In both cases I still got that error. I inadvertently discovered a way around it; I ran the 'alter table' command in its own singleton transaction, with no 'BEGIN WORK' or LOCK TABLE stuff. Mind you, it is still a mystery as to why this failed within the transaction. After all, the other FK constraint fell into place as I was creating the table within a transaction. Not having an answer to this, I know I will run into this again. Still, the immediate problem is over. Thanks for thinking of helping me. ;-) +---- Jacob Salomon DBA JSalomon@bn.com --------------------+ |(In perpetual pursuit of undomesticated semi-aquatic avians)| | The expedient performance of a task with excessive concern | | regarding its duration-to-completion engenders a virtual | | certainty of diminished benefit therefrom. | | -- Benjamin Franklin (but he said it in 3 words) | +------------------------------------------------------------+ Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.