Use DTS to transfer data between SQL Server and the AS/400
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.)
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.
Configuring the Client Access ODBC Driver
To create a new data source that uses the IBM ODBC driver, open the ODBC Administrator on your SQL Server system and click Add to display the list of available ODBC drivers that are installed on the system. From the list, select the Client Access ODBC Driver (32-bit), then click OK to display the Client Access ODBC setup dialog box.
On the General tab, enter the name of the data source and identify the AS/400 system to connect to. I used the data source name (DSN) S1030438. The description field lets you enter a description to help you identify this data source later. The AS/400 System field can contain either the AS/400 system's name or its IP address. After entering the system information, click Connection Options to display the dialog box that Figure 1 shows.
The Connection options dialog box controls the authentication that the ODBC application uses to connect to the AS/400. The default value is Use Operations Navigator default. Operations Navigator is a GUI operations console for the AS/400, but it also stores central configuration information for IBM's Client Access applications such as the ODBC driver. The values that Operations Navigator stores are the same as the sign-on options in Figure 1. If Operations Navigator isn't installed or isn't used or you want to override its settings, you can control the ODBC data source's authentication through the values on the Connection options screen. In this case, I set the ODBC connection options to the value Use Operations Navigator default, which lets the ODBC connection use the same authentication as the other Client Access functions. Although it's not shown here, the Operations Navigator is set to use the Windows user name to log in to the AS/400. This login choice is best because it seamlessly passes your Windows login ID to the AS/400; however, it requires your Windows login to be the same as the AS/400 login, which isn't always possible. Specifying either Use the user ID specified below or None requires you to enter the AS/400 login information as part of the DTS package.
After specifying the connection information, go to the Server tab, which Figure 2 shows. The most important entry on the Server tab is the SQL default library field. This entry sets the default database (a library in AS/400 terminology) for the ODBC driver to use. Leave the Naming convention field at its default value of SQL so that the IBM ODBC driver will use the SQL object-naming standards rather than AS/400 object-naming conventions. Most ODBC client applications, including DTS, don't use the Library list field, so you're done configuring the IBM Client Access ODBC Driver for use with DTS. You can leave the other configuration screens at their default values.
Transferring Data from the AS/400 to SQL Server
After you've installed the ODBC driver and created the data source, transferring data from the AS/400 to SQL Server is a simple and straightforward process. To transfer data, you simply need to use the DTS Import/Export Wizard to build a DTS package, then execute the package. You can start the Import/Export Wizard from the Windows Start menu by choosing Programs, SQL Server, Import and Export Data.
On the Welcome screen, click Next to display the Choose a Data Source screen that Figure 3 shows. This screen lets you select the source of the data that DTS will copy. The Data Source drop-down box lets you select the OLE DB provider or the ODBC driver that DTS will use to connect to the data source. As Figure 3 shows, I used the Other (ODBC Data Source) option in conjunction with the name of the ODBC data source (S1030438) that I configured to use the IBM Client Access ODBC Driver to connect to the AS/400 database. The User/System DSN drop-down box lets you select an existing data source that contains the required AS/400 connection information. I selected the data source named S1030438 that I created earlier. Finally, in the Username and Password fields, you can supply the user ID and password that DTS will use to connect to the AS/400. If you chose to use your Windows login, you can omit these parameters.
Clicking Next displays the Destination dialog box that Figure 4, page 34, shows. This dialog box lets you select a destination for the data. The Destination drop-down box lets you select the OLE DB provider or the ODBC driver that DTS will use to connect to the target data source. Here, I selected the Microsoft OLE DB Provider for SQL Server. Near the bottom of the screen, you can see that the destination is a database named SQLMag that I previously created on the SQL Server.
You configure the next screens essentially the same as if you were using a SQL Server—to—SQL Server DTS transfer. Clicking Next displays the DTS dialog box that lets you select the type of transfer to use. You can either use a SQL query to extract the data from the data source or specify that you want to copy an entire table. Clicking Next displays the Select Source Tables and Views dialog box, which Figure 5 shows.
This box displays a list of the available tables in the source database. The library you specified in the Default library field on the IBM ODBC driver's Server tab determines the tables displayed here. In this example, all the files for the AS/400 library named QGPL appear in the list. As Figure 5 shows, I selected the table named PK2000PF. One important thing to note is the AS/400 object-naming syntax that the ODBC driver reports back to DTS. All the AS/400 file names use a three-part naming syntax in which the first part of the name identifies the data source, the second part identifies the AS/400 library, and the third part identifies the AS/400 file name. Unlike with SQL Server transfers, in which the names are bracketed, the three AS/400 names are enclosed in quotation marks. This naming system doesn't cause problems when you're transferring from the AS/400 to SQL Server because SQL Server automatically strips off the quotes. However, as I show later, that isn't the case when you try to send data back to the AS/400.
Now you've done all the work that's required to create the DTS package and transfer a file from an AS/400 DB2 UDB for iSeries database to a SQL Server database. Clicking Next on the Select Source Tables and Views screen displays additional dialog boxes that let you save your DTS package, then either run the DTS package immediately or schedule it to run at a predetermined time. Choosing to run the package immediately starts the database transfer and displays the Executing DTS Package dialog box.
That's all you need to do to transfer data from the AS/400 to SQL Server. The Client Access ODBC Driver takes care of all the required data-conversion details and handles common data values with no problems. For example, it automatically handles the data-type mapping and conversion of EBCDIC (the AS/400's native data type) to ASCII. You can run into problems, however, in areas where the two systems don't support the same data types. Table 1 shows the automatic data-type conversions the IBM ODBC driver supports for DB2 UDB for iSeries—to—SQL Server data transfers. In cases where automatic data conversion isn't supported because of data-type incompatibilities, you need to either omit the columns with data-type incompatibilities or manipulate the data as it's being transferred by using an ActiveX script in conjunction with DTS's data-transformation capabilities. (For information about creating transformations in DTS, see Don Awalt and Brian Lawton, "Creating DTS Custom Transformations," October 1999, InstantDoc ID 6110.)
Another area where you might experience difficulties is in dealing with the AS/400's multiple-member files. Unlike SQL Server, the AS/400 supports a database concept known as members, which are something like sub-tables. A table can contain multiple members, where each member shares the same schema but has a unique name and unique data contents. The sidebar "Handling Multiple-Member Files," page 32, tells how to work with AS/400 multiple-member files.
Transferring Data from SQL Server to the AS/400
Using DTS to transfer data from SQL Server to the AS/400 is a lot more work than importing AS/400 data to SQL Server, and you can't use the same simple point-and-click techniques. Fortunately, DTS has the flexibility to make all the necessary adjustments for uploading SQL Server data to the AS/400. Like importing data from the AS/400 to SQL Server, to export data to the AS/400, you need to build a DTS package by starting the Import/Export Wizard.
In the data-source selection screen that Figure 6 shows, you use the Data Source drop-down box to select the Microsoft OLE DB Provider for SQL Server to connect to the SQL Server system. In the Server field, enter the name of the SQL Server system that contains the source database (in Figure 6, this is TECA4). Next, select the authentication method. This example uses Windows authentication to connect to the SQL Server. The Database drop-down box shows that the SQL Server Northwind database contains the tables to transfer.
After you specify the data source, clicking Next displays the Choose a destination dialog box that Figure 7 shows. In the Destination drop-down box, I've chosen the Client Access ODBC Driver (32-bit) to connect to the AS/400; DTS will use the data source name S1030438 to obtain the specific connection properties, including the name of the AS/400 to connect to. In the Username and Password fields, supply the user ID and password that DTS will use to connect to the AS/400. Again, if you set your DSN to use the Windows login information, you can omit these parameters. Clicking Next displays the Specify Table Copy or Query dialog box, which lets you select whether to transfer by using a query or by copying an entire table. Choose to copy a table, and click Next to display the screen that Figure 8 shows.
On this screen, I selected the Customers table from the SQL Server sample Northwind database. So far, this setup has been similar to the earlier example of transferring the data into SQL Server. However, the similarities end here. Recall that the AS/400 used a three-part naming scheme to identify files. In Figure 8, although DTS has generated a three-part name for the table, it consists of the DSN, the user name (instead of the library name), and the target table. This type of naming scheme won't work for transfers to the AS/400 unless the target library happens to have the same name as the user. That's typically not the case, so you need to change the middle section of the name to the correct AS/400 library.
In addition, the destination name here is initially enclosed in quotation marks. The SQL Server OLE DB provider automatically strips off the quotes, but the IBM Client Access ODBC Driver doesn't. If you used these default values, the transferred tables would have quote characters included as part of their AS/400 object names. Fortunately, the DTS Import/Export Wizard lets you manually edit the generated destination table names by simply positioning your cursor on the name and typing over the existing value. The first step to customizing this DTS package to work with the AS/400 is to remove all the double-quote characters, then insert the correct three-part object name the Client Access ODBC Driver uses. Figure 8 shows these changes made in the Destination column: I removed the double quotes and used the three-part name for all the tables. In this example, DTS will copy the Customers table to the AS/400 system identified by the S1030438 DSN and into a library named QGPL.
The next step is to adjust the data types and column names for each table that you want to transfer. Clicking the Transform button to the right of the Destination column lets you customize the data types that DTS will use when it builds the target table. Figure 9, page 36, shows the Column Mappings and Transformations dialog box that's displayed when you click the Transform button.
On the Column Mappings tab, you can change the data types for each column. To change the destination data type on the Column Mappings tab, click the value displayed in the Type column. An edit box opens that lets you modify the destination data type. Table 2, page 36, shows how SQL Server data types map to AS/400 data types. Next, if you're creating a new table on the target AS/400, click the Edit SQL button to display the Create Table SQL Statement dialog box that Figure 10, page 36, shows. Here, you can change the SQL statement that the Import/Export Wizard generated to match the SQL syntax that the AS/400 expects to see.
The Import/Export Wizard initially generates the CREATE TABLE SQL statement that DTS will send to the AS/400. The wizard works with the ODBC driver to determine the correct data types, but in my experience, it's often wrong. However, you can freely edit this SQL statement. To make the DTS export to the AS/400 work, remove the double-quote characters that enclose the qualified table name and each column name. Then, delete any parentheses that the Import/Export Wizard appends to numeric data types. If you don't remove the double quotes, DTS will create the columns in the AS/400 destination file with the double-quote characters as part of the field name. You must remove the parentheses after the numeric data types to avoid generating the SQL syntax error message SQL0104 — Token was not Valid when the DTS package executes.
You also need to make sure that you're using the correct data types. If the wizard didn't generate the correct types or sizes, you need to manually adjust them in the CREATE TABLE statement. Figure 10 shows the SQL statement in the process of being changed. The first six columns have had the double quotes removed; the Region, PostalCode, and Country columns contain their original values.
After making the adjustments to the destination table names and data types, click Next to display the two Import/Export Wizard dialog boxes that let you save the DTS package and schedule its execution. Selecting the option to run the DTS package immediately starts the transfer to the AS/400. A progress bar indicates how much of the transfer has completed; the check boxes in the lower part of the dialog box tell which steps in the DTS package have completed.
When running multiple-file DTS transfers, you'll quickly notice that DTS doesn't execute the package steps one at a time. Instead, DTS is multithreaded, which means that, by default, it simultaneously launches four threads. Although this usually works, I've occasionally experienced errors where one or more transfer steps fail when I'm running multithreaded DTS transfers to the AS/400. If you intend to run the DTS package unattended, I recommend altering the DTS package properties and setting the default DTS threading to one, thereby limiting the package to executing only one SQL statement at a time.
It Works Both Ways
Like the classic story of Dr. Jekyll and Mr. Hyde, using DTS to transfer data between SQL Server and the AS/400 has two faces. Transferring data from the AS/400 to SQL Server is an easy process, but that's certainly not the case when you turn around and try to transfer data from SQL Server to the AS/400. Although transferring data from SQL Server to the AS/400 can be difficult to set up the first time, once you've learned how, you can set up the SQL Server—to—AS/400 connection to work flawlessly. DTS's power and flexibility let it handle platform incompatibilities and make its database-transfer mechanism vastly superior to performing multistep flat-file transfers.