db2数据抽取至oracle,快速从db2导出数据到oracle

背景： 一个项目目前是db2 ，需要换系统将db2的数据导入到新的oracle数据库里， 也不是全部导入，仅仅导入需要的一些出入库记录。

步骤：

1. 先写好要导出数据的select  sql。

如：

select b.NPRICE1, b.ninnum ,b.noutnum ,b.NPURCHDISCOUNT as zk , b.NPRICEMONEY as my ,b.NREALMNY as sy , b.CINVBASID,h.CPROVIDERID as gys,h.CCUSTOMERID as ks , b.cgeneralbid, b.cgeneralhid ,b.CBODYBILLTYPECODE,h.DBILLDATE ,h.WLPCH as lx,h.FREPLENISHFLAG as th from db2inst1.ic_general_b b inner join DB2INST1.IC_GENERAL_h h on h.CGENERALHID=b.CGENERALHID

where h.DBILLDATE between '2015-03-01' and '2015-03-31' and h.dr=0 and b.dr=0;

２.在db2的命令编辑器里 执行下面的sql  导出csv格式的。

export to d:\bid3.csv of del select b.NPRICE1, b.ninnum ,b.noutnum ,b.NPURCHDISCOUNT as zk , b.NPRICEMONEY as my ,b.NREALMNY as sy , b.CINVBASID,h.CPROVIDERID as gys,h.CCUSTOMERID as ks , b.cgeneralbid, b.cgeneralhid ,b.CBODYBILLTYPECODE as lx ,h.DBILLDATE ,h.WLPCH as wlpch,h.FREPLENISHFLAG as th from db2inst1.ic_general_b b inner join DB2INST1.IC_GENERAL_h h on h.CGENERALHID=b.CGENERALHID

where h.DBILLDATE between '2015-03-01' and '2015-03-31' and h.dr=0 and b.dr=0;

3.在cmd里链接oracle

conn dbora@orcl

4.执行导入sql 当然，在导入之前需要先在oracle里创建数据表结构。

host sqlldr userid=dbora/orcl@orcl control=D:\control.ctl

命令中的control.ctl是 需要手工敲的，比较麻烦。

下面是这个control.ctl的内容。

load data

infile "D:\bid3.csv"

into table CB

fields terminated by "," optionally enclosed by '"'

trailing nullcols

(

NPRICE1,

ninnum ,

noutnum ,

zk ,

my ,

sy ,

CINVBASID,

gys,

ks ,

cgeneralbid,

cgeneralhid ,

lx

, DBILLDATE ,

wlpch,

th

)

速度还是蛮快的 ，比写代码来回传输要快多了。