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;