DB2_建立備份恢復使用自動儲存的資料庫

目的：
測試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@