106: ISAM error non-exclusive access
Posted in 1997
Running Informix-ODS 7.24UC1 on AIX 4.21, we have UNIX scripts
which load data into our Data Warehouse. These scripts:
1. Drop existing constraints and indexes,
2. Load data with dbload, and
3. Create indexes and constraints.
Our Data Warehouse databases are defined with buffered logging.
If someone is accessing the table when the script runs, it fails
immediately with:
106: ISAM error: non-exclusive accessWe would prefer that the script wait for the table to become
available (like it does with our Oracle-based tables).
I tried adding the following commands to the script
BEGIN WORK;
SET LOCK MODE TO WAIT 7200; LOCK TABLE <table-name> IN EXCLUSIVE MODE;
These commands succeed, but the CREATE INDEX still fails immediately.
How can I force the CREATE INDEX and ALTER TABLE commands to wait
for other user(s) to release share lock(s) on the table?
Below is the complete output from my latest run.
Any assistance or recommendations would be greatly appreciated.
SET PDQPRIORITY 100;PDQ Priority set.
BEGIN WORK;
Started transaction.
SET LOCK MODE TO WAIT 7200;Lockmode set.
LOCK TABLE sales.buy_group IN EXCLUSIVE MODE;
Table locked.
CREATE UNIQUE INDEX sales.buyg_ix_grp
ON sales.buy_group (group_nbr) FILLFACTOR 100
-- IN saleix_base
; 212: Cannot add index.
106: ISAM error: non-exclusive access.
Error in line 18Near character position 18
ALTER TABLE sales.buy_group
ADD CONSTRAINT PRIMARY KEY (group_nbr)
CONSTRAINT sales.buyg_pk
; 242: Could not open database table (sales.buy_group).
106: ISAM error: non-exclusive access.
Error in line 22Near character position 25
COMMIT WORK;
Data committed.
UPDATE STATISTICS MEDIUM FOR TABLE sales.buy_group DISTRIBUTIONS ONLY;Statistics updated.
UPDATE STATISTICS HIGH FOR TABLE sales.buy_group (group_nbr );Statistics updated.
Database closed.