目的：
测试DB2使用自动存储数据库的创建、备份、恢复，本脚本摘录自DB2安装目录admin_scripts/autostore.db2。
版本：Windows DB2 Express-C V9.7

用户要求：
操作系统用户mh有DBADM,SECADM权限,mh1234

操作步骤：
在操作系统命令窗口set DB2TESTPATH=E:;然后,使用"db2cmd db2 -td@"进入交互模式,执行后续操作。

1,创建目录
!mkdir "%DB2TESTPATH%\storpath1"@
!mkdir "%DB2TESTPATH%\storpath2"@
!mkdir "%DB2TESTPATH%\storpath3"@
!mkdir "%DB2TESTPATH%\storpath4"@

2,创建数据库
-- Create a database enabled for automatic storage with two storage paths and
-- on a specified database path
-- The storage paths used are: %DB2TESTPATH%\storpath1, %DB2TESTPATH%\storpath2
-- The database path use is  : %DB2TESTPATH%\dbpath

! db2 "CREATE DATABASE autodb AUTOMATIC STORAGE YES ON
 \"%DB2TESTPATH%\storpath1\", \"%DB2TESTPATH%\storpath2\" DBPATH ON \"%DB2TESTPATH%\""@

3,创建数据库对象
CONNECT TO autodb@

-- Create a tablespace enabled for automatic storage. If no MANAGED BY clause
-- is specified the tablespace is, by default, managed by automatic storage.
CREATE TABLESPACE TS1@

-- Create another tablespace enabled to auto-resize
-- TS2 is created with an initial size of 100 MB and with a maximum size of 1 GB
-- (By default AUTORESIZE is set to YES)
CREATE TABLESPACE TS2 INITIALSIZE 100 M MAXSIZE 1 G@

-- Create tablespace without auto-resize enabled
CREATE TABLESPACE TS3 AUTORESIZE NO@

-- Create tablespace enabled to auto-resize without any upper bound on
-- maximum size
CREATE TABLESPACE TS4
  MANAGED BY DATABASE
  USING (FILE 'TS3File' 1000)
  AUTORESIZE YES
  MAXSIZE NONE@

-- Alter tablespace to increase its size by 5 percent
ALTER TABLESPACE TS4 INCREASESIZE 5 PERCENT@

-- Alter database to add one more storage path, %DB2TESTPATH%\storpath3, to the
-- existing space for automatic storage table spaces
-- Running the ALTER DATABASE statement in a shell as path substitution
-- can be done inside a sheell

!db2 "CONNECT TO AUTODB"@
!db2 "ALTER DATABASE autodb ADD STORAGE ON '%DB2TESTPATH%\storpath3'"@

--测试直接执行ALTER DATABASE
ALTER DATABASE autodb ADD STORAGE ON '%DB2TESTPATH%\storpath4'@
SQL1051N  路径 "%DB2TESTPATH%\storpath4" 不存在或无效。  SQLSTATE=57019

--测试直接执行ALTER DATABASE
ALTER DATABASE autodb ADD STORAGE ON %DB2TESTPATH%\storpath4@
SQL0007N  "AGE ON %DB2TESTPATH%" 后面的字符 "\" 无效。  SQLSTATE=42601

!db2 "CONNECT TO AUTODB"@
!db2 "ALTER DATABASE autodb ADD STORAGE ON '%DB2TESTPATH%\storpath4'"@

connect to autodb@
create table t(x int)@
insert into t values 1,2,3@
commit@

create table t3(x int) in ts4@
insert into t3 values 1,2,3@
commit@

-- Check the status information of tablespaces for database AUTODB
GET SNAPSHOT FOR TABLESPACES ON autodb@

select * from syscat.tablespaces@

4,备份数据库
-- Disconnect from database
!db2 "CONNECT RESET"@

-- Backup the database
BACKUP DATABASE autodb@
备份成功。此备份映像的时间戳记是：20110713144106

5,删除数据库
-- Connect to database
CONNECT TO autodb@

-- Drop the tablespaces
DROP TABLESPACE TS1@
DROP TABLESPACE TS2@
DROP TABLESPACE TS3@
DROP TABLESPACE TS4@

-- Disconnect from database
CONNECT RESET@
-- Drop the database
DROP DATABASE autodb@

6,恢复数据库
-- Restore the database to a set of storage paths
! db2 "RESTORE DATABASE autodb ON \"%DB2TESTPATH%\storpath2\",
 \"%DB2TESTPATH%\storpath3\" DBPATH ON \"%DB2TESTPATH%\" WITHOUT PROMPTING"@
SQL2522N  多个备份文件与为备份的数据库映像提供的时间戳记值相匹配。

! db2 "RESTORE DATABASE autodb taken at 20110713144106 ON \"%DB2TESTPATH%\storpath2\",
 \"%DB2TESTPATH%\storpath3\" DBPATH ON \"%DB2TESTPATH%\" WITHOUT PROMPTING"@
DB20000I  RESTORE DATABASE 命令成功完成。

connect to autodb@
select * from t@
X
-----------
          1
          2
          3
  3 条记录已选择。

select * from t3@
X
-----------
          1
          2
          3
  3 条记录已选择。

7,清理测试环境
-- Drop the database 'AUTODB'
force application all@
DROP DB AUTODB@

-- Remove the directories.
!rmdir "%DB2TESTPATH%\storpath1"@
!rmdir "%DB2TESTPATH%\storpath2"@
!rmdir "%DB2TESTPATH%\storpath3"@
!rmdir "%DB2TESTPATH%\storpath4"@

TERMINATE@