Connecting to SAS Scalable Performance Data (SPD) Server

Introduction

All SAS users should read the Help section on Accessing and Creating SPD Server Tables to review the methods that they can use to access SPD Server.  These methods include LIBNAME statements and SQL pass-through statements.  Syntax statements and options are provided for each method, as well as useful table options and macro variables.   

SAS and SPD Server Tables

SPD Server tables have different physical structures than SAS tables. In a general discussion, a SAS table can also refer to an SPD Server table. If the context is specific, for example, an SPD Server command, then the reference is specific. A SAS table refers to the Base SAS format; an SPD Server table refers to the SPD Server format. 

Using SPD Server and SAS together, you can

SAS Data Libraries

The term 'SAS data library' refers either to a collection of SAS files or SPD Server files. For SPD Server, a SAS data library is a collection of one or more directories that specify the location of stored SPD Server files. A data library has a primary file system. This is the directory an SPD Server administrator defines for the LIBNAME domain when it is set up. Optionally, a data library can have other directories for separation of SPD Server component files.

An SPD Server data library can contain the following LIBNAME domain files:

Temporary LIBNAME Domains

SPD Server allows you to create temporary LIBNAME domains that exist only for the duration of the LIBNAME assignment. Using this capability, SPD Server users can create space analogous to the SAS WORK library. To create a temporary LIBNAME domain, use the SPD Server LIBNAME statement option, TEMP=YES.

When you end your SPD Server session, all the data objects, including tables, catalogs, and utility files in the TEMP=YES temporary domain are automatically deleted. This is similar to how the SAS WORK library functions.

SPD Server Resource Security

SPD Server provides two levels of data security: UNIX file security and ACL file security. ACL file security enforces SPD Server permissions with SPD Server user IDs and Access Control Lists (ACLs).

UNIX File Security

The software enables ACL file security by default. While ACL file security is strongly recommended, the default can be changed. Only an SPD Server administrator can change the default file security setting.  When a SPD Server administrator specifies the NOACL option, all clients for SPD Server obtain the SPD Server user ID 'anonymous'. There is no SPD Server security in effect. SPD Server tables are then secured only by the UNIX file protections that are currently in force.

When UNIX file security controls SPD Server file access, it validates on the user ID associated with SPD Server. Which UNIX user ID is associated with SPD Server? The UNIX ID associated with SPD Server is the UNIX ID of the user that brings up the server. Suppose an SPD Server administrator brings up the SPD Server host machine, using his SPD Server administrator's account named SPDSADMN.  When any SAS client connects to this SPD Server host, they will only be able to read files that have UNIX read permissions set for the SPDSADMN user. As a result,  SAS clients that are connected to this SPD Server host must write all files in a directory created by SPDSADMIN that also has write permission set for SPDSADMN.  SPDSADMN will own all files written in this directory.

How is security maintained? The SPD Server administrator can set up the SPD Server LIBNAME domain directories such that only the administrator has appropriate read and write access to those directories.

It is possible for a site to give different UNIX permissions to a group of users. To do this, an SPD Server administrator must bring up another SPD Server using a different UNIX user account. (Bringing up a different SPD Server affects only the new SPD Server files created, not existing SPD Server files.)

ACL File Security

UNIX file security alone is not adequate for many installations. For more complex workplace environments, SPD Server provides a finer level of controls, called ACL file security. ACL file security is used by default for SPD Server LIBNAME domains.  SPD Server always enforces ACL file security unless an SPD Server administrator specifies the NOACL option when bringing up a Server.

To understand ACL file security, you must know how SPD Server user IDs work. The SPD Server administrator assigns each approved SPD Server user an ID, a password, a level of data authorization, and, optionally, membership in up to five ACLGROUPS. (The SPD Server user ID 'anonymous' does not require a password.)

Once your SPD Server UserID has been created, you and the SPD Server administrator can use PROC SPDO to create ACLs that grant or deny other users access to an SPD Server table. The documentation chapter on Accessing and Creating SAS Scalable Performance Data Server Tables explains how to use the PROC SPDO operator interface to secure SPD Server resources.  

Accessing SPD Server from a SAS Client

SQL Pass-Through Facility

SPD Server SQL pass-through processing supports an associated proxy process for each new client (via the name server).  The proxy issues SQL pass-through requests.  To connect to an SPD Server SQL server from a SAS session, you must submit a CONNECT statement that specifies the SASSPDS engine and SPD Server options, and then issues the SQL commands.  

For example: 

    PROC SQL;
    connect to sasspds
      (dbq='mydomain'
       host='namesvrID'
       serv='5555'
       user='neraksr'
       passwd='siuya');
    select *
      from connection
      to sasspds
       (select * from employee_info);
    disconnect from sasspds;
    quit;

LIBNAME Access

A logical name, or libref, is a name for the data library that you associate with an SPD Server domain during a SAS job or session.  Once a libref is assigned, SPD Server allows you to read, create, or update files in the data library if you have the appropriate access to the data library.

A libref is valid only for the current SAS job or session.  Librefs can be referenced repeatedly during a valid job or session. SAS does not limit the number of librefs that you can assign during a session. Once you define a libref, it is most commonly used as the first element in two-level SAS file names: LibraryName.Tablename.   The library name, or libref, identifies where the SPD Server can find or store the file.

The documentation chapter on Accessing and Creating SAS Scalable Performance Data Server Tables contains several SQL pass-through examples that use librefs. The following example is a libref used with LIBNAME access to an SPD Server.

Example: A LIBREF Used with LIBNAME Access

The statement below creates the table TRAVEL and stores it in a permanent SAS data library with the libref ANNUAL. 

   data annual.travel;

Below is a LIBNAME statement that associates a libref, the SASSPDS engine, and an SPD Server domain. 

libname mydatalib sasspds 'mydomain'
   host='namesvrID'
   serv='5555'
   user='neraksr'
   passwd='siuya';
LIBNAME libref SASSPDS <'SAS-data-library'> <SPD Server-options>;

Use the following arguments:

libref
a name that is up to eight characters long and that conforms to the rules for SAS names.
SASSPDS
the name of the SPD Server engine.
'SAS-data-library'
the logical LIBNAME domain name for an SPD Server data library on the host machine. The name server resolves the domain name into the physical path for the library.
SPD Server-options
one or more SPD Server options.

LIBNAME Options

You must supply the SASSPDS engine name to access SPD Server LIBNAME domains with a LIBNAME statement. You must also specify one or more SPD Server options. The syntax for an SPD Server option is 

 <SPD Server-option>=<value>;
SPD Server-option
a keyword to name the option.
 
value
a value expected by the keyword.

Option values in a LIBNAME statement enable the engine to initiate, manage, and tailor a client session. This section summarizes LIBNAME options and groups them by function.

Connect to a Specified SPD Server Host

To connect to a host, SPD Server needs the network node name for the SPD Server host machine or the IP address of the server machine, and the port number of a name server. SPD Server provides the following options to locate a name server using a named service. 

SERVER=
specifies a node name for an SPD Server host machine and a port number for the name server running on the machine.
 
HOST=
specifies a node for an SPD Server host machine and a port number for the name server running on the machine.

Both options have the same function. SERVER= arguments are compatible with SAS/SHARE software.  HOST= arguments support FTP conventions. The HOST option allows a node to be an IP address (for example, 123.456.76.1); the SERVER option requires a network node name.

SPDSHOST= Macro Variable

If you create a SAS macro variable named SPDSHOST= or an environment variable named SPDSHOST=, whenever a LIBNAME statement does not specify an SPD Server host machine, SPD Server will look for the value of SPDSHOST= to identify the server. 

   %let spdshost=samson;
   libname myref sasspds 'mylib'
     user='yourid'
     password='swami';

The first statement assigns the SPD Server host SAMSON to the macro variable SPDSHOST. Therefore, a subsequent LIBNAME statement does not need to name the host server again.

Validate the Client User ID

SPD Server uses the name server to secure its domains. SPD Server uses ACL file security to secures domain resources. If ACL file security is enabled, the software grants access in the following hierarchy:

You can use SQL pass-through and LIBNAME options to specify the identify of an SPD Server user. SPD Server uses a special ID table to validate user IDs and passwords. The following LIBNAME options identify a client:

ACLGRP=
specifies one of up to five ACL groups that the user may belong to.
 
ACLSPECIAL=
grants special privileges to an SPD Server user who is previously set up as special (ACLSPECIAL=YES is defined for the user in the password file.) Special privileges override other ACL restrictions that apply to resources in the domain.
 
CHNGPASS=
prompts a client user to change his or her SPD Server password.
 
NEWPASSWORD= or NEWPASSWD=
specifies a new password for an SPD Server client user.
 
PASSWORD= or PASSWD=
specifies a password to validate an SPD Server client user.
 
PROMPT=
prompts for a password to validate an SPD Server client user.
 
PASSTHRU=
specifies implicit SQL pass-through options for an SPD Server client user.
 
USER=
specifies the SPD Server user ID.

                                  User ID Options When ACL File Security Is Enabled

User= Password= or Prompt= Grants Access To . . .
Required unless the SAS client process has a User ID, that is, not a Windows client. Submitted values for User= are validated against the SPD Server User ID Table. Required and validated against the SPD Server User ID Table.  Resources that you create within the SPD Server LIBNAME domain and in other resources that are not excluded by ACLs or by UNIX file permissions.

 

                               User ID Options When UNIX File Security Only Is Enabled

User= Password= or Prompt= Grants Access To . . .
Not required. The SPD Server User ID under UNIX file security only is "anonymous". Not required with "anonymous" User ID.  All resources within the LIBNAME domain granted by UNIX permissions for the SPD Server's UNIX ID.


Manage Server Network Traffic

If your SPD Server installation uses the same physical machine to run your SPD Server client process and your SPD Server host services, you can use the two following SPD Server options to improve client / server network traffic:

NETCOMP=
compresses the data stream in an SPD Server network packet.
 
UNIXDOMAIN=
uses UNIX domain sockets for data transfer between the client and the SPD Server.

Additional LIBNAME Options

BYSORT=
performs an implicit sort when a BY clause is encountered.
 
DISCONNECT=
specifies when to close network connections between the SAS client and the SPD Server. This may be after all librefs are cleared or at the end of a SAS session.
 
ENDOBS=
specifies the end row (observation) in a user-defined range.
 
 
 
STARTOBS=
specifies the start row (observation) in a user-defined range.

TRUNCWARN=

Suppresses hard failure on NLS transcoding overflow and character mapping errors. When using the TRUNCWARN=YES LIBNAME option, data integrity may be compromised because significant characters can be lost in this configuration.  The default setting is NO, which causes hard read/write stops when transcode overflow or mapping errors are encountered.  When TRUNCWARN=YES, and an overflow or character mapping error occurs, a warning is posted to the SAS log at data set close time if overflow occurs, but the data overflow is lost.

LIBNAME Example Statements

Example 1

Example 1 creates the libref MINE, associates it with the SASSPDS engine, and specifies the SPD Server LIBNAME domain GOLDMINE. Values for the SPD Server options specify to 

libname mine sasspds 'goldmine'
  user='explorer'
  host='fastcpus'
  prompt=yes
  chngpass=yes;

Example 2

Example 2 represents the first LIBNAME statement that was made for the SPDSDATA domain. It creates the libref MYLIB, associates MYLIB with the SASSPDS engine, and specifies the SPD Server libname domain SPDSDATA. Values for the SPD Server options specify to 

libname mylib sasspds 'spdsdata'
  server=hefty.spdsname
  user='camills' password='escort'
  datapath=('/disk1/maindata'
            '/disk2/moredata'
            '/disk3/moredata');

SPD Server Table Options

SPD Server table options specify processing actions that apply only to a specific table.  When you use a LIBNAME statement, you should specify the options in parentheses next to the table name.  If you use an SQL pass-through statement, use brackets to specify the options next to the table name.

Options to Enhance Performance

BYNOEQUALS=
specifies the index output order of table rows with identical values for the BY column.
 
NETPACKSIZE=
controls the size of an SPD Server network data packet.
 
SEGSIZE=
sizes the segment for index files associated with an SPD Server table.

Options for Other Functions

BYSORT=
performs an implicit sort of a given table when a BY clause is encountered and there is no index available.
 
ENDOBS=
specifies the end row (observation) number in a user-defined range.
 
STARTOBS=
specifies the start row (observation) number in a user-defined range.
 
 
SORTSIZE=
specifies the amount of memory (in number of bytes, not Kbytes or Mbytes) that SPD Server is able to allocate in order to complete a sorting request. The SORTSIZE= table option declared must be less than the global sortsize parameter specified in the spdsserv.parm server parameter file.
 
VERBOSE=
details all indexes associated with an SPD Server table. This option also provides other information, such as who is the table owner and the ACL group.

SPD Server Macro Variables

You can use global macro variables in SPD Server to simplify your work. Global macro variables use default values set by the SPD Server software and operate in the background. You can make global changes to the values of macro variables in your code by specifying a new the default setting for the specified variable. The new default setting is applied to all macro variables in the code that you submit to SPD Server.  You can also override the setting for a single macro variable by using a table option to change the setting for only the specified table.

The default macro variable values automate sophisticated processing decisions. The default settings furnish good performance. However, top performance often requires intelligent changes to some macro variable default settings. When you make changes to the macro variable default settings, you should attempt to find the best processing opportunity for the type of data that you have.

Learning the best way to set SPD Server macro variables and options takes time. Sometimes, performance testing is the only way to determine if changing a setting improves processing performance. Performance testing is time well spent. After you quantify performance parameters under various macro variable settings, you can customize SPD Server so that it solves your real business or data problems with maximum efficiency.

Each SPD Server installation is different. You may want to change many values, or just a few default values. When you make changes, you will find macro variables are friendly, flexible and easily to manipulate.

Use a %LET statement to change macro variable values.  You can place the macro variable assignment anywhere in the open code of a SAS program except data lines. The most convenient place to put your %LET statements to initialize macro variables is in your  autoexec.sas file or at the beginning of a program. The macro variable assignment is valid for the duration of your session or the executing program. Macro variable values remain in effect until they are changed by a subsequent assignment.

Assignments for macro variables with YES|NO arguments must be entered in uppercase (capitalized).

Because the SPD Server macro variables operate behind the scenes, you cannot query SPD Server to find out the status of a macro variable.  SAS does not 'know' about the status of macro variables. If you want to see which SPD Server macro variables are in effect, or their default values, you can use PROC SPDO.

Macro Variables and Corresponding Table Options

When you need to apply the action to a single table that a macro variable applies globally to all tables, you should use a table option instead of the macro variable setting.  A table option is more selective because you can turn the macro variable function on or off for a single table.

Summary of SPD Server Macro Variables

This section summarizes the SPD Server macro variables and groups them by the function of their default value. 

Variable for a Client and Server Running on the Same UNIX Machine

SPDSCOMP=
specifies to compress the data when sending a data packet through the network.

Variable for Compatibility with the Base SAS Engine

SPDSBNEQ=
specifies the output order of table rows with identical values in the BY column.

Variables for Miscellaneous Functions

SPDSEOBS=
specifies, when processing a table, the end row (observation) number in a user-defined range.
 
SPDSSOBS=
specifies, when processing a table, the start row (observation) number in a user-defined range.
 
SPDSUSAV=
specifies, when appending to tables with unique indexes, to save rows with non-unique (rejected) keys to a separate SAS table.
 
SPDSUSDS=
returns the name of a hidden SAS table generated by the SPD Server which stores rows with identical (non-unique) table values.
 
SPDSVERB=
specifies when executing a PROC CONTENTS statement to provide more details that are specific to SPD Server indexes that are associated with the table.  Examples of information include ACL information, index information, PARTSIZE= value, and others.
 
SPDSFSAV=
specifies to retain the table if an abnormal condition is encountered during a table-creation operation. (Normally SAS closes and deletes these tables.)
 
SPDSEINT=
specifies disconnect behavior for the SQL pass-through EXECUTE() statement.

Variables for Sorts

SPDSBSRT=
specifies for the SPD Server to perform a sort whenever it encounters a BY clause, and there is no index available.
 
SPDSNBIX=
specifies whether to turn BY-sorts with an index on or off.
 
SPDSSTAG=
specifies whether to use non-tagged or tagged sorting for PROC SORT or BY processing.

Variables for WHERE Clause Evaluations

SPDSTCNT=
specifies the number of threads to be used for WHERE clause evaluations.
 
SPDSEV1T=
specifies whether the data returned from WHERE clause evaluations that utilize an index should be in strict row (observation) order.
 
SPDSEV2T=
specifies whether the data returned from WHERE clause evaluations that do not utilize an index should be in strict row (observation) order.
 
SPDSWDEB=
specifies when evaluating a WHERE expression, whether WHINIT, the WHERE clause planner, should display a summary of the execution plan.
 
SPDSIRAT=
controls, when WHERE clause processing with enhanced bitmap indexes, whether to perform segment candidate pre-evaluation.

Variables That Affect Disk Space

SPDSCMPF=
specifies to add a number of bytes to a compressed block as growth space.
 
SPDSDCMP=
specifies to compress SPD Server tables on the disk.
 
SPDSIASY=
specifies, when creating multiple indexes on an SPD Server table, whether to create the indexes in parallel.
 
SPDSSIZE=
specifies the size of an SPD Server table partition.

Variables to Enhance Performance

SPDSNETP=
sizes a buffer in server memory for the network data packet.
 
SPDSSADD=
specifies whether to apply a single row, or multiple rows at a time, when appending to a table.
 
SPDSSYRD=
specifies whether to perform data streaming when reading a table.
 
SPDSAUNQ=
specifies whether to abort an append if uniqueness is not maintained.