1

Let's suppose a database with a bad security design, say everyone access to the database with a user with DBADM or SYSADM authority, and also, there could be only username for everyone (i.e. db2inst1).

I would like to perform an administrative task, for example a schema migration, and I need to be the only one connected to the database.

How can I be sure that I will be the only one that access the database?

  • Quiesce database is not enough because other users access with a high privilege user.
  • Quiesce instance does not allow to activate the database if restrict access is selected.
  • DB2COMM could be removed from db2set, however a local cron could be eventually fired.
  • MAXAPPLS to 1. However, I should be that one, if not force apps again should be issued.
  • Connect in exclusive mode could not work if the user is used by others.
  • Changing the workload configuration to allow only me? (from my IP)

However, those are too many changes just for a simple task.

I saw a similar discussion, however I do not see something that convince me: http://database.ittoolbox.com/groups/technical-functional/db2-l/luw-how-to-prevent-connections-to-a-single-database-within-an-instance-3189860

What are your propositions? What would you do in that case?

  • Colleges from SQL Server rename the DB while the operation is performed. However, this is not a simple task in DB2 (rellocatedb) – AngocA May 19 '14 at 11:46
2

The easiest way to do this would be to simply uncatalog and then re-catalog the database with a different name (perform this as the instance owner on the database server):

  1. db2 uncatalog database SAMPLE
  2. db2 catalog database SAMPLE as XXX on <path>

    ... perform maintenance activities ...

  3. db2 uncatalog database XXX

  4. db2 catalog database SAMPLE on <path>

Any user trying to connect locally on the database server, using connect to sample, will get the error:

SQL1013N  The database alias name or database name "SAMPLE" could not be found.  SQLSTATE=42705

However, for remote connections to also be unable to connect, you'll have to restart the DB2 instance. This is required because the DB2 TCP Connection Manager (db2tcpcm) will cache the the database catalog entries, so until the EDU is restarted it will still "know" about the database. After restarting the DB2 instance, remote connections will get the error:

SQL30061N  The database alias or database name "SAMPLE            " was not 
found at the remote node.  SQLSTATE=08004

Having to restart the instance isn't the best solution, but it works.

  • 1
    Deleted my answer and throwing my vote behind Ian as it is the best overall answer. – Chris Aldrich May 20 '14 at 14:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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