DB2建立資料庫樣本

1    建立串連使用者
mkuser id=1028 pgrp=db2user1 groups=db2user1 home=/home/fmusx core=-1 data=491519 stack=32767 rss=-1 fsize=-1 fmusx

2    建立開發資料庫
db2 create database SX3    AUTOMATIC STORAGE YES ON /db2fs/sx2data DBPATH ON /db2fs/sx2  USING CODESET GBK TERRITORY CN

3    建立緩衝池
db2 create bufferpool SX_16K IMMEDIATE SIZE AUTOMATIC PAGESIZE 16K
db2 create bufferpool SX_32K IMMEDIATE SIZE AUTOMATIC PAGESIZE 32K

4    建立系統資料表空間
db2 CREATE SYSTEM TEMPORARY TABLESPACE TEMPSPACE16 PAGESIZE 16K BUFFERPOOL SX_16K NO FILE SYSTEM CACHING
db2 CREATE SYSTEM TEMPORARY TABLESPACE TEMPSPACE32 PAGESIZE 32K BUFFERPOOL SX_32K NO FILE SYSTEM CACHING
db2 CREATE USER TEMPORARY TABLESPACE temp16k PAGESIZE 16K BUFFERPOOL SX_16K NO FILE SYSTEM CACHING
db2 CREATE USER TEMPORARY TABLESPACE temp32k PAGESIZE 32K BUFFERPOOL SX_32K NO FILE SYSTEM CACHING

db2 GRANT USE OF TABLESPACE "temp16k" TO  fmusx
db2 GRANT USE OF TABLESPACE "temp32k" TO  fmusx

5    建立使用者資料表空間
db2 create LARGE tablespace FMUSX32K_D PAGESIZE 32K BUFFERPOOL SX_32K NO FILE SYSTEM CACHING
db2 create LARGE tablespace FMUSX32K_I PAGESIZE 32K BUFFERPOOL SX_32K NO FILE SYSTEM CACHING

db2 GRANT USE OF TABLESPACE FMUSX32K_D TO  fmusx
db2 GRANT USE OF TABLESPACE FMUSX32K_I TO  fmusx

6    刪除預設使用者資料表空間
$ db2 drop tablespace USERSPACE1
DB20000I  The SQL command completed successfully.

7  設定歸檔模式

db2 update db cfg using LOGFILSIZ 102400
db2 update db cfg using LOGPRIMARY 20
db2 update db cfg using LOGSECOND 50
db2 update db cfg using LOGARCHMETH1 disk:/db2fs/db2backup/archive_log/sx3/ immediate

$ db2 update db cfg using LOGFILSIZ 102400
db2 update db cfg using LOGPRIMARY 20
db2 update db cfg using LOGSECOND 50
db2 update db cfg using LOGARCHMETH1 disk:/db2fs/db2backup/archive_log/sx3/ immediate
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W  Database must be deactivated and reactivated before the changes to
one or more of the configuration parameters will be effective.
$ db2 update db cfg using LOGPRIMARY 20
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W  Database must be deactivated and reactivated before the changes to
one or more of the configuration parameters will be effective.
$ db2 update db cfg using LOGSECOND 50
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
$ db2 update db cfg using LOGARCHMETH1 disk:/db2fs/db2backup/archive_log/sx3/ immediate
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
SQL1363W  Database must be deactivated and reactivated before the changes to
one or more of the configuration parameters will be effective.

註：a.歸檔目錄一定要先建立，否則會報錯

    b.除LOGSECOND外，其他參數都需要重啟資料庫才會生效（或者直接重啟instance）

8.  備份資料庫

將資料庫修改為歸檔模式後，必須先離線備份資料庫，否則整個資料庫的狀態將為backup pending，無法訪問。

$ db2 backup db sx3 to /db2fs/db2backup/sx3backup

Backup successful. The timestamp for this backup image is : 20120817225640

備忘：做完離線備份後，建議做一次線上備份（不確定是不是必須，但是正好可以測試是否能線上備份）

$ db2 backup db sx3 online to /db2fs/db2backup/sx3backup include logs

Backup successful. The timestamp for this backup image is : 20120817225824

備忘：在做以上操作時，為防止其他非本地應用串連資料庫，可以將instance的協議登出，類似於Oracle中關閉監聽器的操作。

$ db2set -all
[i] DB2_DATABASE_CF_MEMORY=-1
[i] DB2_COMPATIBILITY_VECTOR=ORA
[i] DB2RSHCMD=/bin/ssh
[i] DB2COUNTRY=86
[i] DB2COMM=TCPIP
[i] DB2CODEPAGE=1386
[i] DB2AUTOSTART=NO
[g] DB2SYSTEM=SXYCDBM0
[g] DB2INSTDEF=db2sdin1

關閉串連連接埠

$ db2set DB2COMM=

$ db2set -all
[i] DB2_DATABASE_CF_MEMORY=-1
[i] DB2_COMPATIBILITY_VECTOR=ORA
[i] DB2RSHCMD=/bin/ssh
[i] DB2COUNTRY=86
[i] DB2CODEPAGE=1386
[i] DB2AUTOSTART=NO
[g] DB2SYSTEM=SXYCDBM0
[g] DB2INSTDEF=db2sdin1

備忘：這些有“[i]”標識的參數為執行個體級參數，需要重啟執行個體才會生效