Accessing and Manipulating Data with the SQL Pass-Through Facility

Overview of the SQL Pass-Through Facility

Accessing Data Using the SQL Pass-Through Facility

SQL Pass-Through Statements

CONNECT Statement

DISCONNECT Statement

EXECUTE Statement

CONNECTION TO Statement

Examples of Using the SQL Pass-Through Facility

Using PROC SQL to Connect to a SQL Server

Nesting SQL Pass-Through Access

Overview of the SQL Pass-Through Facility

SPD Server uses SQL pass-through commands to access and manipulate data. The SQL pass-through facility provides SPD Server clients with an alternative way to establish a connection with an SPD Server host or to directly load from an external database such as Oracle. Users have access in the SPD Server environment and increased connectivity to external databases using the SPD Server engine.

For reference information about SQL syntax in SPD Server, see SAS Scalable Performance Data (SPD) Server SQL Syntax Reference Guide.

Accessing Data Using the SQL Pass-Through Facility

The SQL pass-through facility is an access method that allows SPD Server to connect to an SQL server and manipulate data. To use SQL pass-through, do the following tasks:

Establish a connection from an SPD Server client using a CONNECT statement.
Send SPD Server SQL statements using the EXECUTE statement.
Retrieve data with the CONNECTION TO component in a SELECT statement's FROM clause.
Terminate the connection using the DISCONNECT statement.

For examples of how to do these tasks, see Examples of Using the SQL Pass-Through Facility.

SQL Pass-Through Statements

CONNECT Statement

The CONNECT statement specifies the SAS I/O engine that provides SQL pass-through access.

Syntax

CONNECT TO dbms-name<AS alias>(dbms-args);

Arguments:

dbms-name (required)

specifies the name of the engine.

When you are running SAS and PROC SQL, you must specify sasspds to obtain SQL pass-through to an SPD Server SQL server. You must specify spdseng to obtain SQL pass-through from an SPD Server SQL server.

Note: spdseng is the database that you use to reference an SPD Server from within an existing SPD Server SQL connection.

AS alias (optional)

specifies an alias or logical name for a connection. When you specify an alias to identify the connection, use a string that is not enclosed in quotation marks. Refer to this logical name in subsequent SQL pass-through statements.

Note: For the alias, you must specify the connection that executes the statement.

The following two examples show how to use an alias:

execute(...) by alias

select * from connection to alias(...)

dbms-args (required and optional arguments)

identifies the SQL server and the data source. The following dbms-args arguments are for the SPD Server engines, sasspds and spdseng. SPD Server SQL uses the syntax keyword=value.

DBQ=libname-domain (required)

specifies the primary SPD Server LIBNAME domain for the SQL pass-through connection. The name that you specify must be identical to the LIBNAME domain name that you used when you assigned a SAS LIBNAME to sasspds. Enclose the value in single or double quotation marks.

HOST=name-server-host (optional)

specifies a node name or an IP address for a name server that is currently running. Enclose the string in single or double quotation marks. If you do not specify a value, SPD Server uses the current value of the SAS macro variable spdshost to determine the node name.

SERVICE=name-server-port (optional)

SERV=name-server-port (optional)

specifies the network address (port number) for a name server that is currently running. Enclose the value in single or double quotation marks. If you do not specify a port number for the name server, SPD Server determines the network address from the named service spdsname in the/etc/services file.

USER=SPD Server user ID (required on Windows, but not on UNIX)

specifies an SPD Server user ID to access an SPD Server SQL server. Enclose the value in single or double quotation marks.

Note: On UNIX, it is not necessary to specify USER= in a CONNECT statement because SPD Server assumes the UNIX user ID.

PASSWORD=password (required, or use PROMPT=YES unless USER='anonymous')

PASSWD=password (required, or use PROMPT=YES unless USER='anonymous')

specifies an SPD Server user ID password to access an SPD Server. This value is case sensitive. You should not specify a password in a text file that another user can view. You should use this argument in a batch job that is protected by file- system permissions, which prohibits other users from reading the text file.

PROMPT=YES (required, or use PASSWD= or PASSWORD= unless USER='anonymous')

specifies a password prompt to access an SPD Server SQL server. This value is case sensitive.

DISCONNECT Statement

The DISCONNECT statement disconnects you from your database management system (DBMS) source. When you no longer need the PROC SQL connection, you must disconnect from the DBMS source. You are automatically disconnected when you exit PROC SQL. However, you can explicitly disconnect from the DBMS source by using the DISCONNECT statement.

Syntax

DISCONNECT FROM [dbms-name | alias];

Arguments

dbms-name

the name specified in the CONNECT statement that established the connection.

alias

the alias value specified in the CONNECT statement that established the connection.

EXECUTE Statement

The EXECUTE statement is part of the SQL pass-through facility. Use this statement to use specific SQL statements that do not return a results set during a pass-through connection. Before you use the EXECUTE statement, you must establish a connection by using the CONNECT statement. After you create a pass-through connection, use the EXECUTE statement to submit valid SQL statements (you cannot submit the SELECT statement).

Syntax

EXECUTE (SQL-statement) BY [dbms-name | alias];

Arguments

(SQL-statement)

a valid SQL statement that is passed for execution (you cannot specify the SELECT statement because it attempts to return query results). This argument is required and must be enclosed within parentheses.

dbms-name (required, or use alias)

identifies the DBMS to which you want to direct the SQL statement. The dbms-name value must be preceded by the keyword BY. You must specify either the dbms-name, or the alias in your CONNECT statement.

alias (required if you did not provide dbms-name)

specifies an alias that is used in the CONNECT statement. If you do not specify the dbms-name, in your CONNECT statement, then you must specify the alias.

CONNECTION TO Statement

CONNECTION TO is an SQL pass-through component that you can use in the FROM clause of a SELECT statement as part of the from list. The CONNECTION TO component enables you to make pass-through queries for data and to use that data in a PROC SQL query or table. PROC SQL treats the results of the query like a virtual table.

Syntax

CONNECTION TO [dbms-name | alias](SQL-query)

;

Arguments

dbms-name (required)

If you have a single connection, dbms-name is the same dbms-name value that you specified in your CONNECT statement. If you have multiple connections, use the alias that you specified in the AS clause of the CONNECT statement. If you do not specify dbms-name in your CONNNECTION TO statement, you must specify the alias that was established in the CONNECT statement.

(SQL-query)

specifies the SQL query that you want to send. Your SQL query cannot contain a semicolon because a semicolon represents the end of a statement to SPD Server. Character literals are limited to 32,000 characters. Make sure that your SQL query is enclosed in parentheses.

alias (required if you did not provide dbms-name)

specifies the alias that was used in the CONNECT statement. If you do not specify the dbms-name value, then you must specify the alias value.

alias (optional)

specifies the optional alias that you used in the CONNECT statement.

Examples of Using the SQL Pass-Through Facility

Using PROC SQL to Connect to a SQL Server

In this example, we issue a CONNECT statement to connect from a SAS session to an SPD Server SQL server. After the connection is made, the first EXECUTE statement creates a table named EMPLOYEE_INFO with three columns:EMPLOYEE_NO, EMPLOYEE_NAME, and ANNUAL_SALARY. The second EXECUTE statement inserts an observation into the table where EMPLOYEE_NO equals 1, EMPLOYEE_NAME equals The Prez, and ANNUAL_SALARY equals 10,000.

The subsequent FROM CONNECTION TO statement retrieves all of the records from the new EMPLOYEE_INFO table. (In this example, it retrieves a single observation, which was inserted by the second EXECUTE statement.) The DISCONNECT statement terminates the connection.

    PROC SQL;
    connect to sasspds
     (dbq='mydomain'
      host='workstation1'
      serv='spdsname'
      user='me'
      passwd='noway');

    execute (create table employee_info
      (employee_no num, employee_name char(30),
      annual_salary num)) by sasspds;

    execute (insert into employee_info
      values (1, 'The Prez', 10000)) by sasspds;

    select * from connection to sasspds
      (select * from employee_info);
    disconnect from sasspds;
    quit;

Nesting SQL Pass-Through Access

You can nest SPD Server pass-through access. Nesting allows access to data that is stored on two different networks or network nodes. You can use the spdseng database to reserve an SPD Server from within an existing SPD Server SQL connection.

In the following example, on the DATAGATE host on a local network, SQL pass-through is nested to access the EMPLOYEE_INFO table. This table is available on the PROD host on a remote network. (You must have user access to the PROD host.)

    proc sql;
    connect to sasspds (dbq='domain1'
                        host='datagate' serv='spdsname'
                        user='usr1' passwd='usr1_pw');
    execute (connect to spdseng (dbq='domain2'
                        host='prod' serv='spdsname'
                        user='usr2' passwd='usr2_pw')) by sasspds;
    select * from connection to sasspds(
                select * from connection to spdseng(
                    select employee_no, annual_salary
                    from employee_info));
    execute (disconnect from spdseng) by sasspds;
    disconnect from sasspds;
    quit;

Note: If you would prefer not to use the spdseng database to reference a server, you can use the LIBGEN=YES option. Libraries with the LIBGEN=YES option are automatically available in SQL environments. For more information about the LIBGEN=YES option, see LIBGEN=