The SAS-code below shows you how to make SQL pass-through to DB2 and collect the result in a new table.
proc sql noprint;
connect to db2 as db(database=<Your database> user=<Your username> password=<Your password>);
create table <Dummy> as
select * from connection to db
(
select * from <schema>.<table>
);
disconnect from db;
quit;
It is also possible to use authdomain to collect credentials instead of user and password and the DB2 database has to be available to SAS.
The SAS code below shows you how to make SQL DB2 pass-through.
proc sql noprint;
connect to db2 (database=<Your database> user=<Your username> password=”<Your password>”);
execute
(
<Your SQL sentence>
) by db2;
disconnect from db2;
quit;
If you have your credentials stored in a SAS metadata-server it is possible to use a authdomain and skip entering username and password.
It is done using the following option in SAS:
options
metaserver=”<Your metadataserver>”
metaport=<usually 8561>
metaprotocol=bridge
metarepository=”Foundation”
;
proc sql noprint;
connect to db2 (database=<Your database> authdomain=<Your authdomain>);
execute
(
<Your SQL sentence>
) by db2;
disconnect from db2;
quit;
The database has to be available to SAS. This can eg be done through your DB2 ControlCenter.
This is the danish translation of the english version for alternation of rows in Excel.
English: =MOD(ROW(),2)=0
Dansih: =REST(RÆKKE();2)=0
The syntax is:
libname <name> db2 database=<database> schema=<schema> user=<user> password=<password> in=<tablespace>;
Eg.
libname testname db2 database=mydb schema=myschema user=domain.com\myuser password=”1234Hello” in=mytablespace;
The connection to the database has to be created eg through the DB2 ControlCenter before it is possible to connect to it in SAS.
‘in=’ determins the tablespace where the data is placed.
I haven’t had any luck getting Java 7 update 9 to work on my Windows 8. I was getting a ClassNotFoundException error.
The solution for me was to install Java 6 and everything worked just fine.