Linked server to DB2 - best method?

Question

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

Did you check security on DB2 server beside your syntax in building linked server?

JMI SSChampion Points: 11831 More actions · Answer 2

Actually Linked Server with these credentials

was registered successfully on production SQL Server 2000 box.

But nobody documented this or scripted the process.

It was done through Enterprise Manager.

Now I need to have the same linked server on our DEV SQL Server 200 box.

I was able to create a System DSN with these credentials on DEV box

and it says "Tested Connection Successfully".

Then executed sp_addlinkedserver referring to this DSN.

Now in Enterprise Manager I can view DB2 schemas and tables but

Catalogs are empty and I can't access any of DB2 tables in a query.

DSN is probably not a god option but it got me farthest so far.

Nothing worked with DB2OLEDB provider.

And no error messages.

How to debug this stupid sp_addlinkedserver?

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 3

Did you check whether or not the account you use has permissions to view these objects in DB2?

JMI SSChampion Points: 11831 More actions · Answer 4

I checked.

DB2 account works on DB2 server.

I was able to execute SELECT in Command Editor

using this account.

I'm trying to analyze prod configuration

and see what might be different on DEV box that's causing the problem.

In production they used

provider string:
Hostname=172.16.1.174;Database=DCDB;Protocol=TCPIP;Port=30001

in Linked Server Properties.

In the Linked Server Properties

under Security Tab "be made under this security context"

is checked and DB2 account mentioned above is specified there.

sp_linkedservers_rowset

returns me this on PROD server (I converted columns into rows):

DB2_PROD_DCDB

IBM OLE DB Provider for DB2

IBMDADB2

NULL

...