SQL Programming Considerations

The REMOTE engine supports the SQL procedure pass-through facility, which allows you to pass SQL statements to a SQL server or a DBMS through a SAS/SHARE or a SAS/CONNECT server.

You can use RSPT to reduce network traffic and to shift CPU load by sending requests for data to a server.

For example, consider the following statement:

select emptitle as title, avg(empyears), freq(empnum)
        from sql.employee
        group by title
        order by title;

SQL is the libref for a library that is accessed through a SAS/SHARE or a SAS/CONNECT server. Each row in the table EMPLOYEE must be returned to your client in order for the summary functions AVG() and FREQ() to be applied to them. However, you might specify the statement as follows:

select * from connection to remote
        (select emptitle as title, 
             avg(empyears), freq(empnum)
             from sql.employee
             group by title
             order by title);

In this case, the query is passed through the SAS/SHARE server to the SAS SQL processor, which processes each row in the table EMPLOYEE and returns only the summary rows to your client.

You can also use RSPT to join server data with client data. For example, you might specify the statement as follows:

libname mylib 'c:\sales';

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

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

In this case, the subquery against the DB2 data is sent through the SAS/SHARE server to the DB2 server. The rows for the divisions in the Southeast region are returned to your SAS/SHARE client, where they are joined with the corresponding rows from the client data set MYLIB.SALES97.

If your server is a SAS/CONNECT server, you can also use RSPT to send non-query SQL statements to a DBMS. For example, the following statements send the DELETE statement in PROC SQL through the SAS/SHARE server to the Oracle server.

proc sql;
   connect to remote (server=sunserv dbms=oracle);

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