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
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.
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:
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:
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.
That was my plan B
I was hoping that I had missed something in the transformation.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.