15 May

Integrating SQL Server and the IBM iSeries

Why?

The IBM iSeries servers made quite a name for themselves in the late 80’s and early 90’s. Many large corporations invested in these systems and have spent years customizing them. If you have access to one of these systems and are trying to connect to a Microsoft SQL Server, this is the document you have been looking for. I will briefly discuss the setup and then provide some examples for access the db2 file system using SQL.

The software used in this example was SQL Server 2014 version 12.0.2000.8.

The iBM iSeries was an AS400 R14.

This information should work for most previous versions also.

The setup

In order to get SQL Server talking to the db2 filesystem, IBM Client Access needs to be installed on the SQL Server. Once installed you will see a list of additional providers in SQL Server Management Studio.

We will be using the IBMDA400 provider. Ocasionally, I have had to edit the properties mostly by enabling in-process in order to get it working on some systems.

Next, you will need to create a linked server. The product name and the datasource need to be the dns name of the iSeries machine. Choose IBMDA400 as the provider. On the security page put in a valid iSeries username and password. Save & Test the connection.

The final step will be to construct a SQL statement that can properly call the iSeries db2 file system.

Pay close attention to the machine/table name. QAS400 is the linked server name, S1017583 is the serial number on the iSeries (You can find this using the iSeies Navigator installed with iBM client access), QS36F is the library, and LBRTAGS is the filename.

SELECT * FROM QAS400.S1017582.QS36F.LBRTAGS