db2数据库常用sql脚本

这篇博客分享了在项目管理中常用的DB2数据库脚本,包括导出表结构和数据的SQL命令,以及导入数据的脚本,并提到了执行权限的设置,适合日后查询和使用。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

项目快做完了,想来这里写点东西,一直在做项目管理,技术上也没啥新东西,常用的db2数据库脚本分享下吧,以后用得着也可以来这里找找。


1,导出某些表的表结构语句和数据。


导出表结构:export_table_ddl0601.sql

db2look -d user -t TB_SERIAL TB_AGENT_KTREE TB_AGENT_KISSUE TB_AGENT_KHITS TB_AGENT_KDIFFNODE TB_AGENT_KDATA TB_AGENT_KCONFIG TB_AGENT_KATTACHFILE -e -o `pwd`/`date -u +"%Y%m%d"`_table_ddl.sql;

导出数据:export_data0601.sql

db2 connect to user;
db2 "export to `pwd`/`date -u +"%Y%m%d"`_TB_SERIAL.ixf of ixf select * from TB_SERIAL ";db2 "export to `pwd`/`date -u +"%Y%m%d"`_TB_AGENT_KTREE.ixf of ixf  select * from TB_AGENT_KTREE ";db2 "export to `pwd`/`date -u +"%Y%m%d"`_TB_AGENT_KISSUE.ixf of ixf  select * from TB_AGENT_KISSUE ";db2 "export to `pwd`/`date -u +"%Y%m%d"`_TB_AGENT_KHITS.ixf of ixf  select * from TB_AGENT_KHITS ";db2 "export to `pwd`/`date -u +"%Y%m%d"`_TB_AGENT_KDIFFNODE.ixf of ixf  select * from TB_AGENT_KDIFFNODE ";db2 "export to `pwd`/`date -u +"%Y%m%d"`_TB_AGENT_KDATA.ixf of ixf  select * from TB_AGENT_KDATA ";db2 "export to `pwd`/`date -u +"%Y%m%d"`_TB_AGENT_KCONFIG.ixf of ixf  select * from TB_AGENT_KCONFIG ";db2 "export to `pwd`/`date -u +"%Y%m%d"`_TB_AGENT_KATTACHFILE.ixf of ixf  select * from TB_AGENT_KATTACHFILE ";
db2 connect reset;

导出表结构和数据可以放在一个脚本中bak.sql

db2look -d user -t TB_SERIAL TB_AGENT_KTREE TB_AGENT_KISSUE TB_AGENT_KHITS TB_AGENT_KDIFFNODE TB_AGENT_KDATA TB_AGENT_KCONFIG TB_AGENT_KATTACHFILE -nofed -e -o `pwd`/20160618_table_ddl.sql;

db2 connect to user;
db2 "export to `pwd`/20160618_TB_SERIAL.ixf of ixf select * from TB_SERIAL ";
db2 "export to `pwd`/20160618_TB_AGENT_KTREE.ixf of ixf  select * from TB_AGENT_KTREE ";
db2 "export to `pwd`/20160618_TB_AGENT_KISSUE.ixf of ixf  select * from TB_AGENT_KISSUE ";
db2 "export to `pwd`/20160618_TB_AGENT_KHITS.ixf of ixf  select * from TB_AGENT_KHITS ";
db2 "export to `pwd`/20160618_TB_AGENT_KDIFFNODE.ixf of ixf  select * from TB_AGENT_KDIFFNODE ";
db2 "export to `pwd`/20160618_TB_AGENT_KDATA.ixf of ixf  select * from TB_AGENT_KDATA ";
db2 "export to `pwd`/20160618_TB_AGENT_KCONFIG.ixf of ixf  select * from TB_AGENT_KCONFIG ";
db2 "export to `pwd`/20160618_TB_AGENT_KATTACHFILE.ixf of ixf  select * from TB_AGENT_KATTACHFILE ";
db2 connect reset;

和这个脚本对应的是回退的脚本,也就是重新创建表并导入数据:rollback.sql

db2 connect to user;
db2 "DROP TABLE TB_SERIAL";
db2 "DROP TABLE TB_AGENT_KTREE";
db2 "DROP TABLE TB_AGENT_KDATA";
db2 "DROP TABLE TB_AGENT_KATTACHFILE";
db2 "DROP TABLE TB_AGENT_KHITS";
db2 "DROP TABLE TB_AGENT_KISSUE";
db2 "DROP TABLE TB_AGENT_KCONFIG";
db2 "DROP TABLE TB_AGENT_KDIFFNODE";

db2 -tvf 20160618_table_ddl.sql
db2 connect reset;


db2 connect to user;
db2 "import from `pwd`/20160618_TB_SERIAL.ixf of ixf  insert into TB_SERIAL ";
db2 "import from `pwd`/20160618_TB_AGENT_KTREE.ixf of ixf  insert into TB_AGENT_KTREE ";
db2 "import from `pwd`/20160618_TB_AGENT_KDATA.ixf of ixf  insert into TB_AGENT_KDATA ";
db2 "import from `pwd`/20160618_TB_AGENT_KATTACHFILE.ixf of ixf  insert into TB_AGENT_KATTACHFILE ";
db2 "import from `pwd`/20160618_TB_AGENT_KHITS.ixf of ixf  insert into TB_AGENT_KHITS ";
db2 "import from `pwd`/20160618_TB_AGENT_KISSUE.ixf of ixf  insert into TB_AGENT_KISSUE ";
db2 "import from `pwd`/20160618_TB_AGENT_KCONFIG.ixf of ixf  insert into TB_AGENT_KCONFIG ";
db2 "import from `pwd`/20160618_TB_AGENT_KDIFFNODE.ixf of ixf  insert into TB_AGENT_KDIFFNODE ";
db2 connect reset;

其中

db2 -tvf 20160618_table_ddl.sql
就是重新创建表,
20160618_table_ddl.sql
是备份的表结构。

===============================================================

导出用户的所有对象

db2look -d user -e -o `pwd`/table_ddl_20160413.sql;


2,导入数据 import0601.sql

db2 connect to user;
db2 "import from `pwd`/`date -u +"%Y%m%d"`_TB_AGENT_KATTACHFILE.ixf of ixf  insert into TB_AGENT_KATTACHFILE ";
db2 connect reset;


执行前都要,修改脚本的执行权限。

chmod 775 xxx.sql


======补充,一般的dml,ddl的sql脚本====

db2 connect to user;

db2 "ALTER TABLE TB_AGENT_KTREE ADD COLUMN ABATEDATE	VARCHAR(19)";
db2 "ALTER TABLE TB_AGENT_KTREE ADD COLUMN FIRSTAUDIAGENTID	VARCHAR(400)";
db2 "ALTER TABLE TB_AGENT_KTREE ADD COLUMN ISPUBLISH	VARCHAR(100)";
db2 "ALTER TABLE TB_AGENT_KTREE ALTER COLUMN PUBLISHDATE SET DATA TYPE CHARACTER(19)";
db2 "ALTER TABLE TB_AGENT_KTREE ALTER COLUMN SHORTCUT SET DATA TYPE CHARACTER(50)";
db2 "REORG TABLE TB_AGENT_KTREE";

db2 "ALTER TABLE TB_AGENT_KDATA ALTER COLUMN CONTENT SET DATA TYPE VARCHAR(4000)";
db2 "REORG TABLE TB_AGENT_KDATA";

db2 connect reset;

别忘了reorg表。

db2 connect to user;

db2 "insert into TB_SERIAL(TBNAME,NEXTID,OTHER,LEN) values ('TB_AGENT_KBREMARK',10,'20160414',10)";
db2 "update TB_SERIAL b set b.NEXTID=(b.NEXTID+1) where b.TBNAME='TB_AGENT_KTREE'";

db2 connect reset;

无论是什么数据库,开发人员都要会导入导出,会备份,会写脚本。再往上就是调优。

调优这里就不说了,细节太多,每个问题调优的方式也不相同,不过这个调优确实有很多值得探讨的东西。






评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值