How to get into single user mode in Db2?

Question

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?

• mustaccio: No there isn't a single user mode in Db2.
• Ian Bjorhovde: Have you tried connect to <dbname> in exclusive mode ?
• folow: 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.
• Mark Barinstein: @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.
• Lennart - Slava Ukraini: Somewhat related: ibm-data-and-ai.ideas.ibm.com/ideas/DB24LUW-I-2058

Answer 1

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