Geek Answers Handbook

How to add an IBM DB2 server to a SQL Server Linked Server

From .Net, I can connect to the DB2 database:

  • First I include a link to "IBM.Data.DB2.iSeries"

  • Then I create a new IBM.Data.DB2.iSeries.iDB2Connection. Connection string

    DataSource=ChaDb2Server;UserID=MyUsername;Password=MyPassword;

  • Then I create the IBM.Data.DB2.iSeries.iDB2Command file, etc.

Now I am trying to get my SQL Server 2005 to access the same data directly. In SQL Server Management Studio, right-click on Linked Servers and select "New Linked Server ..."

 Linked Server: ChaDb2Server Provider: IBM OLE DB Provider for DB2 Product Name: ??? Data Source: ChaDb2Server Provider String: DataSource=ChaDb2Server;UserID=MyUsername;Password=MyPassword; Location: ???

I can leave the field empty, but the product name cannot be empty, and I do not know what to fill here.

On the Security tab, I select "Running Using This Security Context" and I repeat the UserID and password.

A linked server is created, but when I try to expand Catalogs / default / Tables, I get an error:

 OLE DB provider 'IBMDADB2.DB2COPY1' reported an error. Authentication failed. (Microsoft SQL Server, Error: 7399)

I do not know where the name IBMDADB2.DB2COPY1 came from.

Also, when I try to select data:

 Select * from ChaDB2Server.ChaDb2Server.Information_Schema.Tables

I get a similar error:

 Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'IBMDADB2.DB2COPY1' reported an error. Authentication failed.

Obviously, something is missing in the way I connect the servers. Does anyone know how to do this?

+4
source share
3 answers

I am out of luck using an IBM provider. I hear it is difficult. However, I have successfully used the OLE DB provider for ODBC drivers.

My configuration points to DSN and works well. The only 3 fields that I filled out are the provider (Microsoft OLE DB provider for ODBC drivers), the product name (value = "not used"), and the data source (DSN name on the SQL server).

If you are not opposed to using an ODBC provider, it will work for you.

+2
source

The method for installing the DB2 LUW server as a linked server in SQL as follows:

1 - I installed the appropriate DB2 LUW client on mySQL Server

 --> This will installed the missing driver for DB2 (IBMADB2.DB2COPY1)

2 - I create a linked server as follows:

 replace the <...text...> with your values /****** Object: LinkedServer [<DB2_DB_Name>] Script Date: 09/08/2014 09:46:02 ******/ EXEC master.dbo.sp_addlinkedserver @server = N'<DB2_DB_Name>', @srvproduct=N'IBMADB2.DB2COPY1', @provider=N'IBMDADB2.DB2COPY1', @provstr=N'Database=<DB2_DB_Name>;Hostname=<DB2 Server running the DB2 database>;Port=5900' /* For security reasons the linked server remote logins password is changed with ######## */ EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'<DB2_DB_Name>',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL GO EXEC master.dbo.sp_serveroption @server=N'<DB2_DB_Name>', @optname=N'collation compatible', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'<DB2_DB_Name>', @optname=N'data access', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'<DB2_DB_Name>', @optname=N'dist', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'<DB2_DB_Name>', @optname=N'pub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'<DB2_DB_Name>', @optname=N'rpc', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'<DB2_DB_Name>', @optname=N'rpc out', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'<DB2_DB_Name>', @optname=N'sub', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'<DB2_DB_Name>', @optname=N'connect timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'<DB2_DB_Name>', @optname=N'collation name', @optvalue=null GO EXEC master.dbo.sp_serveroption @server=N'<DB2_DB_Name>', @optname=N'lazy schema validation', @optvalue=N'false' GO EXEC master.dbo.sp_serveroption @server=N'<DB2_DB_Name>', @optname=N'query timeout', @optvalue=N'0' GO EXEC master.dbo.sp_serveroption @server=N'<DB2_DB_Name>', @optname=N'use remote collation', @optvalue=N'true' GO EXEC master.dbo.sp_serveroption @server=N'<DB2_DB_Name>', @optname=N'remote proc transaction promotion', @optvalue=N'true' GO
+2
source

I don’t know why, but I have never been able to get connected servers to work when setting up through the graphical interface. However, I succeeded with sp_addlinkedserver. See the IBM setup instructions here: https://www-304.ibm.com/support/docview.wss?uid=swg21394344

0
source

More articles:


All Articles