IBM DB2 的數據復制、遷移方法

以下方法經測試，在環境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 = "";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 = "";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 testdb2 "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 testdb2 "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