On Tuesday 07 February 2006 5:57 pm, Kathey Marsden wrote: > I got a request from a user who actually who asked for an option to > automatically shutdown the database if there are no more connections. > Does anyone have any ideas how to handle the scenario below and > conditionally shutdown if there are no active connections? > > From the user ... > > "We have run into an issue where a simple test connection that > customers are used to will cause the database to be useless unless the > NODE is rebooted. When configuring Failover, there was one option to do > a stand-alone where if the fist node fails the second will take on, > Customers typically would test the connection on each of the nodes which > point to the same database. This is failing today because after the > first test, and even though the connection is closed, the database can't > be accessed by the second node (JVM). The test connection GUI can't do > a shutdown since some other customers use it sometimes while > applications are running and shutting the database then will result in > breaking those APPS. > > Uhm... It looks like the "user" is implying that they have two nodes attempting to connect to the same Derby database instance. (Implying that Derby is located on a SAN or some other type of NFS mounted file system.)
So that when one node is up, there is a lock on the derby files that will only get released when the engine goes down. I'd suggest that they either: 1) Enhance Derby by adding some HA/DR feature functionality. 2) Move to a different database that does allow for failover and high availability. The key is that they are attempting to design something with a tool that isn't capable of doing what they want. This isn't a fault of Derby, but of the solution architect. Having said that.... I don't have derby up and I couldn't find a list of the Systables easily in the docs that I have. Supposing that there is a table that tracks authenticated users and their sessions, you could put a wrapper around the network start up script to wait 30 seconds and then start your "user scanner" program. Then when all users are disconnected, the scanner will then close itself and the next line in your wrapper will drop the database.) Note that there has to be a delay so that the first user that calls the connection script will have time to establish a connection else the "user scanner" may drop the database prior to he/she being able to log in. Note one small problem. Performance. Unless you can guarantee that you'll always have at least one user, there will always be a huge performance hit for the first user. (Starting up the database.) Oh and you'll also have to determine which node to connect from to get the database up and running. (So if you indicate a primary and its down, it will attempt to start the engine. You'll have to trap for an error condition where the start up fails on lockout, and then switch to the secondary node. One consolation is that if you fail at start up, you'll know that the second node is already up.) Please also note that some of the startup logic is missing and is left as an exercise to the student. Not a good idea or a good solution. Or am I off base because I didn't under stand the question? (A lack of caffeine and provigil will have that effect.) HTH, -G > Please help, we have 2 defects from the test team on this that I am > reluctant to return as-designed, and I expect many coming from the field > as well." > > Thanks > > Kathey -- -- Michael Segel Principal Michael Segel Consulting Corp. [EMAIL PROTECTED] (312) 952-8175 [mobile]
