项目快做完了,想来这里写点东西,一直在做项目管理,技术上也没啥新东西,常用的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;
无论是什么数据库,开发人员都要会导入导出,会备份,会写脚本。再往上就是调优。
调优这里就不说了,细节太多,每个问题调优的方式也不相同,不过这个调优确实有很多值得探讨的东西。