linux中如何使用db2命令

1、DB2數(shù)據(jù)庫命令簡介1啟動(dòng)數(shù)據(jù)庫db2start2停止數(shù)據(jù)庫db2stop3連接數(shù)據(jù)庫db2 connect to o_yd user db2 using pwd4讀數(shù)據(jù)庫管理程序配置db2 get dbm cfg5寫數(shù)據(jù)庫管理程序配置db2 update dbm cfg using 參數(shù)名 參數(shù)值6讀數(shù)據(jù)庫的配置db2 connect to o_yd user db2 using pwddb2 get db cfg for o_yd7寫數(shù)據(jù)庫的配置db2 connect to o_yd user db2 using pwddb2 update db cfg for o_yd using 參數(shù)

2、名 參數(shù)值8關(guān)閉所有應(yīng)用連接db2 force application alldb2 force application ID1,ID2,Idn MODE ASYNC(db2 list application for db o_yd show detail)9備份數(shù)據(jù)庫db2 force application alldb2 backup db o_yd to d:(db2 initialize tape on .tape0)(db2 rewind tape on .tape0)db2 backup db o_yd to .tape010恢復(fù)數(shù)據(jù)庫db2 restore db o_yd fro

3、m d: to d:db2 restore db o_yd from .tape0 to d:11綁定存儲(chǔ)過程db2 connect to o_yd user db2 using pwddb2 bind c:dfplus.bnd拷貝存儲(chǔ)過程到服務(wù)器上的C:sqllibfunction目錄中12整理表db2 connect to o_yd user db2 using pwddb2 reorg table ydddb2 runstats on table ydd with distribution and indexes all13導(dǎo)出表數(shù)據(jù)db2 export to c:dftz.txt of

4、 del select * from dftzdb2 export to c:dftz.ixf of ixf select * from dftz14導(dǎo)入表數(shù)據(jù)import from c:123.txt of del insert into ylbx.czyxxdb2 import to c:dftz.txt of del commitcount 5000 messages c:dftz.msg insert into dftzdb2 import to c:dftz.ixf of ixf commitcount 5000 messages c:dftz.msg insert into dft

5、zdb2 import to c:dftz.ixf of ixf commitcount 5000 insert into dftzdb2 import to c:dftz.ixf of ixf commitcount 5000 insert_update into dftzdb2 import to c:dftz.ixf of ixf commitcount 5000 replace into dftzdb2 import to c:dftz.ixf of ixf commitcount 5000 create into dftz (僅IXF)db2 import to c:dftz.ixf

6、 of ixf commitcount 5000 replace_create into dftz (僅IXF)15執(zhí)行一個(gè)批處理文件db2 -tf 批處理文件名（文件中每一條命令用 ；結(jié)束）16自動(dòng)生成批處理文件建文本文件：temp.sqlselect 'runstats on table DB2.' | tabname | 'with distribution and detailed indexes all;'from syscat.tables where tabschema='DB2' and type='T'db2 -

7、tf temp.sql>runstats.sql17自動(dòng)生成建表（視圖）語句在服務(wù)器上：C:sqllibmisc目錄中db2 connect to o_yd user db2 using pwddb2look -d o_yd -u db2 -e -p -c c:o_yd.txt18其他命令grant dbadm on database to user bb19select * from czyxx fetch first 1 rows only20db2look -d ylbx -u db2admin -w -asd -a -e -o a.txt21. 顯示當(dāng)前用戶所有表list tab

8、les22.列出所有的系統(tǒng)表list tables for system23.查看表結(jié)構(gòu)db2 describe select * from user.tables一、基礎(chǔ)篇1、db2 connect to <數(shù)據(jù)庫名>    -連接到本地?cái)?shù)據(jù)庫名       db2 connect to <數(shù)據(jù)庫名> user <用戶名> using <密碼> -連接到遠(yuǎn)端數(shù)據(jù)庫2、 db2 force application all

9、0;     -強(qiáng)迫所有應(yīng)用斷開數(shù)據(jù)庫連接   3、db2 backup db db2name<數(shù)據(jù)庫名稱>  -備份整個(gè)數(shù)據(jù)庫數(shù)據(jù)       db2 restore db <db2name>                    

10、-還原數(shù)據(jù)庫4、db2 list application -查看所有連接（需要連接到具體數(shù)據(jù)庫才能查看）5、db2start    -啟動(dòng)數(shù)據(jù)庫          db2stop -停止數(shù)據(jù)庫6、create database <數(shù)據(jù)庫名> using codeset utf-8 territory CN    -創(chuàng)建數(shù)據(jù)庫使用utf-8編碼7、db2 catalog 命令db2 catalog tcpip node

11、0;<接點(diǎn)名稱> remote <遠(yuǎn)程數(shù)據(jù)庫地址> server <端口號(hào)>  -把遠(yuǎn)程數(shù)據(jù)庫映射到本地接點(diǎn)一般為50000db2 catalog db <遠(yuǎn)程數(shù)據(jù)庫名稱> as <接點(diǎn)名稱>  at node PUB11                       -遠(yuǎn)程數(shù)據(jù)庫名稱到本地

12、接點(diǎn)db2 CONNECT TO <接點(diǎn)名稱> user <用戶名> using <密碼>                               -連接本地接點(diǎn)訪問遠(yuǎn)程數(shù)據(jù)庫8、數(shù)據(jù)庫導(dǎo)出db2look -d <數(shù)據(jù)庫名> -u

13、0;<用戶> -e -o <腳本名稱>.sql        -導(dǎo)出數(shù)據(jù)庫的表結(jié)構(gòu)，其中用戶空間一般為db2admin/db2inst1db2look -d <數(shù)據(jù)庫名> -u <用戶> -t <表1> <表2>  -e -o <腳本名稱>.sql   -導(dǎo)出數(shù)據(jù)庫中表1和表2的表結(jié)構(gòu)db2move <數(shù)據(jù)庫名> export    

14、0;         -導(dǎo)出數(shù)據(jù)庫數(shù)據(jù)db2move <數(shù)據(jù)庫名> export -tn <表1>,<表2>       -導(dǎo)出數(shù)據(jù)庫中表和表數(shù)據(jù)9、數(shù)據(jù)庫導(dǎo)入db2 -tvf  <腳本名稱>.sql       -把上述導(dǎo)出的表結(jié)構(gòu)導(dǎo)入到數(shù)據(jù)庫表結(jié)構(gòu)db2move <數(shù)據(jù)庫名> load -lo replace

15、60;  -把上述“db2move <數(shù)據(jù)庫名> export “導(dǎo)出的數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫中并把相同的數(shù)據(jù)替換掉 在實(shí)際使用過程中，如果用到db2自增主鍵，需要使用by default， 而不是always，功能是一樣的，但這樣在數(shù)據(jù)移植時(shí)候會(huì)很方便!10、db2 connect reset 或 db2 terminate    -斷開與數(shù)據(jù)庫的連接11、db2set db2codepage=1208        -修改頁編碼為120812、d

16、b2 describe table <表名>       -查看表結(jié)構(gòu)13、db2 list tables                            -查看數(shù)據(jù)庫中所有表結(jié)構(gòu)     &

17、#160;  list tables for system                  -列出所有系統(tǒng)表14、db2 list tablespaces                  -列出表空間二、高級(jí)篇15、fetch first 10

18、rows  only   -列出表中前10條數(shù)據(jù)例如：select * from <表名> fetch first 10 rows only16、coalesce(字段名，轉(zhuǎn)換后的值)       -對是null的字段進(jìn)行值轉(zhuǎn)換例如：select coalesce(id,1)  from <表名>    -對表中id如果為null轉(zhuǎn)換成117、dayofweek(日期)   

19、0; -計(jì)算出日期中是周幾（1是周日，2是周一.7是周六）       dayofweek_iso    -計(jì)算出日期中是周幾(1是周一.7是周日)       例如：dayofweek(date(2008-01-16)   -它會(huì)返回是4，代表星期三              

20、          dayofweek_iso(date(2008-01-16)   -它會(huì)返回是3，代表星期三18、dayofyear(日期)   -一年中的第幾天，范圍在1-366范圍之內(nèi)注意：參數(shù)中日期的格式是YYYY-MM-DD的形式，如果不是需要進(jìn)行轉(zhuǎn)換，否則函數(shù)不能使用例如：日期是20080116必須要進(jìn)行轉(zhuǎn)換dayofweek(concat(concat(concat(substr(openDate,1,4),'-'),concat(s

21、ubstr(openDate,5,2),'-'),substr(openDate,7,2) as week)這樣格式正確的。19、concatt(參數(shù)1，連接值)       -把參數(shù)加上連接值組成一個(gè)新值。例如：concat('aa','b')    -返回是aab連接數(shù)據(jù)庫： connect to 數(shù)據(jù)庫名 user 操作用戶名 using 密碼 創(chuàng)建緩沖池(8K)： create bufferpool ibmdefault8k IMMEDIATE SI

22、ZE 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 MA

23、NAGED 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/exo

24、a2/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' ) EXTENT

25、SIZE 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

26、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 P

27、AGESIZE 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 SY

28、STEM 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):db2start2. 停止實(shí)例(db2inst1):db2stop3. 列出所有實(shí)例(db2inst1)db2ilist5.列出當(dāng)前實(shí)例:db2 get instance4. 察看示例配置文件:db2 get dbm cfg|more5

29、. 更新數(shù)據(jù)庫管理器參數(shù)信息:db2 update dbm cfg using para_name para_value6. 創(chuàng)建數(shù)據(jù)庫:db2 create db test7. 察看數(shù)據(jù)庫配置參數(shù)信息db2 get db cfg for test|more8. 更新數(shù)據(jù)庫參數(shù)配置信息db2 update db cfg for test using para_name para_value10.刪除數(shù)據(jù)庫:db2 drop db test11.連接數(shù)據(jù)庫db2 connect to test12.列出所有表空間的詳細(xì)信息。db2 list tablespaces show detail13.查

30、詢數(shù)據(jù):db2 select * from tb114.刪除數(shù)據(jù):db2 delete from tb1 where id=115.創(chuàng)建索引:db2 create index idx1 on tb1(id);16.創(chuàng)建視圖:db2 create view view1 as select id from tb117.查詢視圖:db2 select * from view118.節(jié)點(diǎn)編目db2 catalog tcp node node_name remote server_ip server server_port19.察看端口號(hào)db2 get dbm cfg|grep SVCENAME20.測

31、試節(jié)點(diǎn)的附接db2 attach to node_name21.察看本地節(jié)點(diǎn)db2 list node direcotry22.節(jié)點(diǎn)反編目db2 uncatalog node node_name23.數(shù)據(jù)庫編目db2 catalog db db_name as db_alias at node node_name24.察看數(shù)據(jù)庫的編目db2 list db directory25.連接數(shù)據(jù)庫db2 connect to db_alias user user_name using user_password26.數(shù)據(jù)庫反編目db2 uncatalog db db_alias27.導(dǎo)出數(shù)據(jù)db2

32、export to myfile of ixf messages msg select * from tb128.導(dǎo)入數(shù)據(jù)db2 import from myfile of ixf messages msg replace into tb129.導(dǎo)出數(shù)據(jù)庫的所有表數(shù)據(jù)db2move test export30.生成數(shù)據(jù)庫的定義db2look -d db_alias -a -e -m -l -x -f -o db2look.sql31.創(chuàng)建數(shù)據(jù)庫db2 create db test132.生成定義db2 -tvf db2look.sql33.導(dǎo)入數(shù)據(jù)庫所有的數(shù)據(jù)db2move db_alias

33、import34.重組檢查db2 reorgchk35.重組表tb1db2 reorg table tb136.更新統(tǒng)計(jì)信息db2 runstats on table tb137.備份數(shù)據(jù)庫testdb2 backup db test38.恢復(fù)數(shù)據(jù)庫testdb2 restore db test399.列出容器的信息db2 list tablespace containers for tbs_id show detail40.創(chuàng)建表:db2 ceate table tb1(id integer not null,name char(10)41.列出所有表db2 list tables42.插入

34、數(shù)據(jù):db2 insert into tb1 values(1,sam);db2 insert into tb2 values(2,smitty);. 建立數(shù)據(jù)庫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ù)庫 connect to sample1 user db2admin using 8301206 3. 建立別名 create alias db2admin.tables for s

35、ysstat.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. 插

36、入記錄 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_ta

37、bles1 values(substr(o.tabschema,1,8),substr(o.tabname,1,10) 8. 建立唯一性索引 CREATE UNIQUE INDEX I_ztables_tabname size=3ON 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 tab

38、name='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='

39、I_ZTABLES_TABNAME' 15. 查看存貯過程 SELECT SUBSTR(PROCSCHEMA,1,15),SUBSTR(PROCNAME,1,15) FROM SYSCAT.PROCEDURES; 16. 類型轉(zhuǎn)換(cast) ip datatype:varchar select cast(ip as integer)+50 from log_comm_failed 17. 重新連接 connect reset 18. 中斷數(shù)據(jù)庫連接 disconnect db2_gcb 19. view application LIST APPLICATION; 20. kill a

40、pplication FORCE APPLICATION(0); db2 force applications all (強(qiáng)迫所有應(yīng)用程序從數(shù)據(jù)庫斷開) 21. lock tablelock table test in exclusive mode 22. 共享 lock table test in share mode 23. 顯示當(dāng)前用戶所有表 list tables 24. 列出所有的系統(tǒng)表 list tables for system 25. 顯示當(dāng)前活動(dòng)數(shù)據(jù)庫 list active databases 26. 查看命令選項(xiàng) list command options 27. 系統(tǒng)數(shù)據(jù)

41、庫目錄 LIST DATABASE DIRECTORY 28. 表空間 list tablespaces 29. 表空間容器 LIST TABLESPACE CONTAINERS FOR Example: LIST TABLESPACE CONTAINERS FOR 1 30. 顯示用戶數(shù)據(jù)庫的存取權(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 WI

42、TH 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ù)庫特權(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 幫助(說明 SQL 語句的語法)

43、help statement 例如，help SELECT 40. SQLSTATE 幫助(說明 SQL 的狀態(tài)和類別代碼) ? sqlstate 或 ? class-code 41. 更改與"管理服務(wù)器"相關(guān)的口令 db2admin setid username password 42. 創(chuàng)建 SAMPLE 數(shù)據(jù)庫 db2sampl db2sampl F:(指定安裝盤) 43. 使用操作系統(tǒng)命令 ! dir 44. 轉(zhuǎn)換數(shù)據(jù)類型 (cast) SELECT EMPNO, CAST(RESUME AS VARCHAR(370) FROM EMP_RESUME WHERE R

44、ESUME_FORMAT = 'ascii' 45. UDF要運(yùn)行 DB2 Java 存儲(chǔ)過程或 UDF，還需要更新服務(wù)器上的 DB2 數(shù)據(jù)庫管理程序配置，以包括在該機(jī)器上安裝 JDK 的路徑 db2 update dbm cfg using JDK11_PATH d:sqllibjavajdk TERMINATE update dbm cfg using SPM_NAME sample 46. 檢查 DB2 數(shù)據(jù)庫管理程序配置 db2 get dbm cfg 47. 檢索具有特權(quán)的所有授權(quán)名 SELECT DISTINCT GRANTEE, GRANTEETYPE, '

45、;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 DI

46、STINCT 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 varch

47、ar(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

48、into yhdab values ('20000300001','123456','user01','20000300001'), ('20000300002','123456','user02','20000300002'); 49. 業(yè)務(wù)類型說明 insert into ywlbb values ('user01','業(yè)務(wù)申請'), ('user02','業(yè)務(wù)撤消'), ('user03&#

49、39;,'費(fèi)用查詢'), ('user04','費(fèi)用自繳'), ('user05','費(fèi)用預(yù)存'), ('user06','密碼修改'), ('user07','發(fā)票打印'), ('gl01','改用戶基本信息'), ('gl02','更改支付信息'), ('gl03','日統(tǒng)計(jì)功能'), ('gl04','沖帳功能'), ('gl05','對帳功能'), ('gl06','計(jì)費(fèi)功能'), ('gl07','綜合統(tǒng)計(jì)') 備份數(shù)據(jù)庫：CONNECT TO EXOA;QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS;CONNECT RESET;BACKUP DATABASE EXOA TO "/home/exoa2/db2ba