Example 12. DTS: Combining Data from Multiple Server Sessions

Purpose

Using SAS/CONNECT to connect to multiple servers, you can access data on several servers, combine that data on the client, and analyze the combined data. For example, if you have data that is stored under z/OS in a DB2 database and related data that is stored in an Oracle database under UNIX, you can use SAS/CONNECT in combination with SAS/ACCESS to combine that data on your client. This example uses salary and employee data gathered from two servers to illustrate the process.

Program

This example signs on to two servers, downloads data from both servers, and performs analyses of the data on the client. The program uses the SIGNON and RSUBMIT statements.

Note: Bullets

through

apply to downloading both DB2 and Oracle data.

      /*************************************/
      /* connect to z/OS                   */
      /*************************************/
1 options comamid=tcp;
   filename rlink
      '!sasext0\connect\saslink\tcptso.scr';
   signon zoshost;
      /*************************************/
      /* download DB2 data views using     */
      /* SAS/ACCESS engine                 */
      /*************************************/
2 rsubmit zoshost;
3 libname db db2;
4 proc download data=db.employee
      out=db2dat;
   run;
5 endrsubmit;

      /*************************************/
      /* connect to UNIX                   */
      /*************************************/
6 options
      remote=hrunix comamid=tcp;
         filename rlink
            '!sasext0\connect\saslink\tcpunix.scr';
         signon;

      /*************************************/
      /* download Oracle data using        */
      /* SAS/ACCESS engine                 */
      /*************************************/
2 rsubmit hrunix;
3    libname oracle user=scott password=tiger;
4 proc download
      data=oracle.employee out=oracdat;
   run;
5 endrsubmit;

      /*************************************/
      /* sign off both links               */
      /*************************************/
7 signoff hrunix;
   signoff zoshost cscript=
      '!sasext0\connect\saslink\tcptso.scr';

      /*************************************/
      /* join data into SAS view           */
      /*************************************/
8 proc sql;
   create view joindat as
      select * from db2dat, oracdat
      where oracdat.emp=db2dat.emp;

      /*************************************/
      /* create summary table              */
      /*************************************/
9 proc tabulate data=joindat
      format=dollar14.2;
      class workdept sex;
      var salary;
      table workdept*(mean sum) all,
      salary*sex;
      title1 'Worldwide Inc. Salary Analysis
              by Departments';
      title2 'Data Extracted from Corporate
              DB2 Database';
   run;

/* display graphics */
10 proc gchart data=joindat;
      vbar workdept/type=sum
         sumvar=salary
         subgroup=sex
         ascending
         autoref
         width=6
         ctext=cyan;
      pattern1 v=s c=cyan;
      pattern2 v=s c=magenta;
      format salary dollar14.;
      title1 h=5.5pct f=duplex
          c=white
         'Worldwide Inc. Salary Analysis';
      title2 h=4.75pct f=duplex
         c=white
         'Data Extracted from Corporate DB2
          Database';
   run;
   quit;

1 To sign on to a server, you need to provide several items of information:

the server-ID, which is specified in a REMOTE= system option or as an option in the SIGNON statement.
the communications access method, which is specified by using the COMAMID= system option in an OPTIONS statement.
the script file to use when signing on to the server. This script file is usually associated with the fileref RLINK. Using this fileref is the easiest method for accessing the script file.

After you provide all the necessary information, you can submit the SIGNON statement. You can specify the server-ID in the SIGNON statement. If you omit the server-ID from the RSUBMIT statement, the statements are submitted to the server session that was identified most recently in a SIGNON statement, in an RSUBMIT statement or command, or in a REMOTE= system option.

2 After you connect to two or more sessions, you can remotely submit statements to any of the servers by simply identifying in the RSUBMIT statement which server should process the statements. After the server-ID has been specified by a previous statement or option, you are not required to specify it again in the REMOTE statement. However, this example includes the server-ID in the RSUBMIT statements, even though the server-ID is not required, to clarify which server is processing each group of statements.

3 Associate a libref with the library that contains the DB2 database on the server.

4 The data from the DB2 database can then be downloaded to the client. Note that when you download a view of a database, a temporary SAS data set is materialized from the view and downloaded to the client. In this example, the output data set on the client is a temporary SAS data set.

5 The ENDRSUBMIT statement ends the block of statements that are submitted to the server.

6 To establish a second server session, set the REMOTE= and COMAMID= options to values that are appropriate for the second server. You also need to set the fileref RLINK again to associate it with the script file for the second server.

7 Terminate the links to both the UNIX server and the z/OS server. Use the CSCRIPT= option to identify the script file for signing off the z/OS server.

8 On the client, you can now use the SQL procedure to join into a single view the two SAS data sets that were created when you downloaded the views from the server.

9 To analyze the joined data, use the name of the view on the client in a PROC TABULATE step.

10 If you have SAS/GRAPH on your client, you can also use graphics procedures to analyze the view that is created from the two server databases.