topic Re: how to sepcify databse in proc sql pass through in SAS Procedures

how to sepcify databse in proc sql pass through

abcd123 — Sat, 30 Aug 2014 21:29:17 GMT

hello,

the following statement runs successfully which will connect to the default database on My_DB2_Server_Name:

CONNECT TO db2 as myDB2 (datasrc=My_DB2_Server_Name user = 'MyUserName' password = 'MyPassWord');

Now I want to connect to another database; I tried following 2 methods:

CONNECT TO db2 as myDB2 (datasrc=My_DB2_Server_Name user = 'MyUserName' password = 'MyPassWord' database='AnotherDataBase');

CONNECT TO db2 as myDB2 (datasrc=My_DB2_Server_Name user = 'MyUserName' password = 'MyPassWord' schema="AnotherDataBase");

However, neither works.

I know how to do change database connection using LIBNAME; however, is there a way we can specify the databse in the pass through? Thanks for your help.

Re: how to sepcify databse in proc sql pass through

Ksharp — Sun, 31 Aug 2014 11:07:55 GMT

You need to make a DB2 catalog by DB2 client for each database . You can't use it in that way .

Every datasrc= corresponds to a database which you can edit in the DB2 catalog .

if you need to connect to two database and you have to make two DB2 catalog( datasrc= ).

Xia Keshan

Re: how to sepcify databse in proc sql pass through

abcd123 — Mon, 01 Sep 2014 02:07:32 GMT

Thanks for help.

Another question is: if I want to run a sql stored procedure, I have to use pass through (i.e., libname statement cannot run stored procedure), is this correct?

Re: how to sepcify databse in proc sql pass through

jakarman — Mon, 01 Sep 2014 06:27:08 GMT

Did you check the documentation? SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Fifth Edition

Re: how to sepcify databse in proc sql pass through

Ksharp — Mon, 01 Sep 2014 11:49:16 GMT

Sorry. I am not familiar with stored procedure. You should post it at

SAS Stored Processes

Re: how to sepcify databse in proc sql pass through

jakarman — Mon, 01 Sep 2014 12:05:42 GMT

Xia, your first reply was in the correct direction. DB2 is somewhat different as RDBMS as other RDBMS, they are all different in details. As abcd123 can run a libname with an other connection the same goes for explicit pass through.

Strange is the schema-name as userid, that is different with oracle.

Your last reply is confusing. abcd123's Question is about DB2 stored processes not about SAS stored processes. Same name "stored processed" and totally different environments.
That is why I give the link how SAS/Access is going to access the DB2 stored process. Whether he could call a DB/2 SP in a libname is not really sure.

I think on DBCONINIT DBCONTERM DBLIBINIT DBLIBTERM as they are meant to execute commands in DB2. example 2 with DBCONINIT is mentioning a DB2 SP. SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Fifth Edition

Re: how to sepcify databse in proc sql pass through

LinusH — Mon, 01 Sep 2014 12:11:12 GMT

Actually I think it's called Stored Procedures, but still it's confusingly similar both in name, and in what they do.

You can't call Stored Procedures via a libname, you must use explicit SQL pass-thru. (EXECUTE BY...)

Re: how to sepcify databse in proc sql pass through

jakarman — Mon, 01 Sep 2014 12:21:39 GMT

LinusH Copied from the link in the manual. Those mentioned options are statements executed similar as of SQL pass through. Could set some personal favors to a RDBMS not part of SQL.

Your are right, it are Stored procedures abbreviated as SP's. It is confusing always troublesome with the communication with DBA's.

......

Example 2: Pass a Stored Procedure

In this example, a stored procedure is passed to DBCONINIT=.

libname mydblib oracle user=myusr1 pass=mypwd1 dbconinit="begin dept_test(1001,25)"; end;

Re: how to sepcify databse in proc sql pass through

Ksharp — Mon, 01 Sep 2014 12:51:15 GMT

Actually, I think you are expert about this sort of field . I just used DB2 ORACLE SQLSever ODBC and SAS before, and know how to make a DB2 catalog to connect to SAS . I totally have no idea about stored procedure either SAS or DB2 ....

Xia Keshan

Re: how to sepcify databse in proc sql pass through

abcd123 — Mon, 01 Sep 2014 14:15:06 GMT

Thank you all so much for helpful discussion.

Re: how to sepcify databse in proc sql pass through

Ram4sas — Wed, 11 Jan 2017 17:49:39 GMT

Hi Jakarman,

I am running with similar issue.

I have set up SAS library(abc) in SAS SMC for oracle schema. This schema name called ‘ABC’ in Oracle server database(port opened and updated tnsnames.ora with path name details).

Created user group with ‘OraAuth_ABC’ domain added old schema user name and PW then added users.

Path name been defined in server connection and selected OraAuth_ABC domain for authentication field.

Library created : schema name added, selected the oracle connection and selected the username(OraAuth_ABC) for authentication.

Last week I have create another SAS library(xyz) for different schema called ‘XYZ’ (same tables for more data) which exist in same DB server same as above and domain:‘OraAuth_XYZ’ and different user id and PW for XYZ schema.

Users are able to access data from both abc and xyz libraries using SAS data and proc steps when users explicitly referred abc.table or xyz.table library names.

Issue: when users run pass through code to read from XYZ schema, the data is pulling from ABC schema.

If users access table from SAS Data step and Prod step by referring libref xyz.table then the data is coming for 201611 and 201612.

When user run pass through code for 201611, 201612 and 201701 data, as below code result is 0 records which means its pointing to old abc schema.

Pass through code for Last week set up library:

proc sql;

connect to oracle as edw(AuthDomain=OraAuth_XYZ path=Path);

create table test as Select * from connection to xyz

(select distinct(ID) from table

Where date=201611);

disconnect from xyz;

quit;

is this default behavior? why the above code is point to old schema table? am i mssing anything? shoudl i need to mention schema name?