SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Set UNIQUEINDEX option in "Create or Add to a Cluster" Transformation in DIS 4.901

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 84
Accepted Solution

Set UNIQUEINDEX option in "Create or Add to a Cluster" Transformation in DIS 4.901

Hi all,

We are using SAS Data Integration Studio 4.901 on SAS 9.4M3 and make use of Dynamic Cluster Tables in SPDS.

When making use of the "Create or Add to a Cluster" transformation basically the following code is created:

PROC SPDO LIB=<library>;
    CLUSTER CREATE <cluster name>
    MEM=<dataset 1>
    MEM=<dataset 2>
    ....
    MEM=<dataset n>
    MAXSLOT=<n>
    ;
QUIT;

We do have unique indexes per cluster table but do NOT want the unique index to be validated over all tables.

However the option that controls this (UNIQUEINDEX) defaults to YES.

As such we like to add the UNIQUEINDEX=NO option, as described here, to the above indicated code.

 

I have looked for ways to add this option within the indicated transformation but just can not find it. 

Am I overlooking something or is this not possible with the transformation as it is at the moment?

 

TIA,

--Resa


Accepted Solutions
Solution
‎10-14-2016 05:14 AM
Super User
Posts: 5,256

Re: Set UNIQUEINDEX option in "Create or Add to a Cluster" Transformation in DIS 4.901

So it was clearly something I've missed.

If you can't set it to NO in DI Studio (I don't have it at my fingertips at the moment), make a copy of the transformation and add a UNIQUEINDEX parameter, and modify the code accordingly.

Data never sleeps

View solution in original post


All Replies
Super User
Posts: 5,256

Re: Set UNIQUEINDEX option in "Create or Add to a Cluster" Transformation in DIS 4.901

Maybe I missed something but I believe you can't define this for a cluster. It's like you indicate an attribute on a single table index definition.
The consequence is that for a cluster uniqueindex = no is implicit.
Data never sleeps
Frequent Contributor
Posts: 84

Re: Set UNIQUEINDEX option in "Create or Add to a Cluster" Transformation in DIS 4.901

[ Edited ]

Not sure if I fully understand your comment. But my understanding is that this option is defined at cluster level during the creation of the cluster.

 

As described in the SPD Server SQL Cluster Operations for the cluster creation:

After you have organized the SPD Server tables, issue a PROC SPDO command to bind the tables into a dynamic cluster table.
The general form for the PROC SPDO cluster create command is as follows:

CLUSTER CREATE <cluster-tablename>   MEM|MEMBER=<membername>
  MAXSLOT=<max-slot-num-spec>  UNIQUEINDEX=YES|NO
  DELETE=YES|NO;

The CLUSTER CREATE command options are as follows:

  • UNIQUEINDEX=YES|NO validates a unique index. The default setting is YES.
  • DELETE=YES|NO permanently deletes the cluster and its members. The default setting is NO.

 

The indicated transformation, as far as I can see, generates code for the following part of the code:

CLUSTER CREATE <cluster-tablename>   MEM|MEMBER=<membername>
  MAXSLOT=<max-slot-num-spec>;

 

However there are no possibilities - what I can see - to add/alter either the UNIQUEINDEX or DELETE option

 

EDIT:

 For reference (from the SPD Server 4.4 Administration Guide):
"The CLUSTER CREATE command in PROC SPDO has a new option that allows the user to specify whether unique indexes that are defined in the member tables should be validated and marked as unique in the cluster. If the UNIQUEINDEX option is set to No, then unique indexes are not validated, and the cluster metadata does not mark the indexes as unique within the cluster. If the UNIQUEINDEX option is not specified, then it defaults to YES and the indexes are validated and marked unique within the
cluster."
We do not require this validation and as such would like to set the option to NO.
 
Solution
‎10-14-2016 05:14 AM
Super User
Posts: 5,256

Re: Set UNIQUEINDEX option in "Create or Add to a Cluster" Transformation in DIS 4.901

So it was clearly something I've missed.

If you can't set it to NO in DI Studio (I don't have it at my fingertips at the moment), make a copy of the transformation and add a UNIQUEINDEX parameter, and modify the code accordingly.

Data never sleeps
Frequent Contributor
Posts: 84

Re: Set UNIQUEINDEX option in "Create or Add to a Cluster" Transformation in DIS 4.901

[ Edited ]

That was my plan B   Smiley Wink
I was hoping that I had missed something in the transformation.

Frequent Contributor
Posts: 84

Re: Set UNIQUEINDEX option in "Create or Add to a Cluster" Transformation in DIS 4.901

In the mean time I have completed your suggested solution and have a new transformation that allows the user to set the option for UNIQUEINDEX.

 

And - while I was at it - also modified the "List Cluster Content" transformation to include the "/VERBOSE" and "OUT=" option (as described in "Querying and Reading Member Tables in a Dynamic Cluster". This opposed to the included version that uses the content of the macro variable SPDSLIST to generate a list of members, does not allow the inclusion of the values for the variables that are included in the MINMAXVARLIST and does not have an actual output table in the transformation.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 456 views
  • 2 likes
  • 2 in conversation