online 5.10 locks & alter table

online 5.10 locks & alter table

Post by Xavier Trevisan » Sun, 31 Dec 1899 09:00:00



I was wondering if someone could help me with the following issue:

We have just upgraded from SE 4.10 to Online 5.10.  Our database must remain
up 24x7and is made up of a large number of independent groups of tables.  In
SE when we needed to add or change a field on a table we used to find who
was locking the table in question and had them log out, then executed the
following

lock table ... in exclusive mode
alter table ...
unlock table ...

This worked fine

With ONLINE we use tbstat -ku to find those locking the table, once they are
out we execute

begin work;
lock table ... in exclusive mode;     (THIS SUCCEEDS)
alter table ...

but the alter table fails with an error about not having exclusive
access...yet the table is locked in exclusive mode!!

Does anyone know why this is happening and ways around it

thank you

Xavier Trevisani

 
 
 

online 5.10 locks & alter table

Post by Xavier Trevisan » Sun, 31 Dec 1899 09:00:00


More info: these are the errors we get

-242    Could not open database table table-name.

The database server is unable to begin reading a table. Check the
accompanying ISAM error code for more information. There may be
a problem with file permissions, or a hardware error, or the system
catalog may have been corrupted. Unless the ISAM error code or an
operating system message points to another cause, run the bcheck
or secheck utility to verify file integrity.

-106    ISAM error: non-exclusive access.

The ISAM processor has been asked to add or drop an index when it
does not have exclusive access. C-ISAM programs: Before doing this
operation the file must be opened with exclusive access. Review the
program logic and make sure it opens this file passing the
ISEXCLLOCK flag to isopen. SQL products: This error can be
returned on the INFORMIX-OnLine Dynamic Server or the
INFORMIX-SE database server when an exclusive lock is required on
a table. For example, this error appears when a second user tries to
alter a table that has been locked by the first user.


> I was wondering if someone could help me with the following issue:

> We have just upgraded from SE 4.10 to Online 5.10.  Our database must remain
> up 24x7and is made up of a large number of independent groups of tables.  In
> SE when we needed to add or change a field on a table we used to find who
> was locking the table in question and had them log out, then executed the
> following

> lock table ... in exclusive mode
> alter table ...
> unlock table ...

> This worked fine

> With ONLINE we use tbstat -ku to find those locking the table, once they are
> out we execute

> begin work;
> lock table ... in exclusive mode;     (THIS SUCCEEDS)
> alter table ...

> but the alter table fails with an error about not having exclusive
> access...yet the table is locked in exclusive mode!!

> Does anyone know why this is happening and ways around it

> thank you

> Xavier Trevisani


 
 
 

online 5.10 locks & alter table

Post by June Ton » Sun, 31 Dec 1899 09:00:00


-106 means that someone else is reading the table.  This is not inconsistent with
locking the table in exclusive mode.  Remember that people can read the data using
Dirty Read, which does not place any locks, thus allowing you to lock the table in
exclusive mode.  Now you can update rows in the table.  However, you are not
allowed to make any changes to the structure of the table, which would cause their
queries to fail, unless you have 'exclusive access', which is a different thing.

June


> More info: these are the errors we get

> -242    Could not open database table table-name.

> The database server is unable to begin reading a table. Check the
> accompanying ISAM error code for more information. There may be
> a problem with file permissions, or a hardware error, or the system
> catalog may have been corrupted. Unless the ISAM error code or an
> operating system message points to another cause, run the bcheck
> or secheck utility to verify file integrity.

> -106    ISAM error: non-exclusive access.

> The ISAM processor has been asked to add or drop an index when it
> does not have exclusive access. C-ISAM programs: Before doing this
> operation the file must be opened with exclusive access. Review the
> program logic and make sure it opens this file passing the
> ISEXCLLOCK flag to isopen. SQL products: This error can be
> returned on the INFORMIX-OnLine Dynamic Server or the
> INFORMIX-SE database server when an exclusive lock is required on
> a table. For example, this error appears when a second user tries to
> alter a table that has been locked by the first user.


> > I was wondering if someone could help me with the following issue:

> > We have just upgraded from SE 4.10 to Online 5.10.  Our database must remain
> > up 24x7and is made up of a large number of independent groups of tables.  In
> > SE when we needed to add or change a field on a table we used to find who
> > was locking the table in question and had them log out, then executed the
> > following

> > lock table ... in exclusive mode
> > alter table ...
> > unlock table ...

> > This worked fine

> > With ONLINE we use tbstat -ku to find those locking the table, once they are
> > out we execute

> > begin work;
> > lock table ... in exclusive mode;     (THIS SUCCEEDS)
> > alter table ...

> > but the alter table fails with an error about not having exclusive
> > access...yet the table is locked in exclusive mode!!

> > Does anyone know why this is happening and ways around it

> > thank you

> > Xavier Trevisani

--

Grounded in Palo Alto, living on M&M's (plain)
 
 
 

1. OnLine 5.10 w/CSDK & ESQL 5.10

A while back I asked about running D4GL with OnLine 5.10 and CSDK.  
Art and Jonathan pointed out that I-STAR would be needed with that
mix of products when the ESQL/C version is above 5.10.

But Jonathan also wrote that:

Has anyone succeeded with this technique?  Any ideas how I'd go about it?

--

Washington Group International, Inc.         (215) 422-4144
Philadelphia, PA, USA
Any opinions I state are my own and not necessarily those of my employer

2. user-defined function client server question

3. Fail Consistency Check AIX & Informix Online 5.10

4. Query Analyzer problem

5. D4GL 3.0 & OnLine 5.10

6. Newbie: Can't connect to SQL server - login fails

7. I-STAR, OnLine 5.10 & ODBC Question

8. 8.1.6 and JServer installation

9. Online 5.10 and JDBC connectivity problems

10. VB and Online 5.10

11. How does remote client connect to 5.10 online server

12. Experiences with Online 5.10