1、 打开命令行窗口
#db2cmd
2、 打开控制中心
# db2cmd db2cc
3、 打开命令编辑器
db2cmd db2ce
4、 启动数据库实例
#db2start
5、 停止数据库实例
#db2stop
6、备份数据库
#db2 backup db <db name>
7、恢复数据库
#db2 restore db <source db name>
8、导出数据文件
#db2move <db name> export
9、导入数据文件
#db2move <db name> import
10、导出多个指定表
#db2move <db name> export -tn <tablename,tablename,....> -u 用户名称 -p 用户密码
11、恢复数据库
12、导出数据表结构
#db2look -d cypx_hn -e -a -x -o ddlfile.sql
db2look -d cypx_hn -e -a -x -o ddlfile.sql
13、恢复数据库
#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:显示当前实例
db2 list db directory:列出当前实例下的所有数据库
db2 list active databases：列出当前连接的数据库
db2 list applications：列出所有对数据库的连接。
db2 get snapshot for dbm:查看活动的本地数据库（数据库快照）
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数据库调优：
1.参数设置——性能
（1）堆栈 1024
（2）日志 1024
（3）超时时间 15~20秒（重要）
（4）缓冲池 50000（重要）
15、新建数据库
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.为字段添加默认值
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掉字段之后,可能会导致表查询/插入操作不能执行,需要对表进行reorg
添加带默认值的字段
alter table [table_name] add column [column_name] [column_type] not null with default [value]
6. 设置字段默认时间为当前时间
alter table [table_name] alter column [column_name] set default current date;
7.根据sql导出excel
db2 export to d:\data.xlsx of del modified by nochardel coldel0x09 select * from t_xysqxxb where xm='文静'
8、时间函数加减
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导出指定的表数据
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
