Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Location
    India
    Posts
    30

    Red face Unanswered: what quiescers means

    Hi all

    Can anyone explain me what does quiescers for db- Tablespaces means

    and when to use with some example



    Regards,
    Push

  2. #2
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278

    QUIESCE Command

    QUIESCE Command
    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 and dbadmauthority always have access to a |database while it is quiesced.

    Scope

    |QUIESCE DATABASE results in all objects in the database being in the |quiesced mode. Only the allowed user/group and sysadm, |sysmaint, dbadm, or sysctrl will be able to |access the database or its objects.

    QUIESCE INSTANCE instance-name means the instance and the databases in the instance instance-name will be in quiesced mode. The instance will be accessible just for sysadm, sysmaint, and sysctrl and allowed user/group.

    |If an instance is in quiesced mode, a database in the instance |cannot be put in quiesced mode.

    Authorization

    One of the following:

    For database level quiesce:

    sysadm
    dbadm
    For instance level quiesce:

    sysadm
    sysctrl
    Command syntax

    >>-QUIESCE--+-DATABASE-+--+-IMMEDIATE-+------------------------->
    '-DB-------' '-DEFER-----'

    .-FORCE CONNECTIONS-.
    >--+-------------------+---------------------------------------><



    | .-FORCE CONNECTIONS-.
    |>>-QUIESCE INSTANCE--instance-name--+-------------------+--+-IMMEDIATE-+--+-------------------+-><
    | +-USER--user-name---+ '-DEFER-----'
    | '-GROUP--group-name-'
    |
    |
    Required connection

    Database

    (Database connection is not required for an instance quiesce.)

    Command parameters


    DEFER
    Wait for applications until they commit the current unit of work. This parameter is not currently functional.

    IMMEDIATE
    Do not wait for the transactions to be committed, immediately rollback the transactions.

    FORCE CONNECTIONS
    Force the connections off. |

    |DATABASE
    |Quiesce the database. All objects in the database will be |placed in quiesced mode. Only specified users in specified groups and |users with sysadm, sysmaint, and sysctrl |authority will be able to access to the database or its objects.

    INSTANCE instance-name
    The instance instance-name and the databases in the instance will be placed in quiesced mode. The instance will be accessible only to users with sysadm, sysmaint, and sysctrl authority and specified users in specified groups.

    |USER user-name
    |Specifies the name of a user who will be allowed access to the instance |while it is quiesced. |

    |GROUP group-name
    |Specifies the name of a group that will be allowed access to the instance |while the instance is quiesced.
    Examples

    In the following example, the default behavior is to force connections, so it does not need to be explicitly stated and can be removed from this example.

    | db2 quiesce instance crankarm user frank immediate force connections


    |The following example forces off all users with connections to the |database.

    | db2 quiesce db immediate


    The first example will quiesce the instance crankarm, while allowing user frank to continue using the database.
    |The second example will quiesce the database you are attached to, |preventing access by all users except those with one of the following |authorities: sysadm, sysmaint, sysctrl, |or dbadm.

    This command will force all users off the database or instance if FORCE CONNECTION option is supplied. FORCE CONNECTION is the default behavior; the parameter is allowed in the command for compatibility reasons.
    The command will be synchronized with the FORCE and will only complete once the FORCE has completed.
    Usage notes

    |After QUIESCE INSTANCE, only users with sysadm, |sysmaint, or sysctrl authority or a user name and group |name provided as parameters to the command can connect to the |instance.
    |After QUIESCE DATABASE, users with sysadm, |sysmaint, sysctrl, or dbadm authority, and |GRANT/REVOKE privileges can designate who will be able to |connect. This information will be stored permanently in the database |catalog tables.
    |For example,

    | grant quiesce_connect on database to <username/groupname>
    | revoke quiesce_connect on database from <username/groupname>
    HTH

    Nitin

    Ask the experienced rather than the learned

  3. #3
    Join Date
    Jul 2003
    Location
    Austin, TX, USA
    Posts
    278

    QUIESCE TABLESPACES FOR TABLE Command

    QUIESCE TABLESPACES FOR TABLE Command
    Quiesces table spaces for a table. There are three valid quiesce modes: share, intent to update, and exclusive. There are three possible states resulting from the quiesce function: QUIESCED SHARE, QUIESCED UPDATE, and QUIESCED EXCLUSIVE.

    Scope

    In a single-partition environment, this command quiesces all table spaces involved in a load operation in exclusive mode for the duration of the load operation. In a partitioned database environment, this command acts locally on a node. It quiesces only that portion of table spaces belonging to the node on which the load operation is performed.

    Authorization

    One of the following:

    sysadm
    sysctrl
    sysmaint
    dbadm
    load
    Required connection

    Database

    Command syntax

    >>-QUIESCE TABLESPACES FOR TABLE--+-tablename--------+---------->
    '-schema.tablename-'

    >--+-SHARE------------+----------------------------------------><
    +-INTENT TO UPDATE-+
    +-EXCLUSIVE--------+
    '-RESET------------'



    Command parameters


    TABLE

    tablename
    Specifies the unqualified table name. The table cannot be a system catalog table.

    schema.tablename
    Specifies the qualified table name. If schema is not provided, the CURRENT SCHEMA will be used. The table cannot be a system catalog table.

    SHARE
    Specifies that the quiesce is to be in share mode.
    When a "quiesce share" request is made, the transaction requests intent share locks for the table spaces and a share lock for the table. When the transaction obtains the locks, the state of the table spaces is changed to QUIESCED SHARE. The state is granted to the quiescer only if there is no conflicting state held by other users. The state of the table spaces, along with the authorization ID and the database agent ID of the quiescer, are recorded in the table space table, so that the state is persistent. The table cannot be changed while the table spaces for the table are in QUIESCED SHARE state. Other share mode requests to the table and table spaces are allowed. When the transaction commits or rolls back, the locks are released, but the table spaces for the table remain in QUIESCED SHARE state until the state is explicitly reset.


    INTENT TO UPDATE
    Specifies that the quiesce is to be in intent to update mode.
    When a "quiesce intent to update" request is made, the table spaces are locked in intent exclusive (IX) mode, and the table is locked in update (U) mode. The state of the table spaces is recorded in the table space table.


    EXCLUSIVE
    Specifies that the quiesce is to be in exclusive mode.
    When a "quiesce exclusive" request is made, the transaction requests super exclusive locks on the table spaces, and a super exclusive lock on the table. When the transaction obtains the locks, the state of the table spaces changes to QUIESCED EXCLUSIVE. The state of the table spaces, along with the authorization ID and the database agent ID of the quiescer, are recorded in the table space table. Since the table spaces are held in super exclusive mode, no other access to the table spaces is allowed. The user who invokes the quiesce function (the quiescer) has exclusive access to the table and the table spaces.


    RESET
    Specifies that the state of the table spaces is to be reset to normal.
    Examples

    db2 quiesce tablespaces for table staff share

    db2 quiesce tablespaces for table boss.org intent to update

    Usage notes

    This command is not supported for declared temporary tables.

    A quiesce is a persistent lock. Its benefit is that it persists across transaction failures, connection failures, and even across system failures (such as power failure, or reboot).

    A quiesce is owned by a connection. If the connection is lost, the quiesce remains, but it has no owner, and is called a phantom quiesce. For example, if a power outage caused a load operation to be interrupted during the delete phase, the table spaces for the loaded table would be left in delete pending, quiesce exclusive state. Upon database restart, this quiesce would be an unowned (or phantom) quiesce. The removal of a phantom quiesce requires a connection with the same user ID used when the quiesce mode was set.

    To remove a phantom quiesce:

    Connect to the database with the same user ID used when the quiesce mode was set.
    Use the LIST TABLESPACES command to determine which table space is quiesced.
    Re-quiesce the table space using the current quiesce state. For example:
    db2 quiesce tablespaces for table mytable exclusive

    Once completed, the new connection owns the quiesce, and the load operation can be restarted.

    There is a limit of five quiescers on a table space at any given time.

    A quiescer can upgrade the state of a table space from a less restrictive state to a more restrictive one (for example, S to U, or U to X). If a user requests a state lower than one that is already held, the original state is returned. States are not downgraded.
    HTH

    Nitin

    Ask the experienced rather than the learned

Posting Permissions

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