連接數(shù)據(jù)庫(kù)：
??connect?to?[數(shù)據(jù)庫(kù)名]?user?[操作用戶(hù)名]?using?[密碼]?
創(chuàng)建緩沖池(8K)：
??create?bufferpool?ibmdefault8k?IMMEDIATE??SIZE?5000?PAGESIZE?8?K?;
創(chuàng)建緩沖池(16K)(OA_DIVERTASKRECORD)：
??create?bufferpool?ibmdefault16k?IMMEDIATE??SIZE?5000?PAGESIZE?16?K?;
創(chuàng)建緩沖池(32K)(OA_TASK)：
??create?bufferpool?ibmdefault32k?IMMEDIATE??SIZE?5000?PAGESIZE?32?K?;
創(chuàng)建表空間：
??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;
創(chuàng)建系統(tǒng)表空間：
??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.?啟動(dòng)實(shí)例(db2inst1):
db2start
2.?停止實(shí)例(db2inst1):
db2stop
3.?列出所有實(shí)例(db2inst1)
db2ilist
5.列出當(dāng)前實(shí)例:
db2?get?instance
4.?察看示例配置文件:
db2?get?dbm?cfg|more
5.?更新數(shù)據(jù)庫(kù)管理器參數(shù)信息:
db2?update?dbm?cfg?using?para_name?para_value
6.?創(chuàng)建數(shù)據(jù)庫(kù):
db2?create?db?test
7.?察看數(shù)據(jù)庫(kù)配置參數(shù)信息
db2?get?db?cfg?for?test|more
8.?更新數(shù)據(jù)庫(kù)參數(shù)配置信息
db2?update?db?cfg?for?test?using?para_name?para_value
10.刪除數(shù)據(jù)庫(kù):
db2?drop?db?test
11.連接數(shù)據(jù)庫(kù)
db2?connect?to?test
12.列出所有表空間的詳細(xì)信息。
db2?list?tablespaces?show?detail
13.查詢(xún)數(shù)據(jù):
db2?select?*?from?tb1
14.刪除數(shù)據(jù):
db2?delete?from?tb1?where?id=1
15.創(chuàng)建索引:
db2?create?index?idx1?on?tb1(id);
16.創(chuàng)建視圖:
db2?create?view?view1?as?select?id?from?tb1
17.查詢(xún)視圖:
db2?select?*?from?view1
18.節(jié)點(diǎn)編目
db2?catalog?tcp?node?node_name?remote?server_ip?server?server_port
19.察看端口號(hào)
db2?get?dbm?cfg|grep?SVCENAME
20.測(cè)試節(jié)點(diǎn)的附接
db2?attach?to?node_name
21.察看本地節(jié)點(diǎn)
db2?list?node?direcotry
22.節(jié)點(diǎn)反編目
db2?uncatalog?node?node_name
23.數(shù)據(jù)庫(kù)編目
db2?catalog?db?db_name?as?db_alias?at?node?node_name
24.察看數(shù)據(jù)庫(kù)的編目
db2?list?db?directory
25.連接數(shù)據(jù)庫(kù)
db2?connect?to?db_alias?user?user_name?using?user_password
26.數(shù)據(jù)庫(kù)反編目
db2?uncatalog?db?db_alias
27.導(dǎo)出數(shù)據(jù)
db2?export?to?myfile?of?ixf?messages?msg?select?*?from?tb1
28.導(dǎo)入數(shù)據(jù)
db2?import?from?myfile?of?ixf?messages?msg?replace?into?tb1
29.導(dǎo)出數(shù)據(jù)庫(kù)的所有表數(shù)據(jù)
db2move?test?export
30.生成數(shù)據(jù)庫(kù)的定義
db2look?-d?db_alias?-a?-e?-m?-l?-x?-f?-o?db2look.sql
31.創(chuàng)建數(shù)據(jù)庫(kù)
db2?create?db?test1
32.生成定義
db2?-tvf?db2look.sql
33.導(dǎo)入數(shù)據(jù)庫(kù)所有的數(shù)據(jù)
db2move?db_alias?import
34.重組檢查
db2?reorgchk
35.重組表tb1
db2?reorg?table?tb1
36.更新統(tǒng)計(jì)信息
db2?runstats?on?table?tb1
37.備份數(shù)據(jù)庫(kù)test
db2?backup?db?test
38.恢復(fù)數(shù)據(jù)庫(kù)test
db2?restore?db?test
399\.列出容器的信息
db2?list?tablespace?containers?for?tbs_id?show?detail
40.創(chuàng)建表:
db2?ceate?table?tb1(id?integer?not?null,name?char(10))
41.列出所有表
db2?list?tables
42.插入數(shù)據(jù):
db2?insert?into?tb1?values(1,’sam’);
db2?insert?into?tb2?values(2,’smitty’);
.?建立數(shù)據(jù)庫(kù)DB2_GCB?
CREATE?DATABASE?DB2_GCB?ON?G:?ALIAS?DB2_GCB?
USING?CODESET?GBK?TERRITORY?CN?COLLATE?USING?SYSTEM?DFT_EXTENT_SZ?32?
2.?連接數(shù)據(jù)庫(kù)?
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.?建立觸發(fā)器?
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?類(lèi)型,LENGTH?as?長(zhǎng)度?
from?columns?
where?tabname='ZJT_TABLES';?
11.?查看表結(jié)構(gòu)?
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.?查看存貯過(guò)程?
SELECT?SUBSTR(PROCSCHEMA,1,15),SUBSTR(PROCNAME,1,15)?
FROM?SYSCAT.PROCEDURES;?
16.?類(lèi)型轉(zhuǎn)換(cast)?
ip?datatype:varchar?
select?cast(ip?as?integer)+50?from?log_comm_failed?
17.?重新連接?
connect?reset?
18.?中斷數(shù)據(jù)庫(kù)連接?
disconnect?db2_gcb?
19.?view?application?
LIST?APPLICATION;?
20.?kill?application?
FORCE?APPLICATION(0);?
db2?force?applications?all?(強(qiáng)迫所有應(yīng)用程序從數(shù)據(jù)庫(kù)斷開(kāi))?
21.?lock?table
lock?table?test?in?exclusive?mode?
22.?共享?
lock?table?test?in?share?mode?
23.?顯示當(dāng)前用戶(hù)所有表?
list?tables?
24.?列出所有的系統(tǒng)表?
list?tables?for?system?
25.?顯示當(dāng)前活動(dòng)數(shù)據(jù)庫(kù)?
list?active?databases?
26.?查看命令選項(xiàng)?
list?command?options?
27.?系統(tǒng)數(shù)據(jù)庫(kù)目錄?
LIST?DATABASE?DIRECTORY?
28.?表空間?
list?tablespaces?
29.?表空間容器?
LIST?TABLESPACE?CONTAINERS?FOR?
Example:?LIST?TABLESPACE?CONTAINERS?FOR?1?
30.?顯示用戶(hù)數(shù)據(jù)庫(kù)的存取權(quán)限?
GET?AUTHORIZATIONS?
31.?啟動(dòng)實(shí)例?
DB2START?
32.?停止實(shí)例?
db2stop?
33.?表或視圖特權(quán)?
grant?select,delete,insert,update?on?tables?to?user?
grant?all?on?tables?to?user?WITH?GRANT?OPTION?
34.?程序包特權(quán)?
GRANT?EXECUTE?
ON?PACKAGE?PACKAGE-name?
TO?PUBLIC?
35.?模式特權(quán)?
GRANT?CREATEIN?ON?SCHEMA?SCHEMA-name?TO?USER?
36.?數(shù)據(jù)庫(kù)特權(quán)?
grant?connect,createtab,dbadm?on?database?to?user?
37.?索引特權(quán)?
grant?control?on?index?index-name?to?user?
38.?信息幫助?(??XXXnnnnn?)?
例:??SQL30081?
39.?SQL?幫助(說(shuō)明?SQL?語(yǔ)句的語(yǔ)法)?
help?statement?
例如，help?SELECT?
40.?SQLSTATE?幫助(說(shuō)明?SQL?的狀態(tài)和類(lèi)別代碼)?
??sqlstate?或???class-code?
41.?更改與"管理服務(wù)器"相關(guān)的口令?
db2admin?setid?username?password?
42.?創(chuàng)建?SAMPLE?數(shù)據(jù)庫(kù)?
db2sampl?
db2sampl?F:(指定安裝盤(pán))?
43.?使用操作系統(tǒng)命令?
!?dir?
44.?轉(zhuǎn)換數(shù)據(jù)類(lèi)型?(cast)?
SELECT?EMPNO,?CAST(RESUME?AS?VARCHAR(370))?
FROM?EMP_RESUME?
WHERE?RESUME_FORMAT?=?'ascii'?
45.?UDF
要運(yùn)行?DB2?Java?存儲(chǔ)過(guò)程或?UDF，還需要更新服務(wù)器上的?DB2?數(shù)據(jù)庫(kù)管理程序配置，以包括在該機(jī)器上安裝?JDK?的路徑?
db2?update?dbm?cfg?using?JDK11_PATH?d:sqllibjavajdk?
TERMINATE?
update?dbm?cfg?using?SPM_NAME?sample?
46.?檢查?DB2?數(shù)據(jù)庫(kù)管理程序配置?
db2?get?dbm?cfg?
47.?檢索具有特權(quán)的所有授權(quán)名?
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.?修改表結(jié)構(gòu)?
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.?業(yè)務(wù)類(lèi)型說(shuō)明?
insert?into?ywlbb?values?
('user01','業(yè)務(wù)申請(qǐng)'),?
('user02','業(yè)務(wù)撤消'),?
('user03','費(fèi)用查詢(xún)'),?
('user04','費(fèi)用自繳'),?
('user05','費(fèi)用預(yù)存'),?
('user06','密碼修改'),?
('user07','發(fā)票打印'),?
('gl01','改用戶(hù)基本信息'),?
('gl02','更改支付信息'),?
('gl03','日統(tǒng)計(jì)功能'),?
('gl04','沖帳功能'),?
('gl05','對(duì)帳功能'),?
('gl06','計(jì)費(fèi)功能'),?
('gl07','綜合統(tǒng)計(jì)')?
備份數(shù)據(jù)庫(kù)：
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中的一些經(jīng)驗(yàn)，希望對(duì)大家有所幫助。?
?db2???connect???to??YOURDB???
連接數(shù)據(jù)庫(kù)?
?db2look?-d??YOURDB??-a?-e?-x?-o?creatab.sql?
導(dǎo)出建庫(kù)表的SQL?
?db2move???YOURDB??export?
用db2move將數(shù)據(jù)備份出來(lái)?
?vi???creatab.sql?
如要導(dǎo)入的數(shù)據(jù)庫(kù)名與原數(shù)據(jù)庫(kù)不同，要修改creatab.sql中CONNECT?項(xiàng)?
如相同則不用更改?
?db2move??NEWDB??load?
將數(shù)據(jù)導(dǎo)入新庫(kù)中?
在導(dǎo)入中可能因?yàn)榉N種原因發(fā)生中斷，會(huì)使數(shù)據(jù)庫(kù)暫掛?
db2????list?tablespaces???show???detail?
如：?
??????詳細(xì)說(shuō)明：?
?????裝入暫掛?
?總頁(yè)數(shù)??????????????????????????=?1652?
?可用頁(yè)數(shù)????????????????????????=?1652?
?已用頁(yè)數(shù)?????????????????????????=?1652?
?空閑頁(yè)數(shù)?????????????????????????=?不適用?
?高水位標(biāo)記（頁(yè)）?????????????????=?不適用?
?頁(yè)大?。ㄗ止?jié)）???????????????????=?4096?
?盤(pán)區(qū)大?。?yè)）???????????????????=?32?
?預(yù)讀取大小（頁(yè)）?????????????????=?32?
?容器數(shù)???????????????????????????=?1?
?狀態(tài)更改表空間標(biāo)識(shí)????????????????????=?2?
?狀態(tài)更改對(duì)象標(biāo)識(shí)??????????????????????=?59?
?db2?select?tabname,tableid?from?syscat.tables?where?tableid=59?
查看是哪張表掛起?
表名知道后到db2move.lst(在db2move??YOURDB??export的目錄中)中找到相應(yīng)的.ixf文件?
?db2?load?from?tab11.ixf?of?ixf?terminate?into?db2admin.xxxxxxxxx?
tab11.ixf對(duì)應(yīng)的是xxxxxxxxx表?
數(shù)據(jù)庫(kù)會(huì)恢復(fù)正常，可再用db2?list?tablespaces?show?detail查看
30.不能通過(guò)GRANT授權(quán)的權(quán)限有哪種?
SYSAM
SYSCTRL
SYSMAINT
要更該述權(quán)限必須修改數(shù)據(jù)庫(kù)管理器配置參數(shù)
31.表的類(lèi)型有哪些?
永久表(基表)
臨時(shí)表(說(shuō)明表)
臨時(shí)表(派生表)
32.如何知道一個(gè)用戶(hù)有多少表?
SELECT*FROMSYSIBM.SYSTABLESWHERECREATOR='USER'
33.如何知道用戶(hù)下的函數(shù)?
select*fromIWH.USERFUNCTION
select*fromsysibm.SYSFUNCTIONS
34.如何知道用戶(hù)下的VIEW數(shù)?
select*fromsysibm.sysviewsWHERECREATOR='USER'
35.如何知道當(dāng)前DB2的版本?
select*fromsysibm.sysvERSIONS
36.如何知道用戶(hù)下的TRIGGER數(shù)?
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的數(shù)據(jù)類(lèi)型?
select*fromsysibm.SYSDATATYPES
43.如何知道BUFFERPOOLS狀況?
select*fromsysibm.SYSBUFFERPOOLS
44.DB2表的字段的修改限制?
只能修改VARCHAR2類(lèi)型的并且只能增加不能減少.
45.如何查看表的結(jié)構(gòu)?
DESCRIBLETABLETABLE_NAME
OR
DESCRIBLESELECT*FROMSCHEMA.TABLE_NAME