Configure MSSQL Linked Server To DB2 (via ODBC System DSN)
Join the DZone community and get the full member experience.Join For Free
1. Install DB2 on MSSQL machine 2. Start Configuration Assistant. Add a new database mapping to the desired target DB2 database. Select option to create a System DSN along the way. 3. Start Microsoftâ€™s ODBC Data Source Administrator. There should be a System DSN created from the previous step. Configure it with the userid/password for the target DB2 database. 4. Create linked server in MSSQL: EXEC sp_addlinkedserver @server = 'TMON', @srvproduct = '', @provider = 'MSDASQL', @datasrc = 'TMON' 5. Map access to linked server: EXEC sp_addlinkedsrvlogin 'TMON', 'false', NULL, 'db2admin', 'db2admin' 6. 2 ways to test the link: SELECT * FROM TMON..DB2ADMIN.USERS -- use uppercase for server, schema, table names SELECT * from OPENQUERY (TMON,'select * from users') Notes: * TMON is the remote DB2 database, also used as the DSN name. * Remote DB2 server uses access id: db2admin and password: db2admin * USERS is a table in the remote DB2 database
Opinions expressed by DZone contributors are their own.