Creating and Controlling Dynamic Cluster Tables

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 the SPD Server tables are organized, a simple PROC SPDO command is used to bind the tables into a dynamic cluster table.

The following graphic 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 is used to create dynamic cluster tables 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
   maxslot=36 ;
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).

The MEM= syntax 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.

MAXSLOT= specifies the maximum number of member tables that are allowed in the dynamic cluster table Sales_History.

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

A user must have SPD Server control access on any member tables that are used in the CLUSTER CREATE or CLUSTER ADD commands. A user must also have SPD Server control access on the dynamic cluster table itself to submit a CLUSTER UNDO command. There is no restriction on table ownership if the user has control access on all member tables. All users that have access to a domain have default control access on tables that were created by the user Anonymous within that domain. 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, a simple PROC SPDO command is used to add the tables to an existing dynamic cluster table.

The following graphic 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.

The following code shows the PROC SPDO command syntax that is used to add 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).

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

In the following graphic, 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. Undoing a dynamic cluster table is required 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 following graphic shows a dynamic cluster table with 24 members. Each member contains monthly sales transactions for the years 2003 and 2004.

The following code shows the PROC SPDO command syntax that is used to undo an existing dynamic cluster table:

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).

The following graphic shows the dynamic cluster table unbound.

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

To refresh a dynamic cluster table, you perform the same actions that are required to undo a dynamic cluster table. Then, you recreate the dynamic cluster table after you add a member table or change an existing member table. An example of refreshing an SPD Server dynamic cluster table is updating a dynamic cluster table every month. The dynamic cluster table's members are tables containing the previous 24 months of sales transactions data.

To refresh a dynamic cluster table, use two sequential PROC SPDO commands. Submit CLUSTER UNDO and CREATE CLUSTER on the member tables. The dynamic cluster table is first undone. Then, table changes are made, and the dynamic cluster table is rebound.

The following example unbinds the sales transactions tables for 2003 and 2004. It then refreshes the dynamic cluster table with sales transactions tables for the first six months of 2005.

Dynamic Cluster Table Examples contains a more extensive code example of unbinding a dynamic cluster table and refreshing it by recreating it with different member tables.

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

The following code shows the PROC SPDO command syntax that is used to modify a dynamic cluster table:

CLUSTER MODIFY clustername
  MINMAXVARLIST=(varname1 <varname2 varname3 ...>);

The CLUSTER MODIFY command sets the MINMAXVARLIST attribute on variables that belong to an existing dynamic cluster. The variable names that are specified in 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, the dynamic cluster table is unclustered and the variable modifications are made to the individual member tables. The dynamic cluster table is recreated after the variable modifications have completed. Control access and exclusive access to the dynamic cluster table is required 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 recreated, and the user needs to manually recreate it using the CLUSTER CREATE command.

The CLUSTER CREATE command in PROC SPDO has an option that enables you 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 the UNIQUEINDEX option is set 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 the UNIQUEINDEX option is not specified, 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 the validation process is chosen, and the indexes are not unique, the CLUSTER CREATE command fails.

CLUSTER CREATE clustername
MEM=member_table1
MEM=member_table2
 ...
MEM=member_table_n
MAXSLOT=n
UNIQUEINDEX=<yes|no>;