DB2 數據庫常用技巧速查：31 條核心操作指令精簡版

精簡整理 31 條 DB2 高頻操作指令，適合打印貼在工位速查。

節點管理

1. db2 list node directory
2. db2 catalog tcpip node <n> remote <host> server <port> ostype LINUX
3. db2 uncatalog node <n>

數據庫目錄與連接

4. db2 list database directory
5. db2 list database directory on <drive>
6. db2 catalog database <db> as <alias> at node <n>
7. db2 uncatalog database <db>
8. db2 connect to <alias> user <uid> using <pwd>

Schema 與字符集

9. set schema = <schema>
10. create database <db> using codeset UTF-8 territory CN
11. db2set DB2CODEPAGE=1386  -- 需重啟實例

遷移與特殊查詢

12. db2 migrate database <db>
13. db2 "select * from \"Schema\".\"Table\""

DDL 與腳本

14. db2look -d <db> -e -c -o schema.ddl
15. db2 -tvf schema.ddl

元數據查詢

16. select * from sysibm.sysversions
17. alter table <t> alter column <c> set data type varchar(500)
18. describe table <t>
19. alter table <t> activate not logged initially with empty table
20. select * from syscat.procedures
21. select * from syscat.checks where tabname='<t>'
22. select * from syscat.references where tabname='<t>'
23. select * from syscat.bufferpools

配置與緩衝池

24. db2 get dbm cfg / db2 update dbm cfg using <p> <v>
25. db2 get db cfg for <db> / db2 update db cfg for <db> using <p> <v>
26. create bufferpool <bp> size 10000 pagesize 16K
27. alter bufferpool <bp> size 20000
28. drop bufferpool <bp>

進階查詢與數據移動

29. where (a,b,c) in (values (1,2,3), (4,5,6))
30. db2 list applications show detail
31. db2dart <db> /DB
32. db2batch -d <db> -f sql.txt -r out.txt
33. export to /path/t.ixf of ixf select * from t
34. import from /path/t.ixf of ixf insert into t
35. db2move <db> export -sn <schema> -u <uid> -p <pwd>
36. db2move <db> import
37. db2look -d <db> -u creator -z schema -i uid -w pwd -o all.ddl -dp

命令中尖括號內容請替換為實際值。建議配合《DB2 31 條實用技巧全集》深度版食用。