Database lock
User asked how to lock a database indefinitely without using sleep commands. Responses suggested several alternatives: revoking CONNECT privileges, keeping an exclusive database session open without closing it, putting the instance in single-user mode, renaming the database, or using a custom utility to hold the connection. Connection Manager was noted to work only with instances, not individual databases.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Is there a way that I can lock a database indefinetely? database abc exclusive ! sleep 40000 seems a bit cludgy.
The lock will hold as long as your session... Which means that if the session is lost, so is the lock... You may want to consider other means: 1- Remove access privileges 2- Put the instance on single user mode (if that's the only database you have on that instance) 3- Rename the database... weird, but may work... 4- Kill all user sessions and create a public.sysdbopen() procedure that will refuse all connections... Regards. On Mon, Jan 30, 2012 at 5:40 PM, LUKE SIMMONS <luke.simmons@vgregion.se>wrote: > Is there a way that I can lock a database indefinetely? > > database abc exclusive > ! sleep 40000 > > seems a bit cludgy. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --485b397dd695ada99404b7c2c7ac
We are migrating about 40 databases to 11.70 on different hardware. Right now we have 3 different instances on different machines and everything is going to be consolidated on the same machine, same instance. I like the idea of renaming the database: database foo exclusive alter database foo to bar better than my original idea. We are looking right now at connection manager so that we can lock a database (and rename it) and then importing it into our "new" environment. After the import we would like to point our connection manager at the new database, but it doesn't seem like there's any way to point to the new database, rather it seems like you can only tune the instance names. Am I missing something? Or are we going to be forced to do this instance by instance?
Revoke CONNECT privileges from all users? Or ...
DATABASE abc EXCLUSIVE;
then just don't close the session. If you get my package utils_ak, there
is a utility in there, dbping.ec, which connects to a database in order to
time the connection process. Normally it exits immediately, but you could
make the DATABASE statement EXCLUSIVE then make another one line change to
it to have it read from stdin or from a pipe after opening the database and
block on the read until there's something there to be read. Then you could
control the lock by leaving it stuck there and release the lock be writing
to the pipe it is waiting on.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Mon, Jan 30, 2012 at 12:40 PM, LUKE SIMMONS <luke.simmons@vgregion.se>wrote:
> Is there a way that I can lock a database indefinetely?
>
> database abc exclusive
> ! sleep 40000
>
> seems a bit cludgy.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8f22c6811c48dc04b7c3099f
CM only works with instances... Unless you can define services names for each database... Regards. On Mon, Jan 30, 2012 at 6:27 PM, LUKE SIMMONS <luke.simmons@vgregion.se>wrote: > We are migrating about 40 databases to 11.70 on different hardware. Right > now > we have 3 different instances on different machines and everything is > going to > be consolidated on the same machine, same instance. > > I like the idea of renaming the database: > > database foo exclusive > alter database foo to bar > > better than my original idea. > > We are looking right now at connection manager so that we can lock a > database > (and rename it) and then importing it into our "new" environment. After the > import we would like to point our connection manager at the new database, > but > it doesn't seem like there's any way to point to the new database, rather > it > seems like you can only tune the instance names. Am I missing something? Or > are we going to be forced to do this instance by instance? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --20cf300faebb936a9804b7c30c34
Crap. Well... you can always dream. I am also going to pollute this thread a bit and say that I really enjoy both of your blogs (you and Art's), and not just the Informix stuff, but also the daily dealings with typical problems. You had a nice post about dealing with ignorant technicians awhile back. Good stuff!! Keep it up!!