DATA Step, Macro, Functions and more

how to join db2 data with local data

Accepted Solution Solved
Reply
Contributor
Posts: 48
Accepted Solution

how to join db2 data with local data

[ Edited ]

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

 


Accepted Solutions
Solution
‎02-15-2018 12:00 PM
PROC Star
Posts: 549

Re: how to join db2 data with local data

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
     ;

 

View solution in original post


All Replies
Super User
Posts: 24,014

Re: how to join db2 data with local data

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.

PROC Star
Posts: 631

Re: how to join db2 data with local data

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.

Thanks,
Suryakiran
Solution
‎02-15-2018 12:00 PM
PROC Star
Posts: 549

Re: how to join db2 data with local data

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
     ;

 

Contributor
Posts: 48

Re: how to join db2 data with local data

thanks. Code is always best! thanks Kiranv.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 261 views
  • 1 like
  • 4 in conversation