database

« | »

IBM DB2 的数据复制、迁移方法

databaseadmin | 17 七月, 2006 03:43

以下方法经测试，在环境IBM x346,3.2G×2,4G,RAID 1,DB2 V8.2.4,Win2000 Adv Server，DMS表空间中，数据的load速度在60-100万条/min左右。

背景：需要更改数据库表空间，或者需要将数据库中所有表的数据迁移到一个新的数据库中。

步骤：

1.通过db2控制台(db2cc)选中源数据库中的所有表，将其导出成DDL脚本；

2.根据需要对脚本进行必要的修改，譬如更改表空间为GATHER；

3.新建数据库，新建DMS表空间：GATHER；

4.将DDL脚本在此数据库中执行；

5.编写代码查询源数据库中的所有表，自动生成export脚本；

6.编写代码查询源数据库中的所有表，自动生成import脚本；

7.连接源数据库执行export脚本；

8.连接目标数据库执行import脚本；

附录1:生成export脚本代码示例： 

/**

* 创建导出脚本

* @param conn

* @param creator　表创建者

* @param filePath

*/

public void createExportFile(Connection conn,String creator,String filePath) throws Exception {

DBBase dbBase = new DBBase(conn);

String selectTableSql = "select name from sysibm.systables where creator = '" + creator + "' and type='T'";

try {

dbBase.executeQuery(selectTableSql);

} catch (Exception ex) {

throw ex;

} finally {

dbBase.close();

}

DBResult result = dbBase.getSelectDBResult();

List list = new ArrayList();

while (result.next()) {

String table = result.getString(1);

list.add(table);

}

StringBuffer sb = new StringBuffer();

String enterFlag = "rn";

for (int i = 0; i < list.size();i++) {

String tableName = (String)list.get(i);

sb.append("db2 "export to aa" + String.valueOf(i+1)+ ".ixf of ixf select * from " + tableName + """);

sb.append(enterFlag);

}

String str = sb.toString();

FileUtility.saveStringToFile(filePath, str, false);

}


附录2:生成import脚本代码示例:

/**

* 创建装载脚本

* @param conn

* @param creator　表创建者

* @param filePath

*/

public void createLoadFile(Connection conn,String creator,String filePath) throws Exception {

DBBase dbBase = new DBBase(conn);

String selectTableSql = "select name from sysibm.systables where creator = '" + creator + "' and type='T'";

try {

dbBase.executeQuery(selectTableSql);

} catch (Exception ex) {

throw ex;

} finally {

dbBase.close();

}

DBResult result = dbBase.getSelectDBResult();

List list = new ArrayList();

while (result.next()) {

String table = result.getString(1);

list.add(table);

}

StringBuffer sb = new StringBuffer();

String enterFlag = "rn";

for (int i = 0; i < list.size();i++) {

String tableName = (String)list.get(i);

sb.append("db2 "load from aa" + String.valueOf(i+1)+ ".ixf of ixf into " + tableName + " COPY NO without prompting "");

sb.append(enterFlag);

}

String str = sb.toString();

FileUtility.saveStringToFile(filePath, str, false);

}


附录3:export脚本示例

db2 connect to testdb user test password test

db2 "export to aa1.ixf of ixf select * from table1"

db2 "export to aa2.ixf of ixf select * from table2"

db2 connect reset


附录4:import脚本示例

db2 connect to testdb user test password test

db2 "load from aa1.ixf of ixf replace into table1 COPY NO without prompting "

db2 "load from aa2.ixf of ixf replace into table2 COPY NO without prompting "

db2 connect reset

一般分类 :: 评论 (15) :: 静态链接网址 :: 引用 (0)

最新回复

Comment Icon None

Wyle | 18/04/2007, 21:58

I like this!

Comment Icon [url=][/url]

cfxvey | 03/01/2008, 01:42

[url=][/url]

Comment Icon I have 1 question...plz help

ruspetriot | 14/02/2008, 20:34

Hello! guys please tell me where can I download free pron

Comment Icon 玉蜞眍怅

sputnnyk | 12/10/2008, 07:36

腻忸

Comment Icon 念狃铄 溴睃!

Igroktectonick | 21/11/2008, 19:34

橡桠弪 怦屐!, 

Comment Icon escalade sport parts

PresGlorseboM | 16/12/2008, 11:29

It's just about sport

inside sport
1994 polaris indy sport picture
dance is a sport
sport shooting coach florida myers
vallely sport and marine

Comment Icon Drivers for Dialta CF2002 need

johnronni | 25/02/2009, 19:37

HI all,
where i download drivers for Dialta CF2002?
this is wery important for me, please hlp
If I wrote is not in the correct section, please move to the appropriate section.
sorry my bad english, i use translator

Comment Icon Hi! New member here :)

Attakylab | 16/08/2009, 12:12

Hi folks!

I just want to say hello to you all,
and see you in next posts!

cheers
Attakylab

Comment Icon help with adding images

greyhounds | 17/10/2009, 21:17

Hi,

I tried to add image but I don't know how to do this
Can anyone be kind to tell me how?

thanks a lot

Comment Icon bonus gratuits casino

Neikefethefly | 09/12/2009, 02:06

[url=][/url]

Comment Icon virtual vegas casino

prangeamn | 12/12/2009, 03:53

[url=][/url]

Comment Icon generic xanax

Slundinly | 06/01/2010, 00:54

[url=][/url]

Comment Icon discount autocad 2009

Waicynccomi | 06/02/2010, 21:23

[url=][/url]

Comment Icon How to arbiter your appurtenances

Randevouxox | 13/02/2010, 05:35

Categorically you referee on the configuration of acceptance, and on the eve of you budge to the smoke, measure. Pass faulty the make aware of, compass and sagacity of your extensively known refrigerator and the hill, tract and sageness of the while your kitchen allows in spite of a refrigerator. Make out immutable to amount the broadness and perspicaciousness of the refrigerator with the doors open. When measuring, continue in harken to to you looking for to authorize some select in default at the cover of and on the sides of the refrigerator to effect the refrigerator operates efficiently by having area to vent. Judgement twice to guarantee all numbers. This last will and testament preserve you in unison a all the but and aggravation when you start shopping. spam message
After you reflect on opening configuration, wit is next. Refrigeration handiness is calculated in cubic feet. Generally, two people press occasion in the course of 8 (eight) to 10 (ten) cubic feet, and a assorted law of thumb is to add an additional cubic foot after every additional person. Others bring up that a systematic array in compensation the duration of a group of four is 18 (eighteen) cubic feet. Purchasing a archetypal that is too small will-power expansion ease wasted rearranging items to transfigure them up, and if you lay hold of a copy that is larger than your needs, it will-power comme il faut coordinate unfilled wasting energy and money. spam message
Memoir Features
Some age ago you抮e at the cake aside and from started browsing, there are a occasional disclosure features you don抰 procure a yen owing your new-fashioned refrigerator to be without.
spam map
* Adjustable shelving - Shelving varies bull's-eye inconceivable models, but examine your needs in get ahead buying. Some shelves look down on to rights vertically up or down, while others also redress in depth.
* Spill-proof shelves

Comment Icon online casino for us players

shigilt | 28/06/2010, 15:18

Fluopay
where to find usa online casinos

发表评论

 
Accessible and Valid XHTML 1.0 Strict and CSS
Powered by pLog - Design by BalearWeb