[Progress Communities] [Progress OpenEdge ABL] Forum Post: Any proactive schema lock for exclusive access to perform database changes (SQL92 DDL)?

[dbeavon]

Is there a way to administratively get a schema lock on the entire database? I am working in our development environment. I can stop the database and start it back up, but the nature of our environment is that we have *lots* of remote clients making lots of connections (eg. developers who have PDSOE, windows services, PASOE applications, batch processes, etc). Many of these are resilient to outages and they reconnect within minutes of the database becoming available again on the network. There are a large enough number of remote clients that it is not really feasible to "track them down" and ask them to kindly refrain from making connections for a period of time. Without a way to exclude the remote clients, it is extremely hard to perform schema changes. It involves lots of iterative attempts (shut down database, start database, quickly attempt schema change via sqlexp command, if schema lock was unsuccessful then repeat). I'd like to be able to open a SQL (ODBC) connection to the server and take an exclusive/proactive schema lock after the database starts. Then we could perform all our dictionary changes on that single connection (via SQL DDL in ODBC). Is there an explicit way to take an "administrative" schema lock on the entire database for SQL purposes? Any alternate approaches would be welcome. I've also asked our DBA and our Progress consultant to look into this, but we are all stumped. The best we've been able to do thus far is to "track down" all the remote clients one-by-one. We should be allowed to perform administrative operations without being at the mercy of remote client connections. One possible idea I've had (an ugly one) is to add 10,000 to the SQL port number that is used for serving up remote connections. By restarting the database temporarily on a port that nobody knows about, it gives the administrator exclusive access. This idea wasn't a very popular one, for some reason. I'm hoping there are others. This issue has become more and more painful as we've started connecting more remote applications to our databases (client/server and SQL92). The context for doing this is primarily in pre-production environments. Thanks in advance.

Continue reading...