how to join db2 data with local data

[cellurl]

Hi,

Here is what I want:

libname here './'; run;

proc sql;
   connect to db2     d;
   create table here.tmp as 
        select
           *
        from
           d, here.stuff h
        where
           d.thing=h.thing
     ;

I tried below example with no joy. I hope there is a simple solution from you fantastic people!!!

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a002678734.htm

[Reeza]

Can you connect to your DB2 using a libname as well? SQL pass through is the same thing as working entirely on your server so you can't see the local data anymore so that approach won't work. 

Another common workaround is to select the list of 'things' required, pass that as a macro variable to filter it first and then join the data on the SAS side after the remaining portion is received.

[SuryaKiran]

Hi,

First thing when your using Explicit pass-through is the query runs entirely in the database and gets you the result to SAS. Explicit pass-through can't be used if tables are in different platform( DB2 table and SAS table "Heterogeneous" ). 

If your SAS table is very small and want to join with DB2 table which is very large by avoiding SAS to bring the large DB2 data  into SAS environment the efficient way is load the SAS table into DB2 and then you can do in-database queries.

Note: Creating a permanent tables is restricted to most users in many environments. In this case check if you can create a DBMS temp table.

[kiranv_ · Accepted solution]

You cannot use connect statement and  do the join. Once you use connect statement all the queries are moved to the DB2 server and will not have no clue about SAS tables.

One way to accomplish this is by using implicit pass through which is by doing libname for db2 and then doing join

as shown below. @SuryaKiran has some  good suggestions how to do this very efficiently.

libname here './'; 
libname mylib db2 user=user-id password=password datasrc=data-source-name;
proc sql;
 
   create table here.tmp as 
        select
           *
        from
           mylib.d o, here.stuff h
        where
           o.thing=h.thing
     ;

[cellurl]

thanks. Code is always best! thanks Kiranv.