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 write transaction, result set, or sub-connection).

Syntax

control_move_session::=

syntax diagram

syntax diagram 2

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 multiple MOVE 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 or cluster_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;

  • 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 within timeout_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. If timeout_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. 

CONTROL MOVE SESSION 1234567890123456788, 1234567890123456789 TO 'WORKER1' WAIT TIMEOUT 60 FORCE;

Example 2: Move all sessions between two worker clusters.

CONTROL MOVE ALL SESSIONS FROM 'WORKER1' TO 'WORKER2';

All sessions must originally have been created on the main cluster.