DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Weei has posted 5 posts at DZone. View Full User Profile

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

03.26.2007
| 25504 views |
  • submit to reddit
        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