- 每5000条删除一次,提交一次事务
delete from (select * from tablename where condition fetch first 5000 rows noly)
- 显示数据库所有表
db2 list talbes
- 显示某张表的索引
db2 describe indexes for table tbname show detail
- 显示某张表的字段
db2 describe table tbname
- 重组优化表
db2 reorg table tablename
- 远程连接数据库,新建编目节点和编目数据库
db2 uncatalog node DBSRV //删除编目节点 (db2 list node directory查看编目节点)
db2 catalog tcpip node DBSRV remote 192.168.1.1 server 60000 //创建编目节点 DBSRV
db2 uncatalog database dbname //删除数据库编目 (db2 list db directory查看编目数据库)
db2 catalog db dbname at node DBSRV //添加数据库编目
db2 catalog db dbname as bieming at node DBSRV //添加数据库编目加别名
db2 terminate
- 一次更新5000条数据
update (select * from tbname fetch first 5000 rows only) set name='nihao'
- 查看数据库配置
db2 get db cfg for dbname
- 修改数据库日志文件大小
update db cfg for <dbname> using LOGFILSIZ 4096
- 激活数据库
db2 activate db dbname
- 强制关闭所有数据库连接
db2 force application all
- 关闭数据库
db2stop
- 启动数据库
db2start
- 运行sql文件
db2 -tvf xxx.sql
- 清空表数据
truncate table tbname immediate
alter table tabname activate not logged initially with empty table //此种方法出错时会导致表不能用,只能删表重建
- 创建索引
create index table_idx10 on tbname(id,name);
- 删除索引
drop index table_idx10
- 连接数据库
db2 connect to dbname user username using passwd
- 变更某一列的长度,重组表
alter table tbname alter column ID set data type varchar(128)
reorg table tbname
- 查看实例所有进程
db2_ps 或 ps -ef | grep -i instname
- 查看对某个表空间对应裸设备名称
db2 connect to dbtest;
db2 list tablespaces:
Tablespace ID = 4
Name = DAT8_DAT
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
db2 list tablespace containers for 4:
Tablespace Containers for Tablespace 4
Container ID = 4
Name = /dev/rdbdat8_lv
Type = Disk
- runstats命令
runsats可以搜集表的信息,也可以搜集索引信息。作为runstats本身没有优化的功能,但是它更新了统计信息以后,可以让 db2优化器使用最新的统计信息来进行优化,这样优化的效果更好。
db2 runstats on table schema.tabname with DISTRIBUTION and detailed indexes all
- db2move导出导入数据
db2move dbname export -u user -p passwd #导出
db2move dbname import -u user -p passwd #导入
- db2look导出表结构
db2look -d dbname -e -o dbname.sql