db2导入导出数据

--连接数据库
db2 connect to pisdb user db2admin using db2admin;

--导出育龄妇女表数据
db2 export to c:/pistable1.txt of del select * from pistable1;

--往数据库导入数据
load from F:\mdis数据抽取备份数据\pistable1.txt of del modified by codepage=1386 insert into pistable1

import from c:\empty.txt of del replace into 表; 

---删除大量数据时需要用
IMPORT FROM Cnull OF DEL REPLACE INTO pistable1;

--db2 import from c:/pistable1.txt of del commitcount 50000 replace into pistable1;

--是清空表数据
import from c:\empty.txt of del replace into 表; 

--处理bas_information中去重，根据t.PISField001,t.PISField011,t.areaCode
export to e:/bas_information.txt of del select s.HOUSEHOLDS, s.PISFIELD000, s.PISFIELD001, s.PISFIELD002,
    s.PISFIELD003, s.PISFIELD004, s.PISFIELD005, s.PISFIELD006, s.PISFIELD011,
    s.PISFIELD012, s.PISFIELD013, s.PISFIELD014, s.PISFIELD015, s.PISFIELD016,
    s.DEADDATE, s.QIANRUDATE, s.ZHIYE, s.JIATING, s.AREACODE, s.REG_DATE,
    s.ISLOGOUT from (select t.*,rownumber() over(partition by t.PISField001,t.PISField011,t.areaCode) as rn from bas_information t ) s where s.rn = 1;

 

 

---5、统计封装维度表
--------------------------[性别情况表 xingbieqingkuangtb]
--统计10年内所有县级男女人口数 2010年 ----2001年        根据出生日期算出10年内出生的性别为男和女的人口插入到性别情况表中  根据区划 取 县级资料
insert into xingbieqingkuangtb(Statistical_time,areaCode,Pop_structure,man,woman) select char(year(current date)-1),concat(substr(areacode,1,6), '000000'),'01', sum(case when PISField002='1' then 1 end) man,sum(case when PISField002='2' then 1 end) nv from bas_information where  PISField012 < current date - 0 YEARS -(MONTH (current date)-1) MONTHS -(day (current date)-1) DAYS  group by  substr(areacode,1,6) ;