SAS Scalable Performance Data (SPD) Server Table Options

Creates an index that documents the minimum and maximum values of specified variables.  SAS Scalable Performance Data (SPD) Server WHERE Clause evaluations use MINMAXVARLIST= indexes to include or eliminate member tables in a SAS Scalable Performance Data (SPD) Server dynamic cluster table from SQL evaluation scans..

Syntax

MINMAXVARLIST=(varname1, varname2, ... , varnameN) 

Use the following argument:

varname1, varname2, ... , varname N

are SAS Scalable Performance Data (SPD) Server table variable names.

Description

The primary purpose of the MIINMAXVARLIST= table option is for use with SAS Scalable Performance Data (SPD) Server dynamic cluster tables where specific members in the dynamic cluster contain a set or range of values, such as sales data for a given month. When a SAS Scalable Performance Data (SPD) Server SQL sub setting where- clause specifies specific months from a range of sales data, the WHERE planner checks the min/max indexes. Based on the min/max index information, the SAS Scalable Performance Data (SPD) Server WHERE planner includes or eliminates member tables in the dynamic cluster for evaluation.

MINMAXVARLIST= uses the list of columns you submit to build an index.  The MINMAXVARLIST= index contains only the minimum and maximum values for each column. The WHERE Clause planner uses the index to filter SQL predicates quickly, and to include or eliminate member tables belonging to the cluster table from the evaluation.

Although the MINMAXVARLIST= table option is primarily intended for use with dynamic clusters, it also works on standard SAS Scalable Performance Data (SPD) Server tables. MINMAXVARLIST=  can help reduce the need to create many indexes on a table, which can save  valuable resources and space.

Example

%let domain=path3 ;
%let host=kaboom ;
%let port=5201 ;

libname &domain sasspds "&domain"
   server=&host..&port
   user='anonymous' ;

/* Create three tables called */
/* xy1, xy2, and xy3.         */

data &domain..xy1(minmaxvarlist=(x y));
  do x = 1 to 10;
  do y = 1 to 3;
  output;
  end;
end;
run;

data &domain..xy2(minmaxvarlist=(x y));
  do x = 11 to 20;
  do y = 4 to 6 ;
  output;
  end;
end;
run;

data &domain..xy3(minmaxvarlist=(x y));
  do x = 21 to 30;
  do y = 7 to 9 ;
  output;
  end;
end;
run;


/* Create a dynamic cluster table */
/* called cluster_table out of    */
/* new tables xy1, xy2, and xy3   */

PROC SPDO library=&domain ;
   cluster create cluster_table
      mem=xy1
      mem=xy2
      mem=xy3
      maxslot=10;
quit;


/* Enable WHERE evaluation to see  */
/* how the SQL planner selects     */
/* members from the cluster. Each  */
/* member is evaluated using the   */
/* min-max index.                  */

%let SPDSWDEB=YES;


/* The first member has true rows  */

PROC PRINT data=&domain..cluster_table ;
   where x eq 3
   and y eq 3;
run;


/* Examine the other tables */

PROC PRINT data=&domain..cluster_table ;
   where x eq 3
   and y eq 3 ;
run;

PROC PRINT data=&domain..cluster_table ;
   where x eq 3
   and y eq 3;
run;

PROC PRINT data=&domain..cluster_table ;
   where x between 1 and 10
   and y eq 3;
run;

PROC PRINT data=&domain..cluster_table ;
   where x between 11 and 30
   and y eq 8 ;
run;


/* Delete the dynamic cluster table. */

PROC DATASETS lib=&domain nolist;
   delete cluster_table ;
quit ; 

---

THREADNUM=

Specifies the number of threads to be used for WHERE Clause evaluations.

Syntax

THREADNUM=n

Default

THREADNUM= is set equal to the value of the MAXWHTHREADS server parameter.

Used in Conjunction with SAS Scalable Performance Data (SPD) Server Parameter

MAXWHTHREADS

Corresponding Macro Variable

 SPDSTCNT=

Use the following argument:

n

is the number of threads.

Description

THREADNUM= allows you to specify the thread count the SAS Scalable Performance Data (SPD) Server should use when performing a parallel WHERE Clause evaluation.

Use this option to explore scalability for WHERE Clause and GROUP_BY evaluations in non-production jobs.  If you use this option for production jobs, you are likely to lower the level of parallelism that is applied to those clause evaluations.

THREADNUM= works in conjunction with MAXWHTHREADS, a configurable system parameter. MAXWHTHREADS imposes an upper limit on the consumption of system resources. The default value of MAXWHTHREADS is dependent on your operating system.  Your SAS Scalable Performance Data (SPD) Server administrator can change the default value for MAXWHTHREADS.

If you do not use THREADNUM=, the software provides a default thread number, up to the value of MAXWHTHREADS as required. If you use THREADNUM=, the value that you specify is also constrained by the MAXWHTHREADS value.

The THREADNUM= value applies both to parallel table scans (EVAL2 strategy), parallel indexed evaluations (EVAL1 strategy), parallel BY-clause processing, and parallel GROUP_BY evaluations. The SAS Scalable Performance Data (SPD) Server User's Guide Help section on Optimizing SAS Scalable Performance Data (SPD) Server Performance, contains more information on WHERE Clause evaluation.

Example

The SAS Scalable Performance Data (SPD) Server administrator set MAXWHTHREADS=128 in the SAS Scalable Performance Data (SPD) Server's parameter file. Explore the effects of parallelism on a given query by using the following SAS macro:

%macro dotest(maxthr);
%do nthr=1 %to &maxthr;
   data _null_;
     set SPDSCEN.PRECS(threadnum=&nthr);
     WHERE
       occup='022'
       and state in('37','03','06','36');
   run;
%mend dotest;

---

WHERENOINDEX=

Specifies a list of indexes to exclude when making WHERE Clause evaluations.

Syntax

WHERENOINDEX=(name1 name2...)

Use the following arguments:

(name1 name2...)

a list of index names that you wish to exclude from the WHERE planner. 

Example

We have a table PRECS with indexes defined as follows:

PROC DATASETS lib=spdscen;
modify precs(bitindex=(hour89));
index create
  stser=(state serialno)
  occind=(occup industry)
  hour89;
quit;

When evaluating the next query, we want the SAS Scalable Performance Data (SPD) Server to exclude from consideration indexes for both the STATE and HOUR89 columns.

In this case, we know that our AND combination of the predicates for the OCCUP and INDUSTRY columns will produce a very small yield. Few rows satisfy the respective predicates. To avoid the extra index I/O (machine time) that the query requires for a full-indexed evaluation, use the following SAS code:

PROC SQL;
create table hr80spds
  as select
    state,
    age,
    sex,
    hour89,
    industry,
    occup
  from spdscen.precs(wherenoindex=(stser hour89))
    where occup='022'
    and state in('37','03','06','36')
    and industry='012'
    and hour89 > 40;
quit;

Note: Specify index names in the WHERENOINDEX list, not the column names. The example excludes both the composite index for the STATE column STSER and the simple index HOUR89 from consideration by the  WHINIT WHERE planner.

---

Options for Other Functions

BYSORT=

Perform an implicit automatic sort when SAS Scalable Performance Data (SPD) Server encounters a BY clause for a given table.

Syntax

BYSORT=YES | NO

Use the following arguments:

YES

sorts the data based on the BY columns and returns the sorted data to the SAS client. This powerful capability means the user does not have to sort data using a PROC SORT statement before using a BY clause.

 

NO

does not sort the data based on the BY columns. This may be desirable if a DATA step BY clause has a GROUPFORMAT option or if a PROC step reports grouped and formatted data.

Description

The default is YES. The NO argument means the table must have been previously sorted by the requested BY columns. The NO argument allows grouped data to maintain their precise order in the table. A YES argument groups the data correctly but possibly in a different order from the order in the table.

Example 1 - Group Formatting with BYSORT=

libname sport sasspds 'mylib'
   host='samson'
   user='user19'
   passwd='dummy2';

PROC FORMAT;
   value dollars
     0-99.99="low"
     100-199.99="medium"
     200-1000="high";
run;

data sport.racquets;
   input
     raqname $20.
     @22 weight
     @28 balance $2.
     @32 flex
     @36 gripsize
     @42 string $3.
     @47 price
     @55 instock;

   datalines;
Solo Junior          10.1   N   2  3.75  syn   50.00   6
Solo Lobber          11.3   N  10  5.5   syn  160.00   1
Solo Queensize       10.9  HH   6  5.0   syn  130.00   3
Solo Kingsize        13.1  HH   5  5.6   syn  140.00   3
;

PROC PRINT data=sport.racquets (bysort=yes);
   var raqname instock;
   by price;
   format price dollars.;
title 'Solo Brand Racquets by Price Level';
run;

Output 4. 1 Report Output with BYSORT=

Solo Brand Racquets by Price Level ---------------------------- Price=low --------------------------- OBS                       RAQNAME                INSTOCK 1                             Solo Junior                            6 -------------------------- Price=medium ------------------------ OBS                       RAQNAME                INSTOCK   3                        Solo Queensize                         3   4                        Solo Kingsize                            3 2                        Solo Lobber                             1

 

Example 2 - Group Formatting without BYSORT=

PROC PRINT data=sport.racquets (bysort=no);
   var raqname instock;
   by price;
   format price dollars.;
title 'Solo Brand Racquets by Price Level';
run;

Output 4. 2 Report Output without BYSORT=

Solo Brand Racquets by Price Level ---------------------------- Price=low --------------------------- OBS                       RAQNAME                INSTOCK 1                             Solo Junior                            6 -------------------------- Price=medium ------------------------ OBS                       RAQNAME                INSTOCK 2                        Solo Lobber                             1   3                        Solo Queensize                         3   4                        Solo Kingsize                            3

---

ENDOBS=

Specifies the end row (observation) number in a user-defined range for the processing of a given table.

Syntax

ENDOBS=n

Use the following argument:

n

is the number of the end row.

Description

By default, SAS Scalable Performance Data (SPD) Server processes the entire table unless the user specifies a range of rows with the STARTOBS= and ENDOBS= options. If the STARTOBS= option is used without the ENDOBS= option, the implied value of ENDOBS= is the end of the table. When both options are used together, the value of ENDOBS= must be greater than STARTOBS=.

In contrast to the Base SAS software options FIRSTOBS= and OBS=, the STARTOBS= and ENDOBS= SAS Scalable Performance Data (SPD) Server options can be used for WHERE Clause processing in addition to table input operations.

Example

Print only rows 2-4 of the table INVENTORY.OLD_AUTOS.

libname inventory sasspds 'conversion_area'
   server=husky.5105
   user='siteusr1'
   prompt=yes;

data inventory.old_autos;
   input
     year $4.
     @6 manufacturer $12.
     model $10.
     body_style $5.
     engine_liters
     @39 transmission_type $1.
     @41 exterior_color $10.
     options $10.
     mileage conditon;

   datalines;

1971 Buick       Skylark   conv  5.8  A  yellow    00000001 143000 2
1982 Ford        Fiesta    hatch 1.2  M  silver    00000001  70000 3
1975 Lancia      Beta      2door 1.8  M  dk blue   00000010  80000 4
1966 Oldsmobile  Toronado  2door 7.0  A  black     11000010 110000 3
1969 Ford        Mustang   sptrf 7.1  M  red       00000111 125000 3
;

PROC PRINT data=inventory.old_autos (startobs=2 endobs=4);
run;

Output 4. 3 Data in the Printed Output

1982   Ford               Fiesta           hatch    1.2    M   silver         00000001     70000     3 1975   Lancia            Beta             2door   1.3    M   dk blue      00000010     80000    4 1966   Oldsmobile   Toronado      2door   7.0    A    black        11000010    110000    3

---

STARTOBS=

Specifies the start row (observation) number in a user-defined range for the processing of a given table.

Syntax

STARTOBS=n

Use the following argument:

n

is the number of the start row.

Description

By default, SAS Scalable Performance Data (SPD) Server processes the entire table unless the user specifies a range of rows with the STARTOBS= and ENDOBS= options. If the ENDOBS= option is used without the STARTOBS= option, the implied value of STARTOBS= is 1. When both options are used together, the value of STARTOBS= must be less than ENDOBS=.

In contrast to the Base SAS software options FIRSTOBS= and OBS=, the STARTOBS= and ENDOBS= SAS Scalable Performance Data (SPD) Server options can be used for WHERE Clause processing in addition to table input operations.

Example

Print only rows 2-4 of the table INVENTORY.OLD_AUTOS.

libname inventory sasspds 'conversion_area'
   server=husky.5105
   user='siteusr1'
   prompt=yes;

data inventory.old_autos;
   input
     year $4.
     @6 manufacturer $12.
     model $10.
     body_style $5.
     engine_liters
     @39 transmission_type $1.
     @41 exterior_color $10.
     options $10.
     mileage conditon;

   datalines;

1971 Buick       Skylark   conv  5.8  A  yellow    00000001 143000 2
1982 Ford        Fiesta    hatch 1.2  M  silver    00000001  70000 3
1975 Lancia      Beta      2door 1.8  M  dk blue   00000010  80000 4
1966 Oldsmobile  Toronado  2door 7.0  A  black     11000010 110000 3
1969 Ford        Mustang   sptrf 7.1  M  red       00000111 125000 3
;

proc print data=inventory.old_autos (startobs=2 endobs=4);
run;

---

UNIQUESAVE=

Specifies to save rows with non-unique key values (the rejected rows) to a separate table when appending data to tables with unique indexes.

Syntax

UNIQUESAVE=YES|NO|REP

Default

  NO

Complements the Table Option

SYNCADD=

Used in Conjunction with Macro Variable

SPDSUSDS=

Corresponding Macro Variable:  

SPDSUSAV=

Use the following arguments:

YES

writes rejected rows to a separate, system-created table file which can be accessed by a reference to the macro variable SPDSUSDS=.

NO

does not write rejected rows to a separate table, that is, ignores non-unique key values. 

REP

when updating a master table from a transaction table, where the two tables share identical variable structures, the UNIQUESAVE=REP option replaces the row updated row in the master table instead of appending a row to the master table. The REP option only functions in the presence of a /UNIQUE index on the MASTER table. Otherwise the REP setting is ignored..

Description

SYNCADD= is defaulted to NO. When NO, table appends are 'pipelined', meaning that the server data is sent in a stream a block at a time (see table option NETPACKSIZE=). While pipelining is faster than a synchronous append, SAS reports the results of the append operation differently for these two modes.

When applying only a single row (SYNCADD=NO), SAS returns a status code for each ADD operation. The application can determine the next action based upon the status value. If a row is rejected due to containing a non-unique value for a unique index, the user receives a status message. In contrast, when data is pipelined (SYNCADD=YES), SAS returns a status code only after all the rows are applied to a table. As a consequence, the user does not know which rows have been rejected.

To enjoy the performance of data pipelining but still retain the rejected rows, use the UNIQUESAVE= option. When set to YES, SAS Scalable Performance Data (SPD) Server will save any rows that are rejected to a hidden SAS table.

When using this option, SAS returns the name of the hidden table containing the rejected rows in the macro variable SPDSUSDS.  If you want to report the contents of the table, reference SPDSUSDS= .

Note: If SYNCADD= YES is set, data pipelining is overridden and the data is processed synchronously. In this situation, the UNIQUESAVE= option is not relevant and, if set, is ignored.

Example 1
We want to append two tables, NAMES2 and NAMES3, which contain employees' names, to the NAMES1 table. Before performing our append, we create an index on the NAME column in NAMES1, declaring the index unique.

Specify for SAS Scalable Performance Data (SPD) Server, during the append operation, to store rows found with duplicate employee names to a separate table file generated by the macro variable SPDSUSDS=.

Use a %PUT statement to display the table name for SPDSUSDS=. Then request a printout of the duplicate rows to review later.

data employee.names1;
input name $ exten;
datalines;
Jill 4344
Jack 5589
Jim  8888
Sam  3334
;
run;

data employee.names2;
input name $ exten;
datalines;
Jack  4443
Ann   8438
Sam   3334
Susan 5321
Donna 3332
;
run;

data employee.names3;
input name $ exten;
datalines;
Donna 3332
Jerry 3268
Mike  2213
;
run;

PROC DATASETS lib=employee nolist;
  modify names1;
  index create name/unique;
quit;

PROC APPEND data=employee.names2
            out=employee.names1(uniquesave=yes); run;

title 'The NAMES1 table with unique names
       from NAMES2';

PROC PRINT data=employee.names1;
run;

%put Set the macro variable spdsusds to &spdsusds;

title 'Duplicate (non-unique) name rows found in
       NAMES2';

PROC PRINT data=&spdsusds;
run;

PROC APPEND data=employee.names3
   out=employee.names1(uniquesave=yes);
run;

The SAS log provides the messages:

WARNING: Duplicate values not allowed on index NAME for
         file EMPLOYEE.NAMES1. (Occurred 2 times.)
NOTE: Duplicate records have been stored in file
      EMPLOYEE._30E3FD5.

And, an extract from our PROC PRINT shows:

The NAMES1 table with unique names from NAMES2

         OBS    NAME     EXTENs

          1     Jill      4344
          2     Jack      5589
          3     Jim       8888
          4     Sam       3334
          5     Ann       8438
          6     Susan     5321
          7     Donna     3332

Duplicate (non-unique) name rows found in NAMES2

        OBS    NAME     EXTEN    XXX00000

          1     Jack      4443      NAME
          2     Sam       3334      NAME

 

Example 2

Use the UNIQUESAVE=REP option to perform an update / append case using PROC APPEND instead of a DATA step:

/* A MASTER table to update. ID */
/* will get a UNIQUE index      */

  DATA SPDS.MASTER;
    INPUT ID VALUE $;
    CARDS;
     1 one
     2 two
     3 three
  ;

  PROC DATASETS LIB=SPDS;
    MODIFY MASTER;
    INDEX CREATE ID/UNIQUE;
  QUIT;

  /* A transaction table TRANS to use to   */
  /* drive update/appends to MASTER        */

  DATA SPDS.TRANS;
    INPUT ID VALUE $;
     1 ONE
     3 THREE
     4 FOUR
     4 FOUR*
  ;

  /* Use of UNIQUESAVE=REP to update/append  */
  /* TRANS rows to MASTER based on whether   */
  /* TRANS records have an ID column that    */
  /* matches an existing row from the MASTER */
  /* table. Update MASTER rows with a match, */
  /* otherwise append TRANS row to MASTER    */

  PROC APPEND DATA=SPDS.TRANS
    OUT=SPDS.MASTER(UNIQUESAVE=REP);
  run;

Output of the resulting MASTER table would look like:

   Obs       ID      VALUE

    1         1      ONE
    2         2      two
    3         2      THREE
    4         4      FOUR*

---

VERBOSE=

Provides details of all indexes and ACL information associated with a SAS Scalable Performance Data (SPD) Server table.

Syntax

VERBOSE= YES | NO

Use the following arguments:

YES

requests detail information for the indexes, ACLs, and other SAS Scalable Performance Data (SPD) Server table values. This argument must be used with the CONTENTS procedure.

NO

suppresses detail information for the indexes, ACLs, and other SAS Scalable Performance Data (SPD) Server table values. This is the default.

Example

Request details of all the indexes for the table TEMP1 in the domain SPDS44.

PROC CONTENTS data=SPDS44 (verbose=yes);
run; 

                          The CONTENTS Procedure

Data Set Name           SPDS44.TEMP1                            Observations            1000
Member Type             DATA                                    Variables               2
Engine                  SASSPDS                                 Indexes                 2
Created                 Tuesday, May 10, 2005 10:00:02 AM       Observation Length      16
Last Modified           Tuesday, May 10, 2005 11:01:36 AM       Deleted Observations    0
Protection                                                      Compressed              NO
Data Set Type                                                   Sorted                  NO
Label
Data Representation     Default
Encoding                Default


                        Engine / Host Dependent Information

                        Blocking Factor (obs/block)     2047
                        ACL Entry                       NO
                        ACL User Access(R,W,A,C)        (Y,Y,Y,Y)
                        ACL User Name                   ANONYMOU
                        ACL Owner Name                  ANONYMOU
                        Data Set is Ranged              NO
                        Alphabetic List of Index Info   .
                        Bitmap index (No Global Index ) i
                        Keyvalue (Min)                  1
                        Keyvalue (Max)                  100
                        # of Discrete values            100
                        Bitmap index (No Global Index ) j
                        Keyvalue (Min)                  1
                        Keyvalue (Max)                  10
                        # of Discrete values            10
                        Data Partsize                   16777216


                        Alphabetic List of Variables and Attributes

                        *       Variable        Type    Len

                        1       i               Num     8
                        2       j               Num     9


                        Alphabetic List of Indexes and Attributes

                                                # of
                                                Unique
                        *       Index           Values

                        1       i               100
                        2       j               10

---

Options for Security  

ENCRYPT=

Encrypts SAS Scalable Performance Data (SPD) Server tables on disk.  Encryption is a security mechanism that protects table contents from users who have system access to raw SAS Scalable Performance Data (SPD) Server tables.  Access to tables is normally controlled by SAS Scalable Performance Data (SPD) Server ACLs. The SAS Scalable Performance Data (SPD) Server Administrator's Guide contains detailed information about using SAS Scalable Performance Data (SPD) Server ACLs to control access to tables.

When the ENCRYPT= option setting is set to YES, SAS Scalable Performance Data (SPD) Server encrypts newly created tables by blocks. To control the amount of encryption per block, use the table option IOBLOCKSIZE=. The IOBLOCKSIZE= option specifies the number of rows to be encrypted in each block.

Syntax

ENCRYPT= YES | NO

Arguments

YES

encrypts the data set. The encryption method uses passwords. At a minimum, you must specify the READ= or the PW= data set option at the same time that you specify an ENCRYPT=YES option setting.

NO

no table encryption is performed. NO is the default setting for the ENCRYPT= option.

Usage Notes

1. Depending on your query patterns, increasing or decreasing the block size can affect performance.
    
2. SAS Scalable Performance Data (SPD) Server does not encrypt table indexes or metadata. Only table row data are encrypted.
    
3. To encrypt SPD tables with pass-through SQL, use only the READ= or PW= table option. With pass-through SQL, ENCRYPT=YES is implied with these options.
    
4. To access an encrypted table, the user must have appropriate ACL permissions to the table and must provide the encryption key via the READ= or PW= table option.
    
5. Encrypting a SAS Scalable Performance Data (SPD) Server table provides security from users that have system access to dump raw SAS Scalable Performance Data (SPD) Server tables. The section on Security in the SAS Scalable Performance Data (SPD) Server Administrator's Guide contains more information about how to controll system access to SAS Scalable Performance Data (SPD) Server tables.