Querying and Reading Member Tables in a Dynamic Cluster

Dynamic clusters can be read using the MEMNUM= table option. The MEMNUM= option enables you to perform query or read operations on a single member table that belongs to a dynamic cluster. When you use the MEMNUM= option, SPD Server opens only the specified member table, instead of opening all of the member tables that belong to the dynamic cluster. You can determine the member number of a table in the dynamic cluster by issuing a CLUSTER LIST statement or PROC CONTENTS on the dynamic cluster. The SPD Server CLUSTER UNDO statement or PROC CONTENTS output lists the member tables of the dynamic cluster in numbered order.

You can specify verbose output for the CLUSTER LIST statement by using the following option syntax:

CLUSTER LIST clustername [/VERBOSE]

When you issue the VERBOSE option with a CLUSTER LIST statement, the output lists the MINMAXVARLIST information for each member table in a dynamic cluster.

The following example uses PROC SPDO to create a dynamic cluster that has a MINMAXVARLIST on the numeric column STORE_ID in each member table. Then, a CLUSTER LIST statement is issued with the VERBOSE option. The CLUSTER LIST output displays the dynamic cluster name, the names of each member table in the dynamic cluster, and the MINMAXVARLIST information for each member table.

PROC SPDO library=&libdom;

CLUSTER CREATE ussales
  mem=ne_region
  mem=se_region
  mem=central_region
  maxslot=6 ;

CLUSTER LIST ussales /VERBOSE;
MINMAXVARLIST COUNT=1
varname=store_id
Numeric type.

Cluster Name USSALES, Mem=NE_REGION
  Variable Name  (MIN,MAX)
  STORE_ID       ( 1, 20)

Cluster Name USSALES, Mem=SE_REGION
  Variable Name  (MIN,MAX)
  STORE_ID       ( 60, 70)

Cluster Name USSALES, Mem=CENTRAL_REGION
  Variable Name (MIN,MAX)
  STORE_ID      ( 60, 70)

NOTE: The maximum number of possible slots is 6.

You can specify an integer valuen as an argument for the MEMNUM= table option to select the nth member of the table, or you can use the argument LASTCLUSTERMEMBER. When you use the LASTCLUSTERMEMBER argument with MEMNUM=, SPD Server selects the last member of the dynamic cluster table without needing to count the members to determine the number nof the last member.

The following example uses the MEMNUM= table option to query the member table sales200504 that belongs to the dynamic cluster table sales_history:

PROC SPDO library=&domain;
  CLUSTER CREATE sales_history
   mem=sales200501
   mem=sales200502
   mem=sales200503
   mem=sales200504
   mem=sales200505
   mem=sales200506
   maxslot=12;
  quit;

 PROC PRINT data=&domain..sales_history (MEMNUM=4);
   WHERE salesdate=30Apr2005;
 run;

To use the MEMNUM= table option to query the last member table in the dynamic cluster table sales200506, the query is:

PROC SPDO library=&domain;
  CLUSTER CREATE sales_history
   mem=sales200501
   mem=sales200502
   mem=sales200503
   mem=sales200504
   mem=sales200505
   mem=sales200506
   maxslot=12;
  quit;

 PROC PRINT data&domain..sales_history
  (MEMNUM=LASTCLUSTERMEMBER);
   WHERE salesdate=15Jun2005;
 run;