連接數據庫：
??connect?to?[數據庫名]?user?[操作用戶名]?using?[密碼]?
創建緩沖池(8K)：
??create?bufferpool?ibmdefault8k?IMMEDIATE??SIZE?5000?PAGESIZE?8?K?;
創建緩沖池(16K)(OA_DIVERTASKRECORD)：
??create?bufferpool?ibmdefault16k?IMMEDIATE??SIZE?5000?PAGESIZE?16?K?;
創建緩沖池(32K)(OA_TASK)：
??create?bufferpool?ibmdefault32k?IMMEDIATE??SIZE?5000?PAGESIZE?32?K?;
創建表空間：
??CREATE?TABLESPACE?exoatbs?IN?DATABASE?PARTITION?GROUP?IBMDEFAULTGROUP?PAGESIZE?8K?MANAGED?BY?SYSTEM?USING?('/home/exoa2/exoacontainer')?EXTENTSIZE?32?PREFETCHSIZE?16??BUFFERPOOL?IBMDEFAULT8K??OVERHEAD?24.10?TRANSFERRATE?0.90??DROPPED?TABLE?RECOVERY?OFF;
??CREATE?TABLESPACE?exoatbs16k??IN?DATABASE?PARTITION?GROUP?IBMDEFAULTGROUP?PAGESIZE?16K?MANAGED?BY?SYSTEM?USING?('/home/exoa2/exoacontainer16k'???)?EXTENTSIZE?32??PREFETCHSIZE?16??BUFFERPOOL?IBMDEFAULT16K??OVERHEAD?24.1?TRANSFERRATE?0.90??DROPPED?TABLE?RECOVERY?OFF;
??CREATE?TABLESPACE?exoatbs32k??IN?DATABASE?PARTITION?GROUP?IBMDEFAULTGROUP?PAGESIZE?32K?MANAGED?BY?SYSTEM?USING?('/home/exoa2/exoacontainer32k'???)?EXTENTSIZE?32??PREFETCHSIZE?16??BUFFERPOOL?IBMDEFAULT32K??OVERHEAD?24.1?TRANSFERRATE?0.90??DROPPED?TABLE?RECOVERY?OFF;
GRANT?USE?OF?TABLESPACE?exoatbs?TO?PUBLIC;
GRANT?USE?OF?TABLESPACE?exoatbs16k?TO?PUBLIC;
GRANT?USE?OF?TABLESPACE?exoatbs32k?TO?PUBLIC;
創建系統表空間：
??CREATE?TEMPORARY?TABLESPACE?exoasystmp?IN?DATABASE?PARTITION?GROUP?IBMTEMPGROUP?PAGESIZE?8K??MANAGED?BY?SYSTEM?USING?('/home/exoa2/exoasystmp'???)?EXTENTSIZE?32?PREFETCHSIZE?16?BUFFERPOOL?IBMDEFAULT8K??OVERHEAD?24.10?TRANSFERRATE?0.90??DROPPED?TABLE?RECOVERY?OFF;
??CREATE?TEMPORARY?TABLESPACE?exoasystmp16k?IN?DATABASE?PARTITION?GROUP?IBMTEMPGROUP?PAGESIZE?16K?MANAGED?BY?SYSTEM?USING?('/home/exoa2/exoasystmp16k'??)?EXTENTSIZE?32?PREFETCHSIZE?16?BUFFERPOOL?IBMDEFAULT16K?OVERHEAD?24.10?TRANSFERRATE?0.90??DROPPED?TABLE?RECOVERY?OFF;
??CREATE?TEMPORARY?TABLESPACE?exoasystmp32k?IN?DATABASE?PARTITION?GROUP?IBMTEMPGROUP?PAGESIZE?32K?MANAGED?BY?SYSTEM?USING?('/home/exoa2/exoasystmp32k')?EXTENTSIZE?32?PREFETCHSIZE?16?BUFFERPOOL?IBMDEFAULT32K?OVERHEAD?24.10?TRANSFERRATE?0.90??DROPPED?TABLE?RECOVERY?OFF;
1.?啟動實例(db2inst1):
db2start
2.?停止實例(db2inst1):
db2stop
3.?列出所有實例(db2inst1)
db2ilist
5.列出當前實例:
db2?get?instance
4.?察看示例配置文件:
db2?get?dbm?cfg|more
5.?更新數據庫管理器參數信息:
db2?update?dbm?cfg?using?para_name?para_value
6.?創建數據庫:
db2?create?db?test
7.?察看數據庫配置參數信息
db2?get?db?cfg?for?test|more
8.?更新數據庫參數配置信息
db2?update?db?cfg?for?test?using?para_name?para_value
10.刪除數據庫:
db2?drop?db?test
11.連接數據庫
db2?connect?to?test
12.列出所有表空間的詳細信息。
db2?list?tablespaces?show?detail
13.查詢數據:
db2?select?*?from?tb1
14.刪除數據:
db2?delete?from?tb1?where?id=1
15.創建索引:
db2?create?index?idx1?on?tb1(id);
16.創建視圖:
db2?create?view?view1?as?select?id?from?tb1
17.查詢視圖:
db2?select?*?from?view1
18.節點編目
db2?catalog?tcp?node?node_name?remote?server_ip?server?server_port
19.察看端口號
db2?get?dbm?cfg|grep?SVCENAME
20.測試節點的附接
db2?attach?to?node_name
21.察看本地節點
db2?list?node?direcotry
22.節點反編目
db2?uncatalog?node?node_name
23.數據庫編目
db2?catalog?db?db_name?as?db_alias?at?node?node_name
24.察看數據庫的編目
db2?list?db?directory
25.連接數據庫
db2?connect?to?db_alias?user?user_name?using?user_password
26.數據庫反編目
db2?uncatalog?db?db_alias
27.導出數據
db2?export?to?myfile?of?ixf?messages?msg?select?*?from?tb1
28.導入數據
db2?import?from?myfile?of?ixf?messages?msg?replace?into?tb1
29.導出數據庫的所有表數據
db2move?test?export
30.生成數據庫的定義
db2look?-d?db_alias?-a?-e?-m?-l?-x?-f?-o?db2look.sql
31.創建數據庫
db2?create?db?test1
32.生成定義
db2?-tvf?db2look.sql
33.導入數據庫所有的數據
db2move?db_alias?import
34.重組檢查
db2?reorgchk
35.重組表tb1
db2?reorg?table?tb1
36.更新統計信息
db2?runstats?on?table?tb1
37.備份數據庫test
db2?backup?db?test
38.恢復數據庫test
db2?restore?db?test
399\.列出容器的信息
db2?list?tablespace?containers?for?tbs_id?show?detail
40.創建表:
db2?ceate?table?tb1(id?integer?not?null,name?char(10))
41.列出所有表
db2?list?tables
42.插入數據:
db2?insert?into?tb1?values(1,’sam’);
db2?insert?into?tb2?values(2,’smitty’);
.?建立數據庫DB2_GCB?
CREATE?DATABASE?DB2_GCB?ON?G:?ALIAS?DB2_GCB?
USING?CODESET?GBK?TERRITORY?CN?COLLATE?USING?SYSTEM?DFT_EXTENT_SZ?32?
2.?連接數據庫?
connect?to?sample1?user?db2admin?using?8301206?
3.?建立別名?
create?alias?db2admin.tables?for?sysstat.tables;?
CREATE?ALIAS?DB2ADMIN.VIEWS?FOR?SYSCAT.VIEWS?
create?alias?db2admin.columns?for?syscat.columns;?
create?alias?guest.columns?for?syscat.columns;?
4.?建立表?
create?table?zjt_tables?as?
(select?*?from?tables)?definition?only;?
create?table?zjt_views?as?
(select?*?from?views)?definition?only;?
5.?插入記錄?
insert?into?zjt_tables?select?*?from?tables;?
insert?into?zjt_views?select?*?from?views;?
6.?建立視圖?
create?view?V_zjt_tables?as?select?tabschema,tabname?from?zjt_tables;?
7.?建立觸發器?
CREATE?TRIGGER?zjt_tables_del?
AFTER?DELETE?ON?zjt_tables?
REFERENCING?OLD?AS?O?
FOR?EACH?ROW?MODE?DB2SQL?
Insert?into?zjt_tables1?values(substr(o.tabschema,1,8),substr(o.tabname,1,10))?
8.?建立唯一性索引?
CREATE?UNIQUE?INDEX?I_ztables_tabname?
[size=3]ON?zjt_tables(tabname);?
9.?查看表?
select?tabname?from?tables?
where?tabname='ZJT_TABLES';?
10.?查看列?
select?SUBSTR(COLNAME,1,20)?as?列名,TYPENAME?as?類型,LENGTH?as?長度?
from?columns?
where?tabname='ZJT_TABLES';?
11.?查看表結構?
db2?describe?table?user1.department?
db2?describe?select?*?from?user.tables?
12.?查看表的索引?
db2?describe?indexes?for?table?user1.department?
13.?查看視圖?
select?viewname?from?views?
where?viewname='V_ZJT_TABLES';?
14.?查看索引?
select?indname?from?indexes?
where?indname='I_ZTABLES_TABNAME';?
15.?查看存貯過程?
SELECT?SUBSTR(PROCSCHEMA,1,15),SUBSTR(PROCNAME,1,15)?
FROM?SYSCAT.PROCEDURES;?
16.?類型轉換(cast)?
ip?datatype:varchar?
select?cast(ip?as?integer)+50?from?log_comm_failed?
17.?重新連接?
connect?reset?
18.?中斷數據庫連接?
disconnect?db2_gcb?
19.?view?application?
LIST?APPLICATION;?
20.?kill?application?
FORCE?APPLICATION(0);?
db2?force?applications?all?(強迫所有應用程序從數據庫斷開)?
21.?lock?table
lock?table?test?in?exclusive?mode?
22.?共享?
lock?table?test?in?share?mode?
23.?顯示當前用戶所有表?
list?tables?
24.?列出所有的系統表?
list?tables?for?system?
25.?顯示當前活動數據庫?
list?active?databases?
26.?查看命令選項?
list?command?options?
27.?系統數據庫目錄?
LIST?DATABASE?DIRECTORY?
28.?表空間?
list?tablespaces?
29.?表空間容器?
LIST?TABLESPACE?CONTAINERS?FOR?
Example:?LIST?TABLESPACE?CONTAINERS?FOR?1?
30.?顯示用戶數據庫的存取權限?
GET?AUTHORIZATIONS?
31.?啟動實例?
DB2START?
32.?停止實例?
db2stop?
33.?表或視圖特權?
grant?select,delete,insert,update?on?tables?to?user?
grant?all?on?tables?to?user?WITH?GRANT?OPTION?
34.?程序包特權?
GRANT?EXECUTE?
ON?PACKAGE?PACKAGE-name?
TO?PUBLIC?
35.?模式特權?
GRANT?CREATEIN?ON?SCHEMA?SCHEMA-name?TO?USER?
36.?數據庫特權?
grant?connect,createtab,dbadm?on?database?to?user?
37.?索引特權?
grant?control?on?index?index-name?to?user?
38.?信息幫助?(??XXXnnnnn?)?
例:??SQL30081?
39.?SQL?幫助(說明?SQL?語句的語法)?
help?statement?
例如，help?SELECT?
40.?SQLSTATE?幫助(說明?SQL?的狀態和類別代碼)?
??sqlstate?或???class-code?
41.?更改與"管理服務器"相關的口令?
db2admin?setid?username?password?
42.?創建?SAMPLE?數據庫?
db2sampl?
db2sampl?F:(指定安裝盤)?
43.?使用操作系統命令?
!?dir?
44.?轉換數據類型?(cast)?
SELECT?EMPNO,?CAST(RESUME?AS?VARCHAR(370))?
FROM?EMP_RESUME?
WHERE?RESUME_FORMAT?=?'ascii'?
45.?UDF
要運行?DB2?Java?存儲過程或?UDF，還需要更新服務器上的?DB2?數據庫管理程序配置，以包括在該機器上安裝?JDK?的路徑?
db2?update?dbm?cfg?using?JDK11_PATH?d:sqllibjavajdk?
TERMINATE?
update?dbm?cfg?using?SPM_NAME?sample?
46.?檢查?DB2?數據庫管理程序配置?
db2?get?dbm?cfg?
47.?檢索具有特權的所有授權名?
SELECT?DISTINCT?GRANTEE,?GRANTEETYPE,?'DATABASE'?FROM?SYSCAT.DBAUTH?
UNION?
SELECT?DISTINCT?GRANTEE,?GRANTEETYPE,?'TABLE?'?FROM?SYSCAT.TABAUTH?
UNION?
SELECT?DISTINCT?GRANTEE,?GRANTEETYPE,?'PACKAGE?'?FROM?SYSCAT.PACKAGEAUTH?
UNION?
SELECT?DISTINCT?GRANTEE,?GRANTEETYPE,?'INDEX?'?FROM?SYSCAT.INDEXAUTH?
UNION?
SELECT?DISTINCT?GRANTEE,?GRANTEETYPE,?'COLUMN?'?FROM?SYSCAT.COLAUTH?
UNION?
SELECT?DISTINCT?GRANTEE,?GRANTEETYPE,?'SCHEMA?'?FROM?SYSCAT.SCHEMAAUTH?
UNION?
SELECT?DISTINCT?GRANTEE,?GRANTEETYPE,?'SERVER?'?FROM?SYSCAT.PASSTHRUAUTH?
ORDER?BY?GRANTEE,?GRANTEETYPE,?3?
create?table?yhdab?
(id?varchar(10),?
password?varchar(10),?
ywlx?varchar(10),?
kh?varchar(10));?
create?table?ywlbb?
(ywlbbh?varchar(8),?
ywmc?varchar(60))?
48.?修改表結構?
alter?table?yhdab?ALTER?kh?SET?DATA?TYPE?varchar(13);?
alter?table?yhdab?ALTER?ID?SET?DATA?TYPE?varchar(13);?
alter?table?lst_bsi?alter?bsi_money?set?data?type?int;?
insert?into?yhdab?values?
('20000300001','123456','user01','20000300001'),?
('20000300002','123456','user02','20000300002');?
49.?業務類型說明?
insert?into?ywlbb?values?
('user01','業務申請'),?
('user02','業務撤消'),?
('user03','費用查詢'),?
('user04','費用自繳'),?
('user05','費用預存'),?
('user06','密碼修改'),?
('user07','發票打印'),?
('gl01','改用戶基本信息'),?
('gl02','更改支付信息'),?
('gl03','日統計功能'),?
('gl04','沖帳功能'),?
('gl05','對帳功能'),?
('gl06','計費功能'),?
('gl07','綜合統計')?
備份數據庫：
CONNECT?TO?EXOA;
QUIESCE?DATABASE?IMMEDIATE?FORCE?CONNECTIONS;
CONNECT?RESET;
BACKUP?DATABASE?EXOA?TO?"/home/exoa2/db2bak/"?WITH?2?BUFFERS?BUFFER?1024?PARALLELISM?1?WITHOUT?PROMPTING;
CONNECT?TO?EXOA;
UNQUIESCE?DATABASE;
CONNECT?RESET;
以下是小弟在使用db2move中的一些經驗，希望對大家有所幫助。?
?db2???connect???to??YOURDB???
連接數據庫?
?db2look?-d??YOURDB??-a?-e?-x?-o?creatab.sql?
導出建庫表的SQL?
?db2move???YOURDB??export?
用db2move將數據備份出來?
?vi???creatab.sql?
如要導入的數據庫名與原數據庫不同，要修改creatab.sql中CONNECT?項?
如相同則不用更改?
?db2move??NEWDB??load?
將數據導入新庫中?
在導入中可能因為種種原因發生中斷，會使數據庫暫掛?
db2????list?tablespaces???show???detail?
如：?
??????詳細說明：?
?????裝入暫掛?
?總頁數??????????????????????????=?1652?
?可用頁數????????????????????????=?1652?
?已用頁數?????????????????????????=?1652?
?空閑頁數?????????????????????????=?不適用?
?高水位標記（頁）?????????????????=?不適用?
?頁大?。ㄗ止潱???????????????????=?4096?
?盤區大?。摚???????????????????=?32?
?預讀取大?。摚?????????????????=?32?
?容器數???????????????????????????=?1?
?狀態更改表空間標識????????????????????=?2?
?狀態更改對象標識??????????????????????=?59?
?db2?select?tabname,tableid?from?syscat.tables?where?tableid=59?
查看是哪張表掛起?
表名知道后到db2move.lst(在db2move??YOURDB??export的目錄中)中找到相應的.ixf文件?
?db2?load?from?tab11.ixf?of?ixf?terminate?into?db2admin.xxxxxxxxx?
tab11.ixf對應的是xxxxxxxxx表?
數據庫會恢復正常，可再用db2?list?tablespaces?show?detail查看
30.不能通過GRANT授權的權限有哪種?
SYSAM
SYSCTRL
SYSMAINT
要更該述權限必須修改數據庫管理器配置參數
31.表的類型有哪些?
永久表(基表)
臨時表(說明表)
臨時表(派生表)
32.如何知道一個用戶有多少表?
SELECT*FROMSYSIBM.SYSTABLESWHERECREATOR='USER'
33.如何知道用戶下的函數?
select*fromIWH.USERFUNCTION
select*fromsysibm.SYSFUNCTIONS
34.如何知道用戶下的VIEW數?
select*fromsysibm.sysviewsWHERECREATOR='USER'
35.如何知道當前DB2的版本?
select*fromsysibm.sysvERSIONS
36.如何知道用戶下的TRIGGER數?
select*fromsysibm.SYSTRIGGERSWHERESCHEMA='USER'
37.如何知道TABLESPACE的狀況?
select*fromsysibm.SYSTABLESPACES
38.如何知道SEQUENCE的狀況?
select*fromsysibm.SYSSEQUENCES
39.如何知道SCHEMA的狀況?
select*fromsysibm.SYSSCHEMATA
40.如何知道INDEX的狀況?
select*fromsysibm.SYSINDEXES
41.如何知道表的字段的狀況?
select*fromsysibm.SYSCOLUMNSWHERETBNAME='AAAA'
42.如何知道DB2的數據類型?
select*fromsysibm.SYSDATATYPES
43.如何知道BUFFERPOOLS狀況?
select*fromsysibm.SYSBUFFERPOOLS
44.DB2表的字段的修改限制?
只能修改VARCHAR2類型的并且只能增加不能減少.
45.如何查看表的結構?
DESCRIBLETABLETABLE_NAME
OR
DESCRIBLESELECT*FROMSCHEMA.TABLE_NAME