萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> 程式語言綜合

DB2 數據庫操作命令大全:實例管理、備份恢復、數據遷移、性能優化、權限控制

IBM DB2 (現 Db2) 企業級關係數據庫,命令行工具豐富。本文匯總 DBA 日常運維、開發調試高頻命令,覆蓋實例、數據庫、表空間、備份、授權、優化全生命週期。

🔧 環境與實例管理

# 進入實例用戶 (Linux/Unix)
su - db2inst1

# 查看版本
db2level

# 列出所有實例
db2ilist

# 創建實例 (root)
/opt/ibm/db2/V11.5/instance/db2icrt -u db2fenc1 db2inst1

# 刪除實例
db2idrop db2inst1

# 啟動/停止實例
db2start
db2stop [force]

# 設置實例自啟動
db2iauto -on db2inst1
db2iauto -off db2inst1

# 獲取實例配置
db2 get dbm cfg
db2 update dbm cfg using DFT_MON_SWITCH ON

🗄️ 數據庫對象操作

# 創建數據庫 (指定代碼頁/頁大小/表空間)
db2 "CREATE DATABASE mydb AUTOMATIC STORAGE YES ON '/data' DBPATH ON '/db2' USING CODESET UTF-8 TERRITORY CN COLLATE USING SYSTEM PAGESIZE 32768"

# 連接/斷開
db2 connect to mydb user db2inst1 using password
db2 connect reset
db2 terminate  # 徹底斷開後台進程

# 列出數據庫/表/表空間
db2 list db directory
db2 list tables for all
db2 list tables for schema MYSCHEMA
db2 list tablespaces show detail
db2 list tablespace containers for 2 show detail

# 修改數據庫配置 (動態/延遲生效)
db2 get db cfg for mydb
db2 update db cfg for mydb using LOGFILSIZ 8192 LOGPRIMARY 10 LOGSECOND 20
db2 update db cfg for mydb using AUTO_MAINT ON AUTO_TBL_MAINT ON AUTO_RUNSTATS ON

📦 數據移動:導出/導入/載入

# 導出 (EXPORT) - SQL 查詢結果寫文件 (IXF/DEL/WSF)
db2 "EXPORT TO /backup/user.ixf OF IXF SELECT * FROM MYSCHEMA.USER"
db2 "EXPORT TO /backup/user.del OF DEL MODIFIED BY COLDEL| SELECT * FROM MYSCHEMA.USER WHERE STATUS='1'"

# 導入 (IMPORT) - SQL 插入方式,慢,支援觸發器/約束
db2 "IMPORT FROM /backup/user.ixf OF IXF INSERT INTO MYSCHEMA.USER"
db2 "IMPORT FROM /backup/user.del OF DEL MODIFIED BY COLDEL| INSERT_UPDATE INTO MYSCHEMA.USER"

# 載入 (LOAD) - 高速頁面級寫入,**不觸發觸發器/約束**,大數據首選
db2 "LOAD FROM /backup/user.ixf OF IXF REPLACE INTO MYSCHEMA.USER"
db2 "LOAD FROM /backup/user.del OF DEL MODIFIED BY COLDEL| INSERT INTO MYSCHEMA.USER NONRECOVERABLE" # 不記錄日志,極快

# db2move 批量遷移 (整庫/多表)
db2move mydb EXPORT -sn MYSCHEMA -tn TABLE1,TABLE2
db2move mydb IMPORT -io REPLACE_CREATE
db2move mydb LOAD -lo REPLACE -l /backup/load.log

💾 備份與恢復 (核心 DBA 技能)

# 離線備份 (需斷開連接)
db2 backup db mydb to /backup COMPRESS INCLUDE LOGS

# 在線備份 (需啟用日志保留 LOGFILSIZ/LOGPRIMARY/LOGSECOND + logretain ON)
db2 backup db mydb online to /backup COMPRESS INCLUDE LOGS WITH 2 BUFFERS BUFFER 1024 WITHOUT PROMPTING

# 恢復 (離線)
db2 restore db mydb from /backup taken at 20240101120000

# 恢復 + 前滾 (在線備份必須)
db2 restore db mydb from /backup taken at 20240101120000 logtarget /logs
db2 "rollforward db mydb to end of logs and stop overflow log path (/logs)"

# 重定向恢復 (異機/路徑變更)
db2 "restore db mydb from /backup redirect generate script /tmp/restore.clp"
# 編輯 /tmp/restore.clp 修改表空間容器路徑
db2 -tvf /tmp/restore.clp

🔐 權限與授權

# 授予用戶權限
db2 "GRANT CONNECT ON DATABASE TO USER appuser"
db2 "GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE MYSCHEMA.T1 TO USER appuser"
db2 "GRANT EXECUTE ON PROCEDURE MYSCHEMA.P1 TO USER appuser"
db2 "GRANT DBADM ON DATABASE TO USER dba_user"  # 高權限

# 角色管理
db2 "CREATE ROLE APP_READ"
db2 "GRANT SELECT ON TABLE MYSCHEMA.T1 TO ROLE APP_READ"
db2 "GRANT ROLE APP_READ TO USER appuser"

# 撤銷
db2 "REVOKE SELECT ON TABLE MYSCHEMA.T1 FROM USER appuser"

⚡ 性能優化核心命令

# 表/索引重組 (消除碎片、回收空間、更新統計)
db2 reorg table MYSCHEMA.T1 [index IDX1] [allow read access | allow write access]
db2 reorgchk update statistics on table all  # 自動建議哪些表需重組

# 統計信息更新 (優化器依賴)
db2 runstats on table MYSCHEMA.T1 with distribution and indexes all

# 監控快照
db2 get snapshot for database on mydb
db2 get snapshot for applications on mydb
db2 get snapshot for locks on mydb
db2 get snapshot for dynamic sql on mydb  # 找高消耗 SQL

# 事件監視器 (歷史分析)
db2 "CREATE EVENT MONITOR STMT_MON FOR STATEMENTS WRITE TO TABLE"
db2 "SET EVENT MONITOR STMT_MON STATE 1"
# 查詢生成表: SELECT * FROM STMT_MON_STMT ...

# 套接字/連接池監控
db2 list applications show detail

🛠️ 故障診斷與工具

  • db2diag -level severe -readfile:讀取診斷日志 db2diag.log。
  • db2pd -db mydb -transactions -locks -bufferpools -tablespaces:無鎖快照,高併發下安全。
  • db2trc on -f /tmp/trc.dmp / db2trc off / db2trc fmt /tmp/trc.dmp /tmp/trc.txt:詳細追蹤 (IBM 支援常要求)。
  • db2look -d mydb -e -x -o ddl.sql:導出 DDL (結構、授權、統計),遷移/審計必備。

📋 開發者常用速查

任務命令
查看表結構db2 describe table MYSCHEMA.T1
查看索引db2 describe indexes for table MYSCHEMA.T1
查看約束db2 "SELECT * FROM SYSCAT.TABCONST WHERE TABSCHEMA='MYSCHEMA' AND TABNAME='T1'"
查看授權db2 "SELECT * FROM SYSCAT.TABAUTH WHERE TABSCHEMA='MYSCHEMA' AND TABNAME='T1'"
執行 SQL 文件db2 -tvf script.sql (-t 分號終止, -v 回顯, -f 文件)
交互模式db2 → 進入命令行提示符
獲取錯誤詳情db2 "? SQL0803N" (SQLCODE 4 位補零)

掌握這些命令,應對 DB2 日常運維、遷移、調優得心應手。生產環境操作前**務必驗證備份可用、評估影響、溝通窗口**。

copyright © 萬盛學電腦網 all rights reserved