BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Resa
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20

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

5 REPLIES 5
LinusH
Tourmaline | Level 20
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
Resa
Pyrite | Level 9

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.
 
LinusH
Tourmaline | Level 20

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
Resa
Pyrite | Level 9

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

Resa
Pyrite | Level 9

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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