Applying DDL changes without human control

[former_member329524]

Hello, all

We have around 1000 installations of our PMS system in various properties around the world and we need to figure out how to update the DB schema without having to manually connect and run SQLs.

Presuming that the required sql scripts are already available for execution in every client property, we need to solve the following issues.

1. Issue a DB command, which will prevent DB users to make connections.

2. Disconnect any users, which are currently connected to DB (without disconnecting the current connection, of course).

3. Run the scripts

4. Allow the DB to accept new connections.

In the past we ran into issues with the second step - connections would not get disconnected. Also, what would be best ans safest way to disallow new DB connections and then allowing them again? If during step 3 the connection will get accidentally dropped, how will anyone be able to reconnect?

Thank you

Arcady

[former_member182948 · Accepted solution]

Hi Arcady,

I recommend that you start a database server by the different name for maintenance.

It won't receive new connection from clients.

Your request will probably like the following.

1.You can change the server option by sa_server_option system procedure.

To disable new connection :

CALL dbo.sa_server_option( 'ConnsDisabled', 'Yes' );

"sa_server_option system procedure"

http://dcx.sap.com/index.html#sa160/en/dbreference/sa-server-option-system-procedure.html

2.You can disconnect any user by DROP CONNECTION statement.

"DROP CONNECTION statement"

http://dcx.sap.com/index.html#sa160/en/dbreference/drop-connection-statement.html

3.Reads Interactive SQL statements from a file.

"READ statement [Interactive SQL]"

http://dcx.sap.com/index.html#sa160/en/dbreference/read-statement.html

4.To enable new connection :

CALL dbo.sa_server_option( 'ConnsDisabled', 'NO' );

>if during step 3 the connection will get accidentally dropped, how will anyone be able to reconnect?

The new connection will be accepted if all existing connection was lost.

example:

--

CALL dbo.sa_server_option( 'ConnsDisabled', 'Yes' );

--

BEGIN

  DECLARE cur_CONS CURSOR FOR

      SELECT NUMBER FROM sa_conn_list ()

      WHERE NUMBER NOT IN(connection_property('NUMBER'));

  DECLARE conn_number INTEGER;

--

  OPEN cur_CONS;

  lp: LOOP

    FETCH NEXT cur_CONS INTO conn_number;

    IF SQLCODE <> 0 THEN LEAVE lp END IF;

    EXECUTE IMMEDIATE 'DROP CONNECTION ' || conn_number;

  END LOOP;

  CLOSE cur_CONS;

END;

--

READ test.sql;

--

CALL dbo.sa_server_option( 'ConnsDisabled', 'NO' );

--

Regards

Koichi

[former_member329524 · Accepted solution]

Thank you very much Koichi

I was thinking along the same lines. Thank you for filling in the blanks.

Arcady