Comprehensive Dynamic Cluster Table Examples :: SAS® Scalable Performance Data Server 5.3: User's Guide

Example 1: Create a Dynamic Cluster Table

The following example creates a dynamic cluster table named Sales_History. The example assumes the existence of 12 server tables for monthly 2014 sales and 6 tables for monthly 2015 sales in a domain pointed to by the libref MyLib. The tables are named sales201401, sales201402, sales201403, sales201404, sales201405, sales201406, sales201407, sales201408, sales201409, sales201410, sales201411, sales201412, sales201501, sales201502, sales201503, sales201504, sales201505, and sales201506. The tables are indexed.

/* declare main columns */
%let host=kaboom;
%let port=5400;
%let spdssize=256M;
%let spdsiasy=YES;

libname mylib sasspds "sales"
&host..&port
user='anonymous'
ip=YES;
/* Use PROC SPDO to create the dynamic cluster */
/* table sales_history                         */
PROC SPDO library=mylib; ;
   cluster create sales_history
      mem=sales201401
      mem=sales201402
      mem=sales201403
      mem=sales201404
      mem=sales201405
      mem=sales201406
      mem=sales201407
      mem=sales201408
      mem=sales201409
      mem=sales201410
      mem=sales201411
      mem=sales201412
   quit;

Example 2: Add Tables to a Dynamic Cluster

The following example adds member tables to the dynamic cluster table, Sales_History, that was created in Example 1: Create a Dynamic Cluster Table. The Sales_History table currently contains 12 members. This example augments the 12 member tables for 2014 with six new member tables that contain sales data for January through June of 2015.

/* declare main vars */
%let host=kaboom;
%let port=5400;
%let spdssize=256M;
%let spdsiasy=YES;

libname  mylib sasspds "sales"
   &host..&port;   
   user='anonymous'
   ip=YES;

/* Use PROC SPDO to add member tables to */ 
/* the dynamic cluster table sales_history */ 

PROC SPDO library=mylib;
   cluster add sales_history
   mem=sales201501
   mem=sales201502
   mem=sales201503
   mem=sales201504
   mem=sales201505
   mem=sales201506;
quit;

/* Verify the presence of the added tables */
proc contents data=mylib.sales_history;
run;

Example 3: Refresh Dynamic Cluster Table with CLUSTER REPLACE

This example performs a refresh of the dynamic cluster table Sales_History by using the PROC SPDO CLUSTER REPLACE statement. The CLUSTER REPLACE statement enables you to refresh one member table in a dynamic cluster without interrupting continuous cluster operations by undoing and re-creating the cluster.

Note: A member cannot be replaced if the cluster was created with UNIQUEINDEX=YES.

/* declare main vars */
%let host=kaboom;
%let port=5400;
%let spdssize=256M;
%let spdsiasy=YES;

libname mylib sasspds "sales"
   &host..&port
   user='anonymous'
   IP=YES ;

/* Use PROC SPDO to refresh the member tables */
/* in the dynamic cluster table Sales_History */
/* by replacing the member from December 2014 */
/* with a member from January 2015.           */

PROC SPDO library=mylib;
   cluster replace sales_history
      oldmem=sales201412 newmem=sales201501;
   quit;

/* Verify the contents of the refreshed dynamic */
/* cluster table sales_history */

proc contents data=mylib.sales_history;
run;

Example 4: Refresh Dynamic Cluster Table with CLUSTER REMOVE and CLUSTER ADD

This example performs a refresh of the dynamic cluster table Sales_History by using the PROC SPDO CLUSTER REMOVE and CLUSTER ADD statement set. The CLUSTER REMOVE and CLUSTER ADD statement set enables you to refresh one or more member tables in a dynamic cluster without interrupting continuous cluster operations by undoing and re-creating the cluster.

/* declare main vars */
%let host=kaboom;
%let port=5400;
%let spdssize=256M;
%let spdsiasy=YES;

libname mylib sasspds "sales"
   &host..&port
   user='anonymous'
   IP=YES ;

/* Use PROC SPDO to refresh the member tables */
/* in the dynamic cluster table Sales_History */
/* by replacing the members from July 2014 to */
/* December 2014 with members from January    */
/* 2015 to June 2015.                         */

PROC SPDO library=mylib;
   cluster remove sales_history
   mem=sales201407
   mem=sales201408
   mem=sales201409
   mem=sales201410
   mem=sales201411
   mem=sales201412';

  cluster add sales_history
   newmem=sales201501
   newmem=sales201502
   newmem=sales201503
   newmem=sales201504
   newmem=sales201505
   newmem=sales201506;
  quit;

/* Verify the contents of the refreshed dynamic */
/* cluster table sales_history */

proc contents data=mylib.sales_history;
run;

Example 5: Undo and Refresh Dynamic Cluster Table

This example uses an older server method to refresh a dynamic cluster table by unbinding the cluster, changing the member tables, and then re-binding the cluster. This method remains functional. Most users will find that the newer server statements (CLUSTER REMOVE, CLUSTER ADD, and CLUSTER REPLACE) produce identical results without requiring the dynamic cluster to be disassembled.

This example illustrates use of the CLUSTER UNDO and CLUSTER CREATE statements to refresh dynamic cluster table Sales_History. First, the 18-member dynamic cluster table Sales_History is unbound. The 12 member tables that contain 2014 sales data are omitted when the dynamic cluster table Sales_History is re-created. When the table is re-created, only the six member tables that contain 2015 sales data are included. These combined actions refresh the contents of the dynamic cluster table Sales_History.

/* declare main vars */
%let host=kaboom;
%let port=5400;
%let spdssize=256M;
%let spdsiasy=YES;

libname mylib sasspds "sales"
   &host..&port
   user='anonymous'
   IP=YES ;

/* Use PROC SPDO to undo the existing dynamic */
/* cluster table Sales_History, then rebind */
/* it with members from months in 2015 only */

PROC SPDO library=mylib;
   cluster undo sales_history;
   cluster create sales_history
      mem=sales201501
      mem=sales201502
      mem=sales201503
      mem=sales201504
      mem=sales201505
      mem=sales201506;
  quit;

/* Verify the contents of the refreshed dynamic */
/* cluster table sales_history */

proc contents data=mylib.sales_history;
run;