技术开发 频道

使用DB2的LOAD FROM CURSOR转移数据

  【IT168 技术文档】

  使用 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 数据库

C:\>db2sampl

  Creating
database "SAMPLE"...

  Connecting
to database "SAMPLE"...

  Creating tables
and data in schema "FECHNER"...

  
'db2sampl' processing complete.

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

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

C:\>db2 "CONNECT TO SAMPLE"

  
Database Connection Information

  
Database server = DB2/NT 9.5.2

  SQL
authorization ID = FECHNER

  Local
database alias = SAMPLE

  C:\
>db2 "SELECT TABLES.TABSCHEMA, TABLES.TABNAME, TBSPACES.TBSPACE FROM SYSCAT.TABLES AS

  TABLES, SYSCAT.TABLESPACES
AS TBSPACES WHERE TABLES.TBSPACEID = TBSPACES.TBSPACEID AND

  TABNAME
= 'SALES'"

  TABSCHEMA

  TABNAME

  TBSPACE

  
------------------------------------------------------------------------------------------

  
-------- ---------------------------------------------------------------------------------

  
----------------- ------------------------------------------------------------------------

  
--------------------------

  FECHNER

  SALES

  USERSPACE1

  
1 record(s) selected.

 

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

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

C:\>db2 "LIST TABLESPACES"

  Tablespaces
for Current Database

  Tablespace ID
= 0

  Name
= SYSCATSPACE

  Type
= Database managed space

  Contents
= All permanent data. Regular table space.

  State
= 0x0000

  Detailed explanation:

  Normal

  Tablespace ID
= 1

  Name
= TEMPSPACE1

  Type
= System managed space

  Contents
= System Temporary data

  State
= 0x0000

  Detailed explanation:

  Normal

  Tablespace ID
= 2

  Name
= USERSPACE1

  Type
= Database managed space

  Contents
= All permanent data. Large table space.

  State
= 0x0000

  Detailed explanation:

  Normal

  Tablespace ID
= 3

  Name
= IBMDB2SAMPLEREL

  Type
= Database managed space

  Contents
= All permanent data. Large table space.

  State
= 0x0000

  Detailed explanation:

  Normal


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

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

C:\>db2 "CREATE TABLE FECHNER.SALES_TMP LIKE FECHNER.SALES IN IBMDB2SAMPLEREL"

  DB20000I The SQL command completed successfully.

  现在,可以执行数据转移操作了。使用 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 表

C:\>db2 "DECLARE C1 CURSOR FOR SELECT * FROM FECHNER.SALES"

  DB20000I The SQL command completed successfully.

  C:\
>db2 "LOAD FROM C1 OF CURSOR MESSAGES C:\load_sales_tmp.msg INSERT INTO

  FECHNER.SALES_TMP NONRECOVERABLE"

  
Number of rows read = 41

  
Number of rows skipped = 0

  
Number of rows loaded = 41

  
Number of rows rejected = 0

  
Number of rows deleted = 0

  
Number of rows committed = 41


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

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

C:\>db2 "DROP TABLE FECHNER.SALES"

  DB20000I The SQL command completed successfully.

  C:\
>db2 "RENAME TABLE FECHNER.SALES_TMP TO SALES"

  DB20000I The SQL command completed successfully.


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

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

C:\>db2 "SELECT TABLES.TABSCHEMA, TABLES.TABNAME, TBSPACES.TBSPACE FROM SYSCAT.TABLES AS

  TABLES, SYSCAT.TABLESPACES
AS TBSPACES WHERE TABLES.TBSPACEID = TBSPACES.TBSPACEID AND

  TABNAME
= 'SALES'"

  TABSCHEMA

  TABNAME

  TBSPACE

  
------------------------------------------------------------------------------------------

  
-------- ---------------------------------------------------------------------------------

  
----------------- ------------------------------------------------------------------------

  
--------------------------

  FECHNER

  SALES

  IBMDB2SAMPLEREL

  
1 record(s) selected.

  C:\
>db2 "TERMINATE"

  DB20000I The TERMINATE command completed successfully.


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

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

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

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

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

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

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

C:\>db2 "UPDATE DBM CFG USING FEDERATED YES"

  DB20000I The
UPDATE DATABASE MANAGER CONFIGURATION command completed

  successfully.

  SQL1362W One
or more of the parameters submitted for immediate modification

  were
not changed dynamically. Client changes will not be effective until the

  
next time the application is started or the TERMINATE command has been issued.

  Server changes will
not be effective until the next DB2START command.

  C:\
>db2stop

  
2008-09-22 14.55.36 0 0 SQL1064N DB2STOP processing was successful.

  SQL1064N DB2STOP processing was successful.

  C:\
>db2start

  
2008-09-22 14.55.53 0 0 SQL1063N DB2START processing was successful.

  SQL1063N DB2START processing was successful.

  C:\
>db2 "CREATE DATABASE MYSAMPLE"

  DB20000I The
CREATE DATABASE command completed successfully.


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

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

C:\>db2look -d sample -e -z fechner -t sales -o sales.ddl

  
-- USER is: FECHNER

  
-- Specified SCHEMA is: FECHNER

  
-- The db2look utility will consider only the specified tables

  
-- Creating DDL for table(s)

  
-- Schema name is ignored for the Federated Section

  
-- Output is sent to file: sales.ddl

  
-- Binding package automatically ...

  
-- Bind is successful

  
-- Binding package automatically ...

  
-- Bind is successful


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

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

-- This CLP file was created using DB2LOOK Version 9.5

  
-- Timestamp: 23.09.2008 07:35:10

  
-- Database Name: SAMPLE

  
-- Database Manager Version: DB2/NT Version 9.5.2

  
-- Database Codepage: 1208

  
-- Database Collating Sequence is: IDENTITY

  CONNECT
TO SAMPLE;

  
------------------------------------------------

  
-- DDL Statements for table "FECHNER "."SALES"

  
------------------------------------------------

  
CREATE TABLE "FECHNER "."SALES" (

  "SALES_DATE" DATE ,

  "SALES_PERSON"
VARCHAR(15) ,

  "REGION"
VARCHAR(15) ,

  "SALES"
INTEGER )

  
IN "IBMDB2SAMPLEREL" ;

  
COMMIT WORK;

  CONNECT RESET;

  TERMINATE;


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

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

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

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

CONNECT TO SAMPLE; -> CONNECT TO MYSAMPLE;

  
IN "IBMDB2SAMPLEREL" ; -> IN "USERSPACE1";


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

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

C:\>db2 -tf sales.ddl

  
Database Connection Information

  
Database server = DB2/NT 9.5.2

  SQL
authorization ID = FECHNER

  Local
database alias = MYSAMPLE

  DB20000I The SQL command completed successfully.

  DB20000I The SQL command completed successfully.

  DB20000I The SQL command completed successfully.

  DB20000I The TERMINATE command completed successfully.


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

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

C:\>db2 "CATALOG TCPIP NODE SRCNODE REMOTE localhost SERVER 50000"

  DB20000I The CATALOG TCPIP NODE command completed successfully.

  DB21056W Directory changes may
not be effective until the directory cache is

  refreshed.

  C:\
>db2 "CATALOG DATABASE SAMPLE AS SRCDB AT NODE SRCNODE AUTHENTICATION SERVER"

  DB20000I The CATALOG
DATABASE command completed successfully.

  DB21056W Directory changes may
not be effective until the directory cache is

  refreshed.

  C:\
>db2 "TERMINATE"

  DB20000I The TERMINATE command completed successfully.


  准备 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. 创建联邦访问所需的数据库对象 

C:\>db2 "CONNECT TO MYSAMPLE"

  
Database Connection Information

  
Database server = DB2/NT 9.5.2

  SQL
authorization ID = FECHNER

  Local
database alias = MYSAMPLE

  C:\
>db2 "CREATE WRAPPER DRDA"

  DB20000I The SQL command completed successfully.

  C:\
>db2 "CREATE SERVER SRCSRV TYPE DB2/UDB VERSION 9.5 WRAPPER DRDA AUTHORIZATION

  \"fechner\" PASSWORD \"password\" OPTIONS (DBNAME
'SRCDB')"

  DB20000I The SQL command completed successfully.

  C:\
>db2 "CREATE USER MAPPING FOR fechner SERVER SRCSRV OPTIONS (REMOTE_AUTHID 'fechner',

  REMOTE_PASSWORD
'password')"

  DB20000I The SQL command completed successfully.

  C:\
>db2 "CREATE NICKNAME FECHNER.SRCTAB FOR SRCSRV.FECHNER.SALES"

  DB20000I The SQL command completed successfully.


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

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

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

C:\>db2 "DECLARE C1 CURSOR FOR SELECT * FROM FECHNER.SRCTAB"

  DB20000I The SQL command completed successfully.

  C:\
>db2 "LOAD FROM C1 OF CURSOR MESSAGES C:\load_sales.msg INSERT INTO FECHNER.SALES

  NONRECOVERABLE"

  
Number of rows read = 41

  
Number of rows skipped = 0

  
Number of rows loaded = 41

  
Number of rows rejected = 0

  
Number of rows deleted = 0

  
Number of rows committed = 41

  C:\
>db2 "TERMINATE"

  DB20000I The TERMINATE command completed successfully.


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

0
相关文章