DB2_收缩表空间

目的：
测试DB2收缩表空间，本脚本摘录自DB2安装目录admin_scripts/tbsreduce.db2。
版本：Windows DB2 Express-C V9.7

操作步骤：
使用"db2cmd db2 -t"进入交互模式,执行后续操作。
-- Set auto-commit of SQL statements to OFF.
UPDATE COMMAND OPTIONS USING c OFF;

-- Create database.
!db2start;
CREATE DB testdb1;

-- Connect to database.
CONNECT TO testdb1;

1,自动存储管理表空间未使用空间回收:
-- Create table space 'tbsp_auto' managed by AUTOMATIC STORAGE.
CREATE TABLESPACE tbsp_auto PAGESIZE 4096 MANAGED BY AUTOMATIC STORAGE
  EXTENTSIZE 2 AUTORESIZE NO INITIALSIZE 107K;

-- Create table 'tab_auto1' in table space 'tbsp_auto'.
CREATE TABLE tab_auto1 ( c1 char( 250 ),c2 char( 250 ),c3 char( 250 ),
                         c4 char( 250 ),c5 char( 250 ),c6 char( 250 ),
                         c7 char( 250 ),c8 char( 250 ),c9 char( 250 ),
                         c10 char( 250 ) )
  IN tbsp_auto;

-- Populate table 'tab_auto1' with the following data.
INSERT INTO tab_auto1 VALUES ('a', 'b', 'c', 'd', 'e',
                              'f', 'g', 'h', 'i', 'j' );

-- Create table 'tab_auto2' in table space 'tbsp_auto'.
CREATE TABLE tab_auto2 ( c1 char( 250 ),c2 char( 250 ),c3 char( 250 ),
                         c4 char( 250 ),c5 char( 250 ),c6 char( 250 ),
                         c7 char( 250 ),c8 char( 250 ),c9 char( 250 ),
                         c10 char( 250 ) )
  IN tbsp_auto;

-- Populate table 'tab_auto2' with data so that the table space is full.
-------------------------------------------------------------------------------
-- Table space 'tbsp_auto' will be full after the five INSERT statements shown
-- below. Creation of new tables in table space 'tbsp_auto' will fail with an
-- error: "Unable to allocate new pages in table space".
-------------------------------------------------------------------------------
INSERT INTO tab_auto2 VALUES ('a', 'b', 'c', 'd', 'e',
                              'f', 'g', 'h', 'i', 'j' );
INSERT INTO tab_auto2 (SELECT * FROM tab_auto2);
INSERT INTO tab_auto2 (SELECT * FROM tab_auto2);
INSERT INTO tab_auto2 (SELECT * FROM tab_auto1);
INSERT INTO tab_auto2 (SELECT * FROM tab_auto2);

-- Create table 'tab_auto3' in table space 'tbsp_auto'. Table creation
-- will fail with the following error:
-- "Unable to allocate new pages in table space".
CREATE TABLE tab_auto3 ( c1 char( 250 ),c2 char( 250 ),c3 char( 250 ),
                         c4 char( 250 ),c5 char( 250 ),c6 char( 250 ),
                         c7 char( 250 ),c8 char( 250 ),c9 char( 250 ),
                         c10 char( 250 ) )
  IN tbsp_auto;

! echo "Above error is expected !";

-- Take a snapshot of the table space. This will give details such as:
-- table space name, high water mark, extent size, used pages, free pages,
-- pending free pages for table space 'tbsp_auto'.
SELECT SUBSTR (tablespace_name, 1, 10) AS TABLESPACE_NAME,
       high_water_mark, extent_size, used_pages,
       free_pages, pending_free_pages
  FROM TABLE (snapshot_tbs_cfg ('testdb1', -1)) AS snapshot_tbs_cfg
  WHERE tablespace_name='TBSP_AUTO';
--TABLESPACE_NAME HIGH_WATER_MARK   EXTENT_SIZE   USED_PAGES    FREE_PAGES    PENDING_FREE_PAGES
----------------- ----------------- ------------- ------------- ------------- --------------------
--TBSP_AUTO                      24             2            22             0                    2

-- Dr