Getting exclusive access to a database

How can I get exclusive access to one particular database in an instance without bouncing the engine? I am attempting to export a small database on a production server, and do not want to disrupt the other, more important databases in the instance. What I would really like is something similar to the Unix 'who' command that would allow me to identify sessions connected to a database, then I could shut the applications connecting to the db properly or kill the sessions. Any ideas? duff Sent via Deja.com http://www.deja.com/

neurologic wrote: > > How can I get exclusive access to one particular database in an > instance without bouncing the engine? Kill all the sessions connecting to that database using onmode -z. > > I am attempting to export a small database on a production server, and > do not want to disrupt the other, more important databases in the > instance. > > What I would really like is something similar to the Unix 'who' command > that would allow me to identify sessions connected to a database, then > I could shut the applications connecting to the db properly or kill the > sessions. > > Any ideas? Try this script AT YOUR OWN RISK. Execute as informix with environment set. Tested against IDS2000 only. Be aware that this will terminate any database session that is connected to the database selected, including those sessions connected to multiple databases. Enjoy. Brett Randall <script> #!/bin/sh [ $# -ne 1 ] && echo "usage $0 database_to_clear" && exit 1 # start buliding a kill script KILLSCRIPT=/tmp/$$.sh dbaccess sysmaster <<EOF unload to $KILLSCRIPT delimiter ";"select "onmode -z " || odb_sessionid from sysopendb where odb_dbname = "$1"; EOF chmod 700 $KILLSCRIPT /bin/sh $KILLSCRIPT rm -f $KILLSCRIPT </script> > > duff > > Sent via Deja.com > http://www.deja.com/

neurologic wrote in message <937slv$38b$1@nnrp1.deja.com>... >How can I get exclusive access to one particular database in an >instance without bouncing the engine? > >I am attempting to export a small database on a production server, and >do not want to disrupt the other, more important databases in the >instance. dbexport locks the database in exclusive mode, so it will prevent access during the export, and that fact that it starts the export at all will "prove" that there are no users on the database at the time you initiate the export. So, once you have removed the sessions - no further tricks necessary. Cheers.

Brett, Thanks a million, that was exactly what I was looking for. I had been staring at the sysmaster tables for awhile before I posted, I'm suprised I missed something as obvious as "sysopendb"! Thanks again Duff In article <3A57B9F4.26F67394@hotmail.com>, Brett Randall <brett_s_rREMOVECAPITALS@hotmail.com> wrote: > neurologic wrote: > > > > How can I get exclusive access to one particular database in an > > instance without bouncing the engine? > > Kill all the sessions connecting to that database using onmode -z. > > > > > I am attempting to export a small database on a production server, and > > do not want to disrupt the other, more important databases in the > > instance. > > > > What I would really like is something similar to the Unix 'who' command > > that would allow me to identify sessions connected to a database, then > > I could shut the applications connecting to the db properly or kill the > > sessions. > > > > Any ideas? > > Try this script AT YOUR OWN RISK. Execute as informix with environment > set. Tested against IDS2000 only. Be aware that this will terminate > any database session that is connected to the database selected, > including those sessions connected to multiple databases. > > Enjoy. > > Brett Randall > > <script> > > #!/bin/sh > > [ $# -ne 1 ] && echo "usage $0 database_to_clear" && exit 1 > > # start buliding a kill script > KILLSCRIPT=/tmp/$$.sh > > dbaccess sysmaster <<EOF > > unload to $KILLSCRIPT delimiter ";"> select "onmode -z " || odb_sessionid from sysopendb > where odb_dbname = "$1"; > > EOF > > chmod 700 $KILLSCRIPT > /bin/sh $KILLSCRIPT > rm -f $KILLSCRIPT > > </script> > > > > > duff > > > > Sent via Deja.com > > http://www.deja.com/ > Sent via Deja.com http://www.deja.com/

neurologic wrote: > How can I get exclusive access to one particular database in an > instance without bouncing the engine? > > I am attempting to export a small database on a production server, and > do not want to disrupt the other, more important databases in the > instance. > > What I would really like is something similar to the Unix 'who' command > that would allow me to identify sessions connected to a database, then > I could shut the applications connecting to the db properly or kill the > sessions. You've received answers about how to find people to chuck off the database. The other statement which may be of relevance (but probably isn't) is: DATABASE dbname EXCLUSIVE You only succeed in getting the exclusive access if no-one else is using the database. When a database is created, or rolled forward in SE, then you also obtain exclusive access. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"

We have a similar situation where some remote automated processes continuously attempt to access the database. A simple onmode -z is unworkable as the processes fire off too rapidly. (It was deemed inappropriate to simply kick the power switches.) Informix tech support suggested that we add a unique service name and address to the sqlhosts file and comment out all of the otherwise known names. We also had to change the DBSERVERALIASES line in onconfig to match. (/etc/services too.) Then we connect to the instance with the secret name to do the export. neurologic wrote: > How can I get exclusive access to one particular database in an > instance without bouncing the engine? > > I am attempting to export a small database on a production server, and > do not want to disrupt the other, more important databases in the > instance. > > What I would really like is something similar to the Unix 'who' command > that would allow me to identify sessions connected to a database, then > I could shut the applications connecting to the db properly or kill the > sessions. > > Any ideas? > > duff > > Sent via Deja.com > http://www.deja.com/