strange non-exclusive access error (106: ISAM)

[roberto omar]

I'm running IDS 7.30.UC7 on HP-UX 10.20, with buffered logging .

I get this weird problem.

When I try to execute the SQL commands below, I keep getting the following
errors:

Database selected.

Started Transaction.

Table Locked.

242: Could not open database table (cc_district_list)
106: ISAM error: non-exclusive access

Data Commited.

Here's my SQL script:

BEGIN WORK;
LOCK TABLE cc_district_list IN EXCLUSIVE MODE;

ALTER TABLE cc_district_list
ADD CONSTRAINT CHECK (LENGTH(dl_district) > 10);

COMMIT WORK;

This is very strange. I can get the exclusive access on the table, but I
keep getting non-exclusive ISAM error. Moreover, I checked all the locks on
my instance (syslocks) while running the above SQL script, and I didn't see
any locks on this table. The above ALTER command is just the one I used most
recently; I get the same problem with other ALTER options.

I get this problem once in a while, and the only resolution that clears this
problem is restarting the database instance. If I don't restart the
database, I can try 1000 times and it would fail every single time. It seems
like some flag in the shared memory doesn't get cleared sometimes. However,
I don't always have a luxury of restarting the database when I need to make
some changes to my tables.

[Andrew Pearson]

roberto omar wrote:
>
> I'm running IDS 7.30.UC7 on HP-UX 10.20, with buffered logging .
>
> I get this weird problem.
>

<snip>

>
> 242: Could not open database table (cc_district_list)
> 106: ISAM error: non-exclusive access
>
> Data Commited.
>
> Here's my SQL script:
>
> BEGIN WORK;
> LOCK TABLE cc_district_list IN EXCLUSIVE MODE;
>
> ALTER TABLE cc_district_list
> ADD CONSTRAINT CHECK (LENGTH(dl_district) > 10);
>
> COMMIT WORK;
>
> This is very strange. I can get the exclusive access on the table, but I
> keep getting non-exclusive ISAM error.

It's not necessarily a locking thing. You have to be sure that
nobody is accessing the table before you can modify it. That
means absolutely nobody, not even on dirty read. I think (correct
me if I'm wrong) that you CAN lock the table in exclusive mode
even when someone is reading it if they are using DIRTY
READ/READ UNCOMMITTED, but you can't MODIFY the table. You don't
need to restart the database to alter tables, but you do need to
wait for a quiet time, or do a different select/onstat command to
make sure no one is even reading the table that you need to alter.

--
Andrew Pearson: "exactly what the web needs less of".

[Chris Brauer]

I had the same problem last night. Since I hadn't time to wait I had to identify
the user
who 'blocked' my table.
SELECT sqx_sessionid, sqx_sqlstatement
FROM sysmaster:informix.syssqexplain
WHERE sqx_sqlstatement MATCHES '*your_table*'

You will receive a list of session-IDs and their statements in which you will
find
your own (this one) select and the blocking one.

Hope this helps,

Chris

roberto omar schrieb:

> I'm running IDS 7.30.UC7 on HP-UX 10.20, with buffered logging .
>
> I get this weird problem.
>

> When I try to execute the SQL commands below, I keep getting the following
> errors:
>
> Database selected.
>
> Started Transaction.
>
> Table Locked.
>