Connecting to the SPD Server SQL Engine

Implicit Pass-Through Connection

Explicit Pass-Through Connection

LIBNAME Syntax to Specify a Libref

Libref for Another Domain but the Same CONNECT Statement User

Libref to Same Domain but Different CONNECT Statement User

Secondary Libref Using a Different Host

Implicit Pass-Through Connection

You can use an implicit pass-through connection to pass implicit SQL statements to the SPD Server SQL Engine. When you use an implicit pass-through connection, the SAS SQL planner parses SQL statements to determine which, if any, portions can be passed to the SPD Server SQL Engine. In order for a submitted SQL statement to take advantage of implicit pass-through SQL, the tables that are referenced in the SQL statement must be SPD Server tables, and the SPD Server SQL engine must be able to successfully parse the submitted SQL statement.

An example of an SPD Server implicit pass-through connection is available in the Help section in this document on how to Specify SQL Options Using Implicit Pass-Through Code.

Explicit Pass-Through Connection

You can use an explicit pass-through connection to pass explicit SQL statements to the SPD Server SQL Engine. When you use an explicit pass-through connection, you decide explicitly which SQL statements are passed to the SPD Server SQL Engine. The explicit pass-through connection passes the entire SQL statement as written to the SPD Server SQL Engine, which parses and plans the SQL statement. All tables that are referenced in the SQL statement must be SPD Server tables or an error occurs.

An example of an SPD Server implicit pass-through connection is available in Specify SQL Options Using Explicit Pass-Through Code.

LIBNAME Syntax to Specify a Libref

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

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.

Libref Statements

Whenever you issue a CONNECT statement to an SPD Server SQL server with the DBQ option, by default you define a primary LIBNAME domain. The software uses the primary domain to resolve table references in SQL statements executed for that connection.

You can also use the libref statement to assign secondary LIBNAME domains for the SPD Server SQL Server. The additional libref statements assign explicit LIBNAME domains, allowing the software to specify two-part table names for SQL statements executed for the connection.

PROC SQL;
 execute(libref librefname
 <enginename>
  engopt= ' ')
by sasspds;

Libref Clauses

The ENGNAME Clause

Specifies the name of an alternate SAS I/O engine to service the libref's access to data. If you do not specify an alternate SAS I/O engine, the default is spdseng, which accesses SPD Server tables.

The ENGOPT Clause

Specifies options that configure the libref to access a specific data source or storage domain. Use single or double quotes around the clause. (If you have nested quotes within a clause, alternate between single- and double-quoted expressions.) The available options depend on the current value of the ENGNAME option. For the default spdseng, you can specify any SPD Server CONNECT or LIBNAME engine option with the exception of prompt, newpasswd, and chngpass. Use the same keyword or value syntax required by the CONNECT statement.

Note: If you specify the SAS I/O engine spdseng and use explicit options in your CONNECT statement, these options become default ENGOPT clause options. Explicit options can also be specified using the ENGOPT clause. Explicit options specified in an ENGOPT clause override default values or declarations made in previous CONNECT statements.

Libref Examples

Libref for Another Domain but the Same CONNECT Statement User

In this example the client connects to the SPD Server SQL server using the engine sasspds. The domain is mydomain, the server machine is called namesvrID, and the port number is namesvrPortNum. The execute statement assigns the libref cookie to another domain, dough. After the libref is executed, the user issuing the connect statement can now access either the default domain mydomain or the secondary domain dough.

  PROC SQL;
  connect to sasspds
   (dbq='mydomain'
    host='namesvrID'
    serv='namesvrPortNum'
    user='neraksr'
    passwd='siuya');
  execute(libref cookie
   engopt='
   dbq="dough"')
  by sasspds;

In the example above, the libref is cookie, and the secondary domain named is dough. The intent of the example is to show how the CONNECT and libref statements work in conjunction to access multiple domains for the same user.

Libref to Same Domain but Different CONNECT Statement User

This example assigns a libref to the domain specified by the CONNECT statement but for another user (different SPD Server User ID).

  PROC SQL;
  execute(libref samslib
   engopt='
   user="sam"
   passwd="samspwd"')
  by sasspds;

Secondary Libref Using a Different Host

This example assigns a secondary libref to a different host machine.

  PROC SQL;
  execute(libref sam2
   engopt='
   host="flex"
   dbq="samsplace"')
  by sasspds;