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

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

Resa — Thu, 13 Oct 2016 11:06:36 GMT

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

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

LinusH — Thu, 13 Oct 2016 11:45:22 GMT

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.

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

Resa — Thu, 13 Oct 2016 12:21:22 GMT

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.

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

LinusH — Thu, 13 Oct 2016 13:16:10 GMT

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.

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

Resa — Thu, 13 Oct 2016 14:53:40 GMT

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

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

Resa — Fri, 14 Oct 2016 09:22:14 GMT

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.