March 2, 2009 at 3:06 am
Hi
Our current Datawarehouse is running SQL Server 2000 Enterprise SP3a on a Windows 2000 Server Standard SP4 box. We also have a iSeries running V5R4M0. Now we have a SQL Server linked server connecting to the iSeries using the iSeries Access ODBC Driver (version 11.00.08.00) to query the iSeries via Query Analyser which works fine.
But now we are in the process of building a new Datawarehouse running on SQL Server 2005 Enterprise SP2 (soon to be SP3) on a Windows 2003 Enterprise SP2 box. I've created a linked server to the iSeries from this server in the same fashion has on the old box.
And here's the issue, linked query performance on the old Datawarehouse is much faster than on the new Datawarehouse and I'm at a loss to explain it.
Does anyone have any ideas??
Thanks
March 2, 2009 at 5:57 pm
SQL Server 2005 Enterprise edition comes with DB2 OLE DB driver you can use or you could use IBM ADO.NET provider for .NET.
Kind regards,
Gift Peddie
March 3, 2009 at 1:42 am
Thanks for your reply.
However I have already tried using Microsoft's DB2OLEDB provider without success. Although this might be down to how I configured the linked server. Do you know of any documentation I could follow to configure this provider?
Thanks again.
March 3, 2009 at 1:44 am
Ignore the last post, just found a old Microsoft article (from 2004) so will have a read through this first.
March 3, 2009 at 6:32 pm
The linked server configuration tool is under server objects in management studio and you just need to add your OLE DB driver and decide if you want to use security or not.
Kind regards,
Gift Peddie
March 4, 2009 at 1:36 am
Gift
Not quite what I meant, while I know how to configure linked servers using SSMS what I actually meant was the exact configuration of setting up a linked server to a iSeries using either a IBM or Microsoft DB2 provider. Where as a linked server to another SQL Server is easy using another data source requires the appropriate provider being selected, then the product name, data source, provider string and catalog fields being completed. What I need is an example of a linked server to an iSeries that I can follow.
Thanks
March 6, 2009 at 6:49 pm
I sorry it took so long but here is a thread with many IBM connection configuration code let me know if you can use it. There are specific code in the code project article and the IBM provided article.
Kind regards,
Gift Peddie
March 9, 2009 at 2:44 am
I've had another go at creating a linked server this morning. Now on our old SQL Server 2K box the linked server is configured using IBM's own ODBC client access driver. To use this linked server we would use SQL similar to the following
select * from AS400.S65930BF.MMPLPLIB.
AS400 is the name of the linked server, S65930BF is the db schema on our iSeries while MMPLPLIB is one of the catalog's beneath the schema.
This is the command I tried this morning to create a linked server on our SQL Server 2K5 box using IBM's IBMDA400 OLE provider
EXEC master.dbo.sp_addlinkedserver @server = N'AS400B', @srvproduct=N'IBMDA400', @provider=N'IBMDA400', @datasrc= ;Catalog Library List=MMPLPLIB''', @catalog=N'S65930BF'
This creates the linked server ok but when I attempt to use it in a query I get the following error
Cannot create an instance of OLE DB provider "IBMDA400" for linked server "AS400B".
March 9, 2009 at 2:47 am
Just to clarify the SQL we use against the linked server is
select * from AS400.S65930BF.MMPLPLIB.afilename
The descriptor I used for the filename part of the query was omitted when I posted.
May 8, 2009 at 3:18 pm
I'd sure like to know if you get this figured out... I tried previously to add a linked server but gave up in frustration. I have been pulling data from the AS400 into our Intranet using Visual Web Developer 2008, and that works well. I now need the ability to write queries using data from the AS400 and a SQL database, which is why I'm revisiting the Linked Server setup. Here are my details:
OBDC System DSN setup using: iSeries Access for Windows ODBC data source
DSN name = AS400
SQL default library = M800DAT200 (using SQL naming convention)
Default package library = QGPL
(don't know if this is needed - is the General Purpose Library
I used a .udl file to create the connection string:
Provider=MSDASQL.1;Password=xxxxxx;Persist Security Info=True;
User ID=XXXXXXL;Data Source=AS400;Initial Catalog=RCHASE5C
Thanks.
May 10, 2009 at 3:45 am
Yeah I did get it working in the end although I'm still not sure of its performance.
What version of SQL Server you using? If its 2K5 Enterprise then download the DB2 OLEDB driver from Microsoft.
Below is the script I used to configure the linked server. I've removed certain names has they are company specific.
/****** Object: LinkedServer [AS400] Script Date: 03/10/2009 09:28:30 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'AS400', @srvproduct=N'DB2OLEDB', @provider=N'DB2OLEDB', @datasrc=N'server to link', @provstr=N'Provider=DB2OLEDB;Initial Catalog=;Network Transport Library=TCP;PC Code Page=1252;Network Address=;Package Collection=;Default Schema=;Process Binary as Character=False;Units of Work=RUW;DBMS Platform=DB2/AS400;Defer Prepare=False;Rowset Cache Size=0;Persist Security Info=False;Mode=Read;Connection Pooling=False;Derive Parameters=False;', @catalog=N'intial catalog'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'AS400',@useself=N'False',@locallogin=NULL,@rmtuser=N'username',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'AS400', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'AS400', @optname=N'use remote collation', @optvalue=N'true'
May 10, 2009 at 12:10 pm
Glad to see you got it working.
Kind regards,
Gift Peddie
May 11, 2009 at 9:28 am
Our SQL server is 2005, and I'm using MS SQL Server Management Studio to set up my connection. I was able to get a linked server configured using the Microsoft OLE DB Provider for ODBC Driver. Through the New Linked Server window I choose the driver, entered AS400 as the Product name & Data source and set Collation Compatible & Data Access to True. I can't see tables but can execute queries.
Using the DB2 OLEDB driver, are you able to drill in and see tables?
May 11, 2009 at 9:44 am
You need the Enterprise edition of SQL2K5 in order to use the DB2 OLEDB provider.
In order to move through the tree view of tables (which is what I assume you mean by drilling)available on the linked server you need to be using a version of the Management Studio other than the Express Edition.
I say this because on my box I have the Express edition installed but cant view any of the tables. But can see the linked servers. While on the server itself where the Enterprise edition is installed all the catalogs, libraries and tables are fully exposed.
May 11, 2009 at 10:08 am
Thanks, I'll look into another version.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply