topic Re: Getting error while clustering two SPDS table in SAS Enterprise Guide

Getting error while clustering two SPDS table

Tushar — Thu, 10 Oct 2013 10:16:00 GMT

Hi ALL,

I am getting error while clustering two SPDS table. Also checked criteria that are needed two satisfie when you cluster two SPDS table. mentioned in below link.

http://support.sas.com/documentation/cdl/en/spdsug/64018/HTML/default/viewer.htm#n0jg57dvm7iajyn1olkdnnwnel78.htm

this is something i have checked using Proc contents with two clustering table. Any suggestions on this?

the error i am getting is below.

ERROR: Member table not compatible with other cluster members. Compare CONTENTS

for some reason i can't share the code over here but looking for suggestion from anyone who have worked or faced same kind of issue.

Regards,

Tushar Jagtap.

Re: Getting error while clustering two SPDS table

Tushar — Thu, 10 Oct 2013 13:24:50 GMT

Is this necessary to have table compress when we are using engine as SASSPDS?

Re: Getting error while clustering two SPDS table

umashankersaini — Sat, 12 Oct 2013 07:02:52 GMT

Hi Tushar,

I know, you are already done with your homework but may be this one could help.

1.When loading a SPD server table always sort the table by the variable used to join that SPD server table to other SPD server tables

2.When ordering the "Where, aggregations and counts" variables in the sort routine go from lowest to highest.

3.Always use compression on SPD server domain.

4.You can use the LIBNAME options COMPRESS=Yes or the SPD server macro SPDSDCMP=yes to ensure all tables written to an SPD server domain are compressed.

5.Always replace DATA STEP modify code with PROC APPEND using the SPD Server option UNIQUESAVE=REP

6. To use PROC APPEND with UNIQUESAVE=REP the SPD Server table must have a unique index.

Regards

Uma Shanker Saini

Re: Getting error while clustering two SPDS table

umashankersaini — Sat, 12 Oct 2013 07:10:12 GMT

Hi Tushar,

When you are quering SPDS tables, always try to use these LIBNAME options:

1. IP=yes

If the ANSI Standard SQL submitted to SPD server would run faster in Pass-through sql, the query will be converted to pass through SQL.

2. LIBGEN=yes

Required when SAS Marketing Automation uses SPD Server tables.

3. DISCONNECT=yes

Kills the SPD server proxy server when the libname is cleared.

4. COMPRESS=yes

Ensure compression is used when writing to SPD Server table

Regards

Uma shanker Saini

Re: Getting error while clustering two SPDS table

umashankersaini — Sat, 12 Oct 2013 07:11:26 GMT

Hi tushar,

yes, it is necessary to use compression when writting to SPD server table.

Regards

Uma Shanker Saini

Re: Getting error while clustering two SPDS table

umashankersaini — Sat, 12 Oct 2013 07:12:55 GMT

Hi Tushar,

Try to check this one as well...

You can only perform four functions on a dynamic cluster table: creating a cluster, undoing a

cluster, adding tables, and listing member tables. To perform any function other than these four,

you must first undo the cluster with the CLUSTER UNDO command. Then, any changes must be

made to all individual tables in the cluster. Once changes are made, you can re-create the cluster

using the CLUSTER CREATE command.

All member SAS SPD Server tables in a cluster must be identical; that is, they must have the

same definitions for columns names, column widths, table indexes, formats on the columns,

partition size, compression ON/OFF, and so forth. You cannot change table names, column

names, column formats, indexes, or anything else about any member table, including the cluster

name.

The owner of all member tables must be the same. To create a cluster, the owner of the cluster

must be the same user that owns the member table. For example, suppose USER1 creates a

table, and USER2 also creates a table. These tables cannot be members of the same cluster,

regardless of what ACL permissions are granted on the tables

Regards

Uma Shanker Saini

Re: Getting error while clustering two SPDS table

LinusH — Sat, 12 Oct 2013 21:54:07 GMT

Perhaps you could use PROC COMPARE to see if you have any differences. It will cover all attributes that are common to Base and SPDS, but perhaps not SPDS specific ones.

And no, you don't have to use compression with SPDS. If this was the case, I'm pretty sure it would be mandatory or default. Use compression when appropriate, pretty much in the same cases as with Base SAS tables. But of course, in a cluster, all tables need to be either compressed, or uncompressed.

Re: Getting error while clustering two SPDS table

Tushar — Mon, 14 Oct 2013 11:25:37 GMT

Thanks LinusH and umashankersaini, I will try to check above things that you have mentioned. I have used Proc Contents and not Proc Compare. Will try to chek throught proc compare as well. Thanks again both of you!!!

Regards,

Tushar Jagtap

Re: Getting error while clustering two SPDS table

Tushar — Mon, 14 Oct 2013 12:15:27 GMT

Hi,

i got my problem solve, I was using Proc contents without verbose=Yes option. later i done with proc contents with verbose=yes and found difference between minmaxvarlist in tables. That solved my issue after making changes.

Thanks both of you again for your helpful information.

Regards,

Tushar J

Re: Getting error while clustering two SPDS table

umashankersaini — Tue, 15 Oct 2013 07:38:31 GMT

Hi Tushar,

Thank you so much for updating us. It would be add-on note into my bucket.

thank you again.

Regards

Uma Shanker Saini