Syntax for Remote SQL Pass-Through (RSPT)

RSPT Statements

Statements used for Remote SQL Pass-Through.

Valid In:

client session

Syntax
Syntax Description
Compute Services and RSPT

Syntax

CONNECT TO dbms-name <AS alias> <(dbms-argument-1=value ... <dbms-argument-n=value>)>;

SELECT . . . FROM CONNECTION TO dbms-name | alias (dbms-query);

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

DISCONNECT FROM dbms-name | alias;

CONNECT TO REMOTE <AS alias>

(SERVER=serverid <SAPW=server-access-password>

<DBMS=dbms-name>
<PT2DBPW=passthrough-to-DBMS-password>

<DBMSARG=(dbms-argument-1=value ... <dbms-argument-n=value>)>);

SELECT . . . FROM CONNECTION TO REMOTE | alias (dbms-query);

EXECUTE (SQL-statement) BY REMOTE | alias;

DISCONNECT FROM REMOTE | alias;

The REMOTE engine supports the SQL procedure's Pass-Through Facility. Remote SQL Pass-Through (RSPT) enables you to pass SQL statements to a remote SAS SQL processor or to a DBMS through a SAS/SHARE server or to a SAS/CONNECT single-user server.

The SQL syntax for the SQL procedure Pass-Through (SPT) facility consists of three statements and a FROM-clause component.
The SQL syntax for the Remote SQL Pass-Through (RSPT) facility is similar to that for the SPT, but must also include the server ID.

CONNECT TO REMOTE <AS alias>

connects to a remote DBMS or to remote SAS data through a SAS server. This statement is required (RSPT does not support implicit connection). You can establish multiple connections to the same server by specifying different DBMS= values. You can also connect to more than one server at a time.

Note: The term server refers to the SAS/CONNECT single-user server and the SAS/SHARE multi-user server. [cautionend]

SERVER=server-ID

identifies the name of the SAS server. If the SAS/SHARE multi-user server is used, server-ID is the name specified for the ID= option in the PROC SERVER statement. If the SAS/CONNECT single-user server is used, server-ID specifies the server session. In either case, server-ID should be the same name that is specified in the SERVER= option in a LIBNAME statement.

SAPW=server-access-password

specifies the password for controlling user access to a multi-user server as specified in the UAPW= option in the PROC SERVER statement. If UAPW= is specified when the server is started, you must specify SAPW= in a CONNECT TO REMOTE statement that specifies that server.

DBMS=dbms-name

identifies the remote DBMS to connect to. This is the same name that you would specify in a CONNECT TO statement if you were connecting directly to the DBMS. This option is used if you want to connect to a remote DBMS instead of the remote SAS SQL processor.

PT2DBPW=passthrough-to-DBMS-password

specifies the password for controlling pass-through access to remote DBMS databases that are specified by using the PT2DBPW= option in the PROC SERVER statement. If PT2DBPW= is specified when the server is started, you must specify PT2DBPW= in a CONNECT TO REMOTE statement that specifies the same server and specifies DBMS=.

DBMSARG=(dbms-argument-1=value ... <dbms-argument-n=value>)

specifies the arguments that are required by the remote DBMS to establish the connection. These are the same arguments that you would specify in a CONNECT TO statement if you were connecting directly to the DBMS.

FROM CONNECTION TO REMOTE | alias (dbms-query);

specifies the connection to the remote SAS SQL processor or the remote DBMS as the source of data for the SELECT statement and the recipient of the dbms-query. For remote SAS data that is accessed through the PROC SQL view engine, dbms-query is any valid SELECT statement in PROC SQL. For a remote DBMS, dbms-query is the same SQL query that you would specify if you were connected directly to the DBMS.

EXECUTE (SQL-statement) BY REMOTE | alias;

specifies an SQL statement to be executed by the SAS SQL processor or by the remote DBMS in the server session. For remote SAS data that is accessed through the PROC SQL view engine, SQL-statement is any valid PROC SQL statement except SELECT. For a remote DBMS that is accessed through a single-user server in a SAS/CONNECT session, SQL-statement is the same SQL statement that you would specify if you were connected directly to the DBMS. For a remote DBMS, this statement might not be used if the DBMS is accessed through a remote multi-user server.

DISCONNECT FROM REMOTE | alias;

ends the connection to the remote DBMS or to the SAS SQL processor in the server session.

Details

Compute Services and RSPT

You can use RSPT to reduce network traffic and to shift CPU load by sending queries for remote data to a server session. (If the server is a SAS/CONNECT single-user server you can also RSUBMIT queries to achieve the same goals.)

For example, this code contains the libref SQL that points to a server library that is accessed through a SAS/CONNECT or a SAS/SHARE server. Each row in the table EMPLOYEE must be returned to the client session in order for the summary functions AVG() and FREQ() to be applied to them.

select employee_title as title, avg(employee_years), 
   freq(employee_id)
      from sql.employee
      group by title
      order by title;

However, this code contains a query that is passed through the SAS server to the SAS SQL processor, which processes each row of the table and returns only the summary rows to the client session.

select * from connection to remote
   (select employee_title as title, 
    avg(employee_years), 
    freq(employee_id)
       from sql.employee
       group by title
       order by title);

You can also use RSPT to join server data with client data. For example, you can specify a subquery against the DB2 data that is sent through the SAS server to the DB2 server. The rows for the divisions in the southeast region are returned to your client session, where they are joined with the corresponding rows from the local data set MYLIB.SALES08.

libname mylib 'c:\sales';

proc sql;
   connect to remote 
      (server=tso.shr1 dbms=db2 
       dbmsarg=(ssid=db2p));

   select * from mylib.sales08, 
      connection to remote 
         (select qtr, division, 
                 sales, pct
            from revenue.all08
            where region='Southeast')
      where sales08.div=division;

If your server is a SAS/CONNECT single-user server, you can also use RSPT to send non-query SQL statements to a remote DBMS. For example, this code sends the SQL DELETE statement through the SAS server to the remote Oracle server.

proc sql; 
   connect to remote 
      (server=sunserv dbms=oracle dbmsarg=(user=scott password=tiger);

   execute (delete from parts.inventory 
      where part_bin_number='093A6')
      by remote;

Top of Page

RSPT Statements

Syntax

Syntax Description

Details