DB2 Load 和 Oracle SQL*Loader
2008-11-27 16:21:04 来源：WEB开发网Oracle SQL*Loader 使用一个控制文件装载数据，DB2 LOAD 实用程序也是这样。但是，这些控制文件的结构很不一样，许多 Oracle 和 DB2 DBA 希望了解它们的差异。在本文中，要对比这两个实用程序，讨论如何使用 Perl 工具把 Oracle SQL*Loader 脚本转换为 LOAD 脚本。因为在大型数据仓库中空间总是很宝贵的，本文还解释如何修改 DB2 LOAD 的数据文件。
DB2 LOAD 与 Oracle SQL*Loader 的对比
DB2 LOAD 与 DB2 IMPORT — 使用哪种 “路径”？
实际上，DB2 有两种把外部数据源中的数据迁移到 DB2 表的实用程序：LOAD 和 IMPORT。LOAD 在页面级存放数据，这会绕过触发器和日志记录机制，并停止约束检查和索引构建，直到完成数据迁移。另一方面，IMPORT 基本上是执行 INSERT，所以在把数据放到表中时，会触发触发器、执行日志记录并执行约束检查和索引构建。这两个实用程序的选项之间还有其他许多差异，但是这超出了本文的范围。
另一方面，Oracle SQL*Loader 实用程序有两种操作模式，或者说操作路径：直接路径和传统路径。Oracle DBA 在使用这种实用程序时要指定 “路径”，这两种路径的效果分别与两个 DB2 实用程序相似。SQL*Loader “直接路径” 模式的功能与 DB2 LOAD 相似。SQL*Loader “传统路径” 模式的功能与 DB2 IMPORT 相似。
作为 DB2 迁移专家，我们发现大多数 Oracle DBA 在通常使用 SQL*Loader 的传统模式，他们的经验和脚本也是针对传统路径的。实际上，一些 Oracle DBA 从来没有使用过 SQL*Loader 的直接路径模式。但是，当他们开始学习 DB2 时，常常选用 DB2 LOAD 实用程序（可能是因为这个名称）；因为 LOAD 具有 SQL*Loader 直接路径模式的许多特征，而这些 Oracle DBA 没有使用过直接路径，所以他们会遇到许多困难。因此，为了把问题谈清楚，尽管大多数 Oracle DBA 通常使用传统模式，本文会演示如何把所有 SQL*Loader 脚本转换为 DB2 LOAD 实用程序脚本，无论原来的脚本采用哪个路径。我们认为这样做有助于 DB2 产生最佳性能。如果由于某种原因 DBA 希望把这些脚本转换为使用 IMPORT 而不是 LOAD，以后也可以这么做（如果情况允许的话）。
SQL*Loader 命令行 — 调用 SQLLDR
用 SQLLDR 二进制代码调用 Oracle SQL*Loader 实用程序，使用的命令行语法与 DB2 LOAD 相似。命令行可以包含许多关键字，比如告诉 SQL*Loader 实用程序把消息发送到哪里、把丢弃的记录发送到哪里等等。
SQLLDR 命令行还指定 “控制文件” 的名称（常常具有 .CTL 扩展名）。这个控制文件也可以告诉 SQL*Loader 实用程序把消息发送到哪里、把丢弃的记录发送到哪里等等。SQLLDR 命令行中的关键字设置优先于控制文件中的设置，所以要想了解 SQL*Loader 会话的实际工作方式，必须同时关注 SQLLDR 命令行和控制文件。这样的设计方式可能是为了提高 SQL*Loader 的灵活性和功能，但是在把脚本迁移到 DB2 时，如果在这两个位置都使用了一些相同的关键字，而且各个脚本的设置不一致，就可能引起混乱。
SQL*Loader 控制文件指定装载操作的细节，所以在对比 SQL*Loader 和 DB2 LOAD 时，主要对比 SQLLDR 控制文件和 DB2 LOAD 命令行。但是，我们要先讨论 SQLLDR 命令行的所有选项，并将其与 DB2 LOAD 命令行进行比较，看看它们的相似之处。然后，讨论控制文件及其关键字和选项，再与 DB2 LOAD 命令行进行比较。
表 1. DB2 LOAD 与 Oracle SQL*Loader 直接路径
| 特性 | DB2 LOAD | SQL*Loader（直接路径） |
| 直接路径 | 使用 DB2 LOAD 实用程序 | 使用 SQL*Loader 直接路径 —— 传统路径的许多选项不可用。 |
| 在装载后生成统计数据 | 是（如果替换数据），不（如果追加数据） | 不 |
| 可恢复 | 可以，使用 COPY YES 选项 | 在直接路径中不可以 |
| 默认（值） | 可用 | 不可用 |
| 使用多个输入文件 | 可以 | 对于一个 SQLLDR，只能使用一个文件 |
| 异常数据 | 写到一个异常表和/或 DUMP 文件 | 写到一个异常表 |
| 从游标装载 | 是 | 否 |
| 从管道装载 | 是 | 是 |
| BLOBS/CLOBS | 是 | 是 |
| XML 文档 | 是 | 是 |
| 在装载时允许压缩 | 是 | 是 |
| 在线装载 | 是 —— 可以访问表 | 在直接路径中，不是在线装载 |
| 在装载时可以修改数据吗？ | 可以 —— 通过用户退出 | 可以 —— 传统路径（SQL 字符串） 不可以 —— 直接路径 |
| 在列中填充常量值 | 不可以 | 可以，使用 CONSTANT 关键字 |
| 在多个数据库分区中装载 | 可以 | 无 |
| 如何调用？ | 它是一个可以从 SQL 脚本调用的 DB2 命令，也可以使用 API 通过应用程序调用 | 它是一个可以从命令行调用的独立实用程序，可以在应用程序中通过 API 调用 |
| 如何监视装载状态？ | 从另一个连接运行 LOAD QUERY 或 LIST UTILITIES 命令 | 查看日志文件 |
| 并行性 | 经过充分优化，可以使用多个 CPU、多个进程和线程 | 可以通过使用多线程实现并行 |
清单 1. Oracle SQLLDR 命令行语法示例
SQLLDR CONTROL=sample.ctl DATA=sample.dat LOG=sample.log BAD=sample.bad
DISCARD=sample.dsc
USERID=scott/tiger ERRORS=999 LOAD=2000 DISCARDMAX=5
表 2. Oracle SQLLDR 命令行关键字与 DB2 LOAD 关键字的比较
| Oracle SQLLDR 关键字 | Oracle SQLLDR 关键字说明 | DB2 LOAD 关键字 | DB2 LOAD 关键字说明 |
| CONTROL=filename.ctl | 包含详细的 LOAD 命令选项的文件。 | 无 | 不从控制文件单独调用 DB2 LOAD 命令选项。DB2 LOAD 命令在一个调用中包含所有关键字。 |
| DIRECT=true | 调用 Oracle SQL*Loader 实用程序的直接路径模式。 | LOAD | DB2 LOAD 实用程序本身非常接近 Oracle SQL*Loader 实用程序的直接路径模式。 |
| DIRECT=false | 如果不使用这个关键字或值为 “false”，就调用 Oracle SQL*Loader 实用程序的传统路径模式。 | IMPORT | DB2 IMPORT 实用程序本身非常接近 Oracle SQL*Loader 实用程序的传统路径模式。 |
| BAD=filename.bad | 存储被拒绝的记录的地方。 | MODIFIED BY DUMPFILE=filename | 这个 DB2 LOAD 修饰符用来决定在哪里存储被拒绝的记录。 |
| DATA=filename.dat | 输入数据源文件。 | FROM sourcename | DB2 LOAD 的 sourcename 可以是文件、管道、设备或游标。 |
| DISCARD=filename.dsc | 由于各种原因未装载的异常记录。 | FOR EXCEPTION tablename | DB2 LOAD 把违反惟一索引规则的记录（异常）放到以前创建的一个表中。 |
| DISCARDMAX=number | 定义在 SQLLDR 终止之前允许的最大丢弃记录数。 | WARNINGCOUNT=number | 在达到这个警告数时，DB2 LOAD 终止。丢弃仅仅是警告类型之一。 |
| ERRORS=number | 定义在 SQLLDR 终止之前允许的最大错误数。 | NOROWWARNINGS | 修饰符 NOROWWARNINGS 可以关闭行警告，但是仍然保留异常记录的警告。 |
| LOAD=number | 要装载的记录数（ALL 是默认设置）。 | ROWCOUNT number | 指定要装载的记录数。如果省略这个关键字，默认设置是所有记录。 |
| MULTITHREADING=true | 允许在客户端进行流构建，在服务器端进行流装载。 | CPU_PARALLELISM number DISK_PARALLELISM number FETCH_PARALLELISM yes | DB2 LOAD 自动决定这些设置，用来控制为对文件、设备、管道和游标装载中的记录进行解析、转换、格式化和写操作所生成的线程数。也可以使用这些关键字指定自己需要的值。 |
| ROWS=number | 每次数据保存存储的行数。 | SAVECOUNT number | DB2 LOAD 使用一致点确保装载操作的可恢复性。 |
| LOG=logfile | LOG 存储装载操作的输出。 | MESSAGES messagefile | DB2 把消息放到这个消息文件中。如果不指定消息文件，它就不产生消息。 |
| SILENT=options | SILENT=options 可以关闭操作不同部分的消息输出。 | NOROWWARNINGS | 修饰符 NOROWWARNINGS 关闭操作不同部分的消息输出。 |
| SKIP=number | 在 n 个记录之后开始装载。通常，如果装载操作提交了部分装载，但是操作没有完成，就使用这个关键字重新启动这个操作。 注意：如果使用这个特性，SQL*Loader 要求操作者自己决定装载启始点，选择错误的数值会导致丢失数据或数据重复。 | RESTART (REPLACE, INSERT, TERMINATE) | DB2 LOAD 使用这个模式在遇到故障之前的最后一个一致点之后选择重新装载的启始点。DB2 LOAD 会自己决定启始点，不需要操作者计算。 DB2 LOAD 可以使用的其他模式有 REPLACE、INSERT 和 TERMINATE，但是这些模式与 SKIP 关键字无关。 |
| SKIP_INDEX_MAINTENANCE=true | 停止索引维护并把索引标为不可用。 | INDEXING MODE DEFERRED | DB2 LOAD 可以把索引刷新推迟到以后访问数据或数据库激活期间。 |
| SKIP_UNUSABLE_INDEX=true | 跳过已经标为不可用的所有索引的索引维护。 | INDEXING MODE REBUILD, INCREMENTAL, AUTOSELECT | DB2 LOAD 还可以指定 INDEXING MODE REBUILD、INCREMENTAL 或 AUTOSELECT，这决定 LOAD 如何执行索引维护。 |
| READSIZE=number | 在必须提交之前读取的外部数据文件大小。 | DATA BUFFER number | DB2 LOAD 使用许多大小为 4K 的页面传输数据，这个数值通常是自动决定的，但是也可以用这个关键字指定自己需要的值。 |
| USERID/PASSWORD | 连接数据库所用的用户 id。 | CONNECT TO... number | DB2 在后续的所有 LOAD 命令前面使用一个连接命令。 |
SQL*Loader 控制文件 —— SQL*Loader 实用程序的核心
尽管 Oracle SQL*Loader 命令行允许通过许多关键字控制这个实用程序的工作方式，但是我们习惯于通过控制文件而不是命令行来指定大多数关键字。我们来研究一个典型的 SQL*Loader 控制文件以及转换产生的 DB2 LOAD 命令脚本。
表 3. 典型的 Oracle SQL*Loader 控制文件以及转换产生的 DB2 LOAD 脚本
| DB2 LOAD 命令文件示例（INSERT 固定数据） | Oracle SQL*Loader 控制文件示例（INSERT 固定数据） |
(1) LOAD | (1) LOAD DATA |
| DB2 LOAD 命令文件示例（REPLACE 可变数据） | Oracle SQL*Loader 控制文件示例（REPLACE 可变数据） |
(1) LOAD | (1) LOAD DATA |
下面对比以上示例：
(1) LOAD
这会在 DB2 中调用 LOAD 实用程序，还可以用 IMPORT 调用这个实用程序。
在 Oracle 中，使用 LOAD DATA 调用 SQL*Loader 实用程序。要想指定直接路径装载，必须指定 DIRECT=true。默认设置是 DIRECT=false，因此没有在这个示例中给出。
(2) FROM [inputfile_name]
这是包含要装载的数据的文件。DB2 LOAD 还可以从管道、设备或游标装载数据。
Oracle 也指定输入文件或管道，还可以通过控制文件用 BEGIN …END 子句指定内联数据。
(3) OF ASC / DEL
对于 DB2 LOAD，ASC 表示不分界的 ASCII 数据，数据的划分由位置决定。DEL 表示分界的 ASCII 数据，每行的数据长度可变。分界的数据可以使用多种修饰符，主要的两种是 COLDEL 和 CHARDEL；COLDEL 决定列和列之间如何分界，默认设置是逗号；CHARDEL 决定字符串数据如何分界，默认设置是双引号。
Oracle 有 FIX（默认设置，因此在这个示例中没有给出）或 VAR 关键字，但是很少使用。通常使用其他关键字和插入列引用，插入列引用决定数据是固定的还是可变的。例如，关键字 FIELDS TERMINATED BY 和 FIELDS ENCLOSED BY 的作用与 DB2 LOAD 中的 COLDEL 和 CHARDEL 相似，这表示可变的分界数据。
(4) MODIFIED BY DUMPFILE=[dumpfile_name]
DB2 把被拒绝的记录放到这个文件中。
Oracle 使用 BADFILE 关键字完成同样的工作。
(5) METHOD P (1,2,3)
DB2 LOAD 有三个方法：
METHOD L 只用于 ASC 数据，这个方法要指出每列的开头和结尾。它的形式是：METHOD L (start1 end1, start2 end2….)
METHOD N 用于 IXF 或游标数据，它要指定源表中要装载的列。它的形式是：METHOD N (col1, col2, col4…)
METHOD P 用于 DEL、IXF 或游标数据，它要指定源数据中要装载的列的位置号。它的形式是：METHOD P (1, 2, 4…)
如这个示例所示，SQL*Loader 可以在同一行上组合使用列名和字段位置。
(6) INSERT / REPLACE INTO PROD.TABLE
DB2 LOAD 在这里有四个选项。与 Oracle SQL*Loader 对应的两个选项是 INSERT 和 REPLACE。另外两个 DB2 LOAD 选项是 RESTART 和 TERMINATE。当 DB2 LOAD 由于任何原因未完成时，使用这些选项。
SQL*Loader 也有 INSERT，但是这只用于空表；而且 APPEND 对空表的作用与 INSERT 相似，所以 Oracle DBA 很少使用 INSERT。SQL*Loader REPLACE 的作用与 DB2 LOAD REPLACE 相同。
(7) (COL1, COL2, COL3) Insert Column List
DB2 LOAD 使用这个列表决定要放入数据的列。如果省略这个列列表，那么 DB2 LOAD 会尝试按照读取和解析数据的次序装载数据。
如这个示例所示，SQL*Loader 可以在同一行上组合使用列名和字段位置。对于长度可变的数据，不给出位置，而是由分界符决定字段的划分。
(8) FOR EXCEPTION [table_name]
DB2 LOAD 把违反惟一索引规则的记录（异常）放到以前创建的这个表中。
SQL*Loader 使用 DISCARDFILE 完成同样的工作，但使用的是一个操作系统文件而不是 DB2 表。
表 4. Oracle SQLLDR 控制文件关键字与 DB2 LOAD 关键字的比较
| Oracle SQLLDR 关键字 | Oracle SQLLDR 关键字说明 | DB2 LOAD 关键字 | DB2 LOAD 关键字说明 |
| DIRECT=true | 调用 Oracle SQL*Loader 实用程序的直接路径模式。 | LOAD | DB2 LOAD 实用程序本身非常接近 Oracle SQL*Loader 实用程序的直接路径模式。 |
| DIRECT=false | 如果不使用这个关键字或值为 “false”，就调用 Oracle SQL*Loader 实用程序的传统路径模式。 | IMPORT | DB2 IMPORT 实用程序本身非常接近 Oracle SQL*Loader 实用程序的传统路径模式。 |
| ERRORS=number | 定义在 SQLLDR 终止之前允许的最大错误数。 | NOROWWARNINGS | 修饰符 NOROWWARNINGS 可以关闭行警告，但是仍然保留异常记录的警告。 |
| LOAD=number | 要装载的记录数（ALL 是默认设置）。 | ROWCOUNT number | 指定要装载的记录数。如果省略这个关键字，默认设置是所有记录。 |
| MULTITHREADING=true | 允许在客户端进行流构建，在服务器端进行流装载。 | CPU_PARALLELISM number DISK_PARALLELISM number FETCH_PARALLELISM yes | DB2 LOAD 自动决定这些设置，用来控制为对文件、设备、管道和游标装载中的记录进行解析、转换、格式化和写操作所生成的线程数。也可以使用这些关键字指定自己需要的值。 |
| READSIZE=n | 在必须提交之前读取的外部数据文件大小。 | DATA BUFFER number | DB2 LOAD 使用许多大小为 4K 的页面传输数据，这个数值通常是自动决定的，但是也可以用这个关键字指定自己需要的值。 |
| ROWS=number | 每次数据保存存储的行数。 | SAVECOUNT number | DB2 LOAD 使用一致点确保装载操作的可恢复性。 |
| SILENT=options | SILENT=options 可以关闭操作不同部分的消息输出。 | NOROWWARNINGS | 修饰符 NOROWWARNINGS 关闭装载操作不同部分的消息输出。 |
| SKIP=number | 在 n 个记录之后开始装载。通常，如果装载操作提交了部分装载，但是操作没有完成，就使用这个关键字重新启动这个操作。 注意：如果使用这个特性，SQL*Loader 要求操作者自己决定装载启始点，选择错误的数值会导致丢失数据或数据重复。 | RESTART (REPLACE, INSERT, TERMINATE) | DB2 LOAD 使用这个模式在遇到故障之前的最后一个一致点之后选择重新装载的启始点。DB2 LOAD 会自己决定启始点，不需要操作者计算。 DB2 LOAD 可以使用的其他模式有 REPLACE、INSERT 和 TERMINATE，但是这些模式与 SKIP 关键字无关。 |
| SKIP_INDEX_MAINTENANCE=true | 停止索引维护并把索引标为不可用。 | INDEXING MODE DEFERRED | DB2 LOAD 可以把索引刷新推迟到以后访问数据或数据库激活期间。 |
| SKIP_UNUSABLE_INDEX=true | 跳过已经标为不可用的所有索引的索引维护。 | INDEXING MODE REBUILD, INCREMENTAL, AUTOSELECT | DB2 LOAD 还可以指定 INDEXING MODE REBUILD、INCREMENTAL 或 AUTOSELECT，这决定 LOAD 如何执行索引维护。 |
| NOLOGGING | 这个选项允许绕过日志记录机制，但是会使这个表无法通过前滚操作恢复。 | NONRECOVERABLE | 如果使用这个选项，在装载操作之后表空间并不处于备份未完成状态，在装载操作期间不必复制装载的数据。 |
| CONTINUE_LOAD DATA | 重新启动终止的装载操作，自动寻找正确的启始点（只适用于直接路径模式）。 | RESTART | DB2 LOAD 使用遇到故障之前的最后一个一致点选择重新装载的启始点。 |
| LOAD DATA | 调用 SQLLDR 二进制代码，以任何模式（即路径）装载数据。 | 1. LOAD 2. IMPORT | DB2 LOAD 实用程序本身非常接近 Oracle SQL*Loader 实用程序的直接路径模式。 DB2 IMPORT 实用程序本身非常接近 Oracle SQL*Loader 实用程序的传统路径模式。 |
| INFILE filename | 输入数据源文件。 | FROM sourcename | DB2 LOAD 的 sourcename 可以是文件、管道、设备或游标。 |
| RECSIZE n | 固定的输入记录的大小。 | MODIFIED BY RECLEN=x | 固定的输入记录的大小。 |
| BADFILE filename | 存储被拒绝记录的地方。 | MODIFIED BY DUMPFILE=filename | 这个 DB2 LOAD 修饰符用来决定在哪里存储被拒绝的记录。 |
| DISCARDFILE filename | 由于各种原因未装载的异常记录。 | FOR EXCEPTION tablename | DB2 LOAD 把违反惟一索引规则的记录（异常）放到以前创建的一个表中。 |
| DISCARD | 由于各种原因未装载的异常记录。 | FOR EXCEPTION filename | DB2 LOAD 把违反惟一索引规则的记录（异常）放到以前创建的一个表中。 |
| DISCARDMAX | 定义在 SQLLDR 终止之前允许的最大丢弃记录数。 | 1. WARNINGCOUNT=number 2. NOROWWARNINGS | 在达到这个警告数时，DB2 LOAD 终止。丢弃仅仅是警告类型之一。 修饰符 NOROWWARNINGS 可以关闭行警告，但是仍然保留异常记录的警告。 |
| 1. VAR n 2. FIX n | 分别指定数据长度是可变的还是固定的。如果使用 VAR，那么 n 是位于行首用来声明每行长度的数据的字节数。 | 1. OF DEL 2. OF ASC | DB2 可变数据是分界的，固定数据是 ASCII。 |
| 1. INSERT 或 APPEND 2. REPLACE | INSERT 和 APPEND 的相似之处是它们都在已经存在的数据中添加数据，但是 INSERT 要求表是空的。 REPLACE 会在添加新数据之前清除表中的现有数据。 | 1. INSERT 2. REPLACE | DB2 LOAD INSERT 向表中添加数据，即使表是空的。 REPLACE 会在添加新数据之前清除表中的现有数据。 |
| INTO TABLE tablename | 要存储数据的目标表。 | INTO TABLE tablename | 要存储数据的目标表。 |
| TERMINATED BY string | 发现这个字符串时结束数据读取。 | MODIFIED BY COLDELx | 在所有输入数据中以这个字符分隔各个列（默认设置是逗号）。 |
| ENCLOSED BY string | 可以用这个字符串包围数据；通常用于字符数据。 | MODIFIED BY CHARDELx | 用这个字符包围输入的字符数据（默认设置是双引号）。 |
| LOBFILE (filename) | 在 INSERT 列列表中指定这个关键字，用来从外部文件源装载 LOB。 参数本身应该包含文件名和完整的路径名，否则会在 LOAD 脚本所在的目录中搜索 LOB。 | 1. LOBS FROM pathnames 2. MODIFIED BY LOBSINFILE | 寻找 LOB 文件的路径。 要想使用 LOBS FROM 子句，就必须设置这个关键字。 参数本身包含文件名，但是不包含完整的路径名，因为将搜索 LOBS FROM 路径。 |
将 SQL*Loader 转换为 DB2 LOAD 的 Perl 脚本
这里给出的 Perl 脚本用来把 SQL*Loader 控制文件转换为等效的 DB2 LOAD 脚本。可以以传统和直接路径模式调用 SQL*Loader，但是 SQL*Loader 在直接路径模式中不使用 SQL 字符串控制数据格式。Oracle DBA 有时候会忽视这些选项并交换使用传统和直接路径模式，而没有认识到产生的副作用。SQL*Loader 传统路径基本上相当于 DB2 IMPORT 实用程序，IMPORT 使用引擎在 DB2 中执行批量插入。通过使用数据库引擎，可以利用触发器等特性，这在 SQL*Loader 直接路径模式或 DB2 LOAD 实用程序中是不可行的。这个 Perl 脚本把 SQL*Loader 控制文件的直接和传统路径版本都转换为 DB2 LOAD 脚本。如果希望使用与 SQL*Loader 传统路径等效的 DB2 IMPORT 脚本，应该把生成的文件中的 LOAD 关键字改为 IMPORT。
运行 Perl 脚本的前提条件
要想把 SQL*Loader 脚本转换为 DB2 LOAD 脚本 ，需要在目标平台上安装 Perl。本文假设您熟悉如何在目标平台上安装 Perl 工具。这里给出的示例适用于 Windows 平台，但是可以在您选择的任何平台上使用这个工具。
如何运行 Perl 脚本
如果在运行这个工具时没有指定任何参数，它就会显示使用方法，见清单 3：
清单 3. 工具的使用方法
C:>perl ora2db2.pl
USAGE: perl ora2db.pl -c controlfile [options]
-o ostype (Unix|Windows - default is Unix. Other value is Windows
-m message_directory_name (default is MSG_DIR)
-e dump_directory_name (default is DUMP_DIR).
This dir should reside on all partitions on DB2 server.
-d data_file_name (default is INPUT_FILE)
-f defaultif clause set to either (default|null - default is null)
-s schema name. Replace with the schema name in control file
-g timestamp format. Default is YYYY-MM-DD
可以指定一些参数作为 SQLLDR 命令的参数，它们优先于控制文件中定义的选项。如果使用一个脚本带参数调用 SQLLDR，这可能会导致迁移问题。这些参数可以与运行工具时给出的开关匹配，让创建的 DB2 LOAD 脚本使用正确的参数。
下面的示例演示如何转换具有不同格式选项的直接和传统路径控制文件。
C:>perl ora2db2.pl -c test1.ctl -d datatest1.data -m msg -e dump -o Windows
-s ADMIN -f null > load1.db2
C:>perl ora2db2.pl -c test2.ctl -d datatest2.data -m msg -e dump -o Windows
-s ADMIN -f null > load2.db2
 |
|
清单 4. 使用直接路径和位置列的 SQL*Loader test1.ctl
UNRECOVERABLE
LOAD DATA
INFILE 'INPUT_FILE'
APPEND
INTO TABLE JOHN.TABLE1
TRAILING NULLCOLS
(
BC_OFF_BCBANK POSITION(2:4) CHAR ,
BC_OFF_SEGMENT_TYPE POSITION(5:12) CHAR "rtrim(:BC_OFF_SEGMENT_TYPE,' ')" ,
BC_OFF_NUM POSITION(13:18) CHAR DEFAULTIF BC_OFF_NUM= ' ?????',
BC_OFF_SQ_EFF_DATE POSITION(19:26) CHAR DEFAULTIF BC_OFF_SQ_EFF_DATE=" ???????",
BC_OFF_SQ_ENT_DATE POSITION(27:40) CHAR DEFAULTIF BC_OFF_SQ_ENT_DATE = ' ?????????????',
BC_OFF_DELETE POSITION(41:41) CHAR,
BC_OFF_EFF_DATE POSITION(42:53) CHAR DEFAULTIF BC_OFF_EFF_DATE =' ???????????',
BC_OFF_INITIALS POSITION(54:56) CHAR "rtrim(:BC_OFF_INITIALS,' ')",
BC_OFF_NAME POSITION(57:76) CHAR "rtrim(:BC_OFF_NAME,' ')",
BC_OFF_C_L_SECTION POSITION(77:78) CHAR,
BC_OFF_PHONE_NR POSITION(79:90) CHAR DEFAULTIF BC_OFF_PHONE_NR=' ???????????',
BC_OFF_SC_LND_LMT POSITION(91:98) CHAR DEFAULTIF BC_OFF_SC_LND_LMT = ' ???????',
BC_OFF_UNSC_LND_LMT POSITION(99:106) CHAR DEFAULTIF BC_OFF_UNSC_LND_LMT = ' ???????',
BC_OFF_NEWCOL POSITION(107:107) CHAR NULLIF (BC_OFF_NEWCOL=BLANKS),
PRCS_DTE CONSTANT "PROCESSDATE",
PRCS_YR_MTH_NBR CONSTANT "PROCYRMTH"
)
清单 5. 使用方法 L 转换产生的 DB2 LOAD 脚本
-- Converting Oracle SQL*Loader Control File test1.ctl to DB2
-- ALTER Statements to take care of CONSTANT parameters
ALTER TABLE TABLE2 ALTER COLUMN PRCS_DTE SET WITH DEFAULT 'PROCESSDATE';
ALTER TABLE TABLE2 ALTER COLUMN PRCS_YR_MTH_NBR SET WITH DEFAULT 'PROCYRMTH';
-- DB2 LOAD Script
LOAD FROM "datatest1.data"
OF ASC
MODIFIED BY ANYORDER USEDEFAULTS STRIPTBLANKS TIMESTAMPFORMAT="YYYY-MM-DD"
DUMPFILE="dumpadmin_table1.dump"
METHOD L
(
2 4
,5 12
,13 18
,19 26
,27 40
,41 41
,42 53
,54 56
,57 76
,77 78
,79 90
,91 98
,99 106
,107 107
)
MESSAGES "msgadmin_table1.msg"
INSERT INTO "ADMIN"."TABLE1"
( BC_OFF_BCBANK
,BC_OFF_SEGMENT_TYPE
,BC_OFF_NUM
,BC_OFF_SQ_EFF_DATE
,BC_OFF_SQ_ENT_DATE
,BC_OFF_DELETE
,BC_OFF_EFF_DATE
,BC_OFF_INITIALS
,BC_OFF_NAME
,BC_OFF_C_L_SECTION
,BC_OFF_PHONE_NR
,BC_OFF_SC_LND_LMT
,BC_OFF_UNSC_LND_LMT
,BC_OFF_NEWCOL
)
NONRECOVERABLE
INDEXING MODE AUTOSELECT
;
-- UPDATE Statements for setting proper DEFAULTIF parameters
UPDATE "ADMIN"."TABLE1"
SET BC_OFF_NUM = NULL
WHERE BC_OFF_NUM = ' ?????';
UPDATE "ADMIN"."TABLE1"
SET BC_OFF_SQ_EFF_DATE = NULL
WHERE BC_OFF_SQ_EFF_DATE = ' ???????';
UPDATE "ADMIN"."TABLE1"
SET BC_OFF_SQ_ENT_DATE = NULL
WHERE BC_OFF_SQ_ENT_DATE = ' ?????????????';
UPDATE "ADMIN"."TABLE1"
SET BC_OFF_EFF_DATE = NULL
WHERE BC_OFF_EFF_DATE = ' ???????????';
UPDATE "ADMIN"."TABLE1"
SET BC_OFF_PHONE_NR = NULL
WHERE BC_OFF_PHONE_NR = ' ???????????';
UPDATE "ADMIN"."TABLE1"
SET BC_OFF_SC_LND_LMT = NULL
WHERE BC_OFF_SC_LND_LMT = ' ???????';
UPDATE "ADMIN"."TABLE1"
SET BC_OFF_UNSC_LND_LMT = NULL
WHERE BC_OFF_UNSC_LND_LMT = ' ???????';
-- UPDATE Statements for setting proper NULLIF parameters
UPDATE "ADMIN"."TABLE1"
SET BC_OFF_NEWCOL = NULL
WHERE BC_OFF_NEWCOL = 'BLANKS)';
清单 6. 使用传统路径和分界列的 SQL*Loader test2.ctl
LOAD DATA
INFILE 'INPUT_FILE'
APPEND
INTO TABLE JOHN.TABLE2
FIELDS TERMINATED BY ' '
TRAILING NULLCOLS
(
CR_BUR_PTFLO_TYP_DESC char(255) NULLIF CR_BUR_PTFLO_TYP_DESC=BLANKS,
DW_PROD_SERV_FEE_PLN_RCD_ID,
DW_ULT_PROD_SERV_ID,
ACCT_NBR,
ACCT_GRP_NBR,
APPL_CDE,
PRCS_GRP_NBR,
FEE_CAT_CDE,
FEE_PLN_NBR,
FEE_DESC,
FEE_TYP_CDE,
FEE_EARN_CDE,
CMPT_1_NXT_ASSMT_DTE DATE "YYYY-MM-DD",
CMPT_2_NXT_ASSMT_DTE DATE "YYYY-MM-DD",
CMPT_3_NXT_ASSMT_DTE DATE "YYYY-MM-DD",
CUR_PMT_DUE_DTE DATE "YYYY-MM-DD",
EARN_GOOD_THRU_1_DTE DATE "YYYY-MM-DD",
EARN_GOOD_THRU_2_DTE DATE "YYYY-MM-DD",
PR_PMT_DUE_DTE DATE "YYYY-MM-DD",
NXT_PMT_DUE_DTE DATE "YYYY-MM-DD",
DW_ASP_ID,
CLNT_ID,
CUR_REC_IND,
SOR_EXP_DTE "NVL(:SOR_EXP_DTE,'4444-12-31')",
EFF_DTE DATE "YYYY-MM-DD"
)
清单 7. 使用方法 P 转换产生的 DB2 LOAD 脚本
-- Converting Oracle SQL*Loader Control File test2.ctl to DB2
-- DB2 LOAD Script
LOAD FROM "datatest2.data"
OF DEL
MODIFIED BY COLDEL0x09 ANYORDER USEDEFAULTS TIMESTAMPFORMAT="YYYY-MM-DD"
DUMPFILE="dumpadmin_table2.dump"
METHOD P
(
1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25
)
MESSAGES "msgadmin_table2.msg"
INSERT INTO "ADMIN"."TABLE2"
( CR_BUR_PTFLO_TYP_DESC
,DW_PROD_SERV_FEE_PLN_RCD_ID
,DW_ULT_PROD_SERV_ID
,ACCT_NBR
,ACCT_GRP_NBR
,APPL_CDE
,PRCS_GRP_NBR
,FEE_CAT_CDE
,FEE_PLN_NBR
,FEE_DESC
,FEE_TYP_CDE
,FEE_EARN_CDE
,CMPT_1_NXT_ASSMT_DTE
,CMPT_2_NXT_ASSMT_DTE
,CMPT_3_NXT_ASSMT_DTE
,CUR_PMT_DUE_DTE
,EARN_GOOD_THRU_1_DTE
,EARN_GOOD_THRU_2_DTE
,PR_PMT_DUE_DTE
,NXT_PMT_DUE_DTE
,DW_ASP_ID
,CLNT_ID
,CUR_REC_IND
,SOR_EXP_DTE
,EFF_DTE
)
NONRECOVERABLE
INDEXING MODE AUTOSELECT
;
-- UPDATE Statements for setting proper NULLIF parameters
UPDATE "ADMIN"."TABLE2"
SET CR_BUR_PTFLO_TYP_DESC = NULL
WHERE TRIM(CR_BUR_PTFLO_TYP_DESC) = '';
UPDATE "ADMIN"."TABLE2"
SET SOR_EXP_DTE = COALESCE(SOR_EXP_DTE,'4444-12-31-00.00.00');
注意，因为 DB2 不允许在 DB2 LOAD 脚本中使用 CONSTANT 关键字，所以 CONSTANT 列被转换为带 DEFAULT 子句的 ALTER TABLE 语句。DB2 LOAD 会在装载数据时应用 DEFAULT 值，而 SQL*Loader 直接路径不应用默认值。
还要注意 Oracle 控制文件 test1.ctl 示例中 UNRECOVERABLE 选项（使用直接路径）中的 SQL 字符串，但是直接路径忽略这些 SQL 字符串。在 DB2 中可以忽略转换产生的等效更新语句。
上面的转换过程产生了一些 UPDATE 语句，它们根据应用的 SQL 字符串修改数据。如果由于性能问题不愿意在非常大的表上应用这些 UPDATE 语句，那么可以用另一个选项 SOURCEUSEREXIT 修改数据文件，下一节解释这个选项。
DB2 LOAD 的 USEREXIT
DB2 和 Oracle 在体系结构方面的主要差异之一是如何处理空字符串。Oracle 把空字符串当作 NULL 对待，而 DB2 不是这样。如果 SQL*Loader 控制文件中使用位置列，那么在使用 LOAD 实用程序装载数据时，这个差异可能会导致错误。如果这些列是空的，DB2 就认为它们是空的，而 Oracle 认为它们是 NULL。
在下面的示例数据中，在位置 23 和 32 上有 NULL 标志，它们分别针对在位置 24:27 和 28:31 上定义的数据：
清单 8. NULL 标志
FILE1 has 7 elements:
ELE1 positions 01 to 20
ELE2 positions 21 to 22
ELE3 positions 23 to 23
ELE4 positions 24 to 27
ELE5 positions 28 to 31
ELE6 positions 32 to 32
ELE7 positions 33 to 40
1...5...10...15...20...25...30...35...40
Test data 1 XXN 123abcdN
Test data 2 and 3 QQY wxyzN
Test data 4,5 and 6 WWN6789 Y
下面的 LOAD 脚本使用 NULL INDICATORS 选项，如果 NULL 标志设置为 Y，脚本就会把列当作 NULL：
清单 9. NULL INDICATORS 选项
TABLE has 5 columns:
COL1 VARCHAR 20 NOT NULL WITH DEFAULT
COL2 SMALLINT
COL3 CHAR 4
COL4 CHAR 2 NOT NULL WITH DEFAULT
COL5 CHAR 2 NOT NULL
db2 load from file1 of asc modified by striptblanks reclen=40
method L (1 20, 21 22, 24 27, 28 31)
null indicators (0,0,23,32)
insert into table1 (col1, col5, col2, col3)
以 Oracle 控制脚本 test1.ctl 为例（使用我们的 Perl 脚本执行转换）。为了装载 null 值，DB2 LOAD 要求专门指定 NULL INDICATORS。这要求为数据文件中每个记录的所有列添加 NULL 标志。对于这样的情况，可以使用 DB2 LOAD SOURCEUSEREXIT 选项根据用户退出程序中的逻辑修改数据文件。采用这种方法，就不需要在运行 LOAD 之前处理数据文件。使用 SOURCEUSEREXIT 选项会让 DB2 LOAD 读取数据文件并把数据记录传输给可以处理记录的用户退出程序，处理之后再传递给 DB2 LOAD。
清单 10. 使用 SOURCEUSEREXIT 的 DB2 LOAD 脚本
LOAD FROM datatest1.data
OF ASC
MODIFIED BY ANYORDER USEDEFAULTS STRIPTBLANKS TIMESTAMPFORMAT="YYYY-MM-DD"
DUMPFILE="dumpadmin_table1.dump"
METHOD L
(
2 4
,5 12
,13 18
,19 26
,27 40
,41 41
,42 53
,54 56
,57 76
,77 78
,79 90
,91 98
,99 106
,107 107
)
NULL INDICATORS (108,109,110,111,112,113,0,114,115,116,117,118,119,120)
MESSAGES "msgadmin_table1.msg"
INSERT INTO "ADMIN"."TABLE1"
( BC_OFF_BCBANK
,BC_OFF_SEGMENT_TYPE
,BC_OFF_NUM
,BC_OFF_SQ_EFF_DATE
,BC_OFF_SQ_ENT_DATE
,BC_OFF_DELETE
,BC_OFF_EFF_DATE
,BC_OFF_INITIALS
,BC_OFF_NAME
,BC_OFF_C_L_SECTION
,BC_OFF_PHONE_NR
,BC_OFF_SC_LND_LMT
,BC_OFF_UNSC_LND_LMT
,BC_OFF_NEWCOL
)
NONRECOVERABLE
INDEXING MODE AUTOSELECT
SOURCEUSEREXIT userexit.pl redirect input from buffer 'colninds:(2 4,5 12,
13 18,19 26,27 40,41 41,42 53,54 56,57 76,77 78,79 90,91 98,99 106,
107 107) nullinds:(108,109,110,111,112,113,0, 114,115,116,117,118,
119,120)'
output to file userexit.log
;
|
|
结束语
Oracle DBA 熟悉 SQL*Loader，他们很容易利用这些知识学习 DB2 LOAD 和 IMPORT 实用程序。本文主要关注 LOAD，但是许多内容也可以应用于 IMPORT。本文讨论并对比了 SQL*Loader 和 DB2 LOAD 的主要关键字。还提供了一个容易使用的 Perl 脚本，它可以转换大多数 SQL*Loader 脚本。这应该能够帮助您的组织更轻松地迁移到 DB2。
- ››oracle 恢复误删除的表和误更新的表
- ››Oracle分页查询排序数据重复问题
- ››Oracle创建dblink报错:ORA-01017、ORA-02063解决
- ››Oracle 提高SQL执行效率的方法
- ››Oracle 动态查询，EXECUTE IMMEDIATE select into...
- ››Oracle 11g必须开启的服务及服务详细介绍
- ››oracle性能34条优化技巧
- ››oracle数据库生成随机数的函数
- ››Oracle 数据库表空间容量调整脚本
- ››oracle单库彻底删除干净的方法
- ››Oracle创建表空间、创建用户以及授权、查看权限
- ››oracle 中 UPDATE nowait 的使用方法
中查找“DB2 Load 和 Oracle SQL*Loader”更多相关内容
中查找“DB2 Load 和 Oracle SQL*Loader”更多相关内容
赞助商链接
