SPD Server SQL Cluster Operations

To create dynamic cluster tables in SPD Server, you must have a set of related SPD Server tables that you want to cluster, such as tables that contain monthly sales transactions. The SPD Server tables that you want to cluster must all be in the same domain. They must use identical table structures (columns and indexes) and compression. However, member table partition sizes and member table owners can vary. These requirements ensure the metadata compatibility that is necessary to create dynamic cluster tables in SPD Server.

After you have organized the SPD Server tables, issue a PROC SPDO command to bind the tables into a dynamic cluster table.

The general form for the PROC SPDO cluster create command is as follows:

CLUSTER CREATE <cluster-tablename>   MEM|MEMBER=<membername>
  MAXSLOT=<max-slot-num-spec>  UNIQUEINDEX=YES|NO
  DELETE=YES|NO;

<cluster-tablename> is the name of the cluster table to be created.<member name> is the member table name.<max-slot-num-spec> is the maximum number of slots, or member tables, to be allocated for the dynamic cluster. The default SPD Server setting for the MAXSLOT= parameter is -1. A MAXSLOT= value of -1 configures SPD Server to permit dynamic growth of the number of member tables in a cluster up to the specified system maximum value. The system maximum value for the number of slots is specified by the MAXGENNUM variable setting in the spdsserv.parm configuration file. If there is a known maximum number of slots to be enforced for a particular dynamic cluster table, it is more efficient to specify the limitation by using the MAXSLOT= parameter when you issue the PROC SPDO CREATE CLUSTER command.

The CLUSTER CREATE command options are as follows:

UNIQUEINDEX=YES|NO validates a unique index. The default setting is YES.DELETE=YES|NO permanently deletes the cluster and its members. The default setting is NO.

To undo a dynamic cluster table, you must have an existing dynamic cluster table. Undoing the dynamic cluster table reverts the table to its unbound SPD Server tables. You must undo a dynamic cluster table in order to remove a specific member table from a dynamic cluster table, to add data to a specific member table in the dynamic cluster table, or to completely refresh a specific member table that belongs to the dynamic cluster table.

The general form of the PROC SPDO CLUSTER UNDO command is as follows:

CLUSTER UNDO <cluster-tablename> ;

<cluster-tablename> is the name of the cluster table to undo.

The SPD Server PROC SPDO CLUSTER REMOVE and CLUSTER ADD commands enable you to refresh dynamic cluster tables without unbinding and re-binding the cluster, and without making the dynamic cluster table temporarily unavailable during refactoring.

The CLUSTER REMOVE and CLUSTER ADD command set enables you to specify replacement member tables for one or more member tables in a dynamic cluster that have aged out or that are otherwise not wanted. The CLUSTER REMOVE and ADD commands remove old member tables from their original position in the cluster member table list, and append new updated tables to the end of the cluster member table list, in the order in which they were submitted in the command syntax.

The PROC SPDO CLUSTER REMOVE command removes one or more member tables from a dynamic cluster. When a cluster member table is removed, users that currently have that particular cluster open for Read access do not see the change until those users perform a subsequent open or reopen of the cluster after the remove command has completed. The same is true for the CLUSTER ADD command: changes are not reflected until the cluster is opened or reopened after the CLUSTER ADD processing is complete.

A cluster member table that has been removed from a cluster becomes visible as a simple SPD Server table, but the table remains in a read-only state. If you need to update a member table that has been removed from a cluster, use the CLUSTER FIX MEMBER command to restore the member table to a writable state.

The general form of the PROC SPDO CLUSTER REMOVE command is as follows:

CLUSTER REMOVE   <cluster-tablename1>
  <cluster-tablename2>
          ...
  <cluster-tablenameN> ; 

<cluster-tablename_1> <cluster-tablename_2> ... <cluster-tablename_n> is a list of one or more tables to be removed from the cluster.

The general form of the PROC SPDO CLUSTER ADD command is as follows:

CLUSTER ADD   <cluster-tablename_1>
  <cluster-tablename_2>
          ...
  <cluster-tablename_n> ; 

<cluster-tablename_1> <cluster-tablename_2> ... <cluster-tablename_n> is a list of one to n tables to be added to the cluster.

Like the CLUSTER REMOVE and CLUSTER ADD command set, the SPD Server PROC SPDO CLUSTER REPLACE command enables you to refresh dynamic cluster tables without unbinding and re-binding the cluster.

The CLUSTER REPLACE command enables you to specify a replacement member table for a single member table in a dynamic cluster that has aged out or that is otherwise not wanted.

The PROC SPDO CLUSTER REPLACE command replaces one member table from a dynamic cluster. When a cluster member table is removed, users that currently have that particular cluster open for Read access do not see the change until those users performs a subsequent open or reopen of the cluster after the replace command has completed.

A cluster member table that has been replaced in a cluster becomes visible as a simple SPD Server table, but the table remains in a read-only state. If you need to update a member table that has been replaced from a cluster, use the CLUSTER FIX MEMBER command to restore the member table to a writable state.

The general form of the CLUSTER REPLACE command is as follows:

CLUSTER REPLACE <cluster-tablename> 
  OLDMEMBER|OLDMEM= <member-name> 
  NEWMEMBER|NEWMEM= <member-name> ;

<cluster-tablename> is the name of the cluster table that you want to replace members in.OLDMEMBER|OLDMEM= is the name of the old member table that you want to remove from the cluster table.NEWMEMBER|NEWMEM= is the name of the new member table that you want to insert into the cluster table.