June 30, 2008 at 5:34 pm
We have a 64-bit SQL Server 2005 SP2 Enterprise Edition on 64-bit Windows 2003 Server SP2. I need to create a linked server to IBM DB2 - AS/400 on Iseries server to pull data. Which is the best driver to use in this situation?
We are using MSDASQL.dll to get data from Iseries for 32-bit SQL Server 2005 on 32 bit Windows 2003 Server SP2 and I have heard that is the fastest way to do it.
But MSDASQL.dll is not available for 64-bit Windows 2003 servers machines, atleast it was not available the last time I checked.
I was wondering if somebody has worked in a similar environment and which driver they used?
June 30, 2008 at 11:18 pm
Hi KB,
We are under the same environment on 32-bit windows 2003 server. I would appreciate if you could tell us what version of iseries you are using. Our version of iseries is V5R2M0 & for pulling data from DB2 we use IBM DB2 UDB for iseries IBMDA400 OLEDB provider.
Note:
iSeries Access for Windows V5R2M0 OLE DB provider (IBMDA400)
requires that Microsoft Data Access Components (MDAC) version 2.5
or later be installed on your system. The MDAC install file can
be downloaded from the http://www.microsoft.com/data web site. To
determine what version of MDAC you have, check the version of
the msado15.dll file or use the Component Checker, available from
the Microsoft web site.
Iam not sure whether it suits your query, but if at all you find a solution just share with us.
Regards,
Rajesh
July 1, 2008 at 1:35 am
MSDASQL x64 driver is available at: http://www.microsoft.com/downloads/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=en
I have downloaded this and was able to connect to Intersystems Cache using it.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 1, 2008 at 8:42 am
Thanks a lot for the link Jeffrey!!
July 2, 2008 at 6:35 am
Just a heads up, I have recently made this same move, using the IBMDASQL (though I have also tried IBMDA400) and I ran into a fun problem. When your SQL Server (server or just service) is rebooted or a certain period of inactivity is encountered, the linked servers will fail giving a provider error. When you check the provider properties, it will magically start working again. From what I gather Microsoft connects differently when you attempt to view the properties of a linked server of provider than when you try and execute a query, and the connection for the query needs to be "initiated". In order to resolve this problem, after a lot of web surfing and service calls, I eventually stubled on a site that had me create a procedure on the master database to "enumerate" the providers. This enumeration operation basically performs in the backend the properties check done through the front end and initiallizes the connection through the provider. After creating the procedure simply setting it to auto-execute on startup resolved this issue.
SE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[usp_enum_oledb_providers]
AS
exec sp_enum_oledb_providers
GO
sp_procoption 'usp_enum_oledb_providers', 'startup', 1
July 2, 2008 at 11:30 am
Thanks for the headsup MentalWhiteNoise! We actually reboot our SQL Server everyday and so I would have run into this issue pretty soon.
I am doing some testing using DB2OLEDB, IBMDASQL and IBMDA400 and I am actually seeing the best performance with DB2OLEDB. It is taking half the time for the same select query than IBMDASQL and IBMDA400.
I am not giving any information in provider string for IBMDASQL and IBMDA400 though, do you use provider string and for what type of information?
July 2, 2008 at 12:45 pm
I use the Provider String to set the default collection. Literally "Default Collection=DB2Collection" where DB2Collection is the name of the "library" or environment (I am not familiar enough with DB2 to know the correct term) that I need to access. We use several "collections", and the user I login with has access to several of them. Generally speaking, the Provider String appends any additional connection string parameters to the connection string built from the other parameters and options before establishing the connection.
July 2, 2008 at 1:28 pm
I am looking for a place to specify Block Size for the linked server.
The Network Packet Size for our SQL Server is 8192, I am not sure if linked server is using the same size or if it even makes any sense to specify Block Size for a linked server.
I literally gave 'Block Size=8192' in the provider string but am not sure if that is the way to do it! I cannot find any documentation on it.
July 2, 2008 at 1:44 pm
Ah! Sorry, I misunderstood what you were asking. That is beyond my current knowledge, though.
March 1, 2010 at 12:44 pm
Hi, I am using SQL Server 2008, Linked Server (MSDASQL.DLL) to connect to Inersystems Cache database. I am able to establish connection, but while viewing data, which is more the Varchar(40), its causing problem. I want to import data. into SQL Server for reporting purpose. Is there any way I could solve this. If I can establish the live connection, through Linked Server, then there will not be any need for me, to acutually download data.
Thanks
October 27, 2010 at 4:08 pm
How were you able to connect your intersystems Cache database to sql server.
Can you provide an example of your linked server connection string? Are you on a 64bit server.
I am having lots of trouble connecting to the cache server.
October 27, 2010 at 6:59 pm
Brad, please start a new topic for this request. This is a very old thread and you'll get many more people looking at your issue if you start a new thread.
I have setup linked servers from both x86 and x64 systems to Intersystems Cache. I am sure we can get you setup and working in no time.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply