In the data world, being able to work from a familiar platform sourcing data from a third-party system is gold. This is why most data warehousing tools operate from a chosen database system. In this article, we will focus on getting connected to a iSeries/AS400/DB2 environment, via a Linked Server on SQL Server, all this through the Microsoft OLEDB provider for DB2. This provider is widely reported as being faster for data transfer.
Why is it of Interest?
SQL Server is a widely used database platform and one that is recognized to allow developers to be productive. Infor (and Lawson) Business Intelligence are widely reliant on SQL Server for their BI backend. Infor Lawson BI only supports SQL Server. Some Infor Lawson (S3) customers are using AS400, while a bigger chunk of Infor M3 customers leverage the same platform.
Leveraging iSeries/AS400/DB2 data from SQL Server is a productivity boost for day-to-day data analysis – be it during the early stages of the development of a report or when managing a standalone data warehouse other than Infor’s BPW. You can keep writing SQL Server’s SQL code against AS400 databases, leaving AS400 database administration and troubleshooting behind.
The following will only work for the following versions of SQL Server: Enterprise, Enterprise Evaluation and Developer.
The first thing you will need to transfer data via the Microsoft OLEDB driver is a Microsoft’s piece of software. There are two main versions of interest, 3.0 and 4.0. Version 3 is for SQL Server 2005 and 2008 while version 4 is dedicated to SQL Server 2012. Click on the relevant ling below:
Once installed, the strategy consists in creating a Linked Server from a SQL Server instance using the freshly installed driver to an AS400 instance. While the Linked Server can be created directly from SQL Server Management Studio (SSMS), we have found it easier to use Windows and its UDL (Universal Data Link) feature to help us in that regard, and get the data source connectivity right. UDL will assist in creating a “connection string” that will be useful for the Linked Server definition.
Universal Data Link (UDL)
To our knowledge, UDL comes packaged with any Windows Server, and possibly with desktop versions of the Windows OS. A UDL file contains a text connection string that is created via a graphical interface. To create a UDL file, simply start with a text document anywhere on your machine, and rename it to include the udl extension.
Once validated, Windows should associate the right icon to your file.
Double click on your file to launch the graphical tool. This file will end up defining a data source to our AS400 environment.
Under the Provider tab, you will see a list of drivers available to define the UDL against. Select Microsoft OLE DB Provider for DB2. It was installed in the previous step.
Under the Connection tab, key the datasource name (host), credentials (of a data base user with read access), and catalog/default schema.
Note that the catalog is the DB2 catalog. It maps to the catalog you implicitly use when querying the system. For instance, in the following query:
- SELECT * FROM MVXJDTA.M3EDBTST.OCUSMA
“MVXJTDA” would be the catalog.
Under Package collection and Default schema, your schema, or “M3EDBTST” in our example can be keyed in.
Under TCP/IP Connection details, we added the server IP address but keps the default network port. You may ask you DBA for those details.
Under the Advanced tab, there was no need to change default values except for DBMS platform that we set up to DB2/AS400.
A great feature coming with UDL is the ability to test the data source.
We now have connectivity to the database server. Let’s save these properties by hitting OK. This will update the UDL file.
In preparation for the creation of a Linked Server, the idea now is to capture the OLE DB Connection string from the UDL file. To do so, simply open the UDL file in Notepad.
Copy the connection string (starting with “provider”) to your clipboard, all the way to the end of the line.
The UDL allowed us to create the connection string to our AS400 server. It is now time to use this to create a Linked Server under SQL Server.
SQL Server Linked Server
A Linked Server lives in SQL Server Management Studio (SSMS), under the database engine.
Connect to the database engine using your usual credentials.
Browse to the Server Objects folder. Right Click, and select New Linked Server.
We defined the following fields:
- Linked Server: default to the DB server host name
- Server Type: Other Data Source
- Provider: Microsoft OLE DB Provider for DB2
- Product Name: default to database name (e.g.: M3EDBTST)
- Data source: default to the DB server host name
- Provider string: paste the string obtained from the UDL
- Catalog: default to the DB catalog name
OK this screen. SQL Server performs a final check upon validation.
We know have a new entry under SQL Server’s Linked Server section.
Querying the Linked Server
The beauty of this technique is that SQL Server’s features can now be leveraged despite our “end server” being DB2/iSeries/AS400. That may facilitate analysis work as SSMS is superior to the iSeries platform when it comes to development.
Simply put together, to query the Linked Server (AS400) from SQL Server Management Studio, open a new query window, and using the same semantics as above, type:
SELECT * FROM <LINKED_SERVER_NAMNE>.<CATALOG>.<SCHEMA>.<TABLE>
That will allow you to query iSeries straight from SQL Server.