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: 356

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

Jim Reed liked Annette Zawacki's discussion Is your Db2 for z/OS data part of your AI strategy? Learn how you can build AI models anywhere and deploy on IBM Z
yesterday
Jim Reed liked Diego Cardalliaguet's event EMEA IBM Db2 Analytics Accelerator Users Group meeting 2021
yesterday
Jim Reed liked Diego Cardalliaguet's event EMEA IBM Db2 Analytics Accelerator Users Group meeting 2021
yesterday
Jim Reed liked Surekha Parekh's event Achieving Optimal Availability, Usability and Performance with Universal Table Space - Part 2
yesterday
Profile Icon via Twitter
Join the live launch webcast, "What’s New in Db2 AI for z/OS and Watson Machine Learning for z/OS" June 1 at 11 AM… https://t.co/yvz9t7dwcC
TwitterTuesday · Reply · Retweet
A blog post by Surekha Parekh was featured
Tuesday
Diego Cardalliaguet updated an event
Thumbnail

EMEA IBM Db2 Analytics Accelerator Users Group meeting 2021 at Video conference

May 18, 2021 from 10am to 4:15pm
Monday
Annette Zawacki added a discussion to the group Data, Analytics and AI on IBM Z
Saturday
Joe Geller posted an event

TRIDEX Db2 for LUW Q2 2021 Meeting at Virtual Meeting

May 13, 2021 from 9am to 1pm
May 6
Tom Glaser posted events
May 6
Annette Zawacki posted an event
Thumbnail

Is your Db2 for z/OS data part of your AI strategy? at Eastern Time (U.S. and Canada)

May 18, 2021 from 11am to 12pm
May 5
Profile Icon via Twitter
Join “The IBM Enterprise Data & AI Virtual Symposium 2021”https://t.co/uRxOVwMZu6 hear directly from our clients, R… https://t.co/SEGx6axQYf
TwitterApr 27 · Reply · Retweet
Profile Icon via Twitter
Live Now! IBM Enterprise Data & AI Virtual Symposium 2021. Welcome to Ibrahim Parlak from Garanti Bank, over 16 Mil… https://t.co/CtVD3yiMn7
TwitterApr 27 · Reply · Retweet
Profile Icon via Twitter
Join now Panel started “The IBM Enterprise Data & AI Virtual Symposium 2021” https://t.co/uRxOVwMZu6 hear directly… https://t.co/7WN1BLMV4Y
TwitterApr 27 · Reply · Retweet
Profile Icon via Twitter
Join now Panel started “The IBM Enterprise Data & AI Virtual Symposium 2021” https://t.co/uRxOVx4AlE hear directly… https://t.co/7KZmbGpt1O
TwitterApr 27 · Reply · Retweet
Profile Icon via Twitter
Join Now "The road to AI-first enterprises" The IBM Enterprise Data & AI Virtual Symposium. REGISTER NOW… https://t.co/woTAl9ExIQ
TwitterApr 27 · Reply · Retweet

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

Forum

who opened the scoring from the penalty spot

Started by long lin in What's hot ? Apr 22. 0 Replies

fotbollströja barn eget tryck Lionel Messi opened the scoring from the penalty spot, but that was as good as it got for Ronald Koeman's side as Mbappe took over to evoke memories of recent defeats to…Continue

Fact.MR redefines the way

Started by blon sin in What's hot ? Apr 21. 0 Replies

New York, April 20, 2021 - The primary reason for the failure of startups is not lack of funding or intense competition. The main reason is "misunderstanding of market demand."Therefore, for start-ups, it is important to thoroughly study the…Continue

© 2021   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service