Dynamic Cluster Table 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:

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

where

<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 using the MAXSLOT= parameter when issuing 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.

Dynamic Cluster Table shows a dynamic cluster table with 24 members. Each member table is an SPD Server table that contains monthly sales transactions.

The following code shows the PROC SPDO command syntax that creates the dynamic cluster table from the member tables:

PROC SPDO library=domain-name;
   cluster create Sales_History
      mem=sales200301
      mem=sales200302
      mem=sales200303
      mem=sales200304
      mem=sales200305
      mem=sales200306
      mem=sales200307
      mem=sales200308
      mem=sales200309
      mem=sales200310
      mem=sales200311
      mem=sales200312
      mem=sales200401
      mem=sales200402
      mem=sales200403
      mem=sales200404
      mem=sales200405
      mem=sales200406
      mem=sales200407
      mem=sales200408
      mem=sales200409
      mem=sales200410
      mem=sales200411
      mem=sales200412
   quit ;

PROC SPDO uses a LIBRARY statement to identify the domain that contains the tables to be clustered. The CLUSTER CREATE syntax specifies the name of the dynamic cluster table to be created (Sales_History).

MEM= identifies the members of the dynamic cluster table. The tables in the example represent monthly sales transactions. This example uses 24 monthly sales tables for the years 2003 and 2004.

Dynamic Cluster Table Examples contains more extensive code examples of creating dynamic cluster tables.

You must have SPD Server Control access to any member tables that you use in the CLUSTER CREATE or CLUSTER ADD commands. You must also have SPD Server Control access to the dynamic cluster table itself to submit a CLUSTER UNDO command. There is no restriction on table ownership if you have Control access to all the member tables. All users that have access to a domain have default Control access to tables that were created by the user Anonymous within that domain. Access control lists (ACLs) can be defined on a dynamic cluster table after it is created. The permissions that are specified in the dynamic cluster table ACL are applied when SPD Server accesses the dynamic cluster table. Any individual ACL that is defined on a member table does not apply during the time when the member table is part of a created dynamic cluster table.

To add tables to a dynamic cluster table, you must have an existing dynamic cluster table. The SPD Server tables that you want to add to the dynamic cluster table must all be in the same domain as the dynamic cluster table. 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 required to add to a dynamic cluster table.

After the SPD Server tables are organized, issue a PROC SPDO command to add the tables to an existing dynamic cluster table.

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

CLUSTER ADD <cluster-tablename> MEM|MEMBER=<membername>;

where

<cluster-tablename> is the name of the cluster table to be created<member name> is the member table name.

New Monthly Data to Add to an Existing Dynamic Cluster Table shows sales tables for the first six months of 2005. These tables are set up to be added to the dynamic cluster table that contains monthly sales transactions data for 2003 and 2004, which was reviewed in Creating Dynamic Cluster Tables.

The following code shows the PROC SPDO command syntax that adds the new tables to an existing dynamic cluster table:

PROC SPDO library=domain-name;
   cluster add Sales_History
      mem=sales200501
      mem=sales200502
      mem=sales200503
      mem=sales200504
      mem=sales200505
      mem=sales200506;
quit;

PROC SPDO uses a LIBRARY statement to identify the domain that contains the existing dynamic cluster table that you want to add to. The CLUSTER ADD syntax specifies the name of the dynamic cluster table that you want to add to (Sales_History).

MEM= identifies the member tables of the table to be added to the existing dynamic cluster table.

In Adding Member Tables to a Dynamic Cluster Table, six tables that include monthly sales transactions for the first half of 2005 are set up to be added to the existing dynamic cluster table that contains 2003 and 2004 sales transactions data.

Dynamic Cluster Table Examples contains more extensive code examples of adding to a dynamic cluster table.

To undo a dynamic cluster table, you must have an existing dynamic cluster table. Undoing the dynamic cluster table reverts the table back 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> ;

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

Dynamic Cluster Table with 30 Members shows a dynamic cluster table with 30 members. Each member contains monthly sales transactions for the years 2003 and 2004, and part of 2005.

The following code shows the PROC SPDO command syntax to use to undo the dynamic cluster table shown in Dynamic Cluster Table with 30 Members:

PROC SPDO library=domain-name;
   cluster undo Sales_History;
quit;

PROC SPDO uses a LIBRARY statement to identify the domain that contains the existing dynamic cluster table that you want to undo. The CLUSTER UNDO syntax specifies the name of the dynamic cluster table that you want to undo (Sales_History).

Unbound Dynamic Cluster Table shows the dynamic cluster table unbound.

Dynamic Cluster Table Examples contains more extensive code examples of undoing a dynamic cluster table.

PROC SPDO uses a CLUSTER MODIFY command to modify a dynamic cluster table.

The general form for the PROC SPDO CLUSTER MODIFY command is as follows:

CLUSTER MODIFY <cluster-tablename>
  MINMAXVARLIST=(<<varname1> <varname2> <varname3> ...>);

where<cluster-tablename> is the name of the cluster table to be created<<varname1> <varname2> <varname3> ...> are the names of the minmax variables to be added.

The CLUSTER MODIFY command sets the MINMAXVARLIST attribute on variables that belong to an existing dynamic cluster. The variable names that you specify on the CLUSTER MODIFY command must exist in the dynamic cluster tables. The variables must not have a preexisting MINMAXVARLIST setting. When the SPD Server runs the CLUSTER MODIFY command, it unclusters the dynamic cluster table and makes the variable modifications to the individual member tables. The dynamic cluster table is re-created after the variable modifications have completed. You must have Control access and Exclusive access to the dynamic cluster table in order to run the CLUSTER MODIFY command. SPD Server performs a full table scan to initialize the MINMAXVARLIST values in each member table. As a result, the processor time required for the CLUSTER MODIFY command is directly related to the sizes of the tables that belong to the dynamic cluster table. If an error occurs while the CLUSTER MODIFY command is running, the dynamic cluster table cannot be re-created, and you need to manually re-create it by issuing the CLUSTER CREATE command.

Use the UNIQUEINDEX option on the CLUSTER CREATE command in PROC SPDO to specify whether the unique indexes that are defined in the member tables should be validated and marked as unique in the dynamic cluster table. If you set the UNIQUEINDEX option to NO, then unique indexes are not validated, and the dynamic cluster table metadata does not mark the indexes as unique within the cluster. If you do not specify the UNIQUEINDEX option, then the default setting YES is used. In this case, the indexes are validated and marked as unique within the cluster. The processing that is required to validate the unique indexes depends on the number of rows in the tables. Processing can take considerable time for larger tables. If you choose to use the validation process but the indexes are not unique, the CLUSTER CREATE command fails.

CLUSTER CREATE clustername
MEM=member_table_1
MEM=member_table_2
 ...
MEM=member_table_n
UNIQUEINDEX=<yes|no>;

You use the PROC SPDO CLUSTER DESTROY command when you want to delete or destroy an existing cluster table. The general form of the PROC SPDO CLUSTER DESTROY command is as follows:

CLUSTER DESTROY <cluster-tablename> ; 

where<cluster-tablename> is the name of the cluster table that you want to destroy.

The CLUSTER DESTROY command is valid only when used on clusters that were created with the DELETE=YES option configured.

You use the PROC SPDO CLUSTER FIX command when you need to restore removed or replaced cluster member tables to a writable state. The general form for the PROC SPDO CLUSTER FIX command is as follows:

CLUSTER FIX <member-tablename> ; 

where<member-tablename> is the name of the member table that you want to repair.