Creating an SPD Server Cluster Table

Problem

You want to create an SPD Server cluster table. Cluster tables can be used as the inputs or outputs in SAS Data Integration Studio jobs and can improve the performance of the jobs.

Solution

You can use the Create or Add to a Cluster transformation to create or add tables to an SPD Server cluster table. Use this transformation to create an SPD Server cluster table in a SAS Data Integration Studio job and list its contents in the Output tab in the Job Editor window. For more information, see the following tasks:

Tasks

Create and Populate the Job

Perform the following steps to build a job that creates an SPD Server cluster table. If you add the List Cluster Contents transformation to the same job, you can list the source tables in the Output tab in the Job Editor window.
  1. Create a job in SAS Data Integration Studio and give it an appropriate name.
  2. Drop the Create or Add to a Cluster transformation on the Job Editor window. This transformation produces a temporary output table that you can use as a permanent output table or as an input to another transformation or table loader. You can also replace the temporary output table with a permanent target table. The SPD server cluster job does not actually load a physical table. Instead, it creates a virtual table that combines all of the data from the tables included in the SPD Server library into a virtual table that is processed as a single unit. The following example shows the temporary output table.
    Sample SPD Server Cluster Table Job with Temporary Output Table
    Sample SPD Server Cluster Table Job with Temporary Output Table
  3. To replace the temporary output table with the clustered table, right-click the temporary work table that is attached to the Create or Add to a Cluster transformation and click Replace in the pop-up menu. Then, use the Table Selector window to select the cluster table. For additional information about temporary output tables, see Working with Default Temporary Output Tables.
  4. To verify what tables were clustered, add the List Cluster Contents transformation to the process flow, and drop the transformation on the Job Editor window. Then, drag the cursor from the output port of the cluster table to the input port of the List Cluster Contents transformation. The following display shows a process flow diagram for the resulting job. The numbers on the transformations show the order of the job's processes.
    Sample SPD Server Cluster Table Job with List Cluster Contents
    Sample SPD Server Cluster Table Job with List Cluster Contents
    The List Cluster Contents transformation sends a list of all tables included in the cluster table to the Output tab.

Specify Options for the Create or Add to a Cluster Transformation

Perform the following steps to specify options for the Create or Add to a Cluster Transformation and run the job.
  1. Right-click the Create or Add to a Cluster transformation and click Properties to access the Create or add to a cluster Properties window. Then click Options to access the Options tab.
  2. Limit the tables that are included in the cluster table by entering a string in the Filter: table name contains ... field found on the Cluster Options window. In this case, enter CLUSTER because all tables that are required include this string in the table name.
  3. Enter a value into the Set maximum number of slots field. This value must be large enough to accommodate the potential growth of the cluster because the number of slots cannot be increased after the cluster is created. If the slot size does not accommodate all of the clustered tables, then you must delete the existing cluster definition and define a new cluster that includes an adequate value for the maximum number of slots.
  4. Click OK to save the setting and close the properties window.
  5. Submit and run the job. Click Output to access the Output tab and verify that the expected tables were added to the SPD Server cluster table, as shown in the following example:
    Cluster Contents on Output Tab
    Cluster Contents on Output Tab
Last updated: January 16, 2018