Linked Server Issue connecting SQL 2012 SP1 to IBM MVS DB2

• Phil-94129

  Mr or Mrs. 500

  Points: 582

  More actions

  May 20, 2015 at 3:13 pm

  #304127

  I'm attempting to configure a Linked Server to DB2 on our IBM MVS Mainframe using SQL 2012 SP1 Developers Edition.

  I've installed the "Microsoft OLE DB Provider for DB2", version 4.

  The script to configure the linked server is:

  EXEC master.dbo.sp_addlinkedserver @server = N'IBMDB2DEV3',

  @srvproduct=N'Microsoft OLE DB Provider for DB2',

  @provider=N'DB2OLEDB',

  @datasrc=N'10.10.20.1',

  @location=N'HMUTDSND',

  @provstr=N'Provider=DB2OLEDB;Initial Catalog=HMUTDSND;Network Transport Library=TCPIP;Host CCSID=37;PC Code Page=1252;Network Address=10.10.20.1;Network Port=7885;Package Collection=DADMIN;Default Schema=DADMIN;Default Qualifier=DADMIN;DBMS Platform=DB2/MVS',

  @catalog=N'HMUTDSND'

  EXEC master.dbo.sp_addlinkedsrvlogin

  @rmtsrvname = N'IBMDB2Dev3',

  @locallogin = NULL ,

  @useself = N'False',

  @rmtuser = N'userID',

  @rmtpassword = N'password'

  Go

  The Linked Server is created, and when I test the connect it connects successfully.

  But - when I expand the catalog and attempt to view the tables and views, I get the following error:

  TITLE: Microsoft SQL Server Management Studio

  ------------------------------

  Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

  For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

  ------------------------------

  ADDITIONAL INFORMATION:

  An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

  ------------------------------

  Cannot fetch a row from OLE DB provider "SQL Server" for linked server "(null)". (Microsoft SQL Server, Error: 7330)

  In the details, the error references Procedure sp_tables_ex - see below:

  Server Name: SQL2012ServerName

  Error Number: 7330

  Severity: 16

  State: 2

  Procedure: sp_tables_ex

  Line Number: 41

  Line 41 references what appears to be a function "sys.fn_remote_tables" which does not appear to exist.

  When I attempt to query a table in DB2 with the query:

  Select * from [IBMDB2DEV3].HMUTDSND.DADMIN.tablename

  I get the following error:

  OLE DB provider "DB2OLEDB" for linked server "IBMDB2DEV3" returned message "One or more errors occurred during processing of command.".

  OLE DB provider "DB2OLEDB" for linked server "IBMDB2DEV3" returned message "A SQL error has occurred. Please consult the documentation for your specific DB2 version for a description of the associated Native Error and SQL State. SQLSTATE: 42721, SQLCODE: -725".

  Msg 7330, Level 16, State 2, Line 1

  Cannot fetch a row from OLE DB provider "DB2OLEDB" for linked server "IBMDB2DEV3".

  Any ideas?

  Thanks