Creating Dynamic Cluster Tables

Basic Syntax for Creating a Dynamic Cluster Table

Example of Creating a Dynamic Cluster Table

Requirements

A dynamic cluster table can be created with one or more server tables. When multiple server tables are used, the tables should have related content. In addition, the tables must meet the following requirements:

The tables must all be in the same domain.
The tables must have matching table, column, and index attributes. For more information, see Member Table Requirements for Creating Dynamic Cluster Tables.

Basic Syntax for Creating a Dynamic Cluster Table

You create a dynamic cluster table with the CLUSTER CREATE statement. The easiest way to issue CLUSTER CREATE and other cluster statements is in PROC SPDO. You can also submit the statements using explicit SQL pass-through.

Regardless of how it is submitted to the server, the general form of the CLUSTER CREATE statement is as follows:

CLUSTER CREATE cluster-table-name

MEM|MEMBER=member-name-1
MEM|MEMBER=member-name-n
<DELETE=YES|NO>
<MAXSLOT=n>
<UNIQUEINDEX=YES|NO>;

cluster-table-name

specifies the name of the cluster table to be created.

member-name

specifies a member table name. Specify a MEM= (or MEMBER=) argument for each member table that you want to include. You must specify at least one member table.

<DELETE=YES | NO>

specifies whether the cluster table and its members can be destroyed with the CLUSTER DESTROY statement. The default setting is NO. When DELETE=NO, you must use the CLUSTER UNDO statement to unbind the cluster before you can delete member tables. Specify YES if you want the ability to use the CLUSTER DESTROY statement to delete the cluster and all its members.

<MAXSLOT=n>

specifies the maximum number of slots, or member tables, to be allocated for this cluster table. The default server setting for the MAXSLOT= parameter is -1. This value permits dynamic growth of the number of member tables, up to the specified system maximum value. The system maximum value for the number of slots is specified by the MAXGENNUM column setting in the server’s configuration file. If there is a known maximum number of slots to be enforced for a cluster table, it is more efficient to specify the limitation using the MAXSLOT= argument.

<UNIQUEINDEX=YES | NO>

specifies whether the unique indexes that are defined in the member tables should be validated and marked as unique in the dynamic cluster table. The default server setting is YES. 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. In addition, if you choose to use the validation process but the indexes are not unique, the CLUSTER CREATE statement fails. Specify UNIQUEINDEX=NO if you want to turn off the index validation process.

Example of Creating a Dynamic Cluster Table

Suppose your company generates a server table that contains monthly sales transactions. You have 24 tables, representing the past 24 months. You’ve been asked to link the tables so that reports can be written that compare sales figures to assist in identifying any trends. You’ve been told that the reports will be needed twice annually.

The following example shows the PROC SPDO code that you can use to create a dynamic cluster table named Sales_History with the 24 tables.

Dynamic Cluster Table depicts a dynamic cluster table with 24 members.

proc spdo library=libref;
   cluster create Sales_History
      mem=sales201301
      mem=sales201302
      mem=sales201303
      mem=sales201304
      mem=sales201305
      mem=sales201306
      mem=sales201307
      mem=sales201308
      mem=sales201309
      mem=sales201310
      mem=sales201311
      mem=sales201312
      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 ;

Dynamic Cluster Table

The PROC SPDO LIBRARY= argument specifies the libref that represents the SPD Server domain that contains the tables to be clustered. The CLUSTER CREATE statement specifies to create a cluster table named Sales_History. The MEM= argument identifies the members of the dynamic cluster table. The other parameters are left to use their default values:

The cluster table and its members cannot be destroyed.
There is no limit on the number of members, beyond the system configured value, so additional months of sales data can be added as they become available.
Unique indexes are validated.

Last updated: February 8, 2017