For General Information...
I have not yet verified this (have to wait for next maintenance window), but I'm confident I have the solution.
In hindsight, a trivial situation I should have thought of before-hand. But, bottom line is we have a cron job (which is, of course,external to the database) that calls an Informix SPL. The SPL reads from a bunch of tables. I neglected to disable the cron job, and it fired off while I was running the script to build the constraints.
I didn't experience the problem when testing in my private sandbox because it doesn't have the production cron jobs running against it.
Duh.
Thank you, all, for your comments.
Original Message:
Sent: Wed June 26, 2024 03:46 PM
From: David Grove
Subject: ALTER TABLE Error #106: non-exclusive access
Thank you, Art. That is a useful piece of info. I don't think I ever knew that.
DG
Original Message:
Sent: 6/26/2024 2:25:00 PM
From: Art Kagel
Subject: RE: ALTER TABLE Error #106: non-exclusive access
David:
Just an FYI: If you include the -e option to dbaccess it will echo the SQL entered to the output along with the error messages. Witness:
$ dbaccess -e testdb -
Database selected.
> select first 2 * from extents;
select first 2 * from extents;
dbsname sysutils
tabname sysaggregates
chunk 1
offset 75583
size 8
id 4638
dbsname sysutils
tabname sysroleauth
chunk 1
offset 75591
size 8
id 4639
2 row(s) retrieved.
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
Original Message:
Sent: Wed June 26, 2024 02:12 PM
From: David Grove
Subject: ALTER TABLE Error #106: non-exclusive access
Thank you, Andreas.
I'm not sure how to actually apply your suggestion. I would need to "catch it in the act" so to speak, right?
The script has 4,015 ALTER TABLE statements. I can execute it using dbaccess (with stdout and stderr redirected to files to catch the output). When the script encounters an error, it just continues with then next ALTER TABLE statement. So, I can read the stderr.out file, occasionally, while the script is running. But, then I only see that an error has occurred after the script has already continued on to subsequent commands in the script.
I suppose I could just let it run to completion, and then manually go through it, checking each error, and count the position within the stderr.out, and then find the corresponding position within the SQL script (to see what ALTER TABLE command failed), and then manually execute the failed commands. That would probably fix things, but would take time to do this. Since I interrupted the process after I detected any errors, I don't really know how many there are. If only a handful, a manual rerun of the affected tables could work. But, if there are too many, a manual fix could exceed the time available in the maintenance window.
When I test on a duplicate of the production database (created by restoring an ontape level 0 archive to another instance on another machine) there script runs without error. So, I can't duplicate the problem outside of the production system.
At this time, the problem is not a critical issue, and production is running fine. I just figure it might run "more fine" by detaching the indexes and putting them in their own "wider" dbspace.
So, unless it becomes an actual performance problem, I think I'll just "let sleeping dogs lie".
Thank you for your suggestion.
DG
------------------------------
David Grove
Original Message:
Sent: Wed June 26, 2024 03:16 AM
From: Andreas Legner
Subject: ALTER TABLE Error #106: non-exclusive access
Hi David,
onstat -g opn is your friend here, esp. if you know the affected table:
find the table in the output (in 12.10 you might have to go by its partnum (systables) or fragids (sysfragments)), then see which thread(s) is/are having the table open and find their associated session info.
HTH,
Andreas
------------------------------
Andreas Legner
Original Message:
Sent: Tue June 25, 2024 04:13 PM
From: David Grove
Subject: ALTER TABLE Error #106: non-exclusive access
Solaris 10 1/13
Informix 12.10.FC14
I have an SQL command file which contains a couple thousand ALTER TABLE statements to create table constraints. It was generated by Art's myexport utility. I copied the intended database (a production database) to another instance for testing, and the script works perfectly.
So, during a scheduled maintenance window for production, I killed all existing user sessions, put the production database in single-user mode, and used dbaccess to run the SQL command file. After a few hundred constraints, it produced a 106 error (non-exclusive access). The database was in single user mode, no users, and it was running that SQL command script with the ALTER TABLE statements.
I found the command line in the script that it was attempting to execute. That command line was the first command to reference that table. (There were several additional ALTER TABLE statements for that table further down in the command script.)
What could cause a concurrency problem when the instance is in single-user mode, and the only user activity was user "informix" running a single SQL command script? (Recall it ran successfully to completion on another instance.)
Here is an excerpt from stderr when the SQL command file was running:
.
.
.
Table altered.
Table altered.
242: Could not open database table (informix.ofndr_loc_hist).
106: ISAM error: non-exclusive access.
Error in line 269
Near character position 35
Table altered.
Table altered.
.
.
.
Thank you for any thoughts.
Regards,
David Grove
P.S. I propose to run it again during a future maintenance window, and SET LOCK MODE TO WAIT 10;
------------------------------
David Grove
------------------------------