DB2 資料庫建立與 IXF 檔案匯出匯入完整指令範例

1. 建立資料庫 (UTF-8 編碼)

db2set db2codepage=1208
db2 create database wms automatic storage yes using codeset UTF-8 territory cn pagesize 32768

# 或指定儲存路徑與 GBK 編碼
db2 create db xxdb on /app/dbdata using CODEPAGE "GBK" country "Zh_CN"

2. 匯出表為 IXF 檔 (exportReportData.sh 範例)

toDir=/home/cpsinst/bup/
tab="UNTECK_ATTACHMENT_HIS UNTECK_BUSI_LOG UNTECK_DICT_ENTRY UNTECK_DICT_TYPE UNTECK_LOGIN_LOG UNTECK_MENU UNTECK_OPERATION UNTECK_ORGANIZATION UNTECK_PRIMARYKEY UNTECK_RESOURCE UNTECK_ROLE UNTECK_ROLE_RESOURCE UNTECK_USER UNTECK_USER_ROLE"

db2 connect to cpsdb
db2 set schema='ODSUSER'
for tabTemp in $tab
do
  echo $tabTemp
  db2 "export to ${toDir}/${tabTemp}.IXF of IXF select * from ${tabTemp}"
done

3. 匯入 IXF 檔 (含替換建表、批次提交)

db2 connect to myetldb
db2 set schema=odsuser
db2 IMPORT FROM ETL_BUSI_TYPE.IXF OF IXF MODIFIED BY FORCECREATE COMMITCOUNT 10000 REPLACE_CREATE INTO ETL_BUSI_TYPE
db2 IMPORT FROM ETL_BUSI_TYPE_TOSEND.IXF OF IXF MODIFIED BY FORCECREATE COMMITCOUNT 10000 REPLACE_CREATE INTO ETL_BUSI_TYPE_TOSEND
db2 IMPORT FROM ETL_CONTACT_PEPOLE.IXF OF IXF MODIFIED BY FORCECREATE COMMITCOUNT 10000 REPLACE_CREATE INTO ETL_CONTACT_PEPOLE

常見問答

僅匯入表結構不匯入資料？

使用 ROWCOUNT 1 僅匯入 1 列 (結構會建立)；若 ROWCOUNT 0 可能無法建表，建議實測。

IMPORT FROM aa.ixf OF IXF ROWCOUNT 1 CREATE INTO aa IN DATA IDX IN IDX

匯出/匯入格式由指令決定

# 匯出 IXF
db2 export to file.ixf of ixf select * from tab
# 匯出 DEL
db2 export to file.del of del select * from tab

# 匯入 IXF
db2 import from file.ixf of ixf insert into tab
# 匯入 DEL
db2 import from file.del of del insert into tab

另有 LOAD 指令適用大量載入，選項更多，請參考官方文件。