DB2 基礎管理指令大全：從建庫到效能維護

實例與連線

db2start / db2stop / db2stop force
db2 connect to <db> user <usr> using <pwd>
db2 connect reset / db2 disconnect all
db2 force application all

配置參數

db2 get dbm cfg
db2 update dbm cfg using PARA VALUE
db2 get db cfg for <db>
db2 update db cfg for <db> using PARA VALUE

緩衝池與表空間建立範例（8K/16K/32K）

CREATE BUFFERPOOL ibmdefault8k IMMEDIATE SIZE 5000 PAGESIZE 8K;
CREATE TABLESPACE exoatbs PAGESIZE 8K MANAGED BY SYSTEM USING ('/path') BUFFERPOOL IBMDEFAULT8K;
GRANT USE OF TABLESPACE exoatbs TO PUBLIC;

同理建立 16K、32K 緩衝池、表空間、暫存表空間。

常用 DDL/DML

CREATE TABLE tb1(id INT NOT NULL, name CHAR(10));
CREATE INDEX idx1 ON tb1(id);
CREATE VIEW view1 AS SELECT id FROM tb1;
INSERT INTO tb1 VALUES(1,'sam');
DELETE FROM tb1 WHERE id=1;

節點與資料庫編目

db2 catalog tcpip node NODE remote IP server PORT
db2 catalog db DB_NAME as ALIAS at node NODE
db2 list node directory / db2 list db directory

資料移動

db2 export to file.ixf of ixf select * from tb1
db2 import from file.ixf of ixf replace into tb1
db2move db export / import
db2look -d db -a -e -m -l -x -f -o ddl.sql  -- 產生 DDL

維護操作

db2 reorgchk update statistics on table all
db2 reorg table tb1
db2 runstats on table tb1
db2 backup db dbname
db2 restore db dbname

監控與診斷

db2 list applications show detail
db2 get snapshot for all on dbname > snap.out
db2 list tablespaces show detail
db2 reorgchk current statistics on table user

Windows 建庫指定代碼頁

CREATE DATABASE DB2_GCB ON G: ALIAS DB2_GCB USING CODESET GBK TERRITORY CN COLLATE USING SYSTEM DFT_EXTENT_SZ 32