Any proactive schema lock for exclusive access to perform database changes (SQL92 DDL)? - Forum - OpenEdge RDBMS - Progress Community

Any proactive schema lock for exclusive access to perform database changes (SQL92 DDL)?

 Forum

Any proactive schema lock for exclusive access to perform database changes (SQL92 DDL)?

This question is not answered

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.  

All Replies
  • I'm not sure if the trick can be used by SQL client but 4GL can get an exclusive schema lock by the following code:

    DO TRANSACTION:
      CREATE _File.
      MESSAGE "Schema is locked" VIEW-AS ALERT-BOX.
      UNDO.
    END.

    promon/R&D/1/19 shows:

    11/15/19        Status: Schema Locks & Wait Queue by user number for all tenants
    14:46:43
    
      Usr:Ten    Name      Domain    Locked     Queued
    
        5                       0    SHR
        5                       0    EXCL
    
      Total Locks Granted - SHR: 1     EXCL: 1
  • Yes, that is the type of thing I'm looking for on SQL92.  Only I would want to lock it in my SQL connection, and perform a number of DDL commands and then commit at the very end.  

    The end goal is just to be able make schema changes without an interference from all the other (potential) clients of the database.

    What do you think of the idea of temporarily starting the database with a different port number, just for the purpose of dictionary changes?  I'm starting to think that is a far more straightforward solution than anything else I've come across.

  • If you were editing schema from the Data Dictionary, you could just start in single-user mode which would lock everyone else out. But I don't think there is a concept of SQL connections in single-user mode. Starting the database on a different port than all the clients are connecting to would essentially lock all of them out because the server wouldn't be listening on that port. If you already are stopping/starting the database, this seems like a very straightforward option.

    Alternatively, you could mess with database startup parameters to limit the number of connections on that port to a single client, but that seems like a bad idea and you may still have interference with other clients reconnecting before you do.

  • When I hear a "single-user mode" I could not stop thinking why all other databases in this world did not come to that brilliant idea to have a "single-user mode".

    To do a schema changes I use "Three ports method" -- the idea I picked up from George many years ago, So I start database with 1 "dummy port", 1 ABL only port and 1 SQL only port. Separation of 4GL from SQL connections does not need explanation. But first I start database with a "dummy" port number. And at this moment I can make any schema changes I want.

    The side benefit of that method is that I can restart a secondary broker, either 4GL one or SQL one. While I do not have to restart the primary one that I call "dummy" ( the one port number I am not opening to any one).

    Dmitri Levin

    Alphabroder

  • Thanks, its good to know that the port-number-approach is something that others are already doing.  

    If there were a KB to this effect, that would give the approach a bit more weight... IE. maybe it should even be the "authoritative" approach for doing all DDL changes, (for lack of a single-user mode).

    Unfortunately I haven't found a KB article about this yet so I'm struggling to convince our OpenEdge DBA's of the efficacy and efficiency of this approach.  There isn't a lot to go on.  There is mainly just a lot of emphatic hand-waving on my part.  As a side, messing with OE database ports is not new to us.  Our entire "security" model for ABL client/server involves filtering the database ports in HP-UX and restricting access to the ports as much as possible.

  • Maybe I'm having a slow day but... doesn't this port fiddling approach still leave open the possibility of shared memory connections?

    --
    Tom Bascom
    tom@wss.com

  • >> doesn't this port fiddling approach still leave open the possibility of shared memory connections?

    Yes, there is no change in the existing shared-memory connections.

    That's why it is "security" with quotes around it.  Historically we were *only* using shared-memory connections from the CHUI processes on our HP-UX servers.  Security concerns didn't exist back in those days.

    But when we started firing up the *remote* servers for ABL, then all of a sudden we started thinking about "security".  

    This is especially because of the fact that developers can connect their PDSOE (development license) to a remote, production -licensed database.  That allows us to compile and run code that updates any production data we want, any time we want.  It circumvents not only "security" , but also a ton of software controls that we tried to set in place.  At least with the shared-memory connections we could NOT compile and update data in production (*) since the only license available to us on that HP-UX server was a "production" license.

    Anyway to make a long story short, the remote servers were more of a "security" concern than the existing shared-memory connections.  This is probably just because they were new to us, and because developers could do scary things to a production database from our personal workstations.  So we started using ip filtering and disallow all remote connections to the port unless they were white-listed by the OE dba.

  • Yes, port number change does no affect shared-memory connections.

    To avoid shared-memory connections I rename the human users startup procedure, the one in their .profile, to temp name before shutdown and then rename back after they are allowed to login.

    As to the batch jobs they check for the existence of STOP file before connection. So I temporary put a STOP file.

    Dmitri Levin

    Alphabroder

  • I thought he was pointing out that the port fiddling - for security purposes - didn't impact shared memory connections.  It makes more sense that he was talking about schema changes rather than security.  Nobody talks about security.

    The shared-memory connections are *much* easier to manage.  Those consist of only the connections from the same localhost.  In contrast the client-server connections can be made from anywhere on the intranet.  You would have a lot more trouble tracking them down and disabling them all.

  • For ABL sessions (remote or shared memory connections) we can use the dbauthkey option to control db access during maintenance.

  • > On Dec 14, 2019, at 5:30 AM, George Potemkin wrote:

    >

    > For ABL sessions (remote or shared memory connections) we can use the dbauthkey option to control db access during maintenance.

    >

    >

    >

    brilliant