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

Overview of 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 enables 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 filenames: LibraryName.Tablename. The library name or libref identifies where the SPD Server can find or store the file.

Accessing and Creating SAS Scalable Performance Data (SPD) Server Tableshas several examples that use librefs.

Example: A Libref Used with LIBNAME Access

The following statement creates the table TRAVEL and stores it in a permanent SAS library with the libref ANNUAL:

data annual.travel;

The following 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 specify one or more SPD Server options. Here is the syntax for an SPD Server option:

<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 customize a client session.

Connect to a Specified SPD Server Host

Overview of Connecting 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. 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 looks for the value of SPDSHOST= to identify the host 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 SPD Server grants access in the following hierarchy:

uses the permissions that belong to the UNIX ID that is associated with the SPD Server.
uses the permissions that belong to the SPD Server user ID.

You can use SQL pass-through and LIBNAME options to specify the identity 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 to five ACL groups that the user can 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=	Access
Required unless the SAS client process has a user ID. In other words, it 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 Only UNIX File Security Is Enabled

USER=	PASSWORD= or PROMPT=	Access
Not required. The SPD Server user ID under only UNIX file security 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 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. Closure can 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.

NOSASSORT=

ignores an explicit PROC SORT statement.

STARTOBS=

specifies the start row (observation) in a user-defined range.

TRUNCWARN=

suppresses hard failure on NLS transcode overflow and character mapping errors. When using the TRUNCWARN=YES LIBNAME option, data integrity can be compromised because significant characters can be lost in this configuration. The default setting is NO, which causes hard Read and Write stops when transcode overflow or character mapping errors occur. When TRUNCWARN=YES, and a transcode overflow or character mapping error occurs, a warning is posted to the SAS log when the data set is closed 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 the following:

locate the server machine FASTCPUS and use the default service SPDSNAME to get the port number of the Name Server
validate the SPD Server user EXPLORER
prompt for EXPLORER's old SPD Server password
change the password

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 the following:

locate the server machine HEFTY and use the named service SPDSNAME to get the port number of the Name Server
validate the SPD Server user ID camills and account password escort
store data file partitions in the directories MAINDATA on device DISK1, MOREDATA on device DISK2, and MOREDATA on device DISK3. This example implies that the metadata and index partitions for tables are stored in the primary file system, which is the path set up by the SPD Server administrator for SPDSDATA.

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