db2数据库常用sql脚本

项目快做完了,想来这里写点东西,一直在做项目管理,技术上也没啥新东西,常用的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;

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

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






展开阅读全文

没有更多推荐了,返回首页