Move data from iSeries to DB2 Express?

Question

We need to migrate a database from DB2 for i (iSeries AS/400) to DB2 Express. We do not have access to the iSeries, which is managed by a different company, so a direct ODBC/JDBC connection isn't an option. We need them to export their schemas and data into a format that we can import here.

So far, they've sent us a set of field definition files (*.FDF), but I haven't been able to figure out how to do much with them (learning DB2 on the fly here).

I've familiarized myself with db2move, db2look, and db2relocate, but those utilities aren't available on the iSeries. The group that owns the iSeries would prefer to not have to purchase IBM's DB Connect product, if it can be avoided, though that might be negotiable.

If it helps, we will only be reading data from our DB2 Express instance, so we aren't worried about migrating triggers or similar behaviors.

I feel like this should be an easy web search but I've been scrounging all morning and haven't made any progress. It seems like the iSeries is a world unto itself!

Charles Charles · Accepted Answer · 2015-11-03T18:35:13

IBM i (aka AS/400 aka iSeries) is it's own world...primarily because it's origins predate SQL. Additionally, the DB is deeply embedded into the OS.

That being said, it's arguably one of the most SQL standards compliant DB's. Assuming a relatively recent version of the OS and a modern skill set, this task shouldn't be that difficult. You may hear some terms you're not familiar with, but there are equivalents.
Database = Database
Library = Schema
Physical File = Table
Logical File = Index and/or view

FDF file? Does it look like this:

[Data Transfer File Description]
FieldCount=13
FileType=1
Version=2
[Options]
DateFormat=1
DateSep=1
DcmlSep=1
TimeFormat=1
TimeSep=1
[F0001]
Length=7
Name=ITEM
Type=1
[F0002]
Length=30
Name=IDESCR
Type=1

That's designed for IBM i Access (aka Client Access) Data Transfer utility....probably not going to do you much good.

Another part of IBM i Access is called IBM i Navigator, basically a GUI interface into the OS. In the navigation tree is a node, Databases. Most IBM i systems only have a single DB defined. Under the DB's are nodes for the Schemas. By default, only a subset of schemas are displayed. You can right lick on the DB and select the schemas to display. Underneath the schema is a node, Tables. You can right click on the Tables node and select Generate SQL

The generated SQL DLL can be placed in different places, including a local PC file. Taker note of the options tab, you can choose between ANSI/ISO and DB2 (with or w/o extensions).

That gives you the SQL DDL for all the tables. You can actually generate SQL DDL for the entire schema at once. But for your purposes, the tables are probably enough. Assuming an older app, there's probably non-SQL triggers, constraints and views/indexes. In particular, most the "views" shown are actually DDS defined keyed logical files. But in the SQL world, views can't be keyed.

Lastly, as far as data export. Take a look at the . Copy to Import File (CPYTOIMPF) command which exports data from a table into a fixed or delimited stream file in the Integrated File System (IFS)

EDIT If you could connect directly to the system, you'd be better off. Perhaps even making use of the IBM Data Movement Tool (MTK)

Move data from iSeries to DB2 Express?

1 Answers