DIY编程器网

标题: DB2下数据转移任务操作实例 [打印本页]

作者: admin 时间: 2014-10-13 12:51
标题: DB2下数据转移任务操作实例
使用 DB2 LOAD 实用程序的 FROM CURSOR 选项简化 DB2 for Linux, UNIX, and Windows的数据转移过程。本文介绍 LOAD FROM CURSOR 特性并提供两个接口 Command Line Processor 和 ADMIN_CMD 存储过程的使用示例。

简介

      典型的 DB2 数据转移任务涉及三个步骤：

◆把数据以二进制或文本格式从源数据库导出到一个临时数据交换文件

◆在系统之间转移生成的文件

◆把数据从文件导入或装载到目标数据库中

      在数据量很大的情况下，使用 EXPORT 实用程序生成数据交换文件常常要花费很长时间。另外，在把数据移入和移出数据库时，必须考虑不同的数据库编码页和操作系统。

      可以使用 LOAD 实用程序的 FROM CURSOR 选项避免这些问题。当指定 FROM CURSOR 选项时，LOAD 实用程序直接把一个 SQL 查询的结果集作为数据装载操作的来源，这样就不需要生成临时数据交换文件。因此，LOAD FROM CURSOR 是在不同的表空间或数据库之间快速轻松地转移数据的方法。可以在命令行上执行 LOAD FROM CURSOR，也可以通过使用 DB2 的 ADMIN_CMD 存储过程在应用程序或存储过程中执行它。本文介绍 LOAD FROM CURSOR 特性并提供两个接口 Command Line Processor (CLP) 和 ADMIN_CMD 存储过程的使用示例。

把表转移到另一个表空间

      首先，看看如何把表从一个表空间转移到另一个表空间。如果创建表的表空间的页面大小不合适，或者应该用另一个缓冲区池访问表，就可能需要执行这种数据转移。在 9.1 以前的 DB2 版本中，常常由于达到表空间的最大大小而在表空间之间转移表。但是，在 DB2 9.1 和更高版本中，这应该不再是问题了，因为表空间大小限制已经显著提高了(前提是使用大表空间，而不是以前使用的常规表空间)。

      这个示例场景首先创建 DB2 SAMPLE 数据库。这可以通过在命令行上调用 db2sampl 命令来完成，见清单 1。

      清单 1. 创建 SAMPLE 数据库

除了其他表之外，SAMPLE 数据库包含一个名为 SALES 的表。在默认情况下，在表空间 USERSPACE1 中创建这个表。可以通过对 DB2 编目视图 SYSCAT.TABLES 和 SYSCAT.TABLESPACES 执行查询来确认这一点。

清单 2. 判断 SALES 表的表空间

除了 USERSPACE1 表空间之外，还有第二个表空间 IBMDB2SAMPLEREL，它也用于存储用户数据。在这个示例场景中，IBMDB2SAMPLEREL 作为转移 SALES 表的目标表空间。执行 DB2 命令 LIST TABLESPACES，就可以看到一个数据库的所有表空间。清单 3 演示具体做法。

清单 3. 列出 SAMPLE 数据库的所有表空间

在把 SALES 表的内容复制到 IBMDB2SAMPLEREL 表空间之前，必须在目标表空间中创建一个空表，此表的结构应该与 SALES 表相同。因为在同一个数据库模式中不可能有两个同名的表，所以临时用 SALES_TMP 这个名称创建新的表。通过在 CREATE TABLE 命令中指定 LIKE 选项，创建一个与现有表结构相同的空表(清单 4)。通过 IN 选项显式地定义新表 SALES_TMP 的表空间。

清单 4. 创建数据转移操作所需的目标表 SALES_TMP

现在，可以执行数据转移操作了。使用 DECLARE CURSOR 命令定义一个游标，它使用 SELECT 语句读取源表 SALES 的所有数据。可以自由选择游标的名称，在此示例中使用 C1。然后，在用来填充目标表 SALES_TMP 的 LOAD 命令中引用此游标。此示例中的 LOAD 命令把它的消息写到日志文件 load_sales_tmp.msg 中。执行的 LOAD 操作包含 NONRECOVERABLE 选项。这意味着在数据库恢复的前滚阶段无法重新执行这个 LOAD 操作。因此，在执行数据转移操作之后，应该执行数据库备份，至少是表空间备份。LOAD 命令有其他选项可以避免这种情况，但是这些选项超出了本文的范围。更多信息请参见 DB2 Information Center 中对 LOAD 命令的说明(参见参考资料)。

清单 5. 执行 LOAD FROM CURSOR 操作把 SALES 表中的所有行复制到 SALES_TMP 表

在把 SALES 表中的所有行成功地复制到 SALES_TMP 表之后，可以删除源表(DROP TABLE 语句)。然后，把目标表 SALES_TMP 重命名为 SALES(RENAME TABLE 语句)。在使用 RENAME TABLE 时，只能修改表名，而不能修改表的模式名。因此，一定要在正确的模式中创建 SALES_TMP 表。

清单 6. 删除源表 SALES 并重命名目标表 SALES_TMP

再次对 DB2 编目视图执行查询，可以确认 SALES 表已经从原来的表空间 USERSPACE1 转移到了新的表空间 IBMDB2SAMPLEREL 中，见清单 7。

清单 7. 确认新 SALES 表的表空间

使用别名在另一个数据库中创建表拷贝

      除了可以在一个数据库中的表空间之间转移表之外，还可以在不同的数据库之间转移表。这意味着：通过使用 LOAD FROM CURSOR，也可以把表从一个数据库转移到另一个数据库。这可以通过两种方法完成：

      方法 1 - 在目标数据库中，使用 DB2 的联邦数据库机制访问源数据库。

      方法 2 - 使用 LOAD FROM CURSOR 命令的远程访问特性。

      这两种方法各有优点，下面分别讨论。

      方法1 使用 DB2 的联邦数据库机制

      通过对源数据库执行联邦访问来复制表内容。方法 1 要求目标数据库被配置为联邦数据库。因此，对应的 DB2 实例的 FEDERATED 参数必须设置为 YES(UPDATE DBM CFG)。在修改 DBM CFG 参数 FEDERATED 之后，必须重新启动 DB2 实例(db2stop/db2start)。在这个示例场景中，源和目标数据库在同一个 DB2 实例中运行。创建一个名为 MYSAMPLE 的空数据库作为目标数据库。对于没有特殊需求的测试数据库，可以使用 CREATE DATABASE 命令创建此数据库，不需要其他选项。

      清单 8. 在 DBM CFG 中启用联邦数据库支持并创建空的目标数据库 MYSAMPLE

与前面一样，必须在目标数据库 MYSAMPLE 中创建一个空表，它的结构与 SAMPLE 数据库中的 SALES 表相同。因此，应该使用 db2look 实用程序提取源数据库中 SALES 表的 DDL。

清单 9. 使用 db2look 实用程序提取源表 SALES 的 DDL

db2look 调用的结果是 sales.ddl 文件，其中包含 SALES 表的 CREATE TABLE 语句。如果在 SALES 表上定义了约束和/或索引，也会提取出对应的定义并写到 sales.ddl 文件中。清单 10 显示这些结果。

清单 10. db2look 调用的结果文件 sales.ddl

      在对目标数据库 MYSAMPLE 执行 sales.ddl 文件中的语句之前，在文本编辑器中打开此文件并对生成的语句做两处修改：

      在文件的开头，db2look 为源数据库 SAMPLE 生成 CONNECT 语句。因为希望对目标数据库 MYSAMPLE 执行下面的语句，所以要相应地修改 CONNECT 语句。

      因为在目标数据库 MYSAMPLE 中没有用于存储用户数据的表空间 IBMDB2SAMPLREL，所以要把 CREATE TABLE 语句中的表空间名替换为 USERSPACE1。

      清单 11. 结果文件 sales.ddl 中创建目标表所需的修改

在修改 sales.ddl 文件之后，通过调用 DB2 CLP (command line processor) 执行此脚本。

清单 12. 在 MYSAMPLE 数据库中创建目标表

到目前为止，只在目标数据库 MYSAMPLE 中创建了 SALES 表的空拷贝。准备数据转移操作的下一步是把源数据库 SAMPLE 编目为远程数据库。显然，这对于这个示例场景并不是必需的，因为源和目标数据库在同一个服务器上的同一个 DB2 实例中运行。但是在真实的环境中，必须对运行目标数据库的 DB2 实例执行以下 CATALOG 命令，从而允许对源数据库进行 TCP/IP 访问。

清单 13. 在节点和数据库目录中创建访问 SAMPLE 数据库所需的条目

      准备 LOAD FROM CURSOR 操作的最后一步是配置对源数据库 SAMPLE 中 SALES 表的联邦访问。这需要在目标数据库 MYSAMPLE 中创建对另一个数据库执行联邦访问所需的几个特殊对象：

包装器

      包装器支持访问外部数据源。外部数据源可以是另一个 DBMS(数据库管理系统)，比如 Oracle 或 SQL Server，也可以仅仅是 Excel 文件。根据应该访问的数据源，需要适当的包装器。这些包装器包含在单独的 IBM product WebSphere? Federation Server 中。如果只希望访问 DB2 产品系列(DB2 LUW 或 DB2 z/OS)的另一个数据库，那么需要 DRDA 包装器。DB2 LUW 中已经包含这个包装器，所以在这种情况下不需要 WebSphere Federation Server。通过在目标数据库中执行以下命令，创建 DRDA 包装器：CREATE WRAPPER DRDA。

服务器

      “服务器” 这个词在这里有点容易引起误解，因为这实际上意味着源数据库扮演(数据)服务器的角色。为了在目标数据库中识别源数据库，要创建一个服务器对象，它指定数据源的类型 (DB2/UDB VERSION 9.5)、要使用的包装器 (DRDA) 和访问源数据库所需的用户名/密码组合。使用 DBNAME 选项提供源数据库本身的名称。用户名/密码必须在引号中指定。为了避免命令行解释器删除引号，应该加上一个反斜杠 ()。可以自由选择服务器对象的名称。在这个示例场景中，使用名称 SRCSRV。

用户映射

      对于希望用前面定义的服务器对象访问远程数据库的每个用户，都必须创建一个用户映射。用户映射定义本地数据库 (MYSAMPLE) 中的授权 ID 如何映射到远程数据库 (SAMPLE) 中的授权 ID。在这个示例场景中，本地用户和远程用户是相同的，但是仍然必须定义用户映射。

别名

      别名是源数据库中的远程表的本地别名。指定了别名，就可以在目标数据库中用 SQL 语句查询远程表，就像查询任何本地表一样。

      清单 14. 创建联邦访问所需的数据库对象

      注意：这里描述的设置联邦访问所需的步骤完全独立于 LOAD FROM CURSOR 功能。这意味着这些是为远程数据库中的表创建别名的通用步骤。

      配置了对源数据库表的联邦访问之后，就可以像前面一样执行 LOAD FROM CURSOR 操作。首先，定义一个游标，它使用上面创建的别名读取远程表中的所有行。然后，在 LOAD 命令中引用这个游标。

      清单 15. 使用别名执行远程 LOAD FROM CURSOR 操作

      正如前面提到的，与方法 2 相比，结合使用 LOAD FROM CURSOR 操作和联邦访问需要做的配置工作比较多。但是，联邦访问的主要优点是可以从非 DB2 数据源装载数据。通过使用联邦方式，可以访问 Oracle、SQL Server 等数据源以及其他许多关系和非关系数据源，通过创建别名并执行引用别名的 LOAD FROM CURSOR 操作来复制内容。WebSphere Federation Server 产品提供访问非 DB2 数据源所需的包装器。

      方法2 使用 LOAD FROM CURSOR 命令的远程访问特性

      既然已经了解了使用别名的远程 LOAD FROM CURSOR 操作方法，现在看看另一种比较简便的方法。为此，首先删除刚才在目标数据库 MYSAMPLE 中的 SALES 表中导入的所有行，见清单 16。

      清单 16. 删除目标表中的所有行，以便再次执行 LOAD FROM CURSOR 操作

对于第二种方法，不需要配置对远程数据库的联邦访问。只需使用 DATABASE 选项在 DECLARE CURSOR 语句中指定远程数据库。为此，要在本地 DB2 实例的系统数据库目录中对远程数据库进行编目。前面已经给出了所需的 CATALOG 命令。另外，在定义游标时要指定远程访问所需的用户名和密码。LOAD 命令本身保持不变。

清单 17. 在不使用别名的情况下执行远程 LOAD FROM CURSOR 操作

      从 DB2 9.1 开始，可以以这种方式执行远程 LOAD FROM CURSOR 操作，这种方式基于 DB2 8 中的联邦访问方法。这种新方法有两个优点 —— 容易使用且性能好。显然，新方法非常容易使用。性能比联邦方法好是因为涉及的数据传输层更少。但是，不应该忘记联邦方法的优点，即可以访问非 DB2 数据源。

CLP 和 ADMIN_CMD 在 LOAD FROM CURSOR 方面的差异

      可以通过特殊的存储过程 ADMIN_CMD 执行许多管理命令，从而把管理命令嵌入在应用程序代码中。这也适用于 LOAD FROM CURSOR 操作。存储过程 ADMIN_CMD 的使用与应用程序代码的位置无关，也就是说，在客户端代码(例如 Java 应用程序)和服务器端代码(例如 SQL/PL 存储过程)中都可以使用它。下面的示例在一个定制的 SQL/PL 存储过程中使用 ADMIN_CMD 存储过程。create_load_routine.sql 文件包含示例存储过程 REMOTE_LOAD_FROM_CURSOR 的 SQL/PL 源代码。

      清单 18. 包含示例存储过程的 create_load_routine.sql 文件

      存储过程中的第一个语句是 DELETE，它删除本地目标表 SALES 中现有的行。接下来，用适当的 LOAD 命令调用 ADMIN_CMD，从而执行远程 LOAD FROM CURSOR 操作。这种方式与从命令行执行 LOAD FROM CURSOR 操作的差异如下：

      不需要通过执行 DECLARE CURSOR 单独定义所需的游标。在 LOAD 命令中提供相应的 SELECT 语句，就会隐式地定义游标。只有在 ADMIN_CMD 调用中嵌入 LOAD FROM CURSOR 操作的情况下，这种语法才是有效的，在命令行上是无效的。

      在 LOAD 命令中通过 DATABASE 选项定义远程数据库。不可能指定远程访问所需的用户名/密码组合。请在测试存储过程时观察这一限制的影响。

      但是，首先应该在目标数据库 MYSAMPLE 中创建存储过程。

      清单 19. 创建示例存储过程

第一个测试调用失败，返回消息 SQL30082N Security processing failed with reason "3" ("PASSWORD MISSING"). SQLSTATE=08001。

清单 20. 对示例存储过程的第一次测试失败

      这个错误消息是由于建立数据库连接的方式造成的：db2 "CONNECT TO

      MYSAMPLE"。在执行 CONNECT 语句时，没有提供用户名和密码，因此使用登录操作系统所用的用户名建立连接。在这种情况下，DB2 不知道此用户的密码。在存储过程中执行 LOAD FROM CURSOR 操作时，DB2 尝试用本地用户的授权 ID 连接远程数据库 SAMPLE。但是，由于使用隐式的 CONNECT，DB2 不知道相应的密码，所以远程访问失败。因此，这个错误的原因如下：

      因为通过 ADMIN_CMD 执行的 LOAD FROM CURSOR 操作不允许指定远程访问所用的用户，所以 LOAD 操作自动地用属于本地数据库连接的授权 ID 连接远程数据库。

      如果本地连接的用户执行隐式的 CONNECT 而没有指定密码，DB2 就不知道他/她的密码，因此在尝试连接远程数据库时没有密码可用。

      通过 ADMIN_CMD 执行的远程 LOAD FROM CURSOR 操作的这一特点(无法指定远程访问所用的用户)还有一个影响：当前连接本地数据库的用户必须用相同的授权 ID 访问远程数据库。在使用联邦方法访问远程数据库时，没有这一限制，因为必须以用户映射的形式定义额外的抽象层。

      既然找到了错误的原因，就可以重新连接本地数据库，这一次显式地指定用户名和密码。对存储过程的第二次调用应该会成功 (Return Status = 0)，见清单 21。

      清单 21. 对示例存储过程的第二次测试成功

结束语

本文通过示例场景演示了如何使用 DB2 的 LOAD FROM CURSOR 特性在一个数据库中以及不同的数据库之间快速轻松地复制数据。还解释了通过 ADMIN_CMD 存储过程在应用程序代码中执行 LOAD FROM CURSOR 操作时的特殊问题。另外，还讲解了如何为另一个数据库配置联邦访问，从而像访问本地表或视图一样透明地读写远程数据库中的表和视图。

欢迎光临 DIY编程器网 (http://diybcq.com/)