Refreshing Dynamic Cluster Tables :: SAS® Scalable Performance Data Server 5.3: User's Guide

Overview of Refreshing Dynamic Cluster Tables

Over time, member tables can age out and need to be removed to make room to accommodate the addition of more current member tables. Or a member table might need to be replaced by an updated version of itself. The process of replacing or updating one or more member tables is referred to as “refreshing” the cluster table.

The server supports several ways to refresh a cluster table:

You can replace an existing member table with a new member table in place with the CLUSTER REPLACE statement. CLUSTER REPLACE inserts the new member table into the slot vacated by the old member table. You can replace one member table at a time. CLUSTER REPLACE is not supported on cluster tables that are created with UNIQUEINDEX=YES.
You can remove one or more tables from the cluster’s member list with the CLUSTER REMOVE statement. CLUSTER REMOVE removes the member tables and closes their slot in the member list. You can remove multiple member tables at one time with CLUSTER REMOVE.
You can append new tables to the end of the member list with the CLUSTER ADD statement.
You can undo the cluster with the CLUSTER UNDO statement. Then, after making necessary changes to the individual member tables, you can later re-create the cluster with the CLUSTER CREATE statement. When you are making these changes, you must take the cluster offline.

When a member table is removed from a cluster with the CLUSTER REPLACE or CLUSTER REMOVE statement, or a new member is added to the cluster with the CLUSTER ADD statement, users that currently have the cluster table open for reading will remain connected to the table. However, they will not see the changes until the next time they open the cluster table.

Member tables that are removed from a cluster table remain in the domain as regular server tables, although the tables are in a read-only state. If you have a need to update a table that was removed from a cluster, you can use the CLUSTER FIX statement to restore the table to a writable state. For more information, see Restoring Removed or Replaced Cluster Table Members.

Example of Refreshing a Dynamic Cluster Table with CLUSTER REPLACE

The following shows the PROC SPDO code that you use to replace a table in a dynamic cluster table:

proc spdo library=libref;
   cluster replace Sales_History oldmem=sales201503 newmem=sales201503-2;
 quit;

The PROC SPDO LIBRARY= argument specifies a libref that identifies the server domain. Here is what is done in the CLUSTER REPLACE statement:

Sales_History is the name of the cluster table to be modified.
The OLDMEM= argument identifies the member table to be removed.
The NEWMEM= argument specifies the server table to be inserted in its place.

In this example, the dynamic cluster table is refreshed to replace member table Sales201503 with new member Sales201503-2.

Example of Refreshing Dynamic Cluster Tables with CLUSTER REMOVE and CLUSTER ADD

Here is the PROC SPDO code that you might use to refresh a dynamic cluster table by removing and adding tables.

proc spdo library=libref;
   cluster remove Sales_History
      mem=sales201301
      mem=sales201302
      mem=sales201303
      mem=sales201304
      mem=sales201305
      mem=sales201306;
 cluster add Sales_History
      mem=sales201507
      mem=sales201508
      mem=sales201509
      mem=sales201510
      mem=sales201511
      mem=sales201512;
quit;

The PROC SPDO LIBRARY= argument specifies a libref that identifies the server domain that contains the cluster table. Here is what is done in the CLUSTER REMOVE and CLUSTER ADD statements:

Sales_History is the name of the cluster table to be modified.
The MEM= argument identifies the tables to be removed and added.

In this example, you are removing the first six tables from cluster Sales_History’s member list and appending six new ones to the end of the list.

The CLUSTER REMOVE statement does not require that the member tables be contiguous. However, CLUSTER ADD always appends to the end of the list.

Refreshing Dynamic Cluster Tables with CLUSTER UNDO and CLUSTER CREATE

When you refresh a dynamic cluster table using the CLUSTER UNDO and CLUSTER CREATE statements, CLUSTER UNDO unbinds the dynamic cluster table. For an example of how the CLUSTER UNDO statement is used, see Undo a Dynamic Cluster Table. Then you essentially create a new cluster table with the CLUSTER CREATE statement. This new cluster table can include a combination of original member tables and new or updated member tables, in any combination. During the process, the cluster table is unavailable.

The examples at the end of this chapter contain code that unbinds a dynamic cluster table and re-creates the cluster with different member tables.