SAS grid connction issues

[ankan1987]

Hi ,

I am using SAS grid and using the following conditions.

%LET DWPROD='jdbc:db2://pwg1.INFO53.COM:9030/LGRDWP1:useJDBC4ColumnNameAndLabelSemantics=false;';

%LET EDWPROD='jdbc:db2://saflokydcedwc01:50000/EDWPROD:useJDBC4ColumnNameAndLabelSemantics=false;';

LIBNAME edw JDBC URL=&EDWPROD user=&eid_db2conn. password=&pwd_db2conn. DRIVERCLASS=&DB2CLASS;
LIBNAME dwp1 JDBC URL=&DWPROD user=&eid_db2conn. password=&pwd_db2conn. DRIVERCLASS=&DB2CLASS ;

I would like to create a pass through connection to fetch the data from below tables. The below code is throwing error since the connection is different for edw and dwp1 tables . I would like to know the syntax to create a passthrough for both DWP1 and EDW.

(For example only)

proc sql ;
connect using edw;
CREATE TABLE Od_6 AS SELECT * FROM CONNECTION TO dwp1

( select distinct
hper.hh_key,

from dwp1.dep_acct_mo_dw as depm
inner join dwp1.dep_acct_dim as depd on depm.i_rec_key = depd.i_rec_key
inner join dwp1.rltn_dim as rltn on depm.i_rec_key = rltn.i_rec_key
inner join dwp1.time_dim as time on depm.i_prtn = time.i_prtn_mo_84
inner join edw.common_account_daily_dim as cadd on depm.i_rec_key = cadd.i_rec_key
inner join edw.household_periodic as hper on cadd.acct_key = hper.acct_key and time.d_per = hper.date_key

where
/* time.d_per between &prior6_month. and &current_month.*/

time.d_per between '09/01/2021' and '02/28/2022'

/* time.d_per between %bquote(date(&prior6_mth.)) and %bquote(date(&current_mth.))*/
and time.x_lst_day_mo = 'Y'

group by hper.hh_key

WITH UR);
QUIT;

[ballardw]

Any time you have a question about an error copy the code and all messages from the LOG. On the forum open a text box using the </> icon and paste all of the copied text.

The text box is important to maintain text formatting of the error message diagnostics that SAS often provides.

Please don't make us guess what the error is.

[Sajid01 · Accepted solution]

Hello @ankan1987 
As you said   "the connection is different for edw and dwp1 tables", you cannot join the tables like that.
As you want to create sql pass through connections and pull the data , the data needs to be pulled separately and joined.

[ankan1987]

Okay, Thanks

[ankan1987]

Thanks

[SASKiwi]

It looks like you are trying to join data between two different DB2 servers. I know some databases support multi-server queries but I'm not sure about DB2. Ask your DB2 administrator about this. If the DB2 servers are not in the same data centre then performance may be an issue. You may be able to connect to one of the DB2 servers, then reference the other remote DB2 server in queries. You would have to use SQL Passthru to do this though.   

[ankan1987]

Okay , Thanks