DB2执行LOAD操作的同时进行统计信息的收集

DB2 执行LOAD命令之后，数据会有改变，这时候需要使用RUNSTATS收集统计信息，那么有没有办法在LOAD的同时收集统计信息呢？


答案是有的，需要使用到LOAD命令的下面这个选项：

STATISTICS USE PROFILE

收集表统计信息概要文件中指定的统计信息。即使装入操作本身的性能下降（特别是在指定  DETAILED INDEXES ALL 时），与在完成装入操作后调用 RUNSTATS 实用程序相比，使用此参数来收集数据分布和索引统计信息更有效。

为优化性能，应用程序需要尽可能最佳的数据分布和索引统计信息。一旦更新统计信息，应用程序就可以根据最新的统计信息使用新的表数据存取路径。可通过使用 BIND 命令重新绑定应用程序包来创建新的表访问路径。通过运行带有 SET PROFILE 选项的 RUNSTATS 命令来创建表统计信息概要文件。

将数据装入到大表中时，建议对 stat_heap_sz（统计信息堆大小）数据库配置参数指定较大的值。

按照上面的要求，需要先使用RUNSTATS命令创建统计信息概要，这个操作是一次性的，一旦完成，之后所有的LOAD操作都可以按照这个概要文件指定的选项来进行统计信息的收集工作。

此外，对于LOAD的类型有限制：

在装入过程中是否收集统计信息。仅当以 REPLACE 方式运行装入操作时，才支持此选项。根据为表定义的概要文件收集统计信息。必须在执行 LOAD 命令前使用 RUNSTATS 命令创建该概要文件。如果不存在该概要文件，但您指示装入操作根据该概要文件收集统计信息，装入操作就会失败并返回错误消息。

示例：

$ db2 "RUNSTATS ON TABLE T2 AND DETAILED INDEXES ALL SET PROFILE ONLY"
DB20000I  The RUNSTATS command completed successfully.

$ db2 "load from c:\t2.del of del replace into T2 STATISTICS USE PROFILE"
SQL3109N  The utility is beginning to load data from file "c:\t2.del".

SQL3500W  The utility is beginning the "LOAD" phase at time "2016-09-07
13:38:54.597265".

SQL3519W  Begin Load Consistency Point. Input record count = "0".

SQL3520W  Load Consistency Point was successful.

SQL3110N  The utility has completed processing.  "2" rows were read from the
input file.

SQL3519W  Begin Load Consistency Point. Input record count = "2".

SQL3520W  Load Consistency Point was successful.

SQL3515W  The utility has finished the "LOAD" phase at time "2016-09-07
13:38:54.769188".

SQL3500W  The utility is beginning the "BUILD" phase at time "2016-09-07
13:38:54.773675".

SQL3213I  The indexing mode is "REBUILD".

SQL3515W  The utility has finished the "BUILD" phase at time "2016-09-07
13:38:54.896751".


Number of rows read         = 2
Number of rows skipped      = 0
Number of rows loaded       = 2
Number of rows rejected     = 0
Number of rows deleted      = 0
Number of rows committed    = 2

如果没有设置统计信息概要文件的话，LOAD的时候会遇到一个SQL2315N的告警:

$ db2 "RUNSTATS ON TABLE T2 UNSET PROFILE"
DB20000I  The RUNSTATS command completed successfully.

$ db2 "load from c:\t2.del of del replace into T2 STATISTICS USE PROFILE"
SQL3109N  The utility is beginning to load data from file "c:\t2.del".

SQL3500W  The utility is beginning the "LOAD" phase at time "2016-09-07
13:40:42.692386".

SQL3519W  Begin Load Consistency Point. Input record count = "0".

SQL3520W  Load Consistency Point was successful.

SQL2315N  The RUNSTATS utility was called with the " USE PROFILE" option.
However, a statistics profile for this table does not exist.

SQL3110N  The utility has completed processing.  "2" rows were read from the
input file.

SQL3519W  Begin Load Consistency Point. Input record count = "2".

SQL3520W  Load Consistency Point was successful.

SQL3515W  The utility has finished the "LOAD" phase at time "2016-09-07
13:40:42.812534".

SQL3500W  The utility is beginning the "BUILD" phase at time "2016-09-07
13:40:42.814839".

SQL3213I  The indexing mode is "REBUILD".

SQL3515W  The utility has finished the "BUILD" phase at time "2016-09-07
13:40:42.928173".

SQL3107W  At least one warning message was encountered during LOAD processing.


Number of rows read         = 2
Number of rows skipped      = 0
Number of rows loaded       = 2
Number of rows rejected     = 0
Number of rows deleted      = 0
Number of rows committed    = 2

参考资料：

LOAD概述
用于提高装入性能的选项

RUNSTATS命令

LOAD命令