将数据从 iSeries 移动到 DB2 Express? [英] Move data from iSeries to DB2 Express?

问题描述

我们需要将数据库从 DB2 for i (iSeries AS/400) 迁移到 DB2 Express.我们无法访问由不同公司管理的 iSeries，因此不能选择直接的 ODBC/JDBC 连接.我们需要他们将他们的架构和数据导出为我们可以在此处导入的格式.

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.

到目前为止，他们已经向我们发送了一组字段定义文件 (*.FDF)，但我无法弄清楚如何使用它们(在此处快速学习 DB2).

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).

我已经熟悉 db2move、db2look 和 db2relocate，但这些实用程序在 iSeries 上不可用.拥有 iSeries 的集团宁愿不必购买 IBM 的 DB Connect 产品，如果可以避免的话，尽管可以协商.

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.

如果有帮助，我们将只从 DB2 Express 实例中读取数据，因此我们不必担心迁移触发器或类似行为.

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.

我觉得这应该是一个简单的网络搜索，但我整个上午都在搜寻，但没有取得任何进展.似乎 iSeries 本身就是一个世界！

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!

推荐答案

IBM i(又名 AS/400 又名 iSeries)是它自己的世界...主要是因为它的起源早于 SQL.此外，数据库深深嵌入到操作系统中.

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.

话虽如此，它可以说是最符合 SQL 标准的数据库之一.假设一个相对较新的操作系统版本和现代技能组合，这个任务应该不会那么困难.您可能会听到一些您不熟悉的术语，但也有等价的.
数据库 = 数据库
库 = 架构
物理文件 = 表
逻辑文件 = 索引和/或视图

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 文件?是不是像这样:

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

这是为 IBM i Access(又名客户端访问)数据传输实用程序设计的......可能对您没有多大用处.

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

IBM i Access 的另一部分称为 IBM i Navigator，基本上是操作系统的 GUI 界面.在导航树中有一个节点，Databases.大多数 IBM i 系统只定义了一个 DB.在 DB 下是 Schemas 的节点.默认情况下，仅显示模式的子集.您可以右键单击数据库并选择要显示的模式.架构下方是一个节点，Tables.您可以右键单击 Tables 节点并选择 Generate SQL

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

生成的 SQL DLL 可以放在不同的地方，包括本地 PC 文件.请注意选项选项卡，您可以在 ANSI/ISO 和 DB2(带或不带扩展名)之间进行选择.

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).

这为您提供了所有表的 SQL DDL.实际上，您可以一次为整个架构生成 SQL DDL.但出于您的目的，这些表格可能就足够了.假设一个较旧的应用程序，可能有非 SQL 触发器、约束和视图/索引.特别是，大多数显示的视图"实际上是 DDS 定义的键控逻辑文件.但在 SQL 世界中，视图不能被键入.

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.

最后，就数据导出而言.看看.复制到导入文件 (CPYTOIMPF) 命令 将数据从表导出到集成文件系统 (IFS) 中的固定或分隔流文件

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)

编辑如果你可以直接连接到系统，你会更好.甚至可以使用 IBM Data Movement Tool (MTK)

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)

这篇关于将数据从 iSeries 移动到 DB2 Express?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！