db2数据库常用sql脚本

最新推荐文章于 2021-06-23 10:02:31 发布

索隆

最新推荐文章于 2021-06-23 10:02:31 发布

阅读量4.2k

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库

本文链接：https://blog.youkuaiyun.com/lushuaiyin/article/details/53407430

数据库专栏收录该内容

74 篇文章

订阅专栏

这篇博客分享了在项目管理中常用的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;

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

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