Connect to IBM i server from Sql Server 2008 R2

Question

I have a big deal: I have to connect (I just want to read data, not to write) to my customer's IBM AS/400 (aka iSeries, now IBM i) server...

I think I have all parameters needed (given me by the AS/400 programmer), but I can't figure out which driver I have to use, and if I have all software needed to to this!

I've installed IBM AS/400 ClientAccess 5.8 driver (with a patch for latest OS), and now I'm trying to configure a new Linked Server in my Sql Server 2008 R2 (x64) server.

First problem: Which driver should I use?

I have so many choices (but maybe none of these works!!):

IBM DB2 UDB for iSeries IBMDASQL OLE DB Provider
IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider
IBM DB2 UDB for iSeries IBMDARLA OLE DB Provider

...or maybe other generic OLEDB/ODBC drivers?!

Second problem: Where should I put my parameters (in which fields I mean!)

In any case, in I choose a provider for my Linked Server, obviously I have to set my parameters...but I only have this:

• Username of an ADMIN user
• Password of the ADMIN user
• AS/400 server IP address
• The name of "main" archive, in which my data are stored (something like ACG_DATV2)

Third problem: How should I write my queries? How to reference an AS/400 "archive" and tables?

I don't know how to build my reading query: where are tables and views (?!) stored and how can I reference them?

Thank you in advance!

Answer 1

I think there are many ways to achieve what you want, but I'll try to explain what I would do in your case.

With that version of IBM ClientAccess (and also the patch), I should not have troubles in getting connected with a Sql Server 2008 R2 Linked Server if you use the correct data provider.

First of all, try to configure your linked server in this way:

• Linked server name: what you want, it's just a custom name (example: MYAS400)
• Provider: IBM DB2 UDB for iSeries IBMDASQL OLE DB Provider
• Product name: not important, something like iSeries Access OLEDB Driver
• Data source: AS/400 server IP address (example: 192.168.0.1)

Either if you configure the Linked Server using the wizard or by SQL code, the first time it will need to access the data of the AS/400 server, you'll be asked for credentials, with a typical iSeries window (look at my example).

Set your username (User ID) and the relative password, without regarding the case of the strings!!

As a general tip (but this is only related to my experience!), try to avoid special characters and upper/lower cases distinctions...

If you arrive here (no problems in the Linked Server creation), the Linked Server should work (so the first and the second problem are solved): let's build the first query!

Once the Linked Server is created, all you need is just to correctly reference the archive, the library and, of course, the correct table and the Linked Server (by name): with this informations, build a query like this (it's the usual T-SQL syntax):

SELECT
    (Field1)
    , (Field2)
    , *
FROM (Linked Server Name).(Catalog Name).(Library).(TableName)

The only information you're probably missing is the "archive": you can easily find it browsing the Catalogs tree inside your new Linked Server, or just use iSeries Access Navigator tool!

So, in your case, I think the query should be (more or less):

SELECT
    FILIO
    , DTVLD
    , DTVLA
    , SEQZA
    , CFIMP
    , PADRE
    , TPVLD
    , CMVLD
    , *
FROM MYAS400.S242DA0A.ACG_DATV2.ANLE200F

Note that S242DA0A is valid only in my case...

Remember also that:

• AS/400 will probably ask you for credentials very often: also if you close and reopen SSMS.
• Performance?...better to talk of something else :) ... extract the tables in your Sql Server tables and query them from there! Do it with a simple: SELECT (Fields) INTO myTable FROM (AS/400 table)
• I've tried this process many times, I didn't have many troubles (once I get skilled about!)...but only for reading data (as you asked)! Never tried to update data!!!

GOOD LUCK!