Access AS400/DB2 with SQL 2008 Express

Question

I'm trying to add the AS400 as a linked server in SQL 2008 Express in order to access some tables in a DB on the iSeries. I've tried all of the OLE providers with many different strings and have had no luck establishing a connection. Is this even possible with this version of SQL?

Thanks

Answer 1

I've had trouble with the AS/400 OLE DB Providers as well. So I cheated and used ODBC instead. Create a system DSN to connect to your AS/400. Then create the linked server like this:

EXEC sp_addlinkedserver 
   @server = 'MyServer', 
   @srvproduct = '',
   @provider = 'MSDASQL', 
   @datasrc = 'AS400'

In the prior example, I gave my system DSN the name of "AS400". Within SQL Express, the server is called "MyServer". Here's the problem with this solution: I used the iSeries Access ODBC provider and, by default, I don't store my password on my local machine. So I had to make sure I was connected to the AS/400 by bringing up a green-screen session first.

Anyway, once the linked server definition is added and you have an active connection, or a password issue worked out with your ODBC connection, you just run a query to get your data like this:

SELECT * FROM MyServer.MySystem.MyLibrary.MyFile

Where "MyServer" is the name you put in the @server= part of the sp_addlinkedserver call. "MySystem" is your AS/400 database name. You can determine this value by logging on to green screen and using the WRKRDBDIRE command. The entry with the remote location of *LOCAL is your database name. "MyLibrary" and "MyFile" are self explanatory.

It feels like a kludgy solution, but it does work.