Database is currently open
rkusenet
This stupid bug is annoying me.
We want to rename a database. It had few sessions to it attached
which I killed using onmode -z sessionid.
Now when I try to rename it, it gives an error "database is currently
open". I don't see any user connected to it thru onstat -g sql.
I don't want to restart informix just for this. Is there a workaround
for it.
Ravi.
M.J.R.
I'm not sure this is your problem, but you cannot have
the database opened when you do a rename .. i.e.
choose a different database in dbaccess when you go to
rename the database.
HTH
__________________________________________________
Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes
http://finance.yahoo.com
007
Have you tried:
onstat -u? Look for any non-informix processesses.
Since you have no user connection, you could take the database down into
quiesent mode, rename the database, then bring it back up into a
multi-user mode.
James
keith
> IDS 9.21.UC4
>
> This stupid bug is annoying me.
>
I don't think this is a(n) (Informix) bug -- if that's what you
mean....
> We want to rename a database. It had few sessions to it attached
> which I killed using onmode -z sessionid.
>
> Now when I try to rename it, it gives an error "database is currently
> open". I don't see any user connected to it thru onstat -g sql.
Remote sessions won't show up in onstat -g sql
Try this query through sysmaster:
select syslocks.owner
from syslocks,
sysdbspartn,
outer syssessions
where syslocks.tabname = 'sysdatabases' and
syslocks.owner = syssessions.sid and
sysdbspartn.name = '<your db name>'
This will give you all local/remote sessions and do an 'onmode -z' on
them.
Make sure to revoke connect before disconnecting any sessions. You
should now be able to rename the database. (don't forget to grant
connect when your are done).
Keith Ponnapalli
rkusenet
>
> Try this query through sysmaster:
> select syslocks.owner
> from syslocks,
> sysdbspartn,
> outer syssessions
> where syslocks.tabname = 'sysdatabases' and
> syslocks.owner = syssessions.sid and
> sysdbspartn.name = '<your db name>'
The above query returs 1123 rows and when I do
onstat -g sql <id returned by above sql> I get sessions
to other databases which has no relation to this.
I am not sure whether the above query is correct.
BTW I notice that the database which I want to rename
has lot of entries in onstat -g dic. Has it got something
to do with the problem.
keith
> > Remote sessions won't show up in onstat -g sql
> >
> > Try this query through sysmaster:
> > select syslocks.owner
> > from syslocks,
> > sysdbspartn,
> > outer syssessions
> > where syslocks.tabname = 'sysdatabases' and
> > syslocks.owner = syssessions.sid and
> > sysdbspartn.name = '<your db name>'
>
> The above query returs 1123 rows and when I do
> onstat -g sql <id returned by above sql> I get sessions
> to other databases which has no relation to this.
>
> I am not sure whether the above query is correct.
>
clause:
syslocks.rowidlk = sysdbspartn.rowid
sorry. I forgot to add it.
(I use this all the time. I know this query's correct)
> BTW I notice that the database which I want to rename
> has lot of entries in onstat -g dic. Has it got something
> to do with the problem.
onstat -g dic gives a snapshot of all table's data dictionary info
that's in memory (data dictionary cache). I don't think it's part of
the problem.
Keith Ponnapalli
rkusenet
> Ooops. You will need to add this additional qualifier to the where
> clause:
> syslocks.rowidlk = sysdbspartn.rowid
> sorry. I forgot to add it.
> (I use this all the time. I know this query's correct)
I ran the corrected query and it did identify some sessions
which were connected to the database.
I killed those sessions and proceeded to rename the database.
This time I did not get "database is opened" error. However
I got this error:-
355: Cannot rename file for table systables.
Since I am interested in dropping the database,
I tried dropping the database and I got this error:-
214: Cannot remove file for table (systables).
106: ISAM error: non-exclusive access.
I don't see any process locking that table (systables).
Any pointers. I would like to avoid bouncing off informix
to drop this database.
carol
databases and partnum of tables. The 'partnum' you get from the
command is equal to 'lower(hex(sysmaster:systabnames.partnum))'. And
you can find thread-id and session-id from 'sysmaster:sysrstcb.tid'
and 'sysmaster:sysrstcb.sid'.
007 <james...@jbcconsulting.com> wrote in message news:<aliq0d$uti$1...@terabinaries.xmission.com>...
keith
Are you sure you've done (in this order):
1. Revoke connect perms(resource/dba perms from every non-informix
user, if any)
2. Detach all sessions
3. echo "rename database <CurrentName> to <NewName>;" | dbaccess
sysmaster
I am not sure what else is causing lock issues -- see if onstat -g opn
and -k options point to any thing.
Keith
Colin Bull
If Replication is running a 'cdr stop' first might be of some use.
But it might not !
Colin Bull
c.b...@VideoNetworks.com
James Hearn
Your reply interested me as I have just posted a question similar to
this. I am trying to get the session id of any session that has any
kind of lock on a particular table. To be exact I am trying to get
from the sysmaster database a list of sessions with simple locks (such
as generated with select * from ...) upon a table - in otherwords they
do not show in syslocks etc.
These locks prevent exclusive access to a table but as yet I have been
unable to find a record of these in sysmaster.
Your comments regarding 'onstat -g opn' show me the locks I am after
but I need to get this information out of sysmaster - any idea where
this command reads from ?
Regards
James Hearn
Malcolm Weallans
James,
I think you will find that the output of onstat -g opn can be provided
by an SQL statement linking sysrstcb, syssessions, and systabnames.
regards
Malcolm
-----Original Message-----
From: James Hearn [mailto:james...@uk.cunninghamlindsey.com]
Sent: 11 September 2002 11:37
To: inform...@iiug.org
Subject: Re: Database is currently open
Carol
Regards
James Hearn
DISCLAIMER
This message contains confidential information and is intended only for
the individual named. If you are not the named addressee you should not
disseminate, distribute or copy this e-mail. Please notify the sender
immediately by e-mail if you have received this e-mail by mistake and
delete this e-mail from your system. E-mail transmission cannot be
guaranteed to be secure or error-free as information could be
intercepted, corrupted, lost, destroyed, arrive late or incomplete, or
contain viruses. The sender therefore does not accept liability for any
errors or omissions in the contents of this message, which arise as a
result of e-mail transmission. If verification is required please
request a hard-copy version.
James Hearn
Many thanks - this is pretty much perfect
James
>>> "Malcolm Weallans" <MWEA...@lqgroup.org.uk> 11/09/02 13:36:55 >>>
regards
Malcolm
Carol
Regards
James Hearn
------------------------------------------------------------------------------------------------------
The information in this email is confidential and the contents
are not to be disclosed to anyone other than the addressee,
except with the authority of the addressee.
The opinions expressed within this email represent those of
the individual and not necessarily those of Cunningham Lindsey.
If received in error please advise the sender and delete from
your system.