Linked Server to DB2
-
I'm trying to create a linked server to a DB2 database on AS400. However, I'm not able to run queries on the created linked server.
First I tried the Microsoft OLE DB provider for ODBC, using a system DSN with the iSeries Client Access. I've created an .udl file, and I can create a successful connection.
When I create the linked server with a fixed login and password, clicking on "Test Connection" yields success. However, when I browse the tables, I can only see some of them not all. Querying a table that should exists results in the following error message:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "LINKED_ODBC_myLinkedServer" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "LINKED_ODBC_myLinkedServer".
The query:
select top 5 * from openquery(LINKED_ODBC_myLinkedServer,'select * from test.myTable')
When I run the query a second time, it just runs forever without returning results. (and the table exists. If I go to the DB2 database with sqlexplorer, I can browse the table without a problem).
So I tried a different approach. I installed the Microsoft OLE DB provider for DB2 on my machine. Again, I can create an .udl file and create a successfull connection. In SSIS, I can create a connection manager and use it without any problem. However, when I create the linked server, clicking on 'test connection' makes SSMS hang (the screen turns white). Querying the linked server with the same query as above also keeps running forever. (so it is actually worse than the ODBC linked server, although I can use it in SSIS without a problem)
The script to create the linked server:
EXEC master.dbo.sp_addlinkedserver
@server= N'LINKED_DB2SRV_TEST'
,@srvproduct= N'Microsoft OLE DB Provider for DB2'
,@provider= N'DB2OLEDB'
,@provstr= N'Provider=DB2OLEDB;Persist Security Info=False;User ID=SQLAS;
Initial Catalog=myDatabase;Data Source=myServer;
Network Address=myServer;Package Collection=test;Default Schema=test'
,@catalog= N'S65b7f3f'
GO
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname= N'LINKED_DB2SRV_TEST'
,@useself= N'False'
,@locallogin= NULL
,@rmtuser= N'SQLAS'
,@rmtpassword='#######'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKED_DB2SRV_TEST', @optname=N'collation compatible', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'LINKED_DB2SRV_TEST', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'LINKED_DB2SRV_TEST', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LINKED_DB2SRV_TEST', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LINKED_DB2SRV_TEST', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LINKED_DB2SRV_TEST', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LINKED_DB2SRV_TEST', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LINKED_DB2SRV_TEST', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'LINKED_DB2SRV_TEST', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'LINKED_DB2SRV_TEST', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LINKED_DB2SRV_TEST', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'LINKED_DB2SRV_TEST', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'LINKED_DB2SRV_TEST', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
The server option 'Ad Hoc Distributed Queries' has the value 1.
Ther server version is SQL Server 2008R2.
Any ideas? If more information is needed, let me know.
edit: the connection string used in the script is exactly the same as the one used in SSIS. Which works...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Based on personal experience: don't use ODBC (too slow and unreliable) and go for OLEDB.
It could be something wrong in:
A) The iSeries package (have you checked that with your QSECOFR?)
B) The OLEDB provider (is it installed correctly? Review the setup readme and check all the steps involved)
C) Network connectivity (NetMon or WireShark help a lot here. Any firewalls between?)
D) Windows security. Check my article here: http://www.sqlservercentral.com/articles/Linked+Servers/73794/
Try changing syntax: it doesn't solve the issue, but it may help identifying where the problem lies.
You can use:
1) SELECT * FROM LINKEDSERVER.DATABASENAME.LIBRARY.TABLE
2) SELECT * FROM OPENQUERY(LINKEDSERVER,'SELECT * FROM LIBRARY.TABLE')
3) EXEC ('SELECT * FROM LIBRARY.TABLE') AT LINKEDSERVER
I will try to set up a linked server with MS OLEDB provider on my laptop and see if something else comes to my mind.
Hope this helps,
Gianluca
-- Gianluca Sartori
-
Thanks for the reply Gianluca.
However, I can rule out A through D, as I can create a successful connection and extract data from it in SSIS.
So it is somewhere in the linked server itself or at SQL Server that something goes wrong.
Neither of the different syntaxes (or syntaxi?) work. Seems I am a bit at a dead end here...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Koen Verbeeck (7/28/2011)
Thanks for the reply Gianluca.However, I can rule out A through D, as I can create a successful connection and extract data from it in SSIS.
So it is somewhere in the linked server itself or at SQL Server that something goes wrong.
Neither of the different syntaxes (or syntaxi?) work. Seems I am a bit at a dead end here...
SSIS works in a totally different way than SQL Server RDBMS as far as connections is concerned.
If SSIS works, the iSeries package must be ok, and so the OLEDB provider and the network access. Windows security is another thing to look into.
BTW: SSIS works with ODBC, OLEDB or both?
-- Gianluca Sartori
-
I only tested SSIS with OLE DB.
I shall test it with ODBC right away! 🙂
edit: there is no windows security, I use a fixed login and password to connect to the DB2 database.
I'm also sa at the SQL Server database.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
OK, forget about testing ODBC in SSIS, the system DSN is created on the server, together with the Client Access software.
Which I both don't have on my local PC.
Anyway, OLE DB works in SSIS, and it would be great if I got it working in SQL Server itself also.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
OK, thanks. So I guess you will have to stick to OLEDB (which is a good thing IMHO).
With "Windows Security" I mean the permissions to assign to the windows user that starts MSSQL and to the user that connects to it and instantiates the OLEDB provider.
Do you see any clue in Windows event viewer?
Can you trace the process (Sqlservr.exe) with Process Monitor (http://technet.microsoft.com/it-it/sysinternals?ppud=4) and see if something interesting comes out?
-- Gianluca Sartori
-
Koen Verbeeck (7/28/2011)
First I tried the Microsoft OLE DB provider for ODBC, using a system DSN with the iSeries Client Access. I've created an .udl file, and I can create a successful connection.When I create the linked server with a fixed login and password, clicking on "Test Connection" yields success. However, when I browse the tables, I can only see some of them not all. Querying a table that should exists results in the following error message:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "LINKED_ODBC_myLinkedServer" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "LINKED_ODBC_myLinkedServer".
The query:
select top 5 * from openquery(LINKED_ODBC_myLinkedServer,'select * from test.myTable')
When I run the query a second time, it just runs forever without returning results. (and the table exists. If I go to the DB2 database with sqlexplorer, I can browse the table without a problem).
That is the approach I always take and in my case it works. While on SSMS can you browse the link server tables? Have your account on the iSeries enough rights to access the table you are trying to query?
-
Ignacio A. Salom Rangel (7/28/2011)
That is the approach I always take and in my case it works. While on SSMS can you browse the link server tables? Have your account on the iSeries enough rights to acces the table you are trying to query?When using the OLE DB linked server, I cannot browse the tables.
Using the ODBC linked server, I can browse some tables, but not all.
The account has sufficient permissions, I can browse the tables perfectly in SSIS.
I still have to try Gianluca's suggestions though...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Koen Verbeeck (7/28/2011)
edit: the connection string used in the script is exactly the same as the one used in SSIS. Which works...But there's a glaring syntax error connection string in the script in your message (missing quote at end of one of the string parameters). So I have to ask - did you miss some tiny difference between connection string usedin SSIS and the one in the script?
However, I can rule out A through D, as I can create a successful connection and extract data from it in SSIS.
So it is somewhere in the linked server itself or at SQL Server that something goes wrong.
Neither of the different syntaxes (or syntaxi?) work. Seems I am a bit at a dead end here...
So the user under which the DQLServer service runs is the same user name as than under which you run SSIS? If not, how does success in SSIS rule out Gianluca's D for the cause of failure in SQLServer? And are you using the same mechanisms in the two cases (eg is one using OLE-DB directly and the other using ODBC - which uses different bits of MDAC)?
It would also be worth looking at whether the provider is running in process or not, as suggested by Gianluca - and if it's out of process going through the dcom security config, unless the users are the same in the two cases.
My experience of DB2 is not great - just enough to convince me 11 years ago that it was a vastly inferior product to pretty much anything else on the market at the time and throw it out of the shop (replacing it with SQLServer and getting our money back from IBM) - so I don't have any more constructive suggestions than the above.
edit: I see things have moved on quite a bit since the first three posts. That'll teach me not to go and have breakfast in the middle of commenting.
Tom
-
Tom.Thomson (7/28/2011)
But there's a glaring syntax error connection string in the script in your message (missing quote at end of one of the string parameters). So I have to ask - did you miss some tiny difference between connection string usedin SSIS and the one in the script?Nicely spotted 🙂 But the quote just got lost when I was editing the script to replace real values by dummy values. So it is (unfortunately) not the source of the issue.
Tom.Thomson (7/28/2011)
So the user under which the DQLServer service runs is the same user name as than under which you run SSIS? If not, how does success in SSIS rule out Gianluca's D for the cause of failure in SQLServer? And are you using the same mechanisms in the two cases (eg is one using OLE-DB directly and the other using ODBC - which uses different bits of MDAC)?It would also be worth looking at whether the provider is running in process or not, as suggested by Gianluca - and if it's out of process going through the dcom security config, unless the users are the same in the two cases.
I have to read Gianluca's article first. And by first glance, I'm postponing that to after lunch 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Allright. I just went over Gianluca's (incredibly complex) article.
Did almost everything. Just the configuration of the MSDTC I couldn't do, as I apparently have only the option to configure the default coordinator. The other configuration options just aren't there...
Didn't really help. Perhaps because I was executing the provider InProcess. So I configured the provider to run out of process, restarted the server, and I got the following error message:
OLE DB provider "DB2OLEDB" for linked server "LINKED_DB2SRV_TEST" returned message "A TCPIP socket error has occured (10057): A request to send or receive data was disallowed because the socket is not connected and (when sending on a datagram socket using a sendto call) no address was supplied.
".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "DB2OLEDB" for linked server "LINKED_DB2SRV_TEST".
Well, it is at least something. Before I didn't even get an error, the query just kept on running forever.
@tom: no, the user in SSIS is not the same as the SQLServer account. Before I'd read Gianluca's article, I didn't even know it made a difference.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Looks like a TCP/IP connection error.
Can you trace the process with NetMon? You can add a filter for source or destination = your AS/400 machine.
If you don't see packets in the trace, probably the connection string is malformed.
-- Gianluca Sartori
-
Further investigation leads to the fact that I get the same error in the .udl file if I do not use the IP-adres in the TCPIP configuration.
However, I did provide the network address in the connection string in the linked server script. Does the linked server ignores this setting or something like that?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
I have found that SSIS ignores the settings in the UDL file and requires entering ALL the connection parameters in the connection properties.
SQL Server has always been more forgiving and doesn't require the full connection string. However, if you can connect testing the UDL, you can edit the file with notepad and copy/paste the contents in the connection string field of the linked server.
-- Gianluca Sartori
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply