Importing data from AS/400 DB2 into SQL Server database

Question

I have an old AS/400 system that's going offline on Wednesday, and I've been trying to import all of the data into a SQL Server database for backup purposes for about a week now, but I'm having trouble doing so.

The SQL Server has IBM System i Access for Windows installed, as well as the DB2OLEDB (Microsoft OLE DB Provider for DB2) installed. I have tried using both of these providers with the SQL Server Import and Export wizard, and while I have successfully created the connection and found the data I'm looking for, neither provider can seem to handle the data conversion. The DB2OLEDB will automatically locate any DECIMAL based fields, but that's about it. It cannot automatically find and convert CHARACTER NOT NULL fields, which is the majority of the fields. Here is the error I'm getting when I try to import:

Column information for the source and the destination data could not be retrieved, or the data types of source columns were not mapped correctly to those available on the destination provider.

The most common column error:

• Column "[column]": Source data type "CHARACTER" was not found in the data type mapping file.

There are THOUSANDS of tables in the AS/400, so manually scripting this out is absolutely not an option.

Is there an easier way? Is there a setting somewhere I'm missing?

Answer 1

Try setting "Allow unsupported character" in the advanced tab of "Conversions" in your system DSN for the as/400 in ODBC?

Answer 2

After a lot of research, I just couldn't get anything to work the way I needed. I ended up using the iAccess tools to just back it all up into Excel files. Took forever, but it got done.

Thanks for the help.

Answer 3

Reading your question I don't understand if the problem is creating tables. If this is your case, I mean, if your issue is to generate DDL in T-SQL language to get ready database tables, a solution may be to do a Reverse Engineering with a tool like SAP Sybase Power Designer. You have a trial version of Sybase to test if it fits your needs.

With Sybase PowerDeigner steps to create DDL from AS/400 to SQL Server are:

• Prepare a connection to AS/400 from sybase.
• Load a new PDM from AS/400 database.
• Generate CDM from PDM.
• Generate a PDM for SQL Server from CDM.
• Generate database t-sql script from PDM.
• Execute t-sql script in sql server.

In my opinion, PowerDesigner learning curve is not hard, but your issue covers all engineering logic.

At this time you can try to copy data from one database to another.