Transferring data between SQL Server and legacy platforms such as the IBM AS/400 has traditionally been an awkward multistep process. First, you have to extract the data from the source database to a flat file, then you import it into the target database. In many cases, you have to select and preprocess the data before extracting it, massage the flat file to ensure that it's formatted correctly, or validate and manipulate the data before loading it into the target database—and sometimes you have to do all three. However, SQL Server's Data Transformation Services (DTS) utility solves these problems by using OLE DB and ODBC to provide a direct database-to-database link that you can use to efficiently transform and transfer data between two OLE DB—compliant databases.
Let's look at how you can use DTS to transfer data between SQL Server and IBM's DB2 UDB for iSeries database on an AS/400 system. Then, we'll examine how to get around some of the major data type and platform differences that make this process more complicated than just stepping through the wizards. (Note that although IBM changed the name of the AS/400 to iSeries, I use the older, more familiar AS/400 name in this article.)
AS/400 Prerequisites
To use DTS for transferring data between the AS/400 and SQL Server, you must be using SQL Server 7.0 or later, and the AS/400 must be running OS/400 (the AS/400 OS) V3R1 or higher. Before using ODBC to access the AS/400, make sure that the AS/400's Relational Database Name has been configured. To check this value, run the Work Relational Database Directory Entry (WRKRDBDIRE) command from the AS/400 command line. To check and change this value, you need Security Officer authority. If no Relational Database Entry exists, you can add a new one by entering option 1 (Add), then the name of the entry (usually the same as the name of the AS/400) into the blank column at the top of the display.
AS/400 Relational Database Name entries can refer to local or remote systems. To add an entry for the local system, enter the value *LOCAL into the Remote location field. You can leave the Type field blank; the Text field lets you add descriptive text.
After creating the AS/400's Relational Database Entry, make sure the required database server jobs are started. The AS/400 provides access to its database resources through its Host Database Server, which must be started before DTS or any other OLE DB- or ODBC-based program can connect to the database. Running the cwbping [host name or IP address] command on the client PC is a quick way to determine whether the AS/400's Host Database Server is started. (The cwbping command is part of the IBM Client Access for iSeries product, which I discuss later.) If the database server is running, you'll see the message Successfully connected to the server application: Data Access. To start the AS/400 database server, you can run either STRHOSTSVR *ALL or STRHOSTSVR *DATABASE from the AS/400 command line.
That takes care of the AS/400 requirements. But you still need to perform a couple of steps on the SQL Server side of the equation before you can begin transferring data.
SQL Server Prerequisites
To access the AS/400 from SQL Server, you must have an AS/400 OLE DB provider or an ODBC driver installed on the SQL Server system. For this article, I used the IBM Client Access ODBC Driver that comes with iSeries Access for Windows (formerly Client Access Express) V5R1. Microsoft provides the OLE DB Provider for AS/400, but you can get it only as part of Host Integration Server, which is a significant purchase if you just want to transfer data. Fortunately, the IBM Client Access OLE DB Provider and ODBC Driver are freely available to all licensees of OS/400, so you can use them without purchasing any additional products. In working with these two pieces of middleware, I had much better results using the Client Access ODBC Driver. It was fast and reliable and worked well with DTS and the Microsoft OLE DB Provider for ODBC. The IBM OLE DB provider was unreliable and often resulted in locked processes.
DTS can run from a client workstation or the server, but if you want to use the SQL Server Agent to launch scheduled DTS packages, you must install the IBM ODBC driver on the SQL Server system. Just installing the ODBC driver on one of the networked workstations won't work because the SQL Server Agent runs jobs on the server. To minimize the overhead on the SQL Server system, I used the Client Access Express Custom Installation option, which lets you install only the Client Access Base Services required for all installations and the data access components. This minimum installation doesn't copy the 5250-emulation program, the File Transfer program, the network file or print services, or any of the higher-overhead portions of Client Access Express. After installing the Client Access OLE DB Provider and ODBC Driver on the system, you need to create a data source to connect the SQL Server system to the AS/400.