Results 1 to 8 of 8
  1. #1
    Join Date
    May 2003
    Posts
    57

    Unanswered: I want to lock whole db only for me?!

    Hello all!

    I can't find is it possible at all or how I can do this easy.
    There is DB2 db v.7.2.5 on AIX v4 and there I have many applications and users... It is 24hours on line. Now I need to do some changes on many tables - at this moment I don't even know how many... I will see that during the work. At the end I need one off-line backup. So I would like to lock db during that time only for me, and don't want to check for users every time and force their applications. I have dbadm authority.
    Because there are many users and groups who have very different authorities on db I'd like to avoid revoking, and than granting, authorities to all of them.

    Thank you in advance...
    NNicole

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I can think of three options - Choose the one that suits you best:
    1) Force off all applications and connect to the database in exclusive mode
    $ db2 connect to sample in exclusive mode
    Once you terminate, the database will accept connections.
    This will prevent new applications connecting to the database.
    2) If all clients are remote tcpip clients, then update the svcename to '' , stop-start the instance. So there will be no listner and hence the database will not receive any remote requests. Once you have your job done, change the svcename to the correct value and stop-start the instance.
    3) If you have some local clients also, you can probably revoke just the CONNECT Privileges for the group of users before you start your work. On finsihing your work, re-grant the privileges.

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    May 2003
    Posts
    57
    Sathyaram_s, thank you!

    NNicole

  4. #4
    Join Date
    Mar 2004
    Posts
    46
    Another option is to "quiesce the database".....

    Here's the description from the Command Reference Manual -

    Quiesce
    Forces all users off the specified instance and database and puts it into a
    quiesced mode. In quiesced mode, users cannot connect from outside of the
    database engine. While the database instance or database is in quiesced mode,
    you can perform administrative tasks on it. After administrative tasks are
    complete, use the UNQUIESCE command to activate the instance and
    database and allow other users to connect to the database but avoid having to
    shut down and perform another database start.
    In this mode only users with authority in this restricted mode are allowed to
    attach or connect to the instance/database. Users with sysadm, sysmaint, and
    sysctrl authority always have access to an instance while it is quiesced, and
    users with sysadm authority always have access to a database while it is
    quiesced.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Is that available on version 7?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Sep 2003
    Posts
    237
    You could, as an alternative, catalog the database with a different, YOUR OWN alias and then uncatalog the usual alias. You connect with your alias, force others out, finish your work and then catalog the db with the original alias.
    mota

  7. #7
    Join Date
    May 2003
    Posts
    57
    Just thank you all!

  8. #8
    Join Date
    May 2003
    Posts
    57
    ...and one notice more...
    Quiesce db is not possible in v.7.
    But I have other solutions.
    :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •