WLM_ALTER_MEMBER_SUBSET procedure - Alter a member subset object
The WLM_ALTER_MEMBER_SUBSET procedure alters an existing member subset database object.
Authorization
- DBADM authority
- WLMADM authority
Default PUBLIC privilege
None
Syntax
The schema is SYSPROC.
Routine parameters
- subset_name
- An input argument of type VARCHAR(128) that specifies the name of the member subset object to be altered. This is a one-part name. It is an SQL identifier (ordinary or delimited). The name must identify a member subset which already exists in the current database (SQLSTATE 42704). This argument cannot be NULL (SQLSTATE 42815).
- subset_attributes
- An input argument of type CLOB(8K) that enables you to alter one
or more member subset attributes. This parameter defaults to NULL.
Subset attributes are specified as name value pairs using the format:
Each subset attribute can be specified a maximum of one time (SQLSTATE 42613). Subset attribute names are case sensitive. The subset attribute values are case insensitive unless otherwise noted. The available subset attribute name tags are as follows:<subset attribute name tag>value</subset attribute name tag>
- '<enabled>value</enabled>'
- Specifies whether this member subset will be considered when a connection request is assigned to a member subset. This value can be either "yes" or "no". If not specified the current value is not altered.
- '<databaseAlias>value</databaseAlias>'
- Replace the database alias that a client must connect to in order
to be assigned to this member subset. The name must conform to the
naming conventions for local database aliases (SQLSTATE 2E000).
If the existing database alias was cataloged by a member subset operation it is uncataloged by this command.
- '<catalogDatabaseAlias>value</catalogDatabaseAlias>'
- Specifies whether the procedure should catalog the new database
alias specified in the databaseAlias attribute. This value can be
either "yes" or "no". When this attribute is specified a new database
alias attribute must also be provided (SQLSTATE 530AA).
If the value of this attribute is "yes" the new database alias specified in the databaseAlias attribute must not already be cataloged (SQL1005N). If the value of this attribute is "no" the new database alias specified in the databaseAlias attribute must already be cataloged (SQLSTATE 42705).
- '<memberPriorityBasis>value</memberPriorityBasis>'
- Specifies what information should be used when calculating the
relative member priorities in the member subsets server list. This
value can be either "load" or "equalPriority". If not specified,
the current value is not altered.
The value "load" indicates that the member priorities in the member subsets server list will be based on the relative load of each member. The relative load is calculated based on the average CPU load and average page swap rate. This value can be specified only in a Db2® pureScale® instance (SQLSTATE 530AA).
The value "equalPriority" indicates that the member priority in the member subsets server list for each active member will be 101.
- '<inclusiveSubset>value</inclusiveSubset>'
- Specifies whether all members in the instance will be included
in the member subset's server list for high availability (HA) purposes
when no other member of the subset is online.
This attribute can be specified only in a Db2 pureScale instance (SQLSTATE 530AA). When specified the value can be either "yes" or "no". If not specified, the current value is not altered.
- '<alternateServer>value</alternateServer>'
- Specifies whether the host name and port cataloged by the UPDATE ALTERNATE SERVER statement will be included in the member subset's server list for HA purposes when no other member of the subset is online. This value can be either "yes" or "no". If not specified, it defaults to "no".
- '<comment>value</comment>'
- Specifies a comment on the member subset. The comment specified replaces the value in the REMARKS column of the SYSCAT.MEMBERSUBSETS catalog view for the row that describes the member subset. The comment value can be any character string constant of up to 254 bytes.
- member_list
- An input argument of type VARCHAR(32000) that enables you to specify a list of members to add
to, alter, or drop from, the member subset. This parameter defaults to NULL. The list of members are
specified as comma-separated numerical values by using the following syntax:
Each member number can be specified a maximum of one time (SQLSTATE 42613). If the
ADD token is specified, the member must not already be included in the member subset (SQLSTATE
42613). If the ALTER or DROP token is specified, the member must already be included in the member
subset. The member number must be defined in the instance (SQLSTATE 42729). The last
primary member in a member subset
cannot be dropped (SQLSTATE 530AB).
- failover_priority_number
- In a Db2 pureScale environment, the failover_priority_number argument can be specified with member_list. Use failover_priority_number with member_list to specify a failover priority for the members that are being added or altered in the member subset. The parameter defaults to 0. Each subset must have at least one member with FAILOVER_PRIORITY set to 0. The value for failover_priority_number can be 0 - 254 (SQLSTATE 42815).
Usage notes
- As an autonomous procedure, WLM_ALTER_MEMBER_SUBSET executes in its own transaction scope.
- When WLM_ALTER_MEMBER_SUBSET successfully completes, it will commit the work performed within the procedure, but if it is unsuccessful, the procedure rolls back any work performed.
Examples
- Add member 3 to the member subset MY_SUBSET.
CALL SYSPROC.WLM_ALTER_MEMBER_SUBSET('MY_SUBSET', NULL, '(ADD 3)')
- Drop member 2 from the member subset MY_SUBSET.
CALL SYSPROC.WLM_ALTER_MEMBER_SUBSET('MY_SUBSET', NULL, '(DROP 2)')
- Change the member priority basis to equal priorities.
CALL SYSPROC.WLM_ALTER_MEMBER_SUBSET('MY_SUBSET', '<memberPriorityBasis>equalPriority</memberPriorityBasis>', NULL)
- Change the member subset to an exclusive subset.
CALL SYSPROC.WLM_ALTER_MEMBER_SUBSET('MY_SUBSET', '<inclusiveSubset>no</inclusiveSubset>', NULL)
- Replace the current database alias subset attribute from MY_SUBSET
with NEWALIAS.
CALL SYSPROC.WLM_ALTER_MEMBER_SUBSET('MY_SUBSET', '<databaseAlias>NEWALIAS</databaseAlias>', NULL)
- Add member 4 with FAILOVER_PRIORITY 1 to the member subset
MY_SUBSET.
CALL SYSPROC.WLM_ALTER_MEMBER_SUBSET('MY_SUBSET', NULL, '(ADD 4 FAILOVER_PRIORITY 1)')