Exclusive access to a Database
Stephen's dbexport failed with "-425 Database is currently opened by another user" on IDS 9.40 and he needed to find which of 400+ sessions held the database open. Suggestions included a sysmaster query joining sysopendb/sysscblst, onstat -g ses with onmode -z, and onstat -g sql | grep dbname. These didn't reveal the culprit, since a session connected to one database can access tables in another. Art Kagel's advice: find the partnum of the target database's systables and locate the shared table-level lock on it (via sysmaster or onstat -k, mapping lock addresses to sid/pid), as any session using a database holds that lock; an IIUG find_locks script was also suggested. No confirmation of success is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I am attempting to do a dbexport (to reorg a database) but the engine is
telling me that I am unable to get exclusive access to the database because
somebody else is accessing it.
Is there some command or sql statement that I can run to see who is accessing
a database (so I can disconnect them)?
IDS 9.40.FC4
hp-ux 11i (64 bit pa-risc)
Thanks,
Stephen
If you can't do the basics, you should not be attempting a reorg.
"STEPHEN SCOTT" <dba.lumber@gmail.com>
Sent by: ids-bounces@iiug.org
08/11/2006 17:18
Please respond to
ids@iiug.org
To
ids@iiug.org
cc
Subject
Exclusive access to a Database [7758]
I am attempting to do a dbexport (to reorg a database) but the engine is
telling me that I am unable to get exclusive access to the database
because
somebody else is accessing it.
Is there some command or sql statement that I can run to see who is
accessing
a database (so I can disconnect them)?
IDS 9.40.FC4
hp-ux 11i (64 bit pa-risc)
Thanks,
Stephen
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Against sysmaster:
select sid sessionid, trim(username) user, trim(odb_dbname) db,
trim(hostname) host from sysopendb, sysscblst where sid = odb_sessionid
and odb_dbname != 'sysmaster' and odb_iscurrent = 'Y' order by 1
DC
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> STEPHEN SCOTT
> Sent: Wednesday, November 08, 2006 10:19 AM
> To: ids@iiug.org
> Subject: Exclusive access to a Database [7758]
>
>
> I am attempting to do a dbexport (to reorg a database) but the engine
is
> telling me that I am unable to get exclusive access to the database
> because
> somebody else is accessing it.
>
> Is there some command or sql statement that I can run to see who is
> accessing
> a database (so I can disconnect them)?
>
> IDS 9.40.FC4
> hp-ux 11i (64 bit pa-risc)
>
> Thanks,
> Stephen
>
>
>
************************************************************************
**
> *****
> Forum Note: Use "Reply" to post a response in the discussion forum.
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On
> Behalf Of STEPHEN SCOTT
> Sent: Wednesday, November 08, 2006 11:19 AM
> To: ids@iiug.org
> Subject: Exclusive access to a Database [7758]
>
>
> I am attempting to do a dbexport (to reorg a database) but
> the engine is telling me that I am unable to get exclusive
> access to the database because somebody else is accessing it.
Database? or table?
>
> Is there some command or sql statement that I can run to see
> who is accessing a database (so I can disconnect them)?
>
> IDS 9.40.FC4
> hp-ux 11i (64 bit pa-risc)
onstat -g ses will give you the session id and pid for each session. (ifyou have DBSA priveleges)
From there, use onmode -z to terminate a specific session id. Choose
wisely, you can bork your engine if you kill one one of the oninit
processes.
I'd suggest you look at the online manuals for IDS.
http://www-306.ibm.com/software/data/informix/pubs/library/
DT
Doug,
I used your query. The database in question didn't appear in the returns but I
still get the following when I run the dbexport command:
-bash-3.00$ dbexport /dbdump -ss mmls_db-425 - Database is currently opened by another user.
-107 - ISAM error: record is locked.
I know that I can do an onstat -u and an onmode -z {userid} but I have 400+
users of which most likely 1 or 2 are actually the offenders. Is there some
other query that will return to me what informix thinks are the sessions that
have the database open are?
Thanks,
Stephen
"onstat -g sql" shows which sessions are using which db.
Bob Roussey
Unix / Informix Administration
Spirit Airlines
Robert.Roussey@SpiritAir.com
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
STEPHEN SCOTT
Sent: Wednesday, November 08, 2006 3:10 PM
To: ids@iiug.org
Subject: Re: RE: Exclusive access to a Database [7767]
Doug,
I used your query. The database in question didn't appear in the returns
but I
still get the following when I run the dbexport command:
-bash-3.00$ dbexport /dbdump -ss mmls_db-425 - Database is currently opened by another user.
-107 - ISAM error: record is locked.
I know that I can do an onstat -u and an onmode -z {userid} but I have
400+
users of which most likely 1 or 2 are actually the offenders. Is there
some
other query that will return to me what informix thinks are the sessions
that
have the database open are?
Thanks,
Stephen
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
Have you considered using onstat -g sql | grep database_name ?
Take care.
Clifton
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
STEPHEN SCOTT
Sent: Wednesday, November 08, 2006 2:10 PM
To: ids@iiug.org
Subject: Re: RE: Exclusive access to a Database [7767]
Doug,
I used your query. The database in question didn't appear in the returns but
I
still get the following when I run the dbexport command:
-bash-3.00$ dbexport /dbdump -ss mmls_db-425 - Database is currently opened by another user.
-107 - ISAM error: record is locked.
I know that I can do an onstat -u and an onmode -z {userid} but I have 400+
users of which most likely 1 or 2 are actually the offenders. Is there some
other query that will return to me what informix thinks are the sessions
that
have the database open are?
Thanks,
Stephen
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
It could be that a user session is attached to database A but accessing
table(s) in database B. You will not see this with an "onstat -g sql" or
the query Doug gave you. Tho that is a good place to start.
I believe you can download a "find_locks" script from the IIUG site that
might be of help to you.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
Clifton Bean
Sent: Wednesday, November 08, 2006 2:22 PM
To: ids@iiug.org
Subject: RE: RE: Exclusive access to a Database [7770]
Have you considered using onstat -g sql | grep database_name ?
Take care.
Clifton
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
STEPHEN SCOTT
Sent: Wednesday, November 08, 2006 2:10 PM
To: ids@iiug.org
Subject: Re: RE: Exclusive access to a Database [7767]
Doug,
I used your query. The database in question didn't appear in the returns
but
I
still get the following when I run the dbexport command:
-bash-3.00$ dbexport /dbdump -ss mmls_db-425 - Database is currently opened by another user.
-107 - ISAM error: record is locked.
I know that I can do an onstat -u and an onmode -z {userid} but I have
400+
users of which most likely 1 or 2 are actually the offenders. Is there
some
other query that will return to me what informix thinks are the sessions
that
have the database open are?
Thanks,
Stephen
************************************************************************
****
***
Forum Note: Use "Reply" to post a response in the discussion forum.
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
Yes. You have to find the partnum of systables in the database you need
exclusive access to. Then you have to look for a shared table level lock on
that partnum and find the session id for those lockers. Any session that is
accessing a database must take a shared lock on <database>:systables. You can
do all this is sysmaster or using onstat but it's awkward using onstat because
onstat -k doesn't report sessions ids (sid) only addresses which you'd have tomap to a session id in onstat -u and from that to a process is (pid) in onstat
-g ses <sid>.
Art S. Kagel
----- Original Message -----
From: Stephen Scott <ids@iiug.org>
At: 11/08 15:21:52
Doug,
I used your query. The database in question didn't appear in the returns but I
still get the following when I run the dbexport command:
-bash-3.00$ dbexport /dbdump -ss mmls_db-425 - Database is currently opened by another user.
-107 - ISAM error: record is locked.
I know that I can do an onstat -u and an onmode -z {userid} but I have 400+
users of which most likely 1 or 2 are actually the offenders. Is there some
other query that will return to me what informix thinks are the sessions that
have the database open are?
Thanks,
Stephen
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Related threads
- Posting from the Informix-list
- Migrating from IDS 9.40.UC6 to 11.50.UC3
- Ip for a network session
- questions onstat -g