DB2 10.1 DBA for Linux, UNIX, and Windows 基础认证考试 611 备考教程,第 5 部分: DB2 工具

通过本教程学习如何恰当地管理您的 DB2® 数据库服务器。本教程是由 8 部分组成的 “DB2 10.1 DBA 认证考试 611” 系列教程的第 5 部分,该系列教程旨在帮助您为通过 DB2 10.1 for Linux®, UNIX®, and Windows® Database Administration(考试 611)做准备。

Mohamed El-Bishbeashy, IT 专家, IBM

Mohamed El-Bishbeashy 是 IBM Software Group 的 IBM Cairo Technology Development Center (C-TDC) 的 IT 专家。他在软件开发行业拥有 10 年的工作经验,期间曾为 IBM 效力五年。他的技术经验包括应用程序和产品开发、DB2 管理、永久存储层设计和开发。他是 IBM 认证的高级 DBA 和 IBM 认证的应用程序开发人员。目前,他是 Information Management Technology Ecosystem (IMTE) 团队的成员,是 DB2 数据库迁移方面的专家。



2013 年 8 月 19 日

开始之前

关于本系列

您是否正在为 DB2 DBA 认证考试 611 做准备?如果是的话,那么您将非常适合学习本系列教程,本教程为各类人员提供了全面的内容。本系列 DB2 认证准备教程探讨了考试所涉及的一些主要概念。通过本教程为考试做准备,您能够轻松过关。

关于本教程

本教程介绍了对 DB2 服务器进行管理所需的技能。本教程是由 8 部分组成的系列教程的第 5 部分,旨在帮助您为通过 DB2 10.1 for Linux, UNIX, and Windows Database Administration Certification(考试 611)做准备。

本教程的内容基于 “DB2 9 数据库管理(731考试)认证指南,第 5 部分: DB2 实用程序”,包含针对 DB2 10.1 的更新和一些额外的主题和示例。

目标

在本教程中,您将学习以下内容:

  • 使用 EXPORT 工具提取时间。
  • 使用 IMPORTLOAD 工具填充表。
  • 使用 INGEST 工具。
  • 何时以及如何使用 db2movedb2lookdb2batch 命令。
  • 如何在数据迁移过程中使用 ADMIN_MOVE_TABLEADMIN_COPY_SCHEMA
  • 如何使用 RUNSTATS, REORG、REORGCHKREBIND 工具,以及 FLUSH PACKAGE CACHE 声明。
  • 何时以及如何使用 DB2 Design Advisor。

先决条件

要参加 DB2 10.1 DBA 考试,您必须首先通过 DB2 10.1 Fundamentals(考试 610)或 DB2 9 Fundamentals(考试 730)。我们建议先学习 DB2 Fundamentals 系列教程,然后再学习本系列教程。本教程可以帮助您为通过考试 611 做准备。您还应当参考本教程后面提供的参考资料,了解有关 DB2 工具的更多信息(参见 参考资料)。理解本文描述的概念不需要您掌握 Fundamentals 系列教程中介绍的全部内容,但是您至少应当对以下内容有基本的了解:

  • DB2 产品
  • DB2 工具
  • DB2 实例
  • 数据库
  • 数据库对象

系统要求

完成本文不需要用到 DB2。但是,在学习本教程的过程中下载使用 IBM DB2 10.1 的免费版本会使您获益更多。


数据移动工具

611 考试的一个重要目标就是考查使用传统 DB2 数据移动工具的能力。在本节中,我们将讨论各种可用的 DB2 数据移动工具、实用工具、存储流程和命令。

DB2 提供了四种数据移动工具:

  1. EXPORT
  2. LOAD
  3. INGEST
  4. IMPORT

除了上述四种工具外,还可以使用以下命令和存储流程:

  1. ADMIN_MOVE_TABLE 流程
  2. db2move 命令
  3. ADMIN_COPY_SCHEMA 流程
  4. db2look

文件格式和数据源类型

下面是 DB2 导出、导入、获取和加载工具所支持的四种 OS 文件格式:

  • 不带分隔符的或固定长度的 ASCII (ASC):顾名思义,这种文件类型包括固定长度的 ASCII 数据,用于与列数据对齐。每个 ASC 文件都是一个 ASCII 字符流,包含按列和行排列的数据值。数据流中的行使用断行符分隔,断行符默认为回车换行符。
  • 带分隔符的 ASCII (DEL):这是各种数据库管理器最常用于数据交换的文件格式。它包含 ASCII 数据,使用特殊字符分界符分隔列值。数据流中的行则通过使用回车换行符作为分隔符。
  • PC 版 Integrated Exchange Format (PC/IXF):这是数据库表的一种结构化表示。该文件格式可不仅可用于导入数据,还可用于创建目标数据库中不存在的表。
  • 光标(Cursor):光标通过一个问号声明。它只能用于加载操作的输入。
表 1. 支持的文件格式汇总
文件格式IngestLoadImport
ASC (包括二进制)支持支持支持
DEL支持支持支持
IXF不支持支持支持
Cursor不支持支持不支持

DB2 EXPORT 工具

概述

EXPORT 工具使用了一个 SQL SELECTXQUERY 语句,将数据从数据库表提取到文件中。导出的数据可以使用 DEL、IXF 或 WSF 文件格式。建议在导出中包含 MESSAGES 子句,以便可以捕获导出过程中的错误、警告和消息。

要成功调用 EXPORT 工具,那么在使用 EXPORT 命令访问表或视图时,就必须使用 SYSADM 或 DBADM 身份,或具有 CONTROL 或 SELECT 权限。

在提取受 LBAC(基于标签的访问控制)保护的数据时,LBAC 凭证可能会限制导出的行。没有进行读访问的行不会被导出。用户不会获得任何错误或警告提示。然而,如果您的 LBAC 凭证不允许读取导出中包含的一个或多个受保护的列,导出操作将会以一个错误结束。

让我们查看一个简单的导出示例。下面的命令将将 SELECT 语句的结果导出到一个 DEL 格式的文件中。消息文件 msg.out 会记录有用的信息和遇到的任何错误或警告:

清单 1. 简单导出的示例
EXPORT TO myfile.del OF DEL 
MESSAGES msg.out
SELECT staff.name, staff.dept, org.location
FROM org, staff
WHERE org.deptnumb = staff.dept;

文件类型修饰符

在前面的例子中,数据被提取到一个 DEL 格式的文件中。在默认情况下,列值使用逗号(,)分隔,而字符串使用引号括起(")。如果要提取的数据中已经包含逗号和引号,那么导入或加载工具将无法判断哪些符号是数据,哪些是分隔符。要自定义 EXPORT 的操作方式,可以使用 MODIFIED BY 子句,并指定文件类型修饰符要修改的内容。在 EXPORT 命令中使用 MODIFIED BY 子句如下所示:

清单 2. MODIFIED BY 子句的示例
EXPORT TO file_name OF file_type
    MODIFIED BY file_type_modifiers
    MESSAGES message_file
    select_statement

在 EXPORT 下面的 Command Reference Guide 部分中可以找到文件类型修饰符的完整列表。下面列出了一些常用的修饰符以供参考:

  • chardelx
    • 指定 x 作为新的单字符串分隔符。默认值为引号(")。
  • coldelx
    • 指定 x 作为单字符列分隔符。默认值为逗号(,)。
  • codepage=x
    • 指定 x(一个 ASCII 字符串)作为输出数据的新代码页。在导出操作期间,字符数据由应用程序代码页转换为这种代码页。
  • timestampformat="x"
    • x 是指源表中的时间戳格式。
清单 3. 文件类型修饰符的示例
EXPORT TO myfile.del OF DEL
  MODIFIED BY chardel! coldel@ codepage=1208 timestampformat="yyyy.mm.dd hh:mm tt"
MESSAGES msg.out
SELECT * FROM schedule

上面的命令将以 DEL 格式从 SCHEDULE 表导出数据,并伴随以下行为:

  • 字符串用感叹号(!)括起
  • 使用 @ 符号作为列分隔符
  • 字符串被转换为代码页 1208
  • SCHEDULE 表中用户定义的时间戳的格式为 yyyy.mm.dd hh:mm tt

使用 LOBSINFILE 修饰符导出大对象

在导出含有大对象列的表时,默认情况下,只能导出前 32 KB 的 LOB 数据。这部分对象内容会放入与其余列数据相同的文件中。如果您导出的 LOB 值超出 32 KB,那么您应当将 LOB 数据写入单独的文件中,以避免发生截断。

要完整地导出 LOB 数据,并将这些数据存储到与其他列数据不同的文件中,则必须使用 LOB 选项。在 DB2 10.1 中,您可以指定是否希望将多个 LOB 值连在一起并导出到相同的输出文件中,或是将每个 LOB 值导出到不同的文件中。

下面是一个 EXPORT 命令,其中使用了 LOBSINFILE 修饰符,这促使导出工具将多个 LOB 值写入到相同的输出文件中。

清单 4. EXPORT 命令和 LOBSINFILE 修饰符
EXPORT TO file_name OF file_type
    LOBS TO lobfile_directory_1, lobfile_directory_2, ... 
    LOBFILE lobfilename
    MODIFIED BY LOBSINFILE
    MESSAGES message_file
    select_statement

LOBS TO 子句指定将用于保存 LOB 文件的目录。如果未发现 LOBS TO 子句,那么 LOB 数据将会发送到当前使用的目录。请注意,在上面的命令中,您可以指定多个路径作为 LOB 文件的目标目录。每个 LOB 路径至少一个文件,并且每个文件将包含至少一个 LOB。

可以使用用户定义的文件名对提取的 LOB 文件进行区分。LOBFILE 子句可用于实现此目的。每个 LOB 文件将使用一个序列号作为文件扩展名(比如 lobfile.001、lobfile.002、lobfile.003 等)。

在指定了 LOBS TO 或 LOBFILE 选项时,将隐式地激活 LOBSINFILE 行为。不过,一种好的做法是明确地指定 LOBSINFILE 修饰符,从而避免它与 LOBSINSEPFILES 修饰符行为发生混淆,本文稍后会介绍这方面的内容。

使用 LOBSINSEPFILES 修饰符导出大对象

在使用 LOBSINFILE 修饰符导出大对象时,将生成一个 LOB Location Specifier (LLS),并将它保存到导出输出文件中。LLS 是一个字符串,用于表示可以找到 LOB 数据的位置。它的格式为 filename.ext.lob.nnn.mmm/。让我们更详细地查看这个格式:

  • filename.ext.lob 是包含 LOB 数据的文件的名称;ext 是一个序列号,如前面的面板中所述。
  • nnn 是大对象在 LOB 文件中的位移,单位为字节。
  • mmm 是大对象的长度,单位为字节。

例如,格式为 resume.001.lob.1257.2415/ 的 LLS 表示大对象位于文件 resume.001.lob 中,实际的 LOB 数据从文件的 1257 字节位移开始,长度为 2415 字节。

清单 5. LLS 用例
EXPORT TO empresume.del OF DEL 
    LOBS TO d:\lob1\
    LOBFILE resume 
    MODIFIED BY LOBSINFILE
    MESSAGES msg.out    
    SELECT * FROM emp_resume

导出 XML 数据

如前所述,您还可以选择完整地导出 LOB 数据,并将这些数据分别存储到单独的文件中。使用和前面相同的 LOB 选项,但是这一次使用的修饰符是 LOBSINSEPFILES。下面展示了一个使用这个修饰符的示例。

清单 6. 导出 XML 数据的示例
EXPORT TO empresume.del OF DEL 
    LOBS TO d:\lob1\
    LOBFILE resume 
    MODIFIED BY LOBSINSEPFILES
    MESSAGES msg.out    
    SELECT * FROM emp_resume

使用 EXPORT 命令后,导出工具将将 LOB 数据写入名为 resume.ext.lob(即 resume.001.lob、resume.002.lob、resume.003.lob 等)的文件中,所有文件的 LOB 路径均为 d:\lob1。

使用 XML 选项和修饰符导出 XML 数据

在未指定任何 XML 选项的情况下导出表(使用 XML 数据定义)时,相关的 XML 数据会被写入与其他导出的关系数据不同的文件中。

清单 7. 对包含一个 XML 列的 PRODUCT 表发出的 EXPORT 命令的示例
EXPORT TO prodexport.del OF DEL 
    MESSAGES msg.out    
    SELECT * FROM product

在本例中,导出工具将生成两个输出文件,一个用于非 XML 数据,另一个用于 XML 数据。如命令所示,第一个文件名为 prodexport.del,除了包含关系数据外,它还包含 XML Data Specifiers (XDS)。

XDS 是一个字符串,表示为一个 XML 标记(称为 “XDS”)。它的属性描述了有关列中的 XML 数据的信息。您在 XDS 字符串中会看到以下属性。

  • FIL 指定包含 XML 数据的文件的名称。
  • OFF 指定以 FIL 属性命名的文件中的 XML 数据的字节位移量。
  • LEN 指定以 FIL 属性命名的文件中的 XML 数据的长度,单位为字节。
  • SCH 指定用于验证 XML 文档的 XML 模式的完全限定 SQL 修饰符。我们稍后会讨论该属性。

从上面的 prodexport.del 的内容中可以看出,第一个 XML 数据被保存到 prodexport.del.001.xml 中,起始位置为 0 字节位移,长度为 252 个字节。

导出工具在本例中生成的第二个文件是 prodexport.del.001.xml,它包含 XML 内容。所有导出的 XML 数据都将连接在一起并写入该文件。您可以通过下面的 prodexport.del.001.xml 文件示例进一步了解这方面的知识。

和导出大对象一样,您可以指定导出的 XML 文档的路径和输出文件的基本文件名。请考虑下面的示例:

清单 8. 指定 XML 文档的路径
EXPORT TO prodexport.del OF DEL 
    XML TO d:\xmlpath
    XMLFILE proddesc
    MODIFIED BY XMLINSEPFILES XMLNODECLARATION XMLCHAR
    XMLSAVESCHEMA
    MESSAGES msg.out    
    SELECT * FROM product

在这里,PRODUCT 表的关系数据被导出到 prodexport.del 文件中。所有 XML 数据都写入 XML TO 子句中指定的目录:d:\xmlpath。包含 XML 数据的文件被命名为 proddesc.ext.xml,其中 ext 是一个序列号(如 proddesc.001.xml、proddesc.002.xml、proddesc.003.xml 等)。这个基本文件名是使用 XMLFILE 选项定义的。

在本例中,您可能还注意到我们使用了多个修饰符。下面对所有与 XML 有关的修饰符进行了总结。

  • XMLINSEPFILES 使导出工具将每个导出的 XML 文档写入不同的 XML 文件。
  • XMLNODECLARATION 表示 XML 数据导出时不包含 XML 声明标记。XML 声明标记默认情况下位于包含编码属性的 XML 文档的起始位置。
  • XMLCHAR 表示 XML 数据被写入字符编码页中。默认情况下,XML 数据使用的是 Unicode 编码。在使用这种修饰符时,将选择使用代码页文件类型修饰符的值或应用程序代码页。
  • XMLGRAPHIC 表示导出的 XML 数据将使用 UTF-16 代码页编码,与代码页文件类型修饰符或应用程序代码页无关。请注意,本例中未使用 XMLGRAPHIC

最后一个要介绍的选项是 XMLSAVESCHEMA。在插入一个 XML 文档时,可针对 XML 模式对其进行验证。XMLSAVESCHEMA 选项还让导出工具为每个导出的 XML 数据保存 XML 模式信息。该模式的完全限定 SQL 标识符将作为 SCH 属性保存到相应的 XML 数据指示符(XDS)中。请注意,如果导出的 XML 文档未针对 XML 模式进行验证,或模式对象不再位于数据库中,那么 SCH 属性将不会包含在对应的 XDS 中。

使用 XQuery 导出 XML 数据

EXPORT 命令还允许您指定一个 XQuery 语句,这样导出工具就会将 XQuery 的结果写入一个 XML 文件中。让我们看看下面的示例。

清单 9. EXPORT 命令指定了 XQuery 语句
EXPORT TO custexport.del OF DEL 
    XML TO d:\xmlpath
    XMLFILE custphone
    MODIFIED BY XMLINSEPFILES XMLNODECLARATION 
    MESSAGES msg.out    
    SELECT XMLQUERY ('$doc/customerinfo/phone' PASSING INFO AS "doc") FROM customer

上例中的 XQuery 返回了 XML 列 INFO 的 CUSTOMER 表中的所有客户的电话号码。前面讨论的所有 XML 选项和修饰符都适用于 XQuery 语句。因此,本例将为每个 XQuery 结果生成单独的 XML 文档。这些文档位于 d:\xmlpath,它们被命名为 custphone.ext.xml,其中的 ext 是一个序列号。此外,文档中不包含任何 XML 声明标记。下面展示了其中一个导出的 XML 文档的内容: <phone type="work">905-555-7258</phone>

从 IBM Data Studio 中导出数据

除了在 DB2 命令行中执行 EXPORT 命令外,您还可以使用 Data Studio 执行导出。使用该工具可以指定导出支持的所有选项和子句,如大对象和 XML 数据。

图 1. 从 Data Studio 中调用导出工具
图片显示了如何从 Data Studio 中调用导出工具

DB2 IMPORT 工具

概述

IMPORT 通过一个 ASC、DEL 或 IXF 类型的输入文件,将数据填充到表中。目标可以是一个表、一个类型化表或一个视图。然而,不允许执行对分离表和临时表的导入。同时建议您使用 MESSAGES 子句,这样可以记录错误、警告和信息类消息。

要成功导入数据,则必须对目标表或数据库使用 SYSADM 或 DBADM 身份,或具有一些底层权限(SELECT、INSERT、CONTROL 和 CREATETAB),具体取决于您使用的选项。要将数据导入到包含受保护行和列的表中,则必须具有 LBAC 证书,允许对表中的受保护数据执行写访问。此外,要将数据导入到含受保护行的表中,必须将 LBAC 证书包含到表保护安全策略中。

清单 10. 带有五个选项的 IMPORT 命令
IMPORT FROM file_name OF file_type
MESSAGES message_file
[ INSERT | INSERT_UPDATE | REPLACE | REPLACE_CREATE | CREATE ]
INTO target_table_name
  • INSERT 选项将已导入的数据插入到表中。目标表必须已经存在。
  • INSERT_UPDATE 将数据插入表中,或更新具有匹配主键的表中的既有行。目标表必须已存在,且具有已定义的主键。
  • REPLACE 选项会删除所有现有的数据,并将导入数据插入现有的目标表中。
  • 对于 REPLACE_CREATE 选项,如果目标表已存在,那么工具将删除现有数据并插入新数据,类似于使用 REPLACE 选项的情况。如果目标表未定义,那么在导入数据之前,应该先创建表及其相关索引。可以想象到,导入文件必须采用 PC/IXF 格式,因为该格式包含对导出表的结构化描述。如果目标表是由外键引用的父表,则无法使用 REPLACE_CREATE 命令。
  • CREATE 选项创建目标表及其索引,然后将数据导入到新表中。惟一受支持的文件格式是 PC/IXF。您还可以指定用于创建新表的表空间的名称。

示例:

IMPORT FROM emp.ixf OF IXF
MESSAGES msg.out
CREATE INTO employee IN datatbsp INDEX IN indtbsp

IMPORT 选项

IMPORT 实际上是将数据批量插入到表中的一种工具。这种批量插入操作就好象一条普通的插入语句,将会记录行为、更新索引、检查引用完整性和表约束条件。在默认情况下,IMPORT 只在完成操作后执行一次提交。如果将大量行导入或插入到表中,则需要有足够的事务日志进行回滚和恢复。您可以请求定期提交,防止日志溢出。通过定期提交插入,您还可以减少由于导入操作错误而丢失的行数据。COMMITCOUNT 选项将在导入大量记录后强制执行 COMMIT 操作。您还可以指定 AUTOMATIC 选项,它允许导入在内部决定何时执行一个提交操作。该工具将考虑发起一个提交操作以避免发生日志溢出情况,或避免出现锁升级情况。

清单 11. COMMITCOUNT 选项示例
IMPORT FROM myfile.ixf OF IXF
    COMMITCOUNT 500 
    MESSAGES msg.out
    INSERT INTO newtable

如果以上命令在执行时因为某些原因而失败,那么您可以使用消息文件来判断最后一条成功导入和提交的行。然后,您可以通过 RESTARTCOUNT 选项重新启动导入操作。请注意,SKIPCOUNT 选项的行为与 RESTARTCOUNT 相同。在下面的命令中,工具首先会跳过前 30,000 条记录,然后再开始执行 IMPORT 操作。

清单 12. IMPORT 操作的示例
IMPORT FROM myfile.ixf OF IXF
    COMMITCOUNT 500 RESTARTCOUNT 30000 ROWCOUNT 100000 
    MESSAGES msg.out
    INSERT INTO newtable

在本例中,可以注意到还使用了 ROWCOUNT 选项。它指定了将要导入的物理记录的数量。由于使用了 RESTARTCOUNT 选项,导入工具将跳过前 30,000 条记录,而将之后的 100,000 条记录导入到表中。

默认情况下,导入工具会在目标表上获取一个互斥型锁,然后才能插入行。在导入完成后,该互斥型锁会立即获得释放。这是由 ALLOW NO ACCESS 选项主导的行为。为了使并发应用程序同时访问表数据,您可以使用 ALLOW WRITE ACCESS 选项。请注意,该选项不能与 REPLACE、CREATEREPLACE_CREATE 导入选项兼容。

清单 13. ALLOW WRITE ACCESS 选项的示例
IMPORT FROM myfile.ixf OF IXF
    ALLOW WRITE ACCESS
    MESSAGES msg.out
    INSERT INTO newtable

导入 XML 数据

要导入 XML 文件,可以使用 XML FROM 选项指定一个或多个存放 XML 文件的路径。否则,导入工具会在当前目录中查找 XML 文件。您可以选择 XML 文档的解析方式;去掉空白或保留空白。如果未指定 XMLPARSE 选项,对 XML 文档的解析行为将由 CURRENT XMLPARSE OPTION 专用注册表决定。

清单 14. AXML FROMXMLPARSE 选项的示例
IMPORT FROM myfile.ixf OF IXF
    XML FROM d:\xmlpath
    XMLPARSE PRESERVE WHITESPACE
    MESSAGES msg.out
    INSERT INTO newtable

当插入或更新 XML 文档时,您可能希望确定该 XML 文档的结构、内容和数据类型是否有效。导入工具还支持通过使用 XMLVALIDATE 选项对 XML 进行验证。有三种可行的方法:

  • USING XDS:回想一下,您可以导出 XML 模式信息并保存到 XML Data Specifier (XDS) 的 SCH 属性中。SCH 属性的值将用于执行验证。如果 XDS 中不存在 SCH 属性,那么将使用 DEFAULT, IGNOREMAP
  • USING SCHEMA schema-sqlid:使用该子句中指定的 XML 模式。
  • USING SCHEMALOCATION HINTS:根据由源 XML 文档中的 XML 模式位置提示识别的模式对 XML 文档进行验证。
清单 15. XMLVALIDATE 选项的示例
IMPORT FROM myfile.ixf OF IXF
    XML FROM d:\xmlpath
    XMLPARSE PRESERVE WHITESPACE
    XMLVALIDATE USING XDS 
        DEFAULT S1.SCHEMA_A
        IGNORE (S1.SCHEMA_X, S1.SCHEMA_Y, S1.SCHEMA_Z)
        MAP (S1.SCHEMA_A, S1.SCHEMA_B)
    COMMITCOUNT 500 RESTARTCOUNT 30000 
    MESSAGES msg.out
    INSERT INTO newtable

上面的 IMPORT 命令将执行如下操作:

  • 从 myfile.ixf 和位于 d:\xmlpath 的 XML 文件中插入数据
  • 在解析 XML 文档时将保留空格。
  • 使用 XDS 的 SCH 属性识别的模式信息对 XML 文档进行验证。然而,如果任何特定行的 XDS 不包含 SCH 属性,则会使用 S1.SCHEMA_A。
  • 对于指定为 S1.SCHEMA_X、S1.SCHEMA_Y 或 S1.SCHEMA_Z 的 SCH 属性,不会对导入的 XML 文档执行验证。
  • 如果 SCH 属性被指定为 S1.SCHEMA_A,则会将它映射到 S1.SCHEMA_B。请注意,尽管 DEFAULT 子句指定了 S1.SCHEMA_A,但仍然不会执行任何后续映射。
  • 导入工具将在每插入 500 行后执行一次提交操作。
  • 导入操作将从第 30,001 条记录处开始。前 30,000 条记录会被跳过。
  • 任何错误、警告、消息都将写入 msg.out 文件中。
  • 新的数据将被插入(或附加)到新表中。

本例仅展示了如何对导入的 XML 文档进行验证。DB2 Information Center 提供了更多例子演示 XMLVALIDATE 选项的强大功能。

文件类型修饰符

IMPORT 工具也支持使用文件类型修饰符对导入操作进行定制。在 DB2 Command Reference 中的 IMPORT 下方,可以找到完整的修饰符列表。下面将对其中几种进行简要介绍:

  • compound=x
    • 使用非原子复合(non-atomic compound)SQL 插入数据。每次都将执行 x 个语句。
  • indexschema=schema
    • 在创建索引时对索引使用指定的模式。
  • striptblanks
    • 在将数据加载到长度可变的字段时截断所有尾随空格。
  • lobsinfile
    • 表示 LOB 数据正在被导入。该工具将查看 LOBS FROM 子句,以获得输入 LOB 文件的路径。
清单 16. 文件类型修饰符的示例
IMPORT FOR inputfile.asc OF ASC
    LOBS FROM /u/db2load/lob1, /u/db2load/lob2
    MODIFIED BY compount=5 lobinsfile
    INSERT INTO newtable

使用 IBM Data Studio 实现 IMPORT

Data Studio 提供了易于使用的图形界面来执行导入操作。前面提到的所有导入选项和文件修饰符都可用于这个界面。


DB2 LOAD 工具

LOAD 工具概述

LOAD 工具是另一种向表中填充数据的方法。格式化页面被直接写入数据库中。这种机制允许实现比 IMPORT 工具更有效的数据移动。然而,一些操作,如引用或表约束检查和触发器调用,都不会由 LOAD 工具执行。

接下来是 LOAD 命令的核心;其他选项和修饰符也受支持并将在本节后面介绍。要成功地执行该命令,则必须对所加载的表具使用 SYSADM、DBADM 或 LOAD 身份,或者具有 INSERT 和/或 DELETE 权限。要将数据加载到含有受保护列的表,则必须具有 LBAC 凭证,从而允许对表中所有受保护的列执行写访问。要将数据加载到包含受保护行的表中,则必须为写访问授予一个安全标签,这是表安全策略的一部分。

清单 17. LOAD 命令的示例
LOAD FROM input_source OF input_type
    MESSAGES message_file
    [ INSERT | REPLACE | TERMINATE | RESTART ]
    INTO target_tablename

下面是使用 CURSOR 作为载入输入的另一个示例:

DECLARE mycursor CURSOR FOR SELECT col1, col2, col3 FROM tab1;
LOAD FROM mycursor OF CURSOR INSERT INTO newtab;

在启动工具之前,加载对象必须是已经存在的。该对象可以是一个表、一个类型化表或一个表别名。不支持对含有 XML 列的表、系统表和临时表进行加载。使用 MESSAGES 选项捕捉加载期间发生的任何错误、警告和消息。

LOAD 可以通过四种模式执行:

  • INSERT 模式可在不修改现有表数据的情况下将输入数据添加到表中。
  • REPLACE 模式删除了表中的所有现有数据,并填充新的输入数据。
  • TERMINATE 模式终止一个加载操作,并回滚到加载操作开始执行的时间点。此处的一个例外是:在指定; REPLACE 的情况下,表会被截断。
  • RESTART 用于重新启动上一个被中断的加载。它将自动从最后一个一致点开始继续执行操作。要使用该模式,则需要指定与上一个 LOAD 命令相同的选项,但是其中包含 RESTART。它允许工具查找加载期间生成的所有必要的临时文件。因此,不要手动删除加载生成的任何临时文件,除非您确定不会用到它们。当加载成功完成后,会自动清除临时文件。在默认情况下,它们创建于当前的工作目录中。您可以使用 TEMPFILES PATH 指定临时文件的存放目录。

加载过程的不同阶段

一个完整的加载过程可以分为四个不同的阶段:

  1. 加载阶段:
    • 将数据加载到表中。
    • 收集索引键和表统计信息。
    • 记录一致点。
    • 将无效数据放到 dump 文件中并记录消息文件中的消息。当多行数据不符合表定义时,会将它们视为无效数据并拒绝它们(不会将它们加载到表中)。使用 dump 文件修饰符指定某个文件的名称和位置,用它来记录所有遭到拒绝的行。
  2. 构建阶段:
    • 根据加载阶段收集的键创建索引。如果指定了 STATISTICS USE PROFILE,则会根据为目标表定义的配置文件来收集统计信息。该配置文件必须是在执行加载之前创建的,否则会返回一个警告,并且收集不到任何统计信息。
  3. 删除阶段:
    • 删除引起惟一键违背情况的行,并将这些行放在一个异常表中。此外,如前所述,当数据与目标表定义不匹配时,可能会有数据通过加载阶段,但它们违背表中定义的惟一性约束。请注意,这里仅将违背惟一键的行视为坏数据;此时不会检查其他约束条件。由于这种类型的数据已经加载到表中,LOAD 工具将在这个阶段删除出现问题的行。可以使用一个异常表来存储这些被删除的行,这样您可以在完成加载后决定如何处理它们。如果未指定异常表,则会彻底删除有问题的行。稍后我们将详细讨论异常表。
    • 记录消息文件中的消息。
  4. 索引复制阶段:
    • 如果 ALLOW READ ACCESS 被指定了 USE TABLESPACE,那么索引数据将从系统临时表空间中复制到索引所在的表空间。

异常表是一个用户定义的表,必须具有与所加载的目标表相同的列定义。如果异常表中不存在其中一个这样的列,则会丢弃出现问题的行。只有两个额外的列可以添加到表的末端:一个时间戳列(记录插入行的时间)和一个 CLOB 列(保存行被视为坏数据的原因或消息)。

一个加载示例

让我们通过一个示例来了解加载过程中涉及到的步骤。

清单 18. 加载过程的示例
LOAD FROM act.del OF DEL
    MODIFIED BY DUMPFILE=/home/db2inst1/act.dmp
    MESSAGES msg.out
    INSERT INTO act
    FOR EXCEPTION actexp

ACT 表定义如下所示:

清单 19. ACT 表定义的示例
CREATE TABLE "DB2INST1"."ACT" (
        "ACTNO" SMALLINT NOT NULL, 
        "ACTKWD" CHAR(6) NOT NULL, 
        "ACTDESC" VARCHAR(20) NOT NULL,
        PRIMARY KEY (ACTNO)
    )

该表目前填充了一个行。

图 2. 表 ACT 的内容
图片显示了 ACT 表的内容

加载输入数据文件 act.del 包含三个行,最后一行包含 ACT 表内容的副本,因此违反了 PK 约束条件。

图 3. act.del 文件内容
图片显示了 act.del 文件的内容

使用与 ACT 表相同的定义创建了一个异常表 ACTEXP。

 CREATE TABLE ACTEXP LIKE ACT

在加载阶段,输入文件的所有数据都被加载到 ACT 表中。

在删除阶段,最后一行(使用蓝色标识)从 ACT 中删除,因为它违背了 Primary Key 约束并被插入到异常表 ACTEXP 中。

请注意,任何违背惟一索引或主键索引的行都将复制到异常表中,而不是写入到 dump 文件中。另一方面,无法加载 dump 文件包含的行,因为它们是无效的或包含语法错误。

在加载结束时,您应当检查消息文件、dump 文件和异常表,然后决定如何处理遭到拒绝的行。

清单 20. 完成加载后的 ACT 表的内容
ACTNO  ACTKWD ACTDESC             
------ ------ --------------------
    10 MANAGE MANAGE/ADVISE       
    20 ECOST  ESTIMATE COST       
   100 TEACH  TEACH CLASSES

ACTEXP 表的内容:

ACTNO  ACTKWD ACTDESC             
------ ------ --------------------
   100 TEACH  TEACH CLASSES

完成加载后,包含加载表的表空间将处于 BACKUP BENDING 状态,而表将处于 SET INTEGRITY BENDING 状态。

加载选项和文件类型修饰符

前面已经介绍了一些加载选项和文件类型修饰符。下面将讨论更多选项和文件类型修饰符:

  • ROWCOUNT n:允许用户指定只加载输入文件中的第 n 个记录。
  • SAVECOUNT n:每完成 n 个行的加载后建立一致点。消息文件中将生成和记录消息,表示在保存时有多少输入行被成功加载。当输入文件类型为 CURSOR 时则无法实现。
  • WARNINGCOUNT n:在出现 n 条警告后停止加载。
  • INDEXING MODE [ REBUILD | INCREMENTAL | AUTOSELECT | DEFERRED ]:在构建阶段将构建索引。该选项指定 LOAD 工具是否要重新构建索引或增量式进行扩展。支持以下四种模式:
    • REBUILD 强制重新构建所有索引。
    • INCREMENTAL 只使用新的数据扩展索引。
    • AUTOSELECT 允许工具在 REBUILD 和 INCREMENTAL 之间选择。
    • DEFERRED 表示在加载期间不会创建索引。涉及的索引将重新标记并适时刷新。它们将在数据库重启或进行第一次访问时重建。
  • STATISTICS USE PROFILE:完成一次加载后,目标表早期的统计信息很可能变为无效,因为加入了大量的新数据。根据目标表的配置文件,您可以选择在构建阶段收集统计信息。

文件类型修饰符。文件类型修饰符是通过 MODIFIED BY 子句指定的。下面是一些比较有用的修饰符:

  • fastparse:减少了对载入数据的语法检查,从而实现性能改进。
  • identityignore、identitymissing 和 identityoverride:分别用于忽略、表示丢失数据或覆盖标识列数据。
  • indexfreespace n、pagefreespace n 和 totalfreespace n:在索引和数据页中保留指定数量的空白页。
  • norowwarnings:阻止行警告。
  • lobsinfile:表示没有加载 LOB 文件;对 LOB 路径检查 LOBS FROM 选项。

加载期间的表访问

在一个表进行加载时,LOAD 工具会通过一个互斥型锁将它锁定。除非完成加载,否则将无法执行其他任何访问。这是 ALLOW NO ACCESS 选项的默认行为。在这类加载期间,表处于 LOAD IN PROGRESS 状态。有一个方便的命令可用于检查加载操作的状态并返回表状态: LOAD QUERY TABLE table_name

您可能猜到还有一个选项可以允许进行表访问。ALLOW READ ACCESS 选项使表能够在共享模式下锁定。读取程序可以访问表中已有的数据,但是无法访问新的数据。正在被加载的数据只有在完成加载后才能变得可用。该选项将使表处于 LOAD IN PROGRESS 和 READ ACCESS ONLY 状态。

如前所述,在构建期间可以重建一个完整的索引,或用新的数据进行扩展。使用 ALLOW READ ACCESS 后,如果正在重建一个完整的索引,那么还将创建该索引的影子(shadow)副本。当 LOAD 工具进入到索引复制阶段(参见加载流程的四个阶段),目标表将处于离线状态,会将新的索引复制到目标表空间中。

不管指定了何种表访问选项,都需要对加载流程应用几种锁。如果目标表已经被某些应用程序锁定,那么 LOAD 工具将会一直等待,直到锁被释放。或者,您可以在 LOAD 命令中使用 LOCK WITH FORCE,强制其他拥有冲突锁的应用程序释放锁。

SET INTEGRITY PENDING 表状态

目前为止,我们已知不符合目标表定义的输入数据不会加载到表中。此类数据在加载阶段会遭到拒绝,并记录到消息文件中。在删除阶段,LOAD 工具将会删除所有违背惟一性约束的行。这些行被插入到一个指定的异常表中。那么表定义的其他约束(如引用完整性和检查约束)呢?LOAD 工具不会检查这些约束。表将处于 SET INTEGRITY PENDING 状态,这会强制您在访问表之前手动检查数据完整性。表状态可以通过 LOAD QUERY 命令进行查询。系统编目表 SYSCAT.TABLES 中的 CONST_CHECKED 列也指出了表中定义的每个约束的状态。

要手动禁用针对一个或多个表的完整性检查,可以使用 SET INTEGRITY 命令。下面通过几个例子演示了一些选项。要立即查看表 EMPLOYEE 和 STAFF 的附加选项的完整性,请使用 SET INTEGRITY FOR employee, staff IMMEDIATE CHECKED INCREMENTAL

要绕过对表 EMPLOYEE 的外键检查(采用了 IMMEDIATE UNCHECKED 选项),请使用 SET INTEGRITY FOR employee FOREIGN KEY IMMEDIATE UNCHECKED

在某些情况下,您可能希望在加载完成后让目标表及其派生表(带有外键关系)处于 SET INTEGRITY PENDING 状态。这确保所有表都具有访问可控性,直到执行一次手动完整性检查。加载选项 SET INTEGRITY PENDING CASCADE IMMEDIATE 将立即扩展到所有派生的外键表,它表示外键约束的检查暂挂状态。在默认情况下,只有已加载的表会处于加载暂挂状态。

表空间状态

由于 LOAD 工具将经过格式化的页面直接写入数据库中,因此不会执行数据库日志记录来记录所加载的新数据。如果您有一个可恢复的数据库(即启用了 LOGREATIN 和/或 USEREXIT),那么 DB2 需要确保数据库在完成加载后仍然具有可恢复性。要增强这种可恢复能力,则需要让表所在的表空间处于 BACKUP PENDING 模式。这意味着必须在访问前对表空间进行备份。

这是在完成加载操作后恢复表空间可访问性的默认方式。另一个方法是在加载运行过程中使用选项 COPY YES 备份加载的数据。在完成加载后将创建一个备份文件。

还有一个选项可使您不必在完成加载后立即备份表空间。NONRECOVERABLE 将正在加载的表标记为不可恢复。相关的表空间在完成加载后可进行全面访问。DB2 不会阻止您查询和修改表数据。但是,如果您需要在最后恢复表空间并前滚到一个已完成 NONRECOVERABLE 加载操作的时间,那么加载表将不可恢复。恢复过程将跳过所有与表相关的日志。您只能删除并重新创建表。因此,仍然建议您在方便时备份表空间,这样现有数据和加载数据将被保存到备份中。

使用 IBM Data Studio 实现加载

Data Studio 提供了易于使用的图形界面来执行加载操作。上述所有加载选项和文件修饰符都可以在本界面中使用。


DB2 INGEST 工具

INGEST 工具是一个新的 DB2 10.1 数据移动工具。它是一个高速的客户端 DB2,将数据从文件和管道中传递到 DB2 目标表。有时它被称为 Continuous Data Ingest (CDI),因为它可以持续地处理来自管道的连续数据流。此外,它非常适合实现数据流通和可用性,因为它使用了行锁定,因而不会锁住整个目标表。

INGEST 工具可以通过一个类似 SQL 的界面对目标表执行以下 DML 操作:INSERT, UPDATE, MERGE, REPLACEDELETEINGEST 工具还支持使用 SQL 表达式从多个数据字段中构建各种列值。

INGEST 工具的关键特性包括:

  • 处理来自持续数据流的数据,同时维持表可用性,满足现代数据处理需求
  • 能够进行数据转换
  • 可以将不需要的行放到异常文件或表中,或将它们丢弃
  • 能够从最后一次提交重启 INGEST 操作
  • DB2 Client 的一部分,因此可以在非 DB2 服务器的机器上单独安装和执行,不需要额外的许可

INGEST 阶段

一个 INGEST 命令将经历三个主要阶段:

  1. Transport

    在该阶段,传输程序线程(transporter thread)从输入数据源中读取记录,并将这些记录放到格式程序(formatter)队列中。对于 INSERTMERGE 操作,每个输入源有一个传输程序线程(例如,每个输入文件一个线程)。对于 UPDATEDELETE,只有一个传输程序线程。

  2. Format

    在该阶段,输入数据将被转换为必要的 DB2 格式。格式程序从格式程序队列中拉取记录,解析每一个记录,将数据转换为 DB2 数据库系统要求的格式,然后将每个格式化后的记录放到该记录分区(partition)的一个 flusher 队列中。

    格式程序线程的数量由 num_formatters 配置参数指定。默认为(逻辑 CPU 数)/2。

  3. Flush

    在这一阶段,ingest DML 操作将在目标 DB2 表上运行。flusher 发出 SQL 语句以在 DB2 表上执行操作。

    每个分区的 flusher 的数量由 num_flushers_per_partition 配置参数指定。默认值为 max( 1, ((number of logical CPUs)/2)/(number of partitions) )

清单 21. 一个典型的 INGEST 语句
INGEST FROM input_source FORMAT format-definition
MESSAGES message_file
RESTART [NEW | OFF | CONTINUE | TERMINATE]
SQL-statement

SQL 指定将要对目标表运行的 DML 操作。这些操作可以是 DELETE, INSERT, MERGE, REPLACEUPDATE

INGEST 示例

下面的示例从一个使用分隔符的文本文件中插入数据:

INGEST FROM FILE delimited_file.txt      
    FORMAT DELIMITED      
    INSERT INTO mytable;

其他用于计算列值的字段

下面的例子展示了 INGEST 工具的基本转换功能。

您有一个输入文件 myfile.txt,它由 5 个列组成:产品 ID、产品描述、价格、销售税和货运。这些字段使用了 | 分隔符分隔。这些数据将被插入到 mytable 表中,该表由三列组成:prod_iddescription 和 total_price。total_price 列是对输入文件中的最后三列的汇总。

输入文件内容如下所示:

PROD1|product 1|10|5|3
PROD2|product 2|15|5|3
PROD3|product 3|20|15|3
清单 22. INGEST 命令的用法
INGEST FROM FILE myfile.txt      
    FORMAT DELIMITED BY '|'     
    (        
        $prod_ID     CHAR(8),        
        $description CHAR(32),        
        $price       DECIMAL(5,2) EXTERNAL,        
        $sales_tax   DECIMAL(4,2) EXTERNAL,        
        $shipping    DECIMAL(3,2) EXTERNAL     
    )     
    INSERT INTO mytable(prod_ID, description, total_price)        
        VALUES($prod_id, $description, $price + $sales_tax + $shipping);

在上面的语句中,包含:

  • 默认的逗号分隔符,使用 DELIMITED BY | 覆盖。
  • EXTERNAL 关键字结合数值字段使用,表示字段值被指定为 ASCII 字符而不是二进制字符。
  • total_price 列的值是从 INSERT 语句中的外部列中计算得出的。

位置示例

您可以根据字段在输入文件中的位置定义将要插入到表列中的字段。

清单 23. 位置示例语法
INGEST FROM FILE input_file.txt   
    FORMAT POSITIONAL     
    (        
        $field1 POSITION(1:8)   INTEGER,        
        $field2 POSITION(10:19) DATE 'yyyy-mm-dd',        
        $field3 POSITION(25:34) CHAR(10)     
    )     
    INSERT INTO target_table         
        VALUES($field1, $field2, $field3);

因此,field1 从输入文件中的位置 1-8 开始,而 field2 从 10-19 开始,以此类推。

基本 UPDATE 示例

下面的示例更新了表中主键 key1 与输入文件中对应字段 $key1 匹配的行。

可以使用以下任意一种语法写入。

清单 24. UPDATE 示例语法 1
INGEST FROM FILE input_file.txt     
    FORMAT DELIMITED     
    (        
        $key1  INTEGER EXTERNAL,
        $data1 CHAR(8),
        $data2 CHAR(32),
        $data3 DECIMAL(5,2) EXTERNAL
    )
    UPDATE target_table
        SET (data1, data2, data3) = ($data1, $data2, $data3)
        WHERE (key1 = $key1);

或者:

清单 25. UPDATE 示例语法 2
INGEST FROM FILE input_file.txt     
    FORMAT DELIMITED     
    (        
        $key1  INTEGER EXTERNAL,
        $data1 CHAR(8),
        $data2 CHAR(32),
        $data3 DECIMAL(5,2) EXTERNAL
    )
    UPDATE target_table
        SET data1 = $data1, data2 = $data2, data3 = $data3
        WHERE (key1 = $key1);

基本 MERGE 示例

您可以使用 MERGE INTO 子句将来自输入文件的数据合并为目标表的数据。这个示例将输入文件的数据合并到目标表中。对于主键字段匹配表行的输入行,它将使用输入行更新表行。对于其他输入行,它将向表中添加行。

清单 26. 基本 MERGE 示例语法
INGEST FROM FILE input_file.txt     
    FORMAT DELIMITED     
    (        
        $key1  INTEGER EXTERNAL,
        $data1 CHAR(8),
        $data2 CHAR(32),
        $data3 DECIMAL(5,2) EXTERNAL
    )
    MERGE INTO target_table
        ON (key1 = $key1) 
        WHEN MATCHED THEN
            UPDATE SET (data1, data2, data3) = ($data1, $data2, $data3)
        WHEN NOT MATCHED THEN
            INSERT VALUES($key1, $key2, $data1, $data2, $data3);

基本 DELETE 示例

您可以根据输入文件中的键值从目标表中删除行。该例将删除主键匹配输入文件中对应主键字段的表行。

清单 27. 基本 DELETE 示例语法
INGEST FROM FILE input_file.txt     
    FORMAT DELIMITED     
    (        
        $key1  INTEGER EXTERNAL,
        $key2  INTEGER EXTERNAL
    )
    DELETE FROM target_table
        WHERE (key1 = $key1) AND (key2 = $key2);

SQL 示例

考虑以下示例,其中表包含 KEY、DATA 和 ACTION 列,您的输入文件中包含 KEY 和 DATA 两个字段。当 KEY 值匹配输入文件中对应字段且 ACTION 值为 U 时,您可以有条件地更新数据字段。

清单 28. SQL 示例语法
INGEST FROM FILE input_file.txt     
    FORMAT DELIMITED     
    (        
        $key_fld  INTEGER EXTERNAL,
        $data_fld INTEGER EXTERNAL
    )
    UPDATE target_table
        SET data = $data_fld
            WHERE (key = $key_fld) AND (action = 'U');

处理遭到拒绝的行

INGEST 可以使用与 LOAD 相同的方式处理遭到拒绝的行。可以将这些行丢弃(默认行为)或写入一个文件或 DB2 表中,供日后查看。这将允许您查明记录遭到拒绝的原因,并使用新文件重新运行 INGEST

清单 29. 将遭到拒绝的行插入异常表 excp_table 中的示例
INGEST FROM FILE input_file.txt FORMAT DELIMITED ( 
    $field1 INTEGER EXTERNAL, 
    $field2 DATE 'mm/dd/yyyy', 
    $field3 CHAR(32) )
EXCEPTION TABLE excp_table MESSAGES messages.txt
INSERT INTO target_table VALUES($field1, $field2, $field3);

创建重新启动表

如果 INGEST 失败,那么它会从最后一次提交点重启,但您首先需要创建一个重启表,使 INGEST 工具能够在操作时保存其状态,从而能够保留重启所需的必要信息。

数据库中的所有 INGEST 操作只能使用一个重启表。重启表并不会保存摄入数据的副本,而仅仅包含定义状态的计数器和指示符。

要创建一个重启表,请执行以下命令:

db2 "CALL SYSPROC.SYSINSTALLOBJECTS('INGEST', 'C', tablespace-name, NULL)"

重新启动一个失败的 ingest 操作

您可以通过发出 INGEST 命令和 RESTART CONTINUE 选项重新启动一个失败的 INGEST 操作。这将使 INGEST 从最后一个提交点重新启动。

重新启动失败的 INGEST 命令的用户 ID 必须对重启日志表具有 SELECT、INSERT、UPDATE 和 DELETE 权限。

清单 30. INGEST 命令失败的示例
INGEST FROM FILE input_file.txt 
    FORMAT DELIMITED 
    (
        $field1 INTEGER EXTERNAL,
        $field2 CHAR(8)
    )
    RESTART NEW 'job01'
    INSERT INTO target_table 
        VALUES($field1, $field2);
清单 31. 从最近一次提交点重启 INGEST
INGEST FROM FILE input_file.txt 
    FORMAT DELIMITED 
    (
        $field1 INTEGER EXTERNAL,
        $field2 CHAR(8)
    )
    RESTART CONTINUE 'job01'
    INSERT INTO target_table 
        VALUES($field1, $field2);

在上面的示例中,您会注意到:

  • 第一个 INGEST 命令包含 RESTART NEW 随意的 ID 选项。建议为 INGEST 任务分配一个随意的 ID(在本例中为 job01),这样您就可以在失败时使用它重启操作。
  • 第二个 INGEST 命令在 RESTART CONTINUE 子句中使用 job01 id 重启操作。

终止一个失败的 INGEST 操作

如果必须重启一个失败的 INGEST 操作,则需要重新发出命令和 RESTART TERMINATE 选项,从而清除其日志记录。

终止失败命令的用户必须对重启日志表拥有 SELECT 和 DELETE 权限。

示例

要终止上文中的 INGEST 操作而不是重新启动它,可以发出以下命令。

清单 32. 终止 INGEST 操作而不是重新启动它
INGEST FROM FILE input_file.txt 
    FORMAT DELIMITED 
    (
        $field1 INTEGER EXTERNAL,
        $field2 CHAR(8)
    )
    RESTART TERMINATE 'job01'
    INSERT INTO target_table 
        VALUES($field1, $field2)

监视 INGEST 操作

可以使用两个命令监视 ingest 工具的进度:INGEST LIST 和 INGEST GET STATS/STATISTICS 命令。它们必须在运行 ingest 工具的同一台机器上运行,但它们位于不同的 CLP 会话中。

INGEST LIST 提供了机器上所有正在运行的 INGEST 工具的基本信息,而 INGEST GET STATS 提供了有关某个特定 INGEST 命令的详细信息。

示例

清单 33. INGEST LIST 示例
=> INGEST LIST
                    
Ingest job ID       = DB21000:20101116.123456.234567:34567:45678
Ingest temp job ID  = 1
Database Name       = MYDB
Input type          = FILE 
Target table        = MY_SCHEMA.TARGET_TABLE
Start Time          = 04/10/2010 11:54:45.773215
Running Time        = 01:02:03
Number of records processed = 30,000

可以向 INGEST GET STATS 命令分配临时任务 ID,获得有关某个特定 INGEST 命令的详细信息。它实际上能够使您避免重新输入实际的任务 ID。

基本 INGEST GET STATS 命令输出的示例

清单 34. 临时任务 ID 为 4 的 INGEST 任务的统计信息
=> INGEST GET STATS FOR 4
                    
Ingest job ID = DB21000:20101116.123456.234567:34567:4567
Database      = MYDB
Target table  = MY_SCHEMA.TARGET_TABLE
                    
Overall          Overall          Current         Current 
ingest rate      write rate       ingest rate     write rate       Total records
(records/second) (writes/second) (records/second) (writes/second)
---------------- --------------- ---------------  ---------------- ----------------      
54321            5432           76543             87654            98765

您可以使用 EVERY--number—SECONDS 子句按固定时间间隔获取统计信息。此外,您还可以使用 SHOW TOTALS 获得 ingest 的每个阶段(transporter、formatter 和 flusher)的汇总统计数据。有关 INGEST 命令语法和可用选项的更多信息,请参见 DB2 Information Center。


IMPORT, LOADINGEST 对比

表 2. IMPORT, LOADINGEST 工具的对比
IMPORTLOADINGEST
服务器端工具服务器端工具DB2 客户机附带的客户端工具
可相互作用 – 对大量数据的处理速度较慢写入格式化页面 – 对大量数据负载的处理速度较快 高速客户端 DB2,可以持续将数据从文件和管道中传递到 DB2 目标表
所有插入的行均被记录只执行最少量的日志记录所有插入的行均被记录
触发器被触发触发器未被触发触发器被触发
在导入过程中对约束条件进行验证对所有惟一键进行验证。其他约束条件应当在完成加载后由 SET INTEGRITY 命令验证在 ingest 过程中对约束条件进行验证
如被中断,表可恢复到最后一次提交点之前 如被中断,表将处于 LOAD 暂挂状态。可以重启或恢复受影响的表 如被中断,表可恢复到最后一次提交点之前。您可以从该时间点重新启动 ingest 流程
不需要备份需要对受影响的表空间进行备份 不需要备份
可以使用 SET UTIL_IMPACT_PRIORITYutil_impact_lim DBM 参数控制对服务器的影响。可以使用 SET UTIL_IMPACT_PRIORITY command 和 util_impact_lim DBM 参数控制对服务器的影响。可以使用 SET UTIL_IMPACT_PRIORITY 命令或 util_impact_lim DBM 参数消除影响,因为这是一个客户端工具。
支持导入到可更新的视图(类型化视图除外)、范围聚集表(range clustered table,RCT)和别名(nickname)不支持导入到可更新的视图、RCT 或别名支持摄入到可更新的视图(类型化视图除外)、RCT 和别名
支持导入到类型化表不支持导入到类型化表不支持导入到类型化表
支持文件中的 LOB:BLOB、CLOB、DBCLOB、NCLOB支持文件中的 LOB:BLOB、CLOB、DBCLOB、NCLOB不支持文件中的 LOB
支持导入结构化类型不支持导入结构化类型不支持导入结构化类型

数据移动工具和隐藏列

隐藏列 是指由 IMPLICITLY HIDDEN 属性定义的表列。该列只有在显式引用的情况下可用。例如,如果对一个表运行 SELECT * 查询,那么隐式隐藏的列不会返回到结果表中。要访问该列,应当在 SELECT 语句中显式引用其名称。

IMPORT, INGESTLOAD 工具要求您指定隐藏列的数据是否包含到操作中。在处理包含隐式隐藏列的表时,如果未指定列的列表,那么数据移动工具必须使用 implicitlyhiddenincludeimplicitlyhiddenmissing 文件类型修饰符。您还可以使用 DB2_DMU_DEFAULT 注册表变量设置数据移动工具遇到含有隐藏列的表时的默认行为。类似地,EXPORT 需要您指定隐藏列的数据是否包含到操作中。

如果您有一个表 table1,其中含有隐藏列 c3,那么下面的命令将展示如何通过使用 insert into 子句显式声明隐藏列的名称,从而将数据加载到隐藏列中。

db2 load from delfile1 of del
insert into table1 (c1, c2, c3)

或者,当输入文件包含隐藏列的数据时,您可以指定 implicitlyhiddeninclude 修饰符,当输入文件不包含隐藏列数据时,则使用 implicitlyhiddenmissing 修饰符。

db2 load from delfile1 of del modified by implicitlyhiddeninclude
insert into table1

此外,您可以在服务器端使用 DB2_DMU_DEFAULT 注册表变量来设置数据移动工具在遇到隐含列时的行为。

db2set DB2_DMU_DEFAULT=IMPLICITLYHIDDENINCLUDE
db2 load from delfile1 of del insert into table1

ADMIN_MOVE_TABLE 流程

您可以通过 ADMIN_MOVE_TABLE 在线或离线移动数据。事实上,该流程可用于将一个表中的数据移动到一个同名的新的表对象中(但是两个表具有不同的存储特性,比如不同的表空间),同时数据保持在线状态并可以进行访问。您还可以在移动表时生成一个新的最佳压缩字典。

ADMIN_MOVE_TABLE 存储流程创建了一个协议表,表行包含与要移动的表有关的状态信息和配置选项。该流程返回的内容为协议表中与要移动的表有关的行。

您可以通过两种方式调用 ADMIN_MOVE_TABLE。

方法 1:只修改目标表的表定义的某些特定部分(例如,如果您仅仅是虚伪修改表对象的表空间)。您只需要在调用流程时填充 data_tbsp, index_tbsplob_tbsp 参数,将其他可选参数留空。

示例

该示例使用第一种方法调用了存储流程,其中目标表在流程内定义,移动位于模式 SCHEMA1 内的一个名为 T1 的表。此外,目标表的列定义被传递给流程。

清单 35. 存储流程使用第一种方法的示例
CALL SYSPROC.ADMIN_MOVE_TABLE(
'SCHEMA1',
'T1',
'ACCOUNTING',
'ACCOUNT_IDX',
'ACCOUNT_LONG',
'',
'',
'',
'CUSTOMER VARCHAR(80), REGION CHAR(5), YEAR INTEGER, CONTENTS CLOB',
'',
'MOVE')

上面的示例将表 SCHEMA1.T1 移动到一个同名的新表中,新表的列定义包括:CUSTOMER VARCHAR(80)、REGION CHAR(5)、YEAR INTEGER 和 CONTENTS CLOB,它位于表空间 ACCOUNTING 中,索引表空间为 ACCOUNT_IDX,LOB 表空间为 ACCOUNT_LONG。

方法 2— 创建目标表并将表名提供给流程。这为您提供了更多的控制权和灵活性,使您可以事先创建目标表,而不是由存储流程创建它。

示例

本例与前面的示例等效,但是它使用第二种方法调用了存储流程,其中目标表在流程以外创建,并在 target_tabname 参数中命名,移动和前例相同的表。

清单 36. 存储流程使用第二种方法的示例
CREATE TABLE SCHEMA1.T1_TGT (
CUSTOMER VARCHAR(80), 
REGION CHAR(5), 
YEAR INTEGER, 
CONTENTS CLOB) 
IN ACCOUNTING 
INDEX IN ACCOUNT_IDX 
LONG IN ACCOUNT_LONG'
CALL SYSPROC.ADMIN_MOVE_TABLE(
'SCHEMA',
'T1',
'T1_TGT',
'',
'MOVE')

对于在线数据移动:

  1. 流程创建一个在其中复制数据的影子表。
  2. 在复制阶段对源表作出的任何修改都将通过触发器捕捉并放到一个临时表中。
  3. 完成复制阶段后,在临时表中捕捉到的修改将被重放到影子副本中。
  4. 之后,存储流程将使源表处于离线钻探,向影子副本及其索引分配源表名称和索引名称。
  5. 影子表被恢复在线状态并替换源表。默认情况下,会删除源表,但您可以使用 KEEP 选项将它以其他名称保存。

显然,在线操作会消耗更多的服务器资源(硬盘空间和处理功能),因此确保只在可用性比成本、空间、移动性能和处理开销更重要的情况下这样做。此外,避免在线移动不带索引的表,特别是惟一性索引,因为这会导致死锁和复杂或高开销的重放行为。

处理在线移动故障

如果在线移动失败,那么再次运行在线移动:

  1. 修复造成表移动故障的问题。
  2. 判断表移动失败时所处的阶段,方法是查询 SYSTOOLS.ADMIN_MOVE_TABLE 协议表,获得状态信息。
  3. 再次调用存储流程,指定相应的选项:
    • 如果流程的状态为 INIT,则使用 INIT 选项。
    • 如果流程的状态为 COPY,则使用 COPY
    • 如果流程的状态为 REPLAY,则使用 REPLAYSWAP
    • 如果流程状态为 CLEANUP,则使用 CLEANUP

如果在线表移动的状态不是 COMPLETED 或 CLEANUP,那么您可以对存储流程指定 CANCEL 来取消移动操作。

ADMIN_MOVE_TABLE 流程和时间表

在使用 ADMIN_MOVE_TABLE 将活跃的系统周期时间表的数据移动到同名的新表时有一些局限性。以下操作将受到阻塞:

  • 在线移动操作期间会修改系统周期时间表,或者相关历史表的 Alter 表操作会受到阻塞。
  • ADMIN_MOVE_TABLEKEEP 选项无法用于系统周期时间表。

此外,历史表不支持在线表移动操作。


其他 DB2 数据移动工具

db2move

db2move 是一个数据移动工具,可用于在 DB2 数据库之间移动大量数据。该命令支持的操作包括 EXPORT, IMPORT, LOADCOPYEXPORT, IMPORTLOAD 操作的行为与前文所述完全一致。COPY 可能是您惟一不熟悉的操作。该操作将一个或多个模式中的表复制到目标数据库中。db2move 的语法非常简单,如下所示:

db2move database_name
    action
        options

从系统编目表中提取了一组用户表,并且使用 PC/IXF 格式导出每个表。随后可以将 PC/IXF 文件导入或加载到另一个 DB2 数据库中。

下面是一些示例。该命令以 REPLACE 模式导入样例数据库中的所有表,并且指定了用户 ID 和密码:db2move sample IMPORT -io REPLACE -u userid -p password。并且该命令以 REPLACE 模式加载了 db2admin 和 db2user 数据库模式下的所有表:db2move sample LOAD -sn db2admin,db2user -lo REPLACE

请参考 Command Reference 获得所有选项的完整列表和描述。但是,COPY 值得进一步讨论。使用 COPY 时,您将通过 -sn 选项指定一个或多个模式。在 -sn 选项中,唯有具有完全相同模式名的表会被复制(通过导出方式)。如果指定了多个模式名,那么可以使用逗号分隔它们,不能留有空格。请参考下面的示例。

db2move sample COPY -sn db2inst1,prodschema -co TARGET_DB acctdb USER peter 
USING petepasswd DDL_AND_LOAD

上面的 db2move 命令复制了 db2inst1 和 prodschema 模式下受支持的对象。后面的 -co 选项使这一命令更加有趣。TARGET_DB 指定了目标数据库,模式被复制到该数据库(本例中为 acctdb)。在指定了 COPY 的情况下,该选项是强制执行的。此外,目标数据库必须不同于源数据库。在连接到目标数据库时,您可以为用户和密码提供 USERUSING 选项。

默认情况下,将创建来自源模式的受支持对象,并在目标数据库中填充表。这是 DDL_AND_LOAD 模式的行为。还有其他两种模式可用:DDL_ONLY 和 LOAD_ONLY。顾名思义,DDL_ONLY 只从源模式中创建所有受支持的对象,而 LOAD_ONLY 将所有指定的表从源数据库加载到目标数据库。请注意,在使用这一选项时,目标数据库中必须已经存在该表。

在将对象复制到目标数据库时,有时需要对模式进行重命名。SCHEMA_MAP 选项可以实现这个目的。您只需要提供如下所示的一个或多个模式映射对:SCHEMA_MAP ((source_schema1,target_schema1),(source_schema2,target_schema2))

在使用 SCHEMA_MAP 时需要额外留心。只有对象本身的模式被重命名,对象体内部符合资格的对象仍保持不变。例如,CREATE VIEW FOO.v1 AS 'SELECT c1 FROM FOO.T1'

将模式 FOO 重命名为 BAR 会将导致 CREATE VIEW BAR.v1 AS 'SELECT c1 FROM FOO.T1'。如果 FOO.T1 未定义,那么目标数据库中创建的 BAR.v1 可能会失败。

类似的映射方法也适用于表空间。例如,您希望复制后的表保存到与源数据库不同的表空间。db2move 命令进行了扩展,使您可以指定表空间映射的名称。请考虑以下选项:TABLESPACE_MAP ((TS1,TS2),(TS2,TS3),SYS_ANY)

上述表空间名称映射表明 TS1 被映射到目标 TS2,源 TS2 被映射到目标 TS3。SYS_ANY 表示剩余的表空间将使用数据库管理器根据表空间选择算法所选的表空间。让我们通过一个例子展示所有这些内容。

清单 37. db2move 工具的示例
db2move sample COPY -sn db2inst1,prodschema 
    -co TARGET_DB acctdb USER peter USING petepasswd LOAD_ONLY
        SCHEMA_MAP ((db2inst1,db2inst2),(prodschema,devschema))
        TABLESPACE_MAP SYS_ANY
        NONRECOVERABLE

该命令将 db2inst1 和 prodschema 中的受支持对象从 SAMPLE 数据库复制到 ACCTDB 数据库。授权 ID peter 和相关密码被用于连接 ACCTDB。目标表已经存在于 ACCTDB 中,并且这些表将进行重新填充。db2inst1 和 prodschema 模式下的所有对象现在都分别属于 db2inst2 和 devschema。与使用 SAMPLE 数据库中定义的表空间名不同,这次将使用 ACCTDB 中默认的表空间。

NONRECOVERABLE 选项允许用户使用完成复制后立即加载的表空间。不需要对表空间进行备份,但是强烈建议您在早期空闲时对它们进行备份。

ADMIN_COPY_SCHEMA 流程

ADMIN_COPY_SCHEMA 用于复制特定的模式和其中的所有对象。这会创建新的目标模式对象,名称与源模式中的对象相同,但是使用了目标模式限定符。ADMIN_COPY_SCHEMA 可用于复制包含或不包含原始表数据的表。

该流程调用 LOAD 命令将数据加载到目标模式,因此它有三种操作模式:

  • DDL:从源模式中创建所有受支持对象的空副本。
  • COPY:从源模式中创建所有对象的空副本,然后对每个目标模式表加载数据。加载在 NONRECOVERABLE 模式下完成。在调用 ADMIN_COPY_SCHEMA 后必须执行一次备份;否则,复制后的表在恢复后将无法访问。
  • COPYNO:从源模式中创建所有对象的空副本,然后对每个目标模式表加载数据。加载在 COPYNO 模式下完成。

示例

在本例中,位于表空间 SOURCETS1 和 SOURCETS2 的 SOURCE_SCHEMA 对象将移动到目标模式下,并从目标 TARGET_SCHEMA 分别复制到表空间 TARGETTS1 和 TARGETTS2。SYS_ANY 表示剩余的表空间将使用数据库管理器根据表空间选择算法所选的表空间。

CALL SYSPROC.ADMIN_COPY_SCHEMA('SOURCE_SCHEMA', 'TARGET_SCHEMA', 
    'COPY', NULL, 'SOURCETS1 , SOURCETS2', 'TARGETTS1, TARGETTS2, 
    SYS_ANY', 'ERRORSCHEMA', 'ERRORNAME')

db2look

db2look 是一个可以从命令提示行调用的便捷工具。它可以执行下面的操作:

  • 从数据库对象提取数据库定义语言(DDL)语句。
  • 生成 UPDATE 语句,以便更新数据库管理器和数据库配置参数。
  • 生成 db2set 命令,以便设置 DB2 配置文件注册表。
  • 提取和生成数据库统计报告。
  • 生成 UPDATE 语句,以便复制数据库对象的统计数据。

LOAD 之类的工具要求目标表已经存在。您可以使用 db2look 提取表的 DDL 并对目标表运行,然后调用 LOAD 操作。如下例所示,db2look 使用起来非常简单。该命令将为数据库部门的 peter 创建的所有对象生成 DDL 语句,输出将保存在 alltables.sql 中。

db2look -d department -u peter -e -o alltables.sql

下一个命令将生成以下内容:

  • 数据库部门的所有对象的(由 -d, -a-e 选项指定)。
  • UPDATE 语句,复制数据库中的所有表和索引的统计数据(由 -m 选项指定)。
  • GRANT 授权语句(由 -x 选项指定)。
  • 为数据库管理员和数据库配置参数生成 UPDATE 语句,为配置文件注册表生成 db2set 命令(由 -f 选项指定)。
db2look -d department -a -e -m -x -f -o db2look.sql

db2look 还能够生成命令实现 XML 模式注册。下面的例子为模式名为 db2inst1 的对象生成了所需的 REGISTER XMLSCHEMACOMPLETE XMLSCHEMA 命令(由 -xs 选项指定)。输出 db2look.sql 将在 /home/db2inst1 下创建,由 -xdir 选项指定。

db2look -d department -z db2inst1 -xs -xdir /home/db2inst1 -o db2look.sql

db2batch

基准测试 是指对应用程序的各个方面进行评估,如数据库响应时间、CUP 和内存使用情况。基准测试需要在一个可重复的环境中进行,也就是说,在相同的条件下进行相同的测试。然后对测试结果进行评估和比较。

db2batch 就是一个基准测试工具,它获取一组 SQL 和/或 XQuery 语句,动态准备和描述语句,然后返回一个回答集合。根据 db2batch 命令中使用的选项,回答集合 (answer set) 可能会返回语句执行所经过的时间、有关内存使用情况的数据库管理器快照,比如缓冲池和缓存信息。

您可以在一个普通文件或标准输入中指定希望运行标准测试的语句。可以在输入文件中设置许多控制选项。可以使用如下语法指定:--#SET control_option value。下面是一个带有控制选项的输入文件示例。要获得控制选项的完整列表,请参考 Information Center。

清单 38. db2batch 示例
-- db2batch.sql
-- ------------
--#SET PERF_DETAIL 3
--#SET ROWS_OUT 5
                                      
-- This query lists employees, the name of their department
-- and the number of activities to which they are assigned for
-- employees who are assigned to more than one activity less than
-- full-time.
--#COMMENT Query 1
select lastname, firstnme,
       deptname, count(*) as num_act
from employee, department, emp_act
where employee.workdept = department.deptno and
      employee.empno = emp_act.empno and
      emp_act.emptime < 1
group by lastname, firstnme, deptname
having count(*) > 2;
--#SET PERF_DETAIL 1
--#SET ROWS_OUT 5
                    
--#COMMENT Query 2
select lastname, firstnme,
       deptname, count(*) as num_act
from employee, department, emp_act
where employee.workdept = department.deptno and
      employee.empno = emp_act.empno and
      emp_act.emptime < 1
group by lastname, firstnme, deptname
having count(*) <= 2;
  • 选项 PERF_DETAIL 3 表示将返回运行时间所反映的性能信息,数据库管理器、数据库和应用程序的快照。
  • 选项 ROWS_OUT 5 表示不管查询返回多少行数据,只从结果集中获取五行数据。
  • COMMENT Query1 为语句提供了一个名称:Query1。

下面的命令将对 SAMPLE 数据库调用基准测试工具,输入文件为 db2batch.sql: db2batch -d sample -f db2batch.sql

该命令将返回只包含 5 行数据的结果集,包括执行时间、查询所用的 CPU 时间。还将返回数据库管理器、数据库和应用程序快照。由于输出内容很多,我们仅显示了 db2batch 命令的摘要。

清单 39. db2batch 摘要表
* Summary Table:
                    
Type      Number      Repetitions Total Time (s) Min Time (s)   ...
--------- ----------- ----------- -------------- -------------- 
Statement           1           1       0.052655       0.052655 ...
Statement           2           1       0.004518       0.004518 ...
                    
                    
...Max Time (s)   Arithmetic Mean Geometric Mean Row(s) Fetched Row(s) Output 
-------------- --------------- -------------- -------------- ------------- 
...      0.052655        0.052655       0.052655              5             5 
...      0.004518        0.004518       0.004518              8             5 
                    
* Total Entries:              2               
* Total Time:                 0.057173 seconds
* Minimum Time:               0.004518 seconds
* Maximum Time:               0.052655 seconds
* Arithmetic Mean Time:       0.028587 seconds
* Geometric Mean Time:        0.015424 seconds

db2batch 命令支持许多选项。我们仅仅列出了其中一些,供您了解这款工具的强大功能:

  • -m parameter_file 指定了一个输入文件,以及要绑定到 SQL 语句参数标记的参数值。
  • -r result_file 指定了要包含命令结果的输出文件。
  • -i short|long|complete 指定了在运行时间间隔内要测量的内容。short 将测量运行每条语句所需的时间。long 测量运行每条语句所需的时间以及语句之间的时间间隔。complete 测量运行每条语句的时间,并分别报告准备、执行和获取语句所需的时间。
  • -iso 指定了对语句使用的隔离级别。在默认情况下,db2batch 使用 Repeatable Read 隔离级别。

DB2 维护工具

RUNSTATS 工具

DB2 使用一种高级的基于成本的优化器判断数据的访问情况。所作出的决策主要依据关于数据库表和索引大小的统计信息。因此,随时更新数据库统计信息非常重要,这样才能够选择出最高效的数据访问方案。RUNSTATS 工具用于更新表及相关索引的物理特性统计信息。这些特性包括记录数量(基数)、页面数量、平均记录长度,等等。

让我们通过一些例子来了解该工具的用法。下面的命令将收集有关 db2user.employee 表的统计信息。在计算统计信息时允许对表进行读和写访问:RUNSTATS ON TABLE db2user.employee ALLOW WRITE ACCESS

下面的命令将对 db2user.employee 表收集统计信息,同时对 empid 和 empname 列收集分布统计信息。在运行此命令时,该表仅接受读请求:

RUNSTATS ON TABLE db2user.employee WITH DISTRIBUTION ON 
COLUMNS ( empid, empname ) ALLOW READ ACCESS

下面的命令将对 db2user.employee 表收集统计信息,并对该表的所有索引收集详细统计信息:RUNSTATS ON TABLE db2user.employee AND DETAILED INDEXES ALL

您可以具体指定要收集的数据库对象统计信息。可以使用不同的 RUNSTATS 选项组合收集表统计信息、索引统计信息、分布统计信息、抽样信息,等等。要简化统计信息的收集,可以在对统计配置文件发出 RUNSTATS 命令时保存所指定的选项。如果希望对某个表反复收集相同的统计信息,但不希望重新输入命令选项,那么可以使用 RUNSTATS ON TABLE db2user.employee USE PROFILE

该命令将使用 db2user.employee 表的统计配置文件中的选项收集该表的统计信息。那么该如何设置统计配置文件呢?非常简单,只需使用 SET PROFILE ONLY 选项。

RUNSTATS ON TABLE db2user.employee WITH DISTRIBUTION ON COLUMNS ( empid, empname ) 
SET PROFILE ONLY

请注意,该选项将仅设置统计文件,RUNSTATS 命令不会运行。如果需要修改此前注册的统计配置文件,那么可以使用 UPDATE PROFILE ONLY 选项。类似地,该选项将只更新配置文件而不运行 RUNSTATS 命令。如果希望更新配置文件和统计信息,那么可以使用 UPDATE PROFILE

RUNSTATS ON TABLE db2user.employee WITH DISTRIBUTION DEFAULT 
NUM_FREQVALUES 50 NUM_QUANTILES 50
UPDATE PROFILE

RUNSTATS 是一个资源密集型工具。但是,为了维持高效的数据库操作,必须定期收集统计信息。您应当查找减少的数据库行为的常规窗口,这样就可以在不影响数据库性能的情况下收集数据库统计信息。在某些环境中,不存在这样的窗口。可以考虑对 RUNSTATS 进行限制,从而限制该工具消耗的资源的数量。当数据库活跃度很低时,该工具将准许占用更多的资源。另一方面,当数据库行为增多时,这会减少用于执行 RUNSTATS 而收集的资源。下面展示了如何实现对 RUNSTATS 的限制。

RUNSTATS ON TABLE db2user.employee WITH DISTRIBUTION DEFAULT 
NUM_FREQVALUES 50 NUM_QUANTILES 50
UTIL_IMPACT_PRIORITY 30

可接受的优先值范围为 1 到 100,100 表示最高优先级(即未进行限制),1 表示最低优先级;50 是默认的优先级级别。

请注意,在创建数据库时,默认情况下启用了自动统计收集功能。通过将数据库配置参数 AUTO_RUNSTATS 设置为 OFF,可以禁用该功能。

REORG 和 REORGCHK 工具

添加到数据库和从其中移除的数据不一定呈顺序排列。对于这种情况,DB2 必须执行额外的读操作来访问数据。这通常意味着需要执行更多的磁盘 I/O 操作,而众所周知这类操作开销很高。对此,您应当考虑将表物理重组到索引,使相关的数据彼此接近,从而最大程度地减少 I/O 操作。

REORG 是一个对表或索引进行数据重组的工具。尽管数据在物理方式上进行了重组,DB2 仍然提供了进行在线或离线重组的选项。离线 REORG 默认情况下允许用户对表进行读访问。您可以通过指定 ALLOW NO ACCESS 选项对表访问进行限制。在线 REORG(也称为 in-place REORG)支持对表进行读和写访问。由于数据页进行了重组,并发应用程序需要等待 REORG 完成当前的页面。您可以使用相应的选项轻松地停止、暂停或恢复这个流程。

下面的示例非常易于理解:

REORG TABLE db2user.employee INDEX db2user.idxemp INPLACE ALLOW WRITE ACCESS
REORG TABLE db2user.employee INDEX db2user.idxemp INPLACE PAUSE

您还可以对索引进行重组。如果下面的一个示例使用了 CLEANUP 子句,则不会执行重组,而是执行一个 cleanup 操作。

REORG INDEX db2user.idxemp FOR TABLE db2user.employee ALLOW WRITE ACCESS 
REORG INDEX db2user.idxemp FOR TABLE db2user.employee CLEANUP ONLY

REORGCHK 是另一种数据维护工具,它提供了一个选项来获取当前数据库统计信息或更新数据库统计信息。它还将生成一个统计信息报告,其中包含 REORG 指标。通过使用统计公式,如果需要执行 REORG 的话,那么 REORGCHK 将使用星型符(*)对表或索引进行标识。

让我们看一些示例。下面的命令将生成运行时授权 ID 拥有的所有表的当前统计信息的报告:REORGCHK CURRENT STATISTICS ON TABLE USER

以下命令将更新统计信息并生成模式 smith 下创建的所有表的报告:REORGCHK UPDATE STATISTICS ON SCHEMA smith

以下是一些 REORGCHK 输出的样例:

清单 40. REORGCHK 输出的样例
Table statistics:
F1: ... < 5
F2: ... > 70
F3: ... > 80
                    
SCHEMA.NAME                  ...   F1  F2  F3 REORG
---------------------------------------------
Table: DB2INST1.XMLFILES     ...    0  89  98 --- 
---------------------------------------------
                    
Index statistics:
                    
F4: ... > 80
F5: ... > MIN(50, (100 - PCTFREE))
F6: ... < 100
F7: ... < 20
F8: ... < 20
                    
SCHEMA.NAME                  ... PCT_PAGES_SAVED  F4  F5  F6  F7  F8 REORG  
---------------------------- ... -----------------------------------------
Table: DB2INST1.XMLFILES     
Index: DB2INST1.IDX1         ...              50  98  82   -   0   0 ----- 
Index: DB2INST1.IDX2         ...               0  71   -   -   0   0 *---- 
Index: DB2INST1.IDX3         ...              33  98 220   -   0   0 ----- 
--------------------------------------------------------------------------

请注意,DB2INST1.IDX2 在 REORG 列中用星型符号进行了标识,因为它的公式 F4 的计算结果为 71,不符合评估标准:F4: ... > 80。

上面的 REORGCHK 输出表示只有 DB2INST1.IDX2 索引需要重组。因此,您只需对索引而不是整个数据库运行 REORG 命令。

REBIND 工具和 FLUSH PACKAGE CACHE 命令

在执行数据库应用程序或任何 SQL 语句之前,必须先由 DB2 执行预编译并生成一个包。这个包是一个数据库对象,包含编译后的将在应用程序源文件中使用的 SQL 语句。DB2 使用这些包访问 SQL 语句中引用的数据。因此,DB2 优化器如何为这些包选择数据访问方案?它将依据包创建时的数据库统计信息。

对于静态 SQL 语句,会在编译时创建这些包,并将它们绑定到数据库。如果统计信息进行了更新,以反映物理数据库特性,那么现有的包也应当进行更新。REBIND 工具允许您重新创建一个包,这样就可以使用当前的数据库统计信息。这条命令非常简单:REBIND PACKAGE package_name

在许多情况下,SQL 语句包含主机变量、参数标记和专用寄存器。这些变量的值到运行时才会变为已知。在 REBIND 命令中使用 REOPT 子句后,您可以指定是否需要通过对主机变量、参数标记和专用寄存器使用真实值让 DB2 优化访问路径。可以使用三个 REOPT 选项:

  • NONE:在 SQL 语句中使用的主机变量、参数标记、专用寄存器的真实值不会用于优化访问方案。相反,将使用这些变量的默认预计值。
  • ONCE:在第一次执行查询时,将使用主机变量、参数标记或专用寄存器的真实值优化给定 SQL 语句的访问路径。
  • ALWAYS:将始终使用主机变量、参数标记或专用寄存器的真实值编译和优化给定 SQL 语句的访问路径。
 REBIND PACKAGE ACCTPKG REOPT ONCE

不过,如果您准备修改应用程序源,则需要显式丢弃现有的相关包并重新创建它们。REBIND 工具并不会用于此目的。我们在这里提交这一点是因为 DBA 通常会误解 REBIND 的用法。

对于动态 SQL 语句,会在运行时预编译它们,并将它们存储到包缓存中。如果统计信息进行了更新,那么您可以刷新缓存,从而再次编译动态 SQL 语句,获得更新后的统计信息。命令类似如下所:FLUSH PACKAGE CACHE DYNAMIC

数据库维护流程

现在您已经了解了 RUNSTATS, REORG, REORGCHK, REBINDFLUSH PACKAGE CACHE,让我们看一看数据维护流程,应当定期对数据库执行这个流程。该流程将通过下面的流程图演示。

图 4. 数据库维护流程
数据库维护流程

DB2 Advisors

DB2 Design Advisor

Design Advisor 可以帮助您判断哪些数据库对象可以提升给定工作负载的性能。一个工作负载实际上就是一组 SQL 语句,Design Advisor 将根据工作负载的特性、数据库的特性和硬件资源对其进行评估。它使用 DB2 优化器、数据库统计信息和 Explain 机制生成有关新索引、新物化查询表(MQT)、多维聚簇索引(multidimensional clustering,MDC)表转换、表的重新分布、索引删除和指定工作负载未使用的 MQT 等建议。您可能熟悉 MQT 和 MDC;它们是高级的数据库对象,不属于 DBA 考试(611)的内容。如果您对高级数据库对象感兴趣,请参考 DB2 Information Center。

您可以使用 db2advis 从命令行启动 DB2 Advisor,同时提供一些必要的输入。可以使用几种方法通过 db2advis 命令指定一个工作负载。

在发出 db2advis 命令时,您可以指定一个 SQL 语句。下面的示例将评估 SQL 语句并提出相应的建议。

db2advis -d sample -s 
        "select * from employee where workdept='A00' and salary > 40000" 
        -o output.out

您可以使用在快照中捕捉的一组 SQL 语句。为此,您需要使用 db2 reset monitor for database database-name 命令重置数据库监视器。

让您的应用程序运行指定的一段时间,并允许 DB2 快照捕捉动态 SQL 语句。发出以下 db2advis 命令来评估工作负载并提供一些建议。此处的 -g 选项表示 Design Advisor 从动态 SQL 快照获得 SQL 语句。此外,-p 选项会将捕捉到的 SQL 语句保存到 ADVISE_WORKLOAD 系统表中。

 db2advis -d sample -g -p -o output.out

或者,您可以创建一个工作负载文件来包含一组 SQL 语句。在工作负载中设置语句的频率。下面是一个示例工作负载文件。

--#SET FREQUENCY 100
SELECT COUNT(*)FROM EMPLOYEE;
SELECT * FROM EMPLOYEE WHERE LASTNAME='HAAS';
--#SET FREQUENCY 1
SELECT * FROM EMPLOYEE WHERE WORKDEPT='A00' AND SALARY > 40000;

然后,只运行带有 -i 选项的 db2advis 命令。

 db2advis -d sample -i input.sql -o output.out

结束语

本教程介绍了大量可以帮助您维护 DB2 数据的 DB2 工具。您学习了以下内容:

  • DB2 数据移动工具可以处理的文件格式
  • EXPORT 工具,用于从表或视图中提取数据(也支持 XML 数据)
  • IMPORT 工具,用于将数据批量插入到表或视图中(也支持 XML 数据)
  • LOAD 工具,通过将已格式化的页面直接写入数据库,将输入数据填充到表中
  • 加载操作的四个阶段:加载、构建、删除和索引复制
  • 如果已加载的表包含约束条件而未定义惟一性约束,那么该表将处于 SET INTEGRITY PENDING 状态。
  • 使用 SET INTEGRITY 命令删除 SET INTEGRITY PENDING 状态
  • 对于可恢复的表,已加载的表的表空间将被置于 BACKUP PENDING 状态
  • INGEST 工具是一个高速客户端 DB2 工具,它使用类似 SQL 的命令从文件中摄入数据,并将这些数据传递给 DB2 LUW 表
  • 使用不同的选项和文件类型修饰符自定义 EXPORT, IMPORT, LOADINGEST 操作
  • 对 IMPORT、LOAD 和 INGEST 工具进行比较
  • 每种数据维护工具的目的:RUNSTATS, REORG, REORGCHK, REBINDFLUSH PACKAGE CACHE 命令
  • 在数据移动中使用存储流程:ADMIN_MOVE_TABLEADMIN_COPY_SCHEMA
  • 使用 db2move、db2look、db2batch 和 Design Advisor 之类的工具。

最后,祝您顺利通过考试!

参考资料

学习

获得产品和技术

  • 使用可直接从 developerWorks 下载获得的 IBM 试用软件 构建您的下一个开发项目。
  • 您现在可以免费试用 DB2。下载 DB2 Express-C,这是为社区提供的 DB2 Express Edition 的免费版本,它具有与 DB2 Express Edition 相同的核心数据特性,为构建和部署应用程序提供了坚实的基础。

讨论

条评论

developerWorks: 登录

标有星(*)号的字段是必填字段。


需要一个 IBM ID?
忘记 IBM ID?


忘记密码?
更改您的密码

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件

 


在您首次登录 developerWorks 时,会为您创建一份个人概要。您的个人概要中的信息(您的姓名、国家/地区,以及公司名称)是公开显示的,而且会随着您发布的任何内容一起显示,除非您选择隐藏您的公司名称。您可以随时更新您的 IBM 帐户。

所有提交的信息确保安全。

选择您的昵称



当您初次登录到 developerWorks 时,将会为您创建一份概要信息,您需要指定一个昵称。您的昵称将和您在 developerWorks 发布的内容显示在一起。

昵称长度在 3 至 31 个字符之间。 您的昵称在 developerWorks 社区中必须是唯一的,并且出于隐私保护的原因,不能是您的电子邮件地址。

标有星(*)号的字段是必填字段。

(昵称长度在 3 至 31 个字符之间)

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件.

 


所有提交的信息确保安全。


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Information Management
ArticleID=941352
ArticleTitle=DB2 10.1 DBA for Linux, UNIX, and Windows 基础认证考试 611 备考教程,第 5 部分: DB2 工具
publish-date=08192013