1、 打開命令行窗口
#db2cmd
2、 打開控制中心
# db2cmd db2cc
3、 打開命令編輯器
db2cmd db2ce
4、 啟動數(shù)據(jù)庫實例
#db2start
5、 停止數(shù)據(jù)庫實例
#db2stop
6、備份數(shù)據(jù)庫
#db2 backup db <db name>
7、恢復(fù)數(shù)據(jù)庫
#db2 restore db <source db name>
8、導(dǎo)出數(shù)據(jù)文件
#db2move <db name> export
9、導(dǎo)入數(shù)據(jù)文件
#db2move <db name> import
10、導(dǎo)出多個指定表
#db2move <db name> export -tn <tablename,tablename,....>? -u? 用戶名稱? -p 用戶密碼
11、恢復(fù)數(shù)據(jù)庫
12、導(dǎo)出數(shù)據(jù)表結(jié)構(gòu)
#db2look -d? cypx_hn -e? -a -x? -o ddlfile.sql
db2look -d? cypx_hn -e? -a -x? -o ddlfile.sql
13、恢復(fù)數(shù)據(jù)庫
#db2 restore db jdcjsy from D:\db2back\db2 taken at 20140619000010 on D: dbpath on D: into jdcjsy
#db2 rollforward db jdcddd complete
14.
db2ilist:列出DB2所有實例
db2 get instance:顯示當(dāng)前實例
db2 list db directory:列出當(dāng)前實例下的所有數(shù)據(jù)庫
db2 list active databases：列出當(dāng)前連接的數(shù)據(jù)庫
db2 list applications：列出所有對數(shù)據(jù)庫的連接。
db2 get snapshot for dbm:查看活動的本地數(shù)據(jù)庫（數(shù)據(jù)庫快照）
db2 -v reorgchk update statistics on table all:重建索引
db2 get db cfg for jdc_hy
db2 update db cfg for jdc_hy using LOGFILSIZ 10000
update db cfg for <dbname> using <p> <v>
db2數(shù)據(jù)庫調(diào)優(yōu)：
1.參數(shù)設(shè)置——性能
（1）堆棧 1024
（2）日志 1024
（3）超時時間 15~20秒（重要）
（4）緩沖池 50000（重要）
15、新建數(shù)據(jù)庫
db2 CREATE DATABASE jdcjsy AUTOMATIC STORAGE NO ON 'd:\jdcjsy' USING CODESET UTF-8 TERRITORY CN COLLATE USING SYSTEM PAGESIZE 4096 CATALOG TABLESPACE? MANAGED BY DATABASE USING ( FILE 'd:\jdcjsy\system\systemspace1' 64000 )? USER TABLESPACE? MANAGED BY DATABASE USING ( FILE 'd:\jdcjsy\user\userspace1' 64000 )? TEMPORARY TABLESPACE? MANAGED BY DATABASE USING ( FILE 'd:\jdcjsy\temp\tempspace1' 64000 )
16、遷移db2日志,以及修改活動日志大小
db2 update db cfg for <dbname> using LOGFILSIZ 10000
db2 update db cfg for <dbname> using? NEWLOGPATH <f:\jdcjsy-log >
db2 terminate?
db2stop
db2start
ALTER TABLE ADMINISTRATOR.T_MK ALTER COLUMN MKQX SET DATA TYPE VARCHAR ( 300 ) ;
4.為字段添加默認(rèn)值
alter table [table_name] alter column [column_name] set default [value];
2.更改字段類型
alter table? [table_name] alter column [column_name] set data type? [column_type]
3.去掉字段
alter table [table_name] drop column [column_name]
注意:drop掉字段之后,可能會導(dǎo)致表查詢/插入操作不能執(zhí)行,需要對表進(jìn)行reorg
添加帶默認(rèn)值的字段
alter table [table_name] add column [column_name] [column_type] not null with default [value]
6. 設(shè)置字段默認(rèn)時間為當(dāng)前時間
alter table [table_name] alter column [column_name] set default? current date;
7.根據(jù)sql導(dǎo)出excel
db2? export to d:\data.xlsx of del modified by nochardel coldel0x09 select * from t_xysqxxb where xm='文靜'
8、時間函數(shù)加減
update? t_pxjlzsb? set pxjssj =(timestamp(pxkssj) +4 hour) ,pxsc=240? where? pxsc>360 and? czsj >'2015-01-01'
添加約束
alter table t_xysqxxb522600 add CONSTRAINT constraint_xybh UNIQUE (xybh)
db2 export? to d:\data.xlsx of del modified by nochardel coldel0x09? SELECT a.UnitName,a.CarNo,a.Sim,a.CarNo,sum(case when d.MILEAGE is null then 0 else d.MILEAGE/1000 end) as Mileage,sum(case when d.XSSC is null then 0 else d.XSSC/60 end) as TeachTime FROM DB2INST1.V_GPSCARINFO a LEFT OUTER JOIN (SELECT * FROM DB2INST1.G_MONMILEAGE WHERE TIME between '2014-01-01' and '2014-12-31') d ON a.SIM=d.ZDID where 1=1? group by? a.UnitName,a.CarNo,a.Sim? order by UnitName
db2? export? to d:\data.xlsx of del modified by nochardel coldel0x09? SELECT a.UnitName,a.CarNo,a.Sim,sum(case when d.MILEAGE is null then 0 else d.MILEAGE/1000 end) as Mileage,sum(case when d.XSSC is null then 0 else d.XSSC/60 end) as TeachTime FROM DB2INST1.V_GPSCARINFO a LEFT OUTER JOIN (SELECT * FROM DB2INST1.G_MONMILEAGE WHERE TIME between '2014-01-01' and '2014-12-31') d ON a.SIM=d.ZDID where 1=1? group by? a.UnitName,a.CarNo,a.Sim? order by UnitName
6、用export導(dǎo)出指定的表數(shù)據(jù)
? ? db2 "export to d:\data\tab1.ixf of ixf lobs to d:\data\ lobfile lobs modified by lobsinsepfiles? messages d:\data\tab1.msg select * from schema_name.table_name"
db2 EXPORT? TO xyxx20181123.csv? OF? DEL? modified by codepage=1208? select? * From? administrator.t_xysqxxb where pxsj between '2011-07-01' and '2018-11-23'
db2 EXPORT? TO xyxx20190104.csv? OF? DEL? modified by codepage=1208? select? xm,zjh From? administrator.t_xysqxxb where pxsj between '2008-01-01' and '2018-12-31' union all? select? xm,zjh? From? administrator.t_xydaxxb where pxsj between '2008-01-01' and '2018-12-31'
Import from? D:\ xxxxtablenaeme.csv of del? ? OF? ? ? DEL? modified by codepage=1208? ? ? insert into tablename
