08-30-2014 05:29 PM
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.
08-31-2014 07:07 AM
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= ).
08-31-2014 10:07 PM
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?
09-01-2014 08:05 AM
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
09-01-2014 08:11 AM
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...)
09-01-2014 08:21 AM
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.
libname mydblib oracle user=myusr1 pass=mypwd1 dbconinit="begin dept_test(1001,25)"; end;
09-01-2014 08:51 AM
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 ....
01-11-2017 12:49 PM
I am running with similar issue.
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.
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:
connect to oracle as edw(AuthDomain=OraAuth_XYZ path=Path);
create table test as Select * from connection to xyz
(select distinct(ID) from table
disconnect from xyz;
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?