DB2 connect to SQL server

[JtheRipper]

Hi there,

We are currently using oracle's transparent gateway to connect to our AS400/DB2 database from oracle 8i. Now we want to do the same for SQL Server. Is there also a gateway that needs to be set up or will SQL Server's Linked Server alone do the trick? As I said our oracle/db2 link is set up and working fine, but I cannot find any useful information on how to set up the SQL Server/DB2 link. I assume if there is a gateway it will be set up on the SQL Server side (Linked server???), but I do not know what needs to be done on the AS400.

Any advice/links/tips will be very much appreciated.

Thanks,
Jaco

 

[tman24m]

Shouldn't need to do anything on the AS400 side. Create an ODBC connection on the SQL Server and then add the AS400 as a linked server.

 

[kc1313]

Hi, Jaco

Did you ever heard DB2 II(Information integrator) ?
DB2/Unix(NT,Linux)V8 with II can federate heterogeneous data source ( DB2/390, DB2/400, DB2/Unix, Informix, Oracle, Sybase, MS SQL, ODBC datasource, text, excel, etc..)

At DB2/Unix, Using DB2 Single Interface access all federate Database( Select/Insert/Update/Delete ).

If you use oracle application, you have to change data interface into DB2 API. ie. Use DB2 Application.

I'll attach federate script..
-- DB2 Script for Oracle data source
drop wrapper net8;
create wrapper net8;

create server orcl_svr
type oracle version 8.1.5
wrapper net8
authorization scott password tiger
options ( node 'ora8' );

create user mapping for DB2LocalUser server orcl_svr
options ( remote_authid 'scott',
remote_password 'tiger');

create nickname emp for orcl_svr.scott.emp;
create nickname dept for orcl_svr.scott.dept;

drop table ora_emp;
drop table ora_dept;

create table ora_emp like emp;
create table ora_dept like dept;

describe select * from emp;
describe select * from dept;

insert into ora_emp select * from emp;
insert into ora_dept select * from dept;

select * from ora_emp;
select * from ora_dept;

-- DB2 Script for MS SQL source

drop wrapper SQLSERVER_WRAPPER;
CREATE WRAPPER SQLSERVER_WRAPPER LIBRARY 'db2mssql3.dll';

CREATE SERVER SQLSERVER TYPE MSSQLSERVER VERSION '2000'
WRAPPER "SQLSERVER_WRAPPER"
OPTIONS( ADD NODE 'SQL2K', DBNAME 'SPORTSWEST', PASSWORD 'Y');

CREATE USER MAPPING FOR "ENTERPRISE" SERVER "SQLSERVER"
OPTIONS ( ADD REMOTE_AUTHID 'west', ADD REMOTE_PASSWORD '*****') ;

set passthru sqlserver;

CREATE TABLE "west"."customer" (
"customer_num" INTEGER NOT NULL ,
"fname" CHAR(15) ,
"lname" CHAR(15)
);

CREATE TABLE "west"."orders" (
"order_num" INTEGER NOT NULL ,
"order_date" datetime ,
"customer_num" INTEGER NOT NULL ,
"item" CHAR(15),
"quantity" INTEGER,
"unitprice" DECIMAL(7,2)) ;

set passthru reset;

create nickname sportswest.customer for sqlserver."west"."customer";

create nickname sportswest.orders for sqlserver."west"."orders";