0

Microsoft SQL Server

ALTER DATABASE baza SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Above command does:

  1. Forces all of the users from database.
  2. Restricts access to database to all users:
    • except one and only user that has executed the command
    • any other user even super-user can not get access to database
    • even the same user that put database in single user mode but in second connection is restricted to database

Db2

QUIESCE DATABASE IMMEDIATE
  1. Forces all users from database.
  2. Restricts access to database to all users:
    • except all users with SYSADM, SYSMAINT, DBADM, or SYSCTRL authorities.

Is there single user mode in Db2 v11.5/Linux, so only one single user that has put database into single user mode has access to it in one single session?

Improve this question
5
  • 1
    No there isn't a single user mode in Db2.
    – mustaccio
    Commented May 27, 2022 at 12:39
  • 1
    Have you tried connect to <dbname> in exclusive mode ?
    – Ian Bjorhovde
    Commented May 30, 2022 at 7:45
  • I tried it now. Interesting idea. But it is not truly "single user mode". Disadvantages: 1. Command does not forces other user from database; 2. Restricts connections to all users except the user with the same userid (this user can connect in multiple sessions); 3. Another db administrator can force this connection from the database and other users can automatically connect to database.
    – folow
    Commented May 30, 2022 at 12:05
  • @folow You may code the desired behavior in the Connect procedure raising the exception when, say, you have set some flag in a service table (assuming you don't need such a single user mode permanently) + SELECT 1 FROM TABLE (MON_GET_CONNECTION (NULL, -2)) WHERE APPLICATION_HANDLE <> MON_GET_APPLICATION_HANDLE() LIMIT 1 returns a row.
    – Mark Barinstein
    Commented Jul 7, 2022 at 11:12
  • Somewhat related: ibm-data-and-ai.ideas.ibm.com/ideas/DB24LUW-I-2058
    – Lennart - Slava Ukraini
    Commented Dec 4 at 8:45

1 Answer 1

Reset to default
0

In our case all users are connecting to database using TCP/IP protocol. I somehow made a work-around by temporally disable TCPIP protocol.

  1. Connect to Linux server with SSH.

  2. Disabled TCPIP.

    db2set DB2COMM=

  3. Stop instance.

    db2stop force

  4. Do the task I want to do in "single user" mode.

  5. Set back TCPIP and start instance.

    db2set DB2COMM=TCPIP
db2start

I know this is not true "single use" mode. Disadvantages:

  • if there are multiple databases in instance, all databases will lose TCP/IP connections
  • if there are local Db2 tasks within Linux itself, they will not be blocked
  • it is required to coordinate between administrators, so some other admin does not start the database
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.