sp_cluster

Description

Cluster environments only Performs a number of procedures related to clusters.

Syntax

Migrates a connection to a different logical cluster or instance:

sp_cluster connection, migrate, lc_name, instance_name, “spid_list

Determines if previous connection migrations to a new instance are pending, and terminates the migrations if they are:

sp_cluster connection, [‘migrate_status’ | 'migrate_cancel' ][, ‘spid_list’]

Modifies an outstanding action, such as canceling the action or changing the timing of the action:

sp_cluster logical, "action", lc_name, { 
	cancel, action_handle |
	modify_time, action_handle, wait_option[, timeout ] |
	release, action_handle }

Adds a resource or one or more routes to the logical cluster:

sp_cluster logical, "add", lc_name, {
	 route, route_type, key_list |
  	 instance, instance_list |
 	 failover, instance_list }

Moves a route from one logical cluster to another:

sp_cluster logical, “alter”, lc_name, route, route_type, key_list

Creates a new logical cluster:

sp_cluster logical, "create", lc_name

Stops the logical cluster on one or more instances or the entire logical cluster, and places the instances or the cluster in the inactive state:

sp_cluster logical, "deactivate", lc_name, { 
	"cluster" |
	"instance", instance_list }
   [, wait_option[, timeout,[, @handle output ]]]

Drops a logical cluster, or one or more resources from the logical cluster:

sp_cluster logical, "drop",  lc_name, 
	{cluster | instance, instance_list |
	 failover, instance_list |
	 route, route_type, key_list }

Reverses a manual failover, reinstating the original base instances:

sp_cluster logical, “failback”, lc_name, {
	cluster[, wait_option[, timeout[, @handle output ]]] |
	instance, from_instance_list, to_instance_list[, wait_option[,
		timeout[, @handle output ]]] }

Initiates a manual failover from base instances to failover instances.

sp_cluster logical, “failover”, lc_name, {cluster
	[, to_instance_list[, wait_option[, timeout[, @handle output ]]] 
	| instance, from_instance_list, to_instance_list[, wait_option[,
		timeout[, @handle output ]]] }

Manually gathers and migrates a group of connections to a different logical cluster:

sp_cluster logical, 'gather', lc_name

Displays complete syntax for sp_cluster logical:

sp_cluster logical, “help”

Stops the logical cluster on one or more instances or the entire logical cluster:

sp_cluster logical, "offline", lc_name, 
	{cluster | instance, instance_list }
	[, wait_option[, timeout,[, @handle output ]]]

Starts the default logical cluster on one or more instances:

sp_cluster logical, "online", { lc_name[, instance_list]}

Sets logical cluster rules: the open logical cluster, the failover mode, the system view, the start-up mode, and the load profile:

sp_cluster logical, "set", lc_name, { open 
		| failover, failover_mode 
		| system_view, view_mode 
		| startup, { automatic | manual } 
		| load_profile, profile_name }
	login_distribution, { affinity | "round-robin" }

Displays information about a logical cluster:

sp_cluster logical, "show"
[, lc_name[, {action[, state] | route[, type[, key]]}]]

Lets you set up and manage the load profile for the logical cluster:

sp_cluster profile, [ "show" [, profile_name ]
	| "create", profile_name 
	| "drop", profile_name 
	| "set", profile_name [, weight [, wt_metric [, wt_value ] 
	| threshold [, thr_metric [, thr_value ] ] ]

Lets you set up and manage the load profile for the logical cluster:

sp_cluster profile, [ "show" [, profile_name ] | "create", profile_name | "drop", profile_name | "set", profile_name [, weight [, wt_metric [, wt_value ] | threshold [, thr_metric [, thr_value ] ] ]

Parameters

sp_cluster connection, migrate, lc_name, instance_name, “spid_list

where:

  • lc_name – is the name of the logical cluster.

  • instance_name – is the name of the instance.

  • spid_list – is the list of spids you are migrating. Separate multiple spids with semicolons.

sp_cluster connection, [‘migrate_status’ | 'migrate_cancel' ][, ‘spid_list’]

where:

  • spid_list – is the list of spids you are investigating.

  • migrate_cancel – indicates you are terminating the connection migrations.

  • migrate_status – indicates you are investigating the status of connection migrations.

sp_cluster logical, "action", lc_name, {cancel, action_handle | modify_time, action_handle, wait_option[, timeout ] |release, action_handle}

where:

  • cancel – specifies an action to be canceled.

  • action_handle – is the action identifier.

  • modify_time – specifies that the time of the action is to be modified.

  • wait_option – is how the time of the action is to be modified. Values are:

    • wait – indicates that existing connections are given a specified amount of time (or an infinite amount of time if no timeout is given) to migrate or disconnect.

    • nowait – indicates that existing connections are migrated or disconnected immediately.

    • until – indicates that existing connections are given until a specific time of day to migrate or disconnect.

  • timeout – is a specific amount of time (when used with wait) or a specific time (when used with until). The format is “hh:mm:ss” according to a 24-hour clock. For example, timeout records “11:30 p.m” (or “11:30pm”) as “23:30:00”.

  • release – specifies that all resources held by a completed action are to be released.

sp_cluster logical, "add", lc_name, { route, route_type, key_list | instance, instance_list | failover, instance_list}

where:

  • lc_name – is the name of a logical cluster.

  • route – specifies that one or more routes are to be added to the logical cluster

  • route_type – is the type of route to be added. Values are:

    • application – specifies a route for an application name to the logical cluster.

    • login – specifies a route for a login name to the logical cluster.

    • alias – specifies a route for a server name alias to the logical cluster.

  • key_list – is a list of applications, logins, or aliases, depending on the route type. Elements in the key list are delimited by semicolons.

  • instance – specifies that one or more base instances are to be added to the logical cluster.

  • instance_list – is the list of instances to be added. Separate multiple instances with semicolons.

  • failover – specifies that one or more failover instances are to be added to the logical cluster.

sp_cluster logical, “alter”, lc_name, route, route_type, key_list

where:

  • lc_name – is the name of a logical cluster.

  • route – specifies a route is to be altered.

  • route_type – is the type of route to be altered. Values are:

    • application – specifies a route for an application name to the logical cluster.

    • login – specifies a route for a login name to the logical cluster.

    • alias – specifies a route for a server name alias to the logical cluster.

  • key_list – is a list of applications, logins, or aliases, depending on the route type. Elements in a key list are delimited with semicolons.

sp_cluster logical, "create", lc_name

where:

  • lc_name – is name of the logical cluster.

sp_cluster logical, "deactivate", lc_name, { "cluster" |"instance", instance_list } [, wait_option[, timeout,[, @handle output ]]]
  • lc_name – name of a logical cluster.

  • cluster – specifies the entire cluster.

  • instance – specifies that only certain instances in the logical cluster are to be placed in the inactive state.

  • instance_list – list of selected instances in the logical cluster.

  • wait_option – the valid options are:

    • wait – indicates that existing connections are given a specified amount of time (or an infinite amount of time if no timeout is given) to migrate or disconnect.

    • nowait – indicates that existing connections are migrated or disconnected immediately.

    • until – indicates that existing connections are given until a specific time of day to migrate or disconnect.

  • timeout – a specific amount of time (when used with wait) or a specific time (when used with until). The format is “hh:mm:ss” according to a 24-hour clock. For example, timeout records 11:30 p.m. as 23:30:00.

  • @handle output – specifies that an action handle is to be retrieved for the action.

sp_cluster logical, "drop", lc_name, { cluster | instance, instance_list | failover, instance_list | route, route_type, key_list }

where:

  • lc_name – name of a logical cluster.

  • cluster – specifies the entire cluster.

  • instance – specifies that only certain instances in the logical cluster are to be placed in the inactive state.

  • instance_list – list of selected instances in the logical cluster.

  • wait_option – where the valid options are:

    • wait – indicates that existing connections are given a specified amount of time (or an infinite amount of time if no timeout is given) to migrate or disconnect.

    • nowait – indicates that existing connections are migrated or disconnected immediately.

    • until – indicates that existing connections are given until a specific time of day to migrate or disconnect.

  • timeout – a specific amount of time (when used with wait) or a specific time (when used with until). The format is “hh:mm:ss” according to a 24-hour clock. For example, timeout records 11:30 p.m. as 23:30:00.

  • @handle output – specifies that an action handle is to be retrieved for the action.

sp_cluster logical, “failback”, lc_name, { cluster[, wait_option[, timeout[, @handle output ]]] | instance, from_instance_list, to_instance_list[, wait_option[, timeout[, @handle output ]]] }

where:

  • lc_name – name of a logical cluster.

  • cluster – specifies the entire cluster.

  • to_instance_list – list of predefined failover instances. A value of NULL activates the first failover group.

  • from_instance_list – list of instances that are to be taken offline.

  • wait_option– where the valid options are:

    • wait – indicates that existing connections are given a specified amount of time (or an infinite amount of time if no timeout is given) to migrate or disconnect.

    • nowait – indicates that existing connections are migrated or disconnected immediately.

    • until – indicates that existing connections are given until a specific time of day to migrate or disconnect.

  • timeout – a specific amount of time (when used with wait) or a specific time (when used with until). The format is “hh:mm:ss” according to a 24-hour clock. For example, timeout records 11:30 p.m. as 23:30:00.

  • @handle output – specifies that an action handle is to be retrieved for the action.

sp_cluster logical, “failover”, lc_name, {cluster[, to_instance_list[, wait_option[, timeout[, @handle output ]]] | instance, from_instance_list, to_instance_list[, wait_option[,timeout[, @handle output ]]] }

where:

  • lc_name – name of a logical cluster.

  • cluster – specifies the failover of the entire logical cluster.

  • to_instance_list – list of predefined failover instances. A value of NULL activates the first failover group.

  • wait_option – how the time of the action is to be recorded. Values are:

    • wait – indicates that existing connections are given a specified amount of time (or an infinite amount of time if no timeout is given) to migrate or disconnect.

    • nowait – indicates that existing connections are migrated or disconnected immediately.

    • until – indicates that existing connections are given until a specific time of day to migrate or disconnect.

  • timeout – is a specific amount of time (when used with wait) or a specific time (when used with until). The format is "hh:mm:ss" according to a 24-hour clock. For example, timeout records 11:30 pm as 23:30:00.

  • @handle output – specifies that an action handle is to be retrieved for the failover.

  • instance – specifies that only selected instances in the logical cluster are to fail over.

  • from_instance_list – list of instances that are to be taken offline

sp_cluster logical, 'gather', lc_name

where:

  • gather – indicates you are gathering a set of qualified connections to migrate them to another logical cluster.

  • lc_name – name of a logical cluster to which you are migrating the connections.

sp_cluster logical, "offline", lc_name, { cluster | instance, instance_list } [, wait_option[, timeout,[, @handle output ]]]

where:

  • lc_name – name of a logical cluster.

  • cluster – specifies the entire cluster.

  • instance – specifies that only selected instances in the logical cluster are to taken offline.

  • instance_list – list of selected instances in the logical cluster.

  • wait_option – how the time of the action is to be specified. Values are:

    • wait – indicates that existing connections are given a specified amount of time (or an infinite amount of time if no timeout is given) to migrate or disconnect.

    • nowait – indicates that existing connections are migrated or disconnected immediately.

    • until – indicates that existing connections are given until a specific time of day to migrate or disconnect.

  • timeout – is a specific amount of time (when used with wait) or a specific time (when used with until). The format is "hh:mm:ss" according to a 24-hour clock. For example, timeout records 11:30 pm as 23:30:00.

  • @handle output – specifies that an action handle is to be retrieved for the action.

  • from_instance_list – list of instances that are to be taken offline

sp_cluster logical, "online", { lc_name[, instance_list]}

where:

  • lc_name – name of a logical cluster.

  • instance_list – list of selected instances in the logical cluster.

sp_cluster logical, "set", lc_name, {open | failover, failover_mode | system_view, view_mode | startup, { automatic | manual } | load_profile, profile_name } login_distribution, { affinity | "round-robin" }

where:

  • lc_name – name of a logical cluster.

  • open – sets the open logical cluster. Unrouted connections are sent to the open logical cluster.

  • failover failover_mode – sets the failover mode of the logical cluster. Values for failover_mode are:

    • instance – specifies a 1:1 failover strategy; every time a base instance fails, a failover resource is brought online.

    • group – specifies that failover resources are brought online only after all base instances in the cluster fail.

  • system_view view_mode – sets the default system view for tasks running in the logical cluster. Values for view_mode are:

    • instance – specifies that monitoring and informational tools such as sp_who, sp_lock, and monitoring tables describe an instance.

    • cluster – specifies that monitoring and informational tools such as sp_who, sp_lock, and monitoring tables describe the whole cluster.

  • startup { automatic | manual} – sets the start-up mode of the logical cluster.

    • automatic – specifies that the logical cluster is started automatically when the cluster starts.

    • manual – specifies that the logical cluster must be started manually.

  • login_distribution – specifies how the Cluster Edition distributes connections when a logical cluster spans multiple instances.

  • @handle output – specifies that an action handle is to be retrieved for the action.

  • from_instance_list – list of instances that are to be taken offline

sp_cluster logical, "show"[, lc_name[, {action[, state] | route[, type[, key]]}]]

where:

  • lc_name – name of the logical cluster. If NULL is entered, summary information for all logical clusters is displayed.

  • action – specifies information about administrative actions: failover, failback, online, offline, deactivate.

  • state – one of: cancelled, complete, or active.

  • route – specifies information about routes.

  • type – is one of: application, alias, or login.

  • key – a specific login, alias, or application name.

sp_cluster profile, [ "show" [, profile_name ] | "create", profile_name | "drop", profile_name | "set", profile_name [, weight [, wt_metric [, wt_value ] | threshold [, thr_metric [, thr_value ] ] ]
  • show – displays configured load profiles and their settings.

  • profile_name – name of a load profile.

  • creates – creates a new load profile.

  • drop – drops a load profile.

  • set – specifies attributes of a load profile. You must set each attribute individually.

  • weight – specifies a weight attribute.

  • wt_metric – specifies an individual weight metric. Values are:

    • user connections – the capacity of an instance to accept a new connection, based on resource availability.

    • cpu utilization – the capacity of an instance to accept a new connection, based on resource availability.

    • run queue – the capacity of an instance to accept a new connection, based on resource availability.

    • io load – outstanding asynchronous I/Os.

    • engine deficit – the difference in the number of online engines among instances in the cluster.

      Noteengine deficit is measurable only when instances in the cluster have unequal numbers of engines. engine deficit adds a metric for maximum relative capacity to the load score.

    • user metric – an optional, user-supplied metric.

  • wt_value – specifies a weight value. Valid values are 0 to 255. A weight of zero (0) excludes the metric from calculation.

  • threshold – specifies a threshold attribute.

  • thr_metric – specifies a particular threshold attribute. Values are:

    • dynamic – specifies a threshold for dynamic load distribution.

    • login – specifies a threshold for login redirection

    • hysteresis – specifies a minimum load score for any connection redirection.

  • thr_value – depends on value of thr_metric. When thr_metric is:

    • dynamic or loginthr_value is the percentage difference between the load scores of two instances. Valid values are 0 to 100. A weight of zero (0) disables this form of load distribution.

    • hysteresisthr_value is the minimum load score for the target instance that must be met before dynamic load distribution or login redirection can occur.

Examples

Example 1

sp_cluster connection – moves the connection with a spid of 73 into the SalesLC cluster:

sp_cluster connection, migrate, SalesLC, NULL, '73'

Example 2

sp_cluster connection – moves the current connection to the “ase3” instance:

sp_cluster connection, migrate, NULL, ase3

Example 3

sp_cluster connection – moves connections with spid values of 73 and 75 into “ase3” instance and the SalesLC cluster:

sp_cluster connection, migrate, SalesLC, ase3, '73;75'

Example 4

sp_cluster connection – determines if there is a connection migration occurring on spid 73; if there is, the Cluster Edition cancels the migration:

sp_cluster connection, 'migrate_cancel', '73'

Example 5

sp_cluster connection – checks the status of migrated connections for connections with a spid value of 73:

sp_cluster connection, 'migrate_status', '73'
SPID LogicalCluster Instance MigrationLogicalCluster MigrationInstance Command
---- -------------- -------- ----------------------- ------------------ -------
  73 SystemLC        ase1      SalesLC                   ase3    connection migrate

Example 6

sp_cluster local "action" – cancels a timed action on the “SalesLC” logical cluster. The action handle is 4390.

sp_cluster logical, "action", SalesLC, cancel, "4390"

Example 7

sp_cluster local "action" – changes the wait option for existing action 5364 to nowait.

sp_cluster logical, "action", SalesLC, modify_time, "5364", nowait

Example 8

sp_cluster local "action" – releases action 3456 for the “SalesLC” logical cluster.

sp_cluster logical, "action", SalesLC, release, "3456"

Example 9

sp_cluster local "action" – releases all completed or cancelled actions for the “SalesLC” logical cluster.

sp_cluster logical, "action", SalesLC, release, "all"

Example 10

sp_cluster local "add" – adds instances “ase1” and “ase2” to the “SalesLC” logical cluster.

sp_cluster logical, "add", SalesLC, instance, "ase1;ase2"

Example 11

sp_cluster local "add" – creates one failover group with “ase3” for “SalesLC”.

sp_cluster logical, "add", SalesLC, failover, ase3

Example 12

sp_cluster local "add" – routes the logins “tom”, “dick”, and “harry” to the “SalesLC” logical cluster

sp_cluster logical, "add", SalesLC, route, login, "tom;dick;harry"

Example 13

sp_cluster local "add" – routes the field_sales application to the “SalesLC” logical cluster.

sp_cluster logical, "add", SalesLC, route, application, field_sales

Example 14

sp_cluster logical, "alter" – creates a route of type alias to logical cluster “lc1” with the alias “SalesLC”. Then, changes the logical cluster association of the route from “lc1” to “lc2”. The route is identified by its route type (alias) and its key (SalesLC).

sp_cluster logical, "add", "lc1", "route", "alias", "SalesLC"
sp_cluster logical, "alter", "lc2", "route", "alias", "SalesLC"

Example 15

sp_cluster logical, "create" – reates a logical cluster named “SalesLC”:

sp_cluster logical, "create", SalesLC

Example 16

sp_cluster logical, "deactivate" – immediately stops all instances in the “SalesLC” logical cluster, and places “SalesLC” in the inactive state:

sp_cluster logical, "deactivate", SalesLC, cluster, nowait

Example 17

sp_cluster logical, "deactivate" – stops the “ase1” and “ase2” instances, and places “SalesLC” in the inactive state:

sp_cluster logical, "deactivate", SalesLC, instance, "ase1;ase2"

Example 18

sp_cluster logical, "drop" – drops the “SalesLC” logical cluster:

sp_cluster logical, "drop", SalesLC, cluster

Example 19

sp_cluster logical, "drop" – drops the base instances “ase1” and “ase2” from the “SalesLC” logical cluster.

sp_cluster logical, "drop", SalesLC, instance, "ase1;ase2"

Example 20

sp_cluster logical, "drop" – drops the routes from the applications field_sales and web_sales from the “SalesLC” logical cluster.

sp_cluster logical "drop", SalesLC, route, application,
    "field_sales;web_sales"

Example 21

sp_cluster logical, "failback" – fails back the “SalesLC” logical cluster:

sp_cluster logical, "failback", SalesLC, cluster

Example 22

sp_cluster logical, "failback" – “SalesLC” is running on “ase3” and “ase1”. In this example, “ase3” fails back to “ase1”, and “SalesLC” continues to run on “ase2”. The action takes place in two minutes:

declare @out_handle varchar(15)

execute
sp_cluster logical, "failback", SalesLC, instance, 
ase3, ase1, wait, "00:02:00", @handle = @out_handle
output

Example 23

sp_cluster logical, "failover" – fails over the “SalesLC” logical cluster to the first group of predefined failover resources. The failover waits 2 minutes before terminating connections.

declare @out_handle varchar(15)

execute
sp_cluster logical, "failover", SalesLC, cluster, NULL, wait, "00:02:00",
@handle = @out_handle output

Action '2' has been issued for the 'failover cluster' command.Logical Cluster Handle      Action                           From     To
         State            InstancesWaiting ConnectionsRemaining WaitType
         StartTime                  Deadline         CompleteTime
 --------------- ----------- -------------------------------- -------- --
         ---------------- ---------------- -------------------- --------
         -------------------- -------------------- -----------------------SalesLC                 2 failover cluster    2, 4     NULL   
complete                        0                      0 wait
         Nov 15 2007  3:23PM     Nov 15 2007  3:25PM    Nov 15 2007  3:23PM

Remember to issue the 'sp_cluster logical, action, <logical cluster name>,
release, <handle>' command for any cancelled or completed actions.

Example 24

sp_cluster logical, "failover" – “SalesLC” is running on “ase1” and “ase2”. In this example, “ase1” fails over to “ase3”, and “SalesLC” continues to run on “ase2”. No wait option is specified, so it defaults to an indefinite wait.

sp_cluster logical, "failover", SalesLC, instance, ase1, ase3
Action '1' has been issued for the 'failover instance' command.
Logical Cluster Handle       Action                          From To
           State          InstancesWaiting ConnectionsRemaining
          WaitType       StartTime         Deadline        CompleteTime
--------------- ----------- ---------------------------------- ---- --
         ---------------- ---------------- --------------------
         ---------------- ------------- ----------------  --------------- SalesLC                     1 failover instance             1     4
          complete                      0                       0
         infinite              Nov 15 2007  3:06PM    NULL  Nov 15 2007  3:06PMRemember to issue the `sp_cluster logical, action, <logical cluster name>,
release, <handle>' command for any cancelled or completed actions.

Example 25

sp_cluster logical, "gather" – gathers and migrates a group of connections to the new_stores logical cluster:

sp_cluster logical, 'gather', new_stores

Example 26

sp_cluster logical, "help" – displays syntax for the sp_cluster logical stored procedures.

sp_cluster logical, "help"
Usage for sp_cluster 'logical':
sp_cluster 'logical', 'help' [, <module>]

To show the logical cluster configuration:
sp_cluster 'logical', 'show'
sp_cluster 'logical', 'show', <lcname>
sp_cluster 'logical', 'show', <lcname> | NULL, 'action' [, <state>]
sp_cluster 'logical', 'show', <lcname> | NULL, 'route' [, <type [, <key>]]

To create a logical cluster: 
sp_cluster 'logical', 'create', <lcname>

To add resources to a logical cluster:
sp_cluster 'logical', 'add', <lcname>, 'failover', <instance_list> [,<group>]
sp_cluster 'logical', 'add', <lcname>, 'instance', <instance_list
sp_cluster 'logical', 'add', <lcname>, 'route', <route_type>, <key_list>

To drop resources from a logical cluster:
sp_cluster 'logical', 'drop', <lcname>, 'cluster'
sp_cluster 'logical', 'drop', <lcname>, 'failover', <instance_list>
sp_cluster 'logical', 'drop', <lcname>, 'instance', <instance_list>
sp_cluster 'logical', 'drop', <lcname>, 'route', <route_type>, <key_list>

Argument details:
<lcname> is a logical cluster nam
 <instance_list> is a ';' separated list of instance
<route_type> is one of {'user', 'application', 'alias
 <key_list> is a ';' separated list of keys

To set attributes of a logical cluster:
sp_cluster 'logical', 'set', <lcname>, 'open'
sp_cluster 'logical', 'set', <lcname>, 'down_routing', 'disconnect' | 'system' | 'open'
sp_cluster 'logical', 'set', <lcname>, 'failover', 'instance' | 'group'
sp_cluster 'logical', 'set', <lcname>, 'load_profile', <profile_name>
sp_cluster 'logical', 'set', <lcname>, 'startup', 'automatic' | 'manual'
sp_cluster 'logical', 'set', <lcname>, 'system_view', 'instance' | 'cluster'

To start and stop a logical cluster:
sp_cluster 'logical', 'online', <lcname>[, <instance_list>]
sp_cluster 'logical', 'offline', <lcname>, 'cluster'[, <wait_option>[,<time>[, @handle output]]]
sp_cluster 'logical', 'offline', <lcname>, 'instance', <instance_list>[,<wait_option>[, <time>[, 
     @handle output]]]

To failover and failback a logical cluster:
sp_cluster 'logical', 'failover', <lcname>, 'cluster'[, <instance_list>[, <wait_option>[, <time>[, 
     @handle output]]]]
sp_cluster 'logical', 'failover', <lcname>, 'instance', <from_instance_list>, <instance_list>[,
      <wait_option>[,<time>[, @handle output]]]
sp_cluster 'logical', 'failback', <lcname>, 'cluster'[,<instance_list>[, <wait_option>[, <time>[, 
     @handle output]]]]
sp_cluster 'logical', 'failback', <lcname>, 'instance', <from_instance_list>, <instance_list>[,
      <wait_option>[,<time>[, @handle output]]]

To work with action handles:
sp_cluster 'logical', 'action', <lcname>, 'cancel', <handle>
sp_cluster 'logical', 'action', <lcname>, 'modify_time', <handle>, <wait_option>[, <time>]
sp_cluster 'logical', 'action', <lcname>, 'release', <handle>

Argument details:
<wait_option> is one of {'nowait', 'wait', 'until'}
<time> is a time in hh:mm:ss format
<handle> is an action handle

Example 27

sp_cluster logical, "offline" – immediately stops all instances in the “SalesLC”, and places “SalesLC” in the offline state.

sp_cluster logical, "offline", SalesLC, cluster, nowait

Example 28

sp_cluster logical, "offline" – stops the “ase1” and “ase2” instances in “SalesLC”, and places “SalesLC” in the offline state.

sp_cluster logical, "offline", SalesLC, instance, "ase1;ase2"

Example 29

sp_cluster logical, "online" – starts all base instances in the “SalesLC” logical cluster, and brings the cluster online.

sp_cluster logical, "online", SalesLC

Example 30

sp_cluster logical, "online" – starts the “ase1” instance in “SalesLC”, and brings the cluster online.

sp_cluster logical, "online", SalesLC, ase1

Example 31

sp_cluster logical, "set" – sets the load profile for the “SalesLC” logical cluster to the Sybase profile sybase_profile_oltp:

sp_cluster logical, "set", SalesLC, load_profile,
sybase_profile_oltp

Example 32

sp_cluster logical, "set" – sets the default system view to cluster:

sp_cluster logical, "set", SalesLC, system_view, cluster

Example 33

sp_cluster logical, "show" – displays summary information for all configured logical clusters.

sp_cluster logical, "show", NULL
 
ID  Name           State   Online Instances   Connections--- -------        ------- ------------------ ---------------1   mycluster      online        4                  1
2   SalesLC        online        2                  0
3   HRLC           online        1                  0
4   CatchallLC     offline       0                  0

Logical cluster 'mycluster' is the system logical cluster.
Logical cluster 'CatchallLC' is the open logical cluster.

Logical Cluster  Instance    State   Type      Connections     Load Score
---------------- --------   ------    ------   --------------      -----------
HRLC               silk      online     base              0           0.01
SalesLC            cotton    offline    failover          0           0.00
SalesLC            linen     online     base              0           0.00
SalesLC            silk      offline    failover          0           0.01
SalesLC            wool      online     base              0           0.01
mycluster          cotton    online     base              0           0.00
mycluster          linen     online     base              0           0.00
mycluster          silk      online     base              0           0.01
mycluster          wool      online     base              1           0.01

Example 34

sp_cluster logical, "show" – displays a list of all outstanding actions.

sp_cluster logical, "show", NULL, action

Example 35

sp_cluster logical, "show" – displays information for the SalesLC logical cluster.

sp_cluster logical, "show", SalesLC
ID          Name           State         Online Instances      Connections
----------- -------------- ------------  ----------------      ----------
2           OrderLC        online       1                0

Instance         State       Type     Connections Load Score  Failover Gro
---------------  ----------  -------  ----------- ----------  -----------
asedemo1         online      base              0        0.78  NU

Attribute                             Setting
------------------                    ------------------------------------
Down Routing Mode                     system
Failover Mode                         instance with fail_to_any
LC Roles                              none
Load Profile                          sybase_profile_oltp
Login Distribution                    affinity
Startup Mode                          automatic
System View                           cluster

Route Type             Route Key
---------------------- ------------------
application            order_app

Logical cluster 'OrderLC' has no associated actions.
(return status = 0)

Example 36

sp_cluster profile – creates the load profile “my_profile”:

sp_cluster profile, "create", my_profile

Example 37

sp_cluster profile – specifies the metric weights for “my_profile.” “user connections” is set to zero, which excludes that metric from the profile:

sp_cluster profile, "set", my_profile, weight, "user connections", '0'
sp_cluster profile, "set", my_profile, weight, cpu utilization, '20'
sp_cluster profile, "set", my_profile, weight, runqueue, '30'
sp_cluster profile, "set", my_profile, weight, io load, '10'
sp_cluster profile, "set", my_profile, weight, engine deficit, '10'
sp_cluster profile, "set", my_profile, weight, user metric, '30'

Example 38

sp_cluster profile – sets the login redirection threshold to 80 and the hysteresis value to 10 for “my_profile:”

sp_cluster profile, "set", my_profile, threshold, login, '80'
sp_cluster profile, "set", my_profile, threshold, hysteresis, '10'

Example 39

sp_cluster profile – displays information about a configured profile:

sp_cluster profile, "show", my_profile

ID    Profile      Type    Connections   CPU Run   Queue
----  ------------ ------- ------------- --- ---   ---- ---- --- --- --- ---
100   my_profile   user              0   20  30  10  10  30  30  0  20

Profile                    Logical Cluster
-------------------------  --------------
my_profile                 SalesLC

Profile                    Logical Cluster Instance

         Load Score        Connections Score
         CPU Score         Run Queue Score
         IO Load Score     User Score
---------------------------------- -------------------- -----------
    ------------------------------------ ------------------------------
    ------------------------------------ ------------------------------
    ------------------------------------ ------------------------------
----------------------------------
my_profile                          SalesLC          ase1
                                    0.028871                 0.000000
                                    0.028871                 0.000000
                                    0.000000                 0.000000
                                    0.000000
my_profile                                           ase2
                                    0.029474                 0.000000
                                    0.029474                 0.000000
                                    0.000000                 0.000000
                                    0.000000
my_profile                                           ase3
                                    0.019503                 0.000000
                                    0.019503                 0.000000
                                    0.000000                 0.000000
                                    0.000000 
my_profile                                           ase4
                                    0.582675                 0.000000
                                    0.290930                 0.291745
                                    0.000000                 0.000000
                                    0.000000

Usage


sp_cluster connection

To migrate the current spid, omit spid_list from sp_cluster connection, migrate.


sp_cluster logical, action


sp_cluster logical, 'add'


sp_cluster logical, "deactivate"


sp_cluster logical "drop"


sp_cluster logical "failback"

To initiate a failback, the logical cluster must first be failed over.


sp_cluster logical "gather"


sp_cluster logical, "offline"


sp_cluster logical "online"

You cannot use the online command for the system logical cluster.


sp_cluster logical "set"

Only one logical cluster can have the open property. When you set the open property to a new logical cluster, the open property is removed from the previous open logical cluster.


sp_cluster profile