topic connect to DB2 database error in SAS Programming

connect to DB2 database error

radha009 — Fri, 18 Oct 2019 15:49:27 GMT

proc sql;
connect to db2 (user='423423' password='fwewerwr' database='sdfsdf' host='fwerwerwe');
create table table_abc (compress=yes) as select* from connection to db2 (
select *
from table2
where status ='A');
quit;

-- Executing from SAS 9.4 for windows

ERROR: The DB2 engine cannot be found.
ERROR: A Connection to the db2 DBMS is not currently supported, or is not installed at your
site.

Re: connect to DB2 database error

Krueger — Fri, 18 Oct 2019 16:36:48 GMT

Whenever I need to do SQL Pass-Through I use this:

PROC SQL;
CONNECT TO odbc ("dsn={name here}");
CREATE TABLE {Table Name} AS
SELECT * FROM connection to odbc
(
SELECT {Column}
FROM Table
);
DISCONNECT FROM odbc;
QUIT;

I personally don't have access to use the db2 connection method but I believe you would need to do it like this:

proc sql;
connect to db2 (user=_______ database=_______ password=_______);
execute( CREATE TABLE {Table Name} AS
SELECT * FROM connection to odbc
(SELECT {Column}
FROM Table) by db2;
disconnect from db2;
quit;

Re: connect to DB2 database error

VDD — Fri, 18 Oct 2019 17:37:18 GMT

you might need to have the DB2 client installed on your work-station

Re: connect to DB2 database error

radha009 — Fri, 18 Oct 2019 17:51:11 GMT

How about username and password to dB connection.

Re: connect to DB2 database error

Krueger — Fri, 18 Oct 2019 19:02:57 GMT

DSN is something you have pre-configured.

-- This would be at the top of your code
libname rptLib odbc dsn=rpt_Lib_name schema=dbo spool=NO;


--You would then reference it like this.

PROC SQL;
CREATE TABLE Test AS
SELECT *
    FROM rptLib.Table
;QUIT;

If you have this then you can use the first snippet. If not then you'll need to configure the connection with DB2 which would require UserName, Password and Database.