包含生成列的 DB2 表上的数据移入和移出 — 入门

文档选项 未显示需要 JavaScript 的文档选项

级别： 初级

Paul C. Zikopolous, IBM 加拿大

2003 年 3 月 01 日

生成列（包括标识列）是 DB2 中的一个重要特性，用于自动生成列值。作者 Paul Zikopoulos 帮助您，让您了解有关如何移动该数据的选项。

简介

生成列是 IBM® DB2® Universal Database™ 的一个便捷特性。 一个生成列（generated column）是从表达式而不是从 INSERT 或 UPDATE 操作派生其值。

回页首

生成列和标识列

请参考下列数据定义语言（DDL），它将在数据库中创建一个表：

db2 create table db2admin.actor (
     actor_id int generated by default as identity , 
     actor_name varchar(20) , 
     act_yr_of_birth smallint ,
     act_yr_of_death smallint ,
     age_at_death smallint generated always as (act_yr_of_death - act_yr_of_birth) 
    )
    in userspace1
db2 alter table db2admin.actor
    add primary key (actor_id)

该 DDL 包含生成列的简单示例。通过使用下列表达式，AGE_AT_DEATH 列由 DB2 自动生成：

ACT_YR_OF_DEATH - ACT_YR_OF_BIRTH = AGE_AT_DEATH 

通过在 CREATE TABLE 或 altER TABLE 语句中为列指定 GENERATED ALWAYS AS ... 子句，可以在表中创建生成列。

我的示例还包括特定类型的生成列（称为标识列）的定义。 标识列包含可由 DB2 自动生成的数值。对于创建主键值而言，标识列确实有用。实际上，该示例包含一个 altER TABLE 语句，它将 ACTOR_ID 列定义成 ACTOR 表的主键。

始终由 DB2 生成：DB2 可以保证以 GENERATED ALWAYS 方式定义的标识列的唯一性，因为它的值始终是由 DB2 生成的。

我使用了 可以一词，因为许多用户误解了这种唯一性保证。这可能对也可能不对，它取决于您如何定义标识列的属性。例如，假定您已经定义了一个标识列，它在最小值 1 和最大值 10 之间循环：根据定义，除非该表包含的行非常少（10 行或更少），否则该列中的值将不唯一。

保证列值唯一性的方法就是对列定义唯一性约束。上述表中的 actor_id 列之所以能够保证唯一性，是因为我使用了 alter table 命令将该列定义为表上的主关键字。您是不是想知道为什么我选择使用标识列，这是因为我们可以在不产生与设置表计数器有关的并发性问题的情况下生成数值。

用户提供的标识：以 GENERATED BY DEFAULT 方式定义的标识列将接受由应用程序提供的值。如果应用程序不为带有这类列的表提供标识值，则 DB2 会为您生成那些值（但是它不能保证标识值的唯一性，除非已经为该表定义了唯一约束 — 就象我的示例那样）。

在我的 actor 表中，我以 GENERATED BY DEFAULT 的方式定义了 actor_id 列，这意味着，如果我愿意，那么我可以插入自己的 actor 标识。例如，下列 INSERT 语句将三个新的行添加到 ACTOR 表：

db2 insert into db2admin.actor (actor_id, actor_name, act_yr_of_birth)
    values (150,'Bruce Willis',1955), 
           (default,'Tom Cruise',1962),
           (default,'Tommy Lee Jones',1946)

在上面的示例中，为第一行指定了标识值（在本例中是 150），DEFAULT 关键字用来说明 DB2 将为其余行生成标识值。在运行了这个 INSERT 语句后，您可以看到由 DB2 为 Tom Cruise 和 Tommy Lee Jones 生成的值，还可以看到用户为 Bruce Willis 提供的值 150。通过查看如 图 1所示的 ACTOR 表的内容，您可以验证这一点。

如果我以 GENERATED ALWAYS 方式（不是 GENERATED BY DEFAULT 方式）定义 actor_id 列，那么要成功地完成运行，本示例中的 INSERT 语句将不得不指定 DEFAULT 关键字，而不是值 150；否则应用程序将收到 SQL00798 错误。

自己动手试着进行下面的工作。利用与创建 ACTOR 表相同的 DDL 创建名为 ACTOR2 的新表，只是对下面的 粗体部分进行一些更改：

db2 create table db2admin.
        actor2 (
     actor_id int 
        generated always as identity , 
     actor_name varchar(20) , 
     act_yr_of_birth smallint ,
     act_yr_of_death smallint ,
     age_at_death smallint generated always as (act_yr_of_death - act_yr_of_birth) 
    )
    in userspace1
db2 alter table db2admin.
        actor2
    add primary key (actor_id)
      

现在，对 ACTOR2 表运行与用于 ACTOR 表相同的 INSERT 语句（别忘了更改： ...insert into db2admin. actor2... ）。发生了什么？您收到 SQL00798N 错误。

现在，请运行相同的 INSERT 语句，只是对下面的 粗体部分进行一些更改：

db2 insert into db2admin.
        actor2 (actor_id, actor_name, act_yr_of_birth)
    values (
        default,'Bruce Willis',1955), 
           (default,'Tom Cruise',1962),
           (default,'Tommy Lee Jones',1946)
      

成功了！为什么呢？因为 ACTOR2 表创建时带有要求 DB2 必须生成标识值的选项。现在，如果浏览 ACTOR2 表的内容，它看上去将如 图 2所示。

现在您应该有两个表：

• ACTOR（利用 GENERATED BY DEFAULT 选项定义的）
• ACTOR2（利用 GENERATED ALWAYS 选项定义的）。

回页首

导入、导出和装入标识列

导入和导出

因为集成交换格式（Integrated Exchange Format，IXF）格式保存标识列属性，所以您可以使用 DB2 EXPORT 实用程序从包含标识列的表中导出数据。DB2 IMPORT 实用程序可以使用 IXF 文件格式重新创建表。

例如，要生成 ACTOR 表（请记住，该表中有一个标识列，以 GENERATED BY DEFAULT 方式定义）的 IXF 文件，请输入下列命令：

db2 export to actor.ixf of ixf messages export.msg select * from db2admin.actor

现在使用如下命令导入数据并创建名为 ACTOR3 的新表，在一条语句中输入下述命令：

db2 import from actor.ixf of ixf modified by identityignore messages import.msg
            create into db2admin.actor3

现在，浏览新创建的 ACTOR3 表的内容；它看上去应当如 图 3所示。

ACTOR3 表的内容让您觉得奇怪吗？您将 ACTOR 表的内容导出到 IXF 文件，并使用该文件来创建 ACTOR3 表。但是，ACTOR3 表的内容与 ACTOR2 表的内容是相同的。

在我使用的示例中，导出的数据被导入新创建的名为 ACTOR3 的表，该表与 ACTOR 表具有相同的属性。但是，这个特殊的命令调用包含了一个名为 identityignore 的修饰符。

IMPORT 实用程序支持两种与标识列相关的文件类型修饰符：

• identityignore— 该修饰符规定输入文件中的任何标识值都被忽略，并且规定由 DB2 为每行生成新的标识值。
• identitymissing— 该修饰符规定，输入文件不包含目标表中标识列的任何值，甚至不包含 空值。（ 注：如果您试图导入包含标识列的 IXF 文件，但是您已经（通过使用 identitymissing 修饰符）告知 IMPORT 实用程序该文件不包含标识列，那么您将得不到您希望的结果。）

其它两种文件类型修饰符 — generatedignore 和 generatedmissing — 具有适用于导入的生成列的相同作用。

在我的示例中，如果 未指定 identityignore 修饰符， 并且目标表定义包含 GENERATED ALWAYS 标识列，那么 IXF 文件中的所有输入数据都将被拒绝，因为始终要求 DB2 生成标识值。（回忆一下我给出的用于 ACTOR2 表的 DDL，它使用了 GENERATED ALWAYS 列。）

每当输入文件中的对应行丢失了标识列的值（或者包含空值），DB2 都会为这样的列生成标识值。如果为标识列指定了非空值，则该行被拒绝。另一方面，如果未指定 identityignore 修饰符，并且标识列以 generated by default 方式定义，那么 IMPORT 实用程序接受包含在输入文件中的值；如果这样的值丢失或为空，则 DB2 生成新的值。

现在，我打赌您会重新阅读上面一段，并对自己说“哼？”。 表 1 汇总了最常用的 IXF IMPORT 选项：INSERT、CREATE 和 REPLACE 的这些情形。（我将让您自己研究使用 identitymissing 修饰符的情形；只要记住在将数据导出到 IXF 时 别包含标识列，否则使用该修饰符就没有理由了。）

生成导出文件：下面的所有示例都是使用从 DDL（我在本文开始的时候为您提供的）生成的 IXF 文件完成的。所有 EXPORT 和 IMPORT 命令都是从 CLP 运行的。如果您使用控制中心来生成您的导出文件，并且使用 Column选项卡选择了所有的表列，控制中心将包含每列的 METHOD N 参数，如 图 4所示。

这不是您所希望的，因为 METHOD N 参数不包括 IXF 文件中的所有索引和标识列信息，因此这些样本将失败。您必须在不使用该选项的前提下生成的 IXF 文件。

EXPORT 的缺省值是使用 ...select * from ... 来选择数据 — 因此只要不使用 Column选项卡，那么一切都将正常工作。比较未显式地指定列（ 图 5）与显式地指定列（ 图 4）时 Show Command按钮的结果：

如果您继续研究，为了您方便起见， 这里给出了 actor 和 actor2 表的导出文件。

表 1. 标识列导入结果的汇总

LOAD 实用程序

DB2 LOAD 实用程序支持 三种与标识列相关的文件类型修饰符。除了 identityignore 和 identitymissing 之外，该装入实用程序还支持 identityoverride修饰符。

identityoverride 修饰符指定：当将数据装入带有 GENERATED ALWAYS 标识列的表时使用输入文件中的标识值。当指定了该修饰符时，任何标识列没有值或为 空值的行都被拒绝。如果标识列 不是主键，或者 未对标识列定义唯一索引，那么当使用该修饰符时，可能会违背 GENERATED ALWAYS 列的唯一性属性。

其它三种文件类型修饰符 — generatedignore 、 generatedmissing 和 generatedoverride — 具有适用于装入的生成列的相同作用。

回页首

对于 DB2 V7.2 用户

在 DB2 V7.2 中，LOAD 不能生成非标识生成列值，因此装入带有非标识生成列的表将使目标表处于 CHECK PENDING 状态，直到您运行 SET INTEGRITY 为止。

如果您运行 SET INTEGRITY FOR <tablename> IMMEDIATE CHECKED FORCE GENERATED ，那么 SET INTEGRITY 会为您生成列值。如果您指定 generateoverride ，然后提供您自己的确信有效的值，那么您可以使用下述命令来使表脱离 CHECK PENDING 状态：

SET INTEGRITY FOR <tablename> GENERATED COLUMN IMMEDIATE UNCHECKED 

输入带有该选项的 SET INTEGRITY 命令使 DB2 跳过一项工作 — 验证所提供的值确实正确（当然，这是有风险的）。为使表脱离检查挂起状态，并且强制对用户提供的值进行验证，请发出下述命令： SET INTEGRITY FOR <tablename> IMMEDIATE CHECKED 。

回页首

参考资料

• 在 DB2 通用数据库中自动生成的数值序列
• SQL 参考大全中的 CREATE TABLE

回页首

下载

描述	文件类型	文件大小	下载方法
actor.ixf	IXF	7 KB	HTTP
actor2.ixf	IXF	7 KB	HTTP

关于作者

		Paul C. Zikopoulos是一位 BA、MBA，他还是 IBM Global Sales Support 团队的 IDUG 专家和获奖演讲者。他在 DB2 方面有七年多的经验，并且写了大量关于 DB2 的期刊文章和书籍。Paul 为许多杂志撰写过有关 DB2 的文章，并且还与他人合著了以下书籍： DB2 - The Complete Reference、DB2 Fundamentals Certification for Dummies、DB2 For Dummies和 A DBA's Guide to Databases on Linux。Paul 是一位 DB2 认证的高级技术专家（DB2 Certified Advanced Technical Expert）（DRDA 和 Cluster/EEE）以及 DB2 认证的解决方案专家（DB2 Certified Solutions Expert）（Business Intelligence 和 Database Administration）。可以通过 paulz_ibm@msn.com与他联系。

对本文的评价

太差！ (1) 需提高 (2) 一般；尚可 (3) 好文章 (4) 真棒！(5) 建议？

回页首