启动/停止管理服务器
db2admin start/stop
启动/停止数据库实例
db2start/db2stop
连接数据库
db2 connect to dbname user username using password
断开连接
db2 terminate
db2 disconnec current
清空大量数据表(使用delete语句经常出现日志空间满的错误)
新建一个空的文件empty.del,运行下面的命令
db2 load from empty.del of del messages msg.out replace into TABLE_NAME
db2 alter table TABLE_NAME activate not logged initially with empty table;
查询用户数据表SQL
select "NAME", "CREATOR", "TYPE", "REMARKS" from "SYSIBM"."SYSTABLES" where creator='XX' and type='T'
查询用户数据表列SQL
select TBNAME,NAME from "SYSIBM"."SYSCOLUMNS" WHERE TBCREATOR='XX' ORDER BY TBNAME,NAME
查看表结构
db2 describe table tabalename [show detail]
导出数据库表DDL命令
db2look -d dbname -e -z schemaname -i username -w password -o ddlfile.sql
导出数据命令
export to data.ixf of ixf messages d:\export.log select * from tablename;
执行SQL文件
db2 -tvf d:\export.sql -l d:\log.txt
查看连接数据库的应用程序
db2 list applications
强制断开与数据库的连接
db2 force application all
移动数据库
导出
db2move db-name export -tc table-creators -sn schema-names -tn table-names [-l lobfile-path] -u userid -p passwd
导入
db2move db-name import -io import-option [-l lobfile-path] -u userid -p passwd
import-option: INSERT, INSERT_UPDATE, REPLACE, CREATE , REPLACE_CREATE
default value: REPLACE_CREATE
或
db2move db-name load -lo load-option [-l lobfile-path] -u userid -p passwd
load-option: INSERT, REPLACE
default value: INSERT
注意:replace 选项表示先清空数据,再插入
快速复制表
--复制表结构
create table table_name_new as (select * from table_name_old) definition only;
--插入数据
insert into table_name_new (select * from table_name_old);
计算表达式的值
values locate('N','ABNCD')
特殊字符转义
示例:account like '%@_hgb'escape '@' 。转义'_',查询所有account字段含"_hgb"的记录