Over a million developers have joined DZone.

Configure MSSQL Linked Server To DB2 (via ODBC System DSN)

DZone's Guide to

Configure MSSQL Linked Server To DB2 (via ODBC System DSN)

· ·
Free Resource
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')

    * 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.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}