db2数据库常用sql脚本

灰太狼 2022-07-13 21:12 329阅读 0赞

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

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

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