CONTROL MOVE SESSION
Purpose
Use this statement to move inactive sessions between clusters.
Prerequisite
- The user must be assigned the DBA role.
- The session must have been initiated on the main cluster. Sessions that have been initiated on worker clusters cannot be moved.
- The session must be inactive (the session must not be executing a statement and must not have an open transaction, result set, or subconnection).
Syntax
control_move_session::=
Usage Notes
- This statement allows a DBA user to move inactive sessions between clusters in order to better manage the load on the clusters. For more information see Session Management and Move Sessions Between Clusters.
- Specify a single session to be moved defined by
session_id
, or several sessions in a comma-separated list (see Examples). - To move all inactive sessions on the cluster, use
MOVE ALL SESSIONS
. - Only one
MOVE
statement at a time is currently supported. Running multipleMOVE
statements in parallel may produce undefined behavior. - The current session (the session running the
MOVE
statement) cannot be moved. Attempting this will result in an exception. - A session can be moved between worker clusters only if the initial client connection for the session was to the main cluster.
For example, a session that was created on the main cluster and subsequently moved to the worker cluster WORKER1 can be moved again to the worker cluster WORKER2. A session that initially connected to WORKER1 can however not be moved to WORKER2. - If a session is already on the specified target cluster it will be ignored. An exception will not be generated in this case.
-
Clusters can be specified by
cluster_name
orcluster_uid
.- To determine the current cluster name for the current session, use
SELECT CURRENT_CLUSTER;
- To determine the current cluster name for all other sessions, use
SELECT CLUSTER_NAME, SESSION_ID FROM EXA_DBA_SESSIONS;
- To determine the current cluster UID for a session, use
SELECT CURRENT_CLUSTER_UID;
- To see all cluster names, use
SELECT CLUSTER_NAME FROM EXA_CLUSTERS;
- To determine the current cluster name for the current session, use
-
The following options are available:
Option Description None (WAIT TIMEOUT and FORCE not provided)
The client will attempt to move the sessions immediately.
If a session cannot be moved, the client will receive an exception message concerning that session. The other sessions will be moved if possible.
WAIT TIMEOUT
The client will wait up to
timeout_in_seconds
for the sessions to be moved. If a session has not been moved withintimeout_in_seconds
, the client will receive an exception message concerning that session. The other sessions will be moved if possible.The default value for
timeout_in_seconds
is 4 seconds, which is the estimated minimum time required for a move to complete. Iftimeout_in_seconds
is set to a value less than 4, WAIT TIMEOUT may be ignored.FORCE
All sessions that cannot be moved will have their currently running statements killed, and the clients will receive an exception message.
Examples
Example 1: Move two sessions from the main cluster to a worker cluster, and kill all currently running statements on sessions that cannot be moved within 60 seconds.
Example 2: Move all sessions between two worker clusters.
All sessions must originally have been created on the main cluster.