Quiescing a member

Certain circumstances might require you to temporarily remove a member from the cluster (for example, maintenance operations).

About this task

In a Db2® pureScale® environment, the db2stop and STOP DATABASE MANAGER commands offer the optional QUIESCE parameter. The optional QUIESCE parameter allows you to drain all activity against a single member and shut it down, effectively killing the member. While the member is being drained, automatic client reroute (ACR) and workload balancing will reroute new transactions and new connections to other members. After the completion of the command, db2start should be issued on the stopped member to ensure that any locks held by that member are recovered. The member can then be stopped again with db2stop.

When a db2stop QUIESCE command is issued against a member, applications are subject to these conditions:
  • If you specify a timeout value, applications have up to that amount of time to finish their active units of work.
  • If no timeout (or a value of -1) is specified, then the server waits indefinitely, until all active transactions and associated connections on the member have ended.
  • If you specify a value of 0 (zero) for the timeout value, connections are forced off immediately.
Note: When a stop member quiesce is issued (with no or non-zero timeout), active transactions are marked for deferred connection termination. When a transaction commits or rolls back successfully, the connection will be terminated unless one of these conditions is true:
  • The connection uses global variables
  • An encrypted password is used
  • There is an open WITH HOLD cursor
  • Declared temporary tables (DGTT) are used
  • A TRANSFORM GROUP is set
  • The SESSION AUTHID is changed
  • PL/SQL packages or SQL/PL modules are used
  • Cursor variables are used
  • Sequence values are used and DB2_ALLOW_WLB_WITH_SEQUENCES is not enabled
  • Created temporary tables (CGTT) with PRESERVE ROWS are used
  • Dynamic SQL prepared in a package bound with KEEPDYNAMIC YES. This restriction does not apply when preparing statements in a stored procedure or user-defined function, or when a statement is prepared by an IBM® non-embedded API such as CLI/JDBC/ODBC/.NET.

If any of the preceding conditions is true, you must either remove it (such as WITH HOLD cursors), or explicitly stop the connection. In the absence of any of these conditions, the client connection will be terminated at the next transaction end point. If your client (.NET, CLI, JDBC) supports seamless ACR, your connections are automatically rerouted to another member.

Procedure

Run the db2stop command or the STOP DATABASE MANAGER command and specify the QUIESCE parameter.
This example takes member 2 offline and allows 30 minutes for the active workload to finish. After 30 minutes, all applications are forced off the member.
db2stop MEMBER 2 QUIESCE 30
To bring member 2 back online and recover locks held by the member, issue the db2start command against it.
db2start MEMBER 2

Example

This example uses the db2InstanceStop API to bring member 10 offline.
struct sqlca sqlca;     // sqlca to carry the sqlcode
struct db2InstanceStopStruct instanceStopStruct;
struct db2StopOptionsStruct  stopOptions;

instanceStopStruct.iIsRemote = FALSE; // demo local instance
instanceStopStruct.piRemoteInstName = NULL;
instanceStopStruct.piCommData = NULL; // don't care DAS
instanceStopStruct.piStopOpts = &stopOptions; 

stopOptions.iOption = SQLE_QUIESCE; // Member quiesce option
stopOptions.iIsType = TRUE;        
stopOptions.iType = DB2_NODE_MEMBER;        
stopOptions.iIsNodeNum = TRUE;        
stopOptions.iNodeNum = 10;        
stopOptions.iQuiesceDeferMinutes = 0; // no explicit timeout

// Finally, invoke the API to shut down the instance 
db2InstanceStop(db2Version1010, &instanceStopStruct, &sqlca);
This examples uses the db2InstanceStop API to bring member 10 offline and specifies a timeout of 5 minutes.
struct sqlca sqlca;     // sqlca to carry the sqlcode
struct db2InstanceStopStruct instanceStopStruct;
struct db2StopOptionsStruct  stopOptions;

instanceStopStruct.iIsRemote = FALSE; // demo local instance
instanceStopStruct.piRemoteInstName = NULL;
instanceStopStruct.piCommData = NULL; // don't care DAS
instanceStopStruct.piStopOpts = &stopOptions; 

stopOptions.iOption = SQLE_QUIESCE; // Member quiesce option
stopOptions.iIsType = TRUE;        
stopOptions.iType = DB2_NODE_MEMBER;        
stopOptions.iIsNodeNum = TRUE;        
stopOptions.iNodeNum = 10;        
stopOptions.iQuiesceDeferMinutes = 5; // timeout of 5 minutes

// Finally, invoke the API to shut down the instance 
db2InstanceStop(db2Version1010, &instanceStopStruct, &sqlca);