DB2_收集表统计数据

目的：
测试DB2收集表统计数据，本脚本摘录自DB2安装目录admin_scripts/tbrunstats.db2。
版本：Windows DB2 Express-C V9.7

操作步骤：
使用"db2cmd db2 -t"进入交互模式,执行后续操作。

connect to sample;

-- create a schema
CREATE SCHEMA testschema;

-- create a table
CREATE TABLE testschema.employee_temp LIKE employee;

-- insert into the table employee_temp
INSERT INTO testschema.employee_temp (SELECT * FROM employee);

-- perform. Runstats on the table

-- update statistics for the table employee_temp

-- perform. runstats on table employee_temp for column empno
-- with the following options:
--
--                 Distribution statistics for all partitions
--                 Frequent values for table set to 30
--                 Quantiles for table set to -1 (NUM_QUANTILES as in DB Cfg)
--                 Allow others to have read-only while gathering statistics

RUNSTATS ON TABLE testschema.employee_temp
  ON COLUMNS(empno LIKE STATISTICS)
  WITH DISTRIBUTION ON KEY COLUMNS
  DEFAULT 
  NUM_FREQVALUES 30
  NUM_QUANTILES -1
  ALLOW READ ACCESS;

-- make sure to rebind all packages that use this table.

-- drop the table employee_temp
DROP TABLE testschema.employee_temp;

-- drop the schema
DROP SCHEMA testschema RESTRICT;

-- disconnect from the database
CONNECT RESET;

TERMINATE;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-702304/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18922393/viewspace-702304/