在 DB2 for Linux, UNIX, and Windows 中为 ETL 用户执行加载和导入错误检查

检查分区数据库环境中的加载和导入操作

本文将演示如何在 DB2® for Linux®, UNIX®, and Windows® 数据库分区 (DPF) 环境中,通过对 SYSPROC.ADMIN_CMD 执行 ETL 调用来检查加载和导入操作。使用 ETL 调用,您可以完成错误检查,并且拥有比大多数 ETL 工具更多的控制权和更高的准确性。

Alex Levy, 顾问, Sustainable Software Ltd.

Alex Levy 的照片Alex Levy 是 Sustainable Software Ltd. 的一名独立 DB2 顾问。他的兴趣包括扩大 DB2 在非政府机构和中小型业务领域的采用。



2014 年 1 月 28 日

简介

为什么有必要这么做?许多 ETL 工具都是一般性的和数据库不可知的。它们仅能通过调用存储过程 SYSPROC.ADMIN_CMD 使用 Java™ 来运行所有实用程序,或者使用 SAP 或 BODS 批量加载器等供应商扩展的约束。这些扩展实际上是未发布的 API,常常依赖于低效的提交计数和警告计数处理。它们也不是很擅长查明输出中的准确的数据错误。此外,一些 ETL 工具的内部设计仅支持以逐行顺序插入方式来使用 IMPORT,而不能使用缓存的插入,因为这是它们捕获错误的惟一方式,或者因为它们希望允许在更新目标表时向其写入数据。

从命令行接口执行报告和错误处理实际上清楚明白得多、更加简单且更加简短,尤其因为您可从实用程序测试操作系统返回代码,而无需从存储过程测试。

DB2 的 LOAD 实用程序提供了与 IMPORT 相同、经常改进的功能来执行报告和错误处理。由于 LOAD 没有日志记录,它会处理自己的并行性,在批量处理时比 IMPORT 快得多,所以很容易增强 ETL 操作来执行错误检查,并尽可能地将 IMPORT 替换为 LOAD,当然,这也非常适合批量数据。

下面的示例将使用一个 5 分区数据库(1 个目录和 4 个数据分区)中的 ADMIN_CMD 过程,它们将演示如何使用简单的 SQL 调用来检查错误。

测试设置

这些示例使用清单 1 中定义和填充的一个简单表:

清单 1. 定义和填充测试表
-- Demonstrate LOAD and IMPORT options. Create a simple test table of 100 
rows (approx 25 rows per partition) and populate:

set current schema foo;
create table bar (
  col1 smallint not null,col2 char(1) not null, some_date date not null)
  distribute by hash(col1) in s_fact4k1;

-- Add a unique constraint so we can later simulate invalid data on load or 
import files.
alter table foo.bar add constraint bar_u1 unique (col1);

-- create an exceptions table for the load, if needed;
-- this is only for constraint violations.
create table bar_exception like bar distribute by hash(col1) in s_fact4k1;

insert into bar
  with units(unit) as (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)),
     tens(ten) as (select unit * 10 from units)
select ten + unit, 'Y', current date
  from tens, units;

-- Another benefit of load: create a sampled statistics profile for use 
with the load command; this only works in load replace mode. 
runstats on table foo.bar with distribution on all columns and detailed 
  indexes all tablesample system(10) set profile only;

-- create a load/import file 
--(the order by clause is not required but makes the file easier to read)
export to /tmp/foo.bar.del of del select * from foo.bar order by col1;

场景 1 — 正常运行

清单 2 显示了在正常运行时 IMPORT 的输出。

清单 2. S1.1 IMPORT 的输出
set current schema foo
DB20000I  The SQL command completed successfully.
delete from bar
DB20000I  The SQL command completed successfully.
call sysproc.admin_cmd('import from /tmp/foo.bar.del of del allow write access messages
on server insert into foo.bar')
  Result set 1
  --------------
  ROWS_READ            ROWS_SKIPPED         ROWS_INSERTED        ROWS_UPDATED        
ROWS_REJECTED        ROWS_COMMITTED       MSG_RETRIEVAL
MSG_REMOVAL
  -------------------- -------------------- -------------------- ------------------
-- -------------------- -------------------- --------------------------------------
100                    0                  100                    0
0                  100 SELECT SQLCODE, MSG FROM 
TABLE(SYSPROC.ADMIN_GET_MSGS('71481989_DB2INST1')) AS MSG
CALL SYSPROC.ADMIN_REMOVE_MSGS('71481989_DB2INST1')   

  1 record(s) selected  
  Return Status = 0

这段输出不言自明,但有一个地方需要注意:返回状态 0 是从对 sysproc.admin_cmd 的调用返回的,而不是实际的 IMPORT 本身的状态。0 表示 “我在运行中未遇到错误,无论我执行的参数命令实际上是否成功。”要测试导入操作本身是否存在错误或拒绝情形,您需要做两件事:

  1. 测试操作系统返回代码。
  2. 查询结果集来对比 ROWS_READ 与 ROWS_INSERTED,或者测试 ROWS_REJECTED。

也可以获取 “底线” 消息(这可能对应用程序日志记录也很有用):

清单 3. 测试导入时的错误或拒绝
$ db2 –x " select substr(msg,1,254) from 
TABLE(SYSPROC.ADMIN_GET_MSGS('71481989_DB2INST1')) AS MSG where sqlcode = 
'SQL3149N'"

"100" rows were processed from the input file.  "100" rows were successfully 
inserted into the table.  "0" rows were rejected.

进一步的观察结果:表函数 SYSPROC.ADMIN_GET_MSGS 从 IMPORT 的每一步返回一个 SQLCODE 表。它没有返回您期望为 0 的总体 SQLCODE。所以如果查询表函数,您将获得:

清单 4. 查询表函数 ADMIN_GET_MSGS
$ db2 " select dbpartitionnum, sqlcode, substr(msg,1,254) from 
TABLE(SYSPROC.ADMIN_GET_MSGS('71481989_DB2INST1')) AS MSG"
DBPARTITIONNUM SQLCODE   3
-------------- --------- -------
             - SQL3109N  The utility is beginning to load data from file
                         "/db2/landing/foo.bar.del".
             - SQL3110N  The utility has completed processing.  
                         "100" rows were read from the input file.
             - SQL3221W  ...Begin COMMIT WORK. Input Record Count = "100".
             - SQL3222W  ...COMMIT of any database changes was successful.
             - SQL3149N  "100" rows were processed from the input file.  
                         "100" rows were successfully inserted into the table.
                         "0" rows were rejected.

现在,我们将此输出与成功 LOAD 的输出进行比较。

S1.2 LOAD 的输出

您将收到 2 个结果集。结果集 1 是对加载文件进行拆分和分区的结果,这个步骤实际上在将数据放入表中之前执行。结果集 2 是数据放置步骤本身的结果。

清单 5. LOAD 输出 - 结果集 1
call sysproc.admin_cmd('load from /db2/landing/foo.bar.del of del messages on 
server insert into foo.bar nonrecoverable without prompting')
  Result set 1
  --------------

  ROWS_READ            ROWS_SKIPPED         ROWS_LOADED          ROWS_REJECTED     
ROWS_DELETED         ROWS_COMMITTED       ROWS_PARTITIONED     
NUM_AGENTINFO_ENTRIES MSG_RETRIEVAL
MSG_REMOVAL
  -------------------- -------------------- -------------------- ------------------
-- -------------------- -------------------- -------------------- -----------------
---- ------------------------------------------------------------------------------
----------------------------------
                   100                    -                    -                   
 0                    -                    -                  100                  
 6 SELECT DBPARTITIONNUM, AGENTTYPE, SQLCODE, MSG FROM 
TABLE(SYSPROC.ADMIN_GET_MSGS('548225794_DB2INST1')) AS MSG
CALL SYSPROC.ADMIN_REMOVE_MSGS('548225794_DB2INST1')

因此在这个阶段,ROWS_LOADED 和 ROWS_COMMITTED 将始终为 NULL,不为 0。另请注意,有 6 个代理:一个执行协调,一个执行分区或拆分,其余 4 个代理分别对应每个数据分区。

清单 6. LOAD 输出 - 结果集 2
Result set 2
  --------------
  DBPARTITIONNUM SQLCODE     TABSTATE             AGENTTYPE
  -------------- ----------- -------------------- --------------------
               1           0 NORMAL               LOAD
               2           0 NORMAL               LOAD
               3           0 NORMAL               LOAD
               4           0 NORMAL               LOAD
               0           0 NORMAL               PARTITIONING
               0           0 NORMAL               PRE_PARTITIONING

  6 record(s) selected.

  Return Status = 0

现在请注意,这里您实际上获得了 ‘真实的’ SQLCODE!对一次干净加载的测试是:对于每个分区,预分区和分区代理都会返回一个 SQLCODE 0。

您仍会通过调用 SYSPROC.ADMIN_GET_MSGS 获得每一步的 SQLCODE。因此, 成功步骤的 SQLCODE 的列表很简单:

清单 7. 从 ADMIN_GET_MSGS 获取 SQLCODE
$ db2 "SELECT  distinct SQLCODE FROM 
TABLE(SYSPROC.ADMIN_GET_MSGS('548225794_DB2INST1')) AS MSG where sqlcode is not 
null order by 1 for fetch only"

SQLCODE
---------
SQL27903I
SQL27910I
SQL27914I
SQL27920I
SQL27921I
SQL27935I
SQL27936I
SQL27937I
SQL27939I
SQL27950I
SQL3109N
SQL3110N
SQL3213I
SQL3500W
SQL3501W
SQL3515W
SQL3519W
SQL3520W

  18 record(s) selected.

场景 2 — 重复的错误

我们现在将在加载文件中引入重复,这将破坏惟一性约束。

清单 8. 引入一个错误
 set current schema foo ;
-- clear down table for IMPORT
truncate table foo.bar immediate;

-- introduce some invalid (duplicate) data into the import/load file
!echo "99,\"Y,\"20110527" >> /tmp/foo.bar.del ;
-- import, with messages
call sysproc.admin_cmd('import from /tmp/foo.bar.del of del allow write access 
messages on server insert into foo.bar');

-- clear down table again, this time for LOAD
truncate table foo.bar immediate;

-- A load which will also capture the duplicate data in an exceptions table:
call sysproc.admin_cmd('load from /tmp/foo.bar.del of del
     messages on server
     insert into foo.bar
     FOR EXCEPTION FOO.BAR_EXCEPTION nonrecoverable without prompting');

select * from foo.bar_exception;

下一个清单显示了输出:

清单 9. 引入错误后的 IMPORT 输出
  Result set 1
  --------------

  ROWS_READ            ROWS_SKIPPED         ROWS_INSERTED        ROWS_UPDATED
ROWS_REJECTED        ROWS_COMMITTED       MSG_RETRIEVAL
MSG_REMOVAL
  -------------------- -------------------- -------------------- ------------------
-- -------------------- -------------------- --------------------------------------
101                    0                  100                    0                  
1                  101 SELECT SQLCODE, MSG FROM 
TABLE(SYSPROC.ADMIN_GET_MSGS('2021311734_DB2INST1')) AS MSG
CALL SYSPROC.ADMIN_REMOVE_MSGS('2021311734_DB2INST1')

  1 record(s) selected.
  Return Status = 0
SQL3107W  There is at least one warning message in the message file.

输出一目了然。和以前一样,从 SYSPROC.ADMIN_CMD 返回的状态为 0,但 ROWS_REJECTED 显示了 1,ROWS_READ 为 101,ROWS_INSERTED 仅为 100。但是等等,您能否解释一下为什么 ROWS_COMMITTED 为 101?答案如下。

检索来自生成的命令的消息不会查明错误。

清单 10. 引入错误后的 IMPORT 输出
SQL3148W  A row from the input file was not inserted into the table.  SQLCODE
"-803" was returned.
SQL0803N  One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains
table "FOO.BAR" from having duplicate values for the index key.
SQLSTATE=23505     
SQL3185W  The previous error occurred while processing data from row "101" of
the input file.
SQL3110N  The utility has completed processing.  "101" rows were read from the
input file.
SQL3221W ...Begin COMMIT WORK. Input Record Count = "101".
SQL3222W ...COMMIT of any database changes was successful.
SQL3149N  "101" rows were processed from the input file.  "100" rows were
successfully inserted into the table.  "1" rows were rejected.

这突出了 IMPORT 与 LOAD 之间的一个重要区别。IMPORT 将尝试加载文件上的每一条记录(而不是先检查它),然后在发生错误时从错误中恢复。LOAD 更加复杂:

清单 11. 引入错误后的 LOAD 输出
call sysproc.admin_cmd('load from /tmp/foo.bar.del of del WARNINGCOUNT 0 messages 
on server insert into foo.bar FOR EXCEPTION FOO.BAR_EXCEPTION nonrecoverable 
without prompting')

  Result set 1
  --------------

  ROWS_READ            ROWS_SKIPPED         ROWS_LOADED          ROWS_REJECTED        
ROWS_DELETED         ROWS_COMMITTED       ROWS_PARTITIONED     
NUM_AGENTINFO_ENTRIES MSG_RETRIEVAL
MSG_REMOVAL
  -------------------- -------------------- -------------------- ------------------
-- -------------------- -------------------- --------
                   101                    -                    -
0                    -                    -                  101
6 SELECT DBPARTITIONNUM, AGENTTYPE, SQLCODE, MSG FROM
 TABLE(SYSPROC.ADMIN_GET_MSGS('1672226768_DB2INST1')) AS MSG 
CALL SYSPROC.ADMIN_REMOVE_MSGS('1672226768_DB2INST1')

为什么没有拒绝任何内容?因为这是来自分区前的步骤的输出,还没有执行任何分区 — 重复记录是隔离的。请注意,没有任何列显示 ROWS_LOADED,因为还未加载任何信息。看看第二个结果集:

清单 12. 引入错误后的 LOAD 输出 -- 结果集 2
  Result set 2
  --------------                

  DBPARTITIONNUM SQLCODE     TABSTATE             AGENTTYPE
  -------------- ----------- -------------------- --------------------
               1           0 NORMAL               LOAD
               2           0 NORMAL               LOAD
               3           0 NORMAL               LOAD
               4           0 NORMAL               LOAD
               0           0 NORMAL               PARTITIONING
               0           0 NORMAL               PRE_PARTITIONING

  6 record(s) selected.

拒绝记录的证据在哪里?在此场景中,可查看异常表;查找不符合 S1.2 中可接受的 SQLCODE 模式的 SQLCODE。在 SQL 中此操作很容易完成。这个查询模板对每次加载都是完全相同的 — 需要更改的只是 ADMIN_GET_MSGS 的参数:

清单 13. 查找不符合可接受的模式的 SQLCODE
select  dbpartitionnum, agenttype, sqlcode, substr(msg,1,254) message
from    table(sysproc.admin_get_msgs('1672226768_DB2INST1')) msg
            left outer join
        foo.load_sqlcode fl
            on msg.sqlcode = fl.ok_sqlcode
where   fl.ok_sqlcode is null
and     msg.sqlcode is not null
order   by 1
;

DBPARTITIONNUM AGENTTYPE SQLCODE   MESSAGE
-------------- --------- --------- ----------------------------------------------
---------------------------------------------------------------------------------
----------------------------------------------------------------------------------
---------------------------------------------
            3 load      SQL3509W  The utility has deleted "1" rows from the table.


$ db2 "select * from foo.bar_exception"

COL1   COL2 SOME_DATE
------ ---- ----------
    99 Y    05/27/2011

  1 record(s) selected.

场景 3 — 无效的数据

此示例显示了在向导入/加载文件中引入无效数据时发生的情形。

清单 14. 引入无效的数据
-- - 1 row with an out of range numeric value ( > smallint)
-- - 1 row with a strapline from Cato instead of a date
-- - 1 row with a correctly formatted date but an invalid value of 32nd February
!echo "32768,\"N\",20110531" >> /tmp/foo.bar.del ;
!echo "32767,\"N\",\"Ceterum censeo Carthaginem esse delendam.\"" >> /tmp/foo.bar.del ;
!echo "32767,\"N\",20110232" >> /tmp/foo.bar.del ;

delete from bar
DB20000I  The SQL command completed successfully.

S3.1 包含无效数据的 IMPORT

清单 15. 引入无效数据后的 IMPORT 输出
call sysproc.admin_cmd('import from /tmp/foo.bar.del of del allow write access 
messages on server insert into foo.bar')

  Result set 1
  --------------

  ROWS_READ            ROWS_SKIPPED         ROWS_INSERTED        ROWS_UPDATED
ROWS_REJECTED        ROWS_COMMITTED       MSG_RETRIEVAL
MSG_REMOVAL
  -------------------- -------------------- -------------------- ------------------
-- -------------------- -------------------- --------                   103
 0                  100                    0                    3                  
103 SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('245743373_DB2INST1')) AS 
MSG
CALL SYSPROC.ADMIN_REMOVE_MSGS('245743373_DB2INST1')

  1 record(s) selected.

  Return Status = 0

SQL3107W  There is at least one warning message in the message file.

这个结果是正确的。拒绝了 3 条记录,但请注意 IMPORT 实用程序没有预先验证记录。它只是尝试顺序地插入它们,然后从每个 SQL 错误中恢复。如果我们坚持调用 sysproc.admin_get_msgs,您会看到每种错误都会在一些消息上被识别出来,但无效行的身份(也即它在输入文件中的位置)可能位于存在该错误的任何消息中,而且有时是重复的 — 这取决于错误本身,而且报告的这方面并不总是存在。

清单 16. 引入错误后的 IMPORT 输出
SQL3118W  The field value in row "101" and column "1" cannot be converted to a
SMALLINT value, but the target column is not nullable.  The row was not
loaded. 
SQL3128W  The field containing ""Ceterum censeo Carthaginem es" in row "102"
and column "3" was truncated into a DATE field because the data is longer than
the database column.
SQL3148W  A row from the input file was not inserted into the table.  SQLCODE
"=180" was returned.
SQL0180N  The syntax of the string representation of a datetime value is
incorrect. SQLSTATE=22007
SQL3185W  The previous error occurred while processing data from row "102" of
the input file.
SQL3148W  A row from the input file was not inserted into the table.  SQLCODE
"-181" was returned.
SQL0181N  The string representation of a datetime value is out of range.
SQLSTATE=22007
SQL3185W  The previous error occurred while processing data from row "103" of
the input file.
SQL3110N  The utility has completed processing.  "103" rows were read from the
input file.            
SQL3149N  "103" rows were processed from the input file.  "100" rows were
successfully inserted into the table.  "3" rows were rejected.

值得注意的第二点是,消息 SQL3107W There is at least one warning message in the message file 是在来自 SYSPROC.ADMIN_CMD 的返回代码之后返回的。重复行场景中没有出现这种情况。但它确实表明该 Java 代码现在可以测试操作系统返回代码。

S3.2 具有无效数据的 LOAD

对于加载,请注意使用转储文件修饰符来捕获无效的输入记录:

清单 17. 引入无效数据后的 LOAD 输出
call sysproc.admin_cmd('load from /tmp/foo.bar.del of del modified by 
dumpfile=/tmp/humptydumpty messages on server insert into foo.bar nonrecoverable 
without prompting')


  Result set 1
  --------------

  ROWS_READ            ROWS_SKIPPED         ROWS_LOADED          ROWS_REJECTED
ROWS_DELETED         ROWS_COMMITTED       ROWS_PARTITIONED     
NUM_AGENTINFO_ENTRIES MSG_RETRIEVAL
MSG_REMOVAL
  -------------------- -------------------- -------------------- ------------------
-- -------------------- -------------------- -------------------- -----------------
102                    -                    -                    2
-                    -                  102                    33 SELECT 
DBPARTITIONNUM, AGENTTYPE, SQLCODE, MSG FROM 
TABLE(SYSPROC.ADMIN_GET_MSGS('721439627_DB2INST1')) AS MSG
CALL SYSPROC.ADMIN_REMOVE_MSGS('721439627_DB2INST1')

  1 record(s) selected.


  Result set 2
  --------------
  --------------

  DBPARTITIONNUM SQLCODE     TABSTATE             AGENTTYPE
  -------------- ----------- -------------------- --------------------
               1           0 NORMAL               LOAD
               2           0 NORMAL               LOAD
               3        3107 NORMAL               LOAD
               4           0 NORMAL               LOAD
               0        3107 NORMAL               PARTITIONING
               0           0 NORMAL               PRE_PARTITIONING

  6 record(s) selected.

  Return Status = 0

SQL3107W  There is at least one warning message in the message file.

这里有一些有趣的区别:

  • 仅加载了 102 个记录,2 个记录被拒绝。剩余的无效记录发生了什么?它们在进入表中之前就被分区代理拒绝了(如下所示)。
  • 在结果集 2 中,-3107 非常显眼。
  • 对 SYSPROC.ADMIN_GET_MSGS 的调用将识别拒绝记录的分区,就像上面的结果集 2 所做的那样。
  • 被拒绝的行的行号位于分区的加载文件中,而不是单一文件中。
清单 18. 调用 SYSPROC.ADMIN_GET_MSGS
select  dbpartitionnum, agenttype, sqlcode, substr(msg,1,254) message
from    table(sysproc.admin_get_msgs('721439627_DB2INST1')) msg
            left outer join
        foo.load_sqlcode fl
            on msg.sqlcode = fl.ok_sqlcode
where   fl.ok_sqlcode is null
and     msg.sqlcode is not null
order   by 1
;

DBPARTITIONNUM AGENTTYPE SQLCODE   MESSAGE
-------------- --------- --------- ------------------------------------------------
             0 part      SQL27930N A record was rejected during partitioning with
reason  code "1" and partition-relative record number  "1".
             0 part      SQL6044N  The syntax of the string representation "32768" 
with value of datatype "SMALL INTEGER" and length "5" is not correct.
             3 load      SQL0180N  The syntax of the string representation of a 
datetime value is incorrect.  SQLSTATE=22007
             3 load      SQL0181N  The string representation of a datetime value is 
out of range.SQLSTATE=22007
             3 load      SQL3125W  The character data in row "4" and column "3" was 
truncated because the data is longer than the target database column.
             3 load      SQL3185W  The previous error occurred while processing data
 from row "4" of the input file.
             3 load      SQL3185W  The previous error occurred while processing data
 from row "5" of the input file.
             - -         SQL3107W  There is at least one warning message in the 
message file.

在 load 命令中,我们将转储文件指定为 /tmp/humptydumpty。DB2 将实用程序名称和分区号附加到每个文件,结果如下:

清单 19. 转储文件
-rw-r-----  1 db2inst1   db2sygrp            21 May 31 11:28 humptydumpty.part.000
-rw-r-----  1 db2inst1   db2sygrp             0 May 31 11:28 humptydumpty.load.001
-rw-r-----  1 db2inst1   db2sygrp             0 May 31 11:28 humptydumpty.load.002
-rw-r-----  1 db2inst1   db2sygrp            73 May 31 11:28 humptydumpty.load.003
-rw-r-----  1 db2inst1   db2sygrp             0 May 31 11:28 humptydumpty.load.004

在任何非 0 长度的文件中,无效的数据已可根据分区进行识别:

清单 20. 根据分区识别无效数据
$ for i in $(ls -1 /tmp/humptydumpty*)
> do
> cat $i
> done
清单 21. 结果
32767,"N","Ceterum censeo Carthaginem esse delendam."
32767,"N",20110232
32768,"N",20110531

S4 — 其他技巧

技巧 1:加载文件拆分

拆分文件可能很适合 DB2 外部的转换和数据擦除编辑。我曾广泛使用该技术将 iSeries 数据转换为 DB2 数据仓库。没有有效的方法来使用 IMPORT 拆分文件,因为数据仍然是文件格式的。需要将所有数据加载到一个短期存在的表中,然后调用 dbpartitionnum() 函数来查询它。

但是,LOAD 实用程序会自动拆分该文件并加载每个拆分的文件。要在拆分之后和实际加载之前停止该实用程序,可使用以下语法:

清单 22. 拆分之后停止加载
call sysproc.admin_cmd('load from /tmp/foo.bar.del of del
                        modified by dumpfile=/tmp/humptydumpty
                        messages on server
                        insert into foo.bar
                        nonrecoverable without prompting
                        partitioned db config mode partition_only
                        part_file_location /db2/landing/db2utility_messages')

如果未显式指定 PART_FILE_LOCATION,那么 PART_FILE_LOCATION 被默认为当前的工作目录。结果如下:

清单 23. 结果集 1
  Result set 1
  --------------

  ROWS_READ            ROWS_SKIPPED         ROWS_LOADED          ROWS_REJECTED
ROWS_DELETED         ROWS_COMMITTED       ROWS_PARTITIONED
NUM_AGENTINFO_ENTRIES MSG_RETRIEVAL
MSG_REMOVAL
  -------------------- -------------------- -------------------- ------------------
-- -------------------- -------------------- --------------------
                     0                    -                    - 
0                    -                    -                  100
6 SELECT DBPARTITIONNUM, AGENTTYPE, SQLCODE, MSG FROM 
TABLE(SYSPROC.ADMIN_GET_MSGS('1679601175_DB2INST1')) AS MSG
CALL SYSPROC.ADMIN_REMOVE_MSGS('1679601175_DB2INST1')

  1 record(s) selected.

请注意,ROWS_READ 为 0,ROWS_LOADED 为 null。但是,ROWS_PARTITIONED 为 100。在结果集 2 中,可以看到 AGENTTYPE 为 ‘LOAD_TO_FILE’ 而不是 ‘LOAD’。

清单 24. 结果集 2
Result set 2
  --------------

  DBPARTITIONNUM SQLCODE     TABSTATE             AGENTTYPE
  -------------- ----------- -------------------- --------------------
               1           0 NORMAL               LOAD_TO_FILE
               2           0 NORMAL               LOAD_TO_FILE
               3           0 NORMAL               LOAD_TO_FILE
               4           0 NORMAL               LOAD_TO_FILE
               0           0 NORMAL               PARTITIONING
               0           0 NORMAL               PRE_PARTITIONING

  6 record(s) selected.

  Return Status = 0

要加载所有拆分的文件,只需在 LOAD_ONLY 模式下运行:

清单 25. 加载所有拆分文件
call sysproc.admin_cmd('load from /tmp/foo.bar.del of del
                        modified by dumpfile=/tmp/humptydumpty
                        messages on server
                        insert into foo.bar
                        nonrecoverable without prompting
                        partitioned db config mode load_only
                        part_file_location /tmp/db2utility_messages')

输出与上面场景 1 中的加载相同。惟一的区别是,分区 0 上没有分区和预分区代理,因为该工作已完成。

要加载至少 1 个分区,比如说分区 1,可添加 OUTPUT_DBPARTNUMS 参数。这样,您肯定会失去所有加载并行性:

清单 26. 加载至少一个分区
call sysproc.admin_cmd('load from /tmp/foo.bar.del of del
                        modified by dumpfile=/tmp/humptydumpty
                        messages on server
                        insert into foo.bar
                        nonrecoverable without prompting
                        partitioned db config mode load_only
			            output_dbpartnums (1)	
                        part_file_location /tmp/db2utility_messages')

要指定多个分区,只需使用逗号分隔它们,例如 output_dbpartnums(1,2,4)

技巧 2:不使用文件加载

要从一个表加载到另一个表中,不需要通过一个平面文件来暂存数据,除非您希望先拆分数据。在加载时仍然可以通过游标语法,使用一种变体来完成此任务,无需引用 dbpartitionnum() 函数。假设 foo.bar2 中包含 foo.bar 的一个副本:

清单 27. 从一个表加载
call sysproc.admin_cmd('load from  (select * from foo.bar2) of cursor
                        messages on server
                        insert into foo.bar
                        nonrecoverable without prompting
                        partitioned db config mode partition_only
                        part_file_location /db2/landing/db2utility_messages')

最后,您应负责整理来自 PART_FILE_LOCATION 的拆分文件。文件名将以 ‘LOAD’ 开头,标识了分区和运行者的登录名,否则将没有什么意义,例如:

清单 28. 拆分文件
-rw-r----- 1 db2fgvp  db2fgvp          0 May 31 14:19 LOAD_6P9P52_DB2INST1
-rw-r--r-- 1 db2fgvp  db2fgvp       1008 May 31 14:19 LOAD_6P9P52.load.001_DB2INST1

(备注:这些文件由受保护的用户写入。)


结束语

上述示例演示了 ETL 开发人员如何通过简单的 SQL 调用,对数据移动实用程序执行错误检查,实现比许多 ETL 工具更多的控制权和更高的准确性。

文中提供了一个正常运行的示例(数据输入中没有错误);一个重复错误示例(违背了一个惟一或主要的约束);一个 “糟糕的” 数据处理的示例(例如,无效的日期或时间戳表示)。还演示了如何使用 LOAD 实用程序在 DPF 环境中根据分区拆分一个主要文件。这不是提供给 DBA 的最佳实践建议,只是一种在需要时非常高效地拆分文件(或表)的方式。

最后还提供了一个按游标加载的示例,用于消除对文件输入的需求。

参考资料

学习

获得产品和技术

讨论

条评论

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=961452
ArticleTitle=在 DB2 for Linux, UNIX, and Windows 中为 ETL 用户执行加载和导入错误检查
publish-date=01282014