I am trying to create a linked server from MSSQL 2014  to DB2 11.1  I am able to create one linked server and get it to work with one DB.  I have not been able to figure out how to add additional catalogs(DB's).  Is it possible to use just one linked server for multiple databases.

Views: 381

Reply to This

Replies to This Discussion

Hello Mike,

I tried to connect to db2 11.1 from sqlserver 2016 using IBMOLEDB driver. It works partially. i.e I can get table column headers but having issues in getting data.

I did it through below TSQL:

Create a ODBC system DSN to connect to a db2 database and test the connection. My DSN name is "test"

My db2 11.1 database is also named "test".

Database 1 entry:
 Database alias                       = TEST
 Database name                        = TEST
 Local database directory             = C:
 Database release level               = 15.00

[CLI_ODBC>TEST]
DataSourceName=test
DataSourceType=User
DBALIAS=TEST
PWD=xxx
UID=xxx
ODBCParameters=Yes

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'TEST3', @srvproduct=N'db2', @provider=N'IBMOLEDB', @datasrc=N'test'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'TEST3', @locallogin = NULL , @useself = N'False', @rmtuser = N'loginID', @rmtpassword = N'password
GO
GO
EXEC master.dbo.sp_serveroption @server=N'TEST3', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST3', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST3', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST3', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST3', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST3', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST3', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST3', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST3', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'TEST3', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST3', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST3', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'TEST3', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_MSset_oledb_prop N'IBMOLEDB.test', N'AllowInProcess', 0
GO

EXEC sp_linkedservers;
SRV_NAME  SRV_PROVIDERNAME SRV_PRODUCT SRV_DATASOURCE SRV_PROVIDERSTRING SRV_LOCATION SRV_CAT
TEST3   IBMOLEDB   db2   test NULL NULL NULL

select * from OPENQUERY(TEST3,'SELECT * FROM SYSCAT.TABLES');
TABSCHEMA TABNAME
It didn't bring the data, only headers with the following message. Hope more research will get the data.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "IBMOLEDB" for linked server "TEST3" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "IBMOLEDB" for linked server "TEST3".
If you get more luck, let me know.
Rgds,
Thiru Swamy

Thiru,

I appreciate your feedback.  I am new to DB2 and still learning but i would verify the permissions you have set for your linked server account as it looks like maybe you don't have them set properly.

Thanks,

Michael Jett

RSS

Latest Activity

William Shipley liked Surekha Parekh's blog post Live Db2 12 Client Panel 30th June 2021 - Sign Up today!
yesterday
A blog post by Surekha Parekh was featured
Tuesday
Daniel Luksetich posted a blog post
Monday
Gulfem Ogutgen liked Annette Zawacki's event Garanti-BBVA shares Db2 Analytics Accelerator migration and operational experiences
Monday
Gulfem Ogutgen liked Surekha Parekh's event Interactive Db2 12 Client Panel with John Campbell IBM DE
Monday
edmundo nascimento liked Surekha Parekh's event Interactive Db2 12 Client Panel with John Campbell IBM DE
Monday
Michael Tiefenbacher posted an event
Thumbnail

30. DeDUG User Group Meeting at virtual

September 24, 2021 from 9am to 1:30pm
Monday
Earl Dugan posted an event

Virtual New England DB2 User's Group Thursday, June 24th, 2021. at Virtual Meeting

June 24, 2021 from 9am to 12pm
Monday
Willy Unger liked Annette Zawacki's event Garanti-BBVA shares Db2 Analytics Accelerator migration and operational experiences
Saturday
carol goldberg liked Surekha Parekh's event Interactive Db2 12 Client Panel with John Campbell IBM DE
Jun 17
Adrian Collett liked Surekha Parekh's event Interactive Db2 12 Client Panel with John Campbell IBM DE
Jun 17
ONLO posted a discussion
Jun 16
GABRIEL FAJARDO liked Surekha Parekh's event Interactive Db2 12 Client Panel with John Campbell IBM DE
Jun 15
Pedro Diaz liked Surekha Parekh's event Interactive Db2 12 Client Panel with John Campbell IBM DE
Jun 14
An event by Surekha Parekh was featured
Thumbnail

Interactive Db2 12 Client Panel with John Campbell IBM DE at https://ibm.biz/Db212ClientPanel

June 29, 2021 from 11am to 12:30pm
Jun 14
Carol Davis-Mann posted a blog post
Jun 11

Bringing Db2 enthusiasts together virtually. Expert or novice, distributed or mainframe, this is the place for everything DB2.

Forum

ADM10501W - Health indicator "Database Automatic Storage Utilization"

Started by ONLO in Application Development and DB2 Jun 16. 0 Replies

AIX Version 6.1DB2 10.5.6Hi every body,I got the above error:MESSAGE : ADM10501W Health indicator "Database Automatic Storage Utilization" ("db.auto_storage_util") breached the "upper" warning threshold of "80 %" with value "88 %" on "database"…Continue

DB2 Future

Started by Peter E Saxe in Application Development and DB2 May 20. 0 Replies

Has anyone ever submitted ideas of new functions in DBMS software like DB2?   have an idea for a future enhancement to DB2 that I would like to see.  Is this discussion area a good place to discuss this ?  Thanks.Continue

Tags: Enhancements, Future, DB2

© 2021   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service