某银行east报送数据18年12月份交易流水表(JYLS)某个字段编码转换错误。交易流水表整个月的数据5个亿。重新追数耗费时间太长。
方案一:用export导出,在export导出时 select语句中用 case when 将字段转换。
"db2 export to jyls.ixf of ixf select 字段很多,case when xxx='1' then '中文' when xxx=2 then '还是中文' else '其他' end from tablename "
考虑到5个亿的数据量 导出耗时同样大,大约需要5 6个小时。放弃方案一。
方案二:利用db2迁移神器hpu导出(hpu怎么使用可以参考这里 https://blog.csdn.net/lyc417356935/article/details/44803309 )
果然神器就是神器,5个亿的数据利用hpu导出来花了半个小时吧。多长时间不太记得,反正就是比export快。
新建一个文件 命名为 1.ctl
global connect to east;
unload tablespace ccsid(1386);
select 字段很多,case when xxx='1' then '中文' when xxx=2 then '还是中文' else xxx end from tablename;
format ixf;
另建一个shell文件 1.sh
export LANG=EN_US
/opt/IBM/HPU/V5.1/bin/db2hpu -i eastinst -f 1.ctl
数据导出后,再利用db2 分区load方式将数据文件replace进原表
将数据文件链接到每个节点上
ln -s -f JYLS.ixf JYLS..ixf.000
ln -s -f JYLS.ixf JYLS..ixf.001
ln -s -f JYLS.ixf JYLS..ixf.002
ln -s -f JYLS.ixf JYLS..ixf.003
ln -s -f JYLS.ixf JYLS..ixf.004
ln -s -f JYLS.ixf JYLS..ixf.005
ln -s -f JYLS.ixf JYLS..ixf.006
ln -s -f JYLS.ixf JYLS..ixf.007
ln -s -f JYLS.ixf JYLS..ixf.008
在命令行执行
"db2 load from JYLS.ixf of ixf messages 1.msg replace into east.JYLS partitioned db config mode load_only_verify_part part_file_location ."
最后这个点 表示在当前目录(数据文件所在目录)
你以为这就结束了吗。
第二天我才发现这数据 tmd 跟原来一模一样。hpu导出来 load导进去 那个字段压根就没转换掉。气炸了。搞了一天 白费劲。应该是hpu ctl文件不认识中文,我不知道是不是不认识中文,但可以肯定的是 ctl文件不认识trim 加了trim就报错。
方案三:利用存储过程中load 游标的方式
新建临时表,将数据从原表load进临时表,然后再从临时表load进原表,详见代码
CREATE PROCEDURE EAST.LOAD_JYLS (IN INDATE CHAR(8), OUT OUTCODE
INTEGER,OUT OUTMSG VARCHAR(1000) )
LANGUAGE SQL
NOT DETERMINISTIC
EXTERNAL ACTION
MODIFIES SQL DATA
CALLED ON NULL INPUT
INHERIT SPECIAL REGISTERS
BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;--
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';--
DECLARE V_SQL VARCHAR(1024); --
DECLARE V_COUNT INTEGER; --
DECLARE V_MONTH CHAR(6); --
DECLARE SP_NAME VARCHAR(50) DEFAULT 'PROC_XDFHZ_ZFJY';--
DECLARE SQL_CODE INT DEFAULT 0; --
DECLARE SQLMESSAGE VARCHAR (1024); --
DECLARE ROWS_READ INTEGER DEFAULT 0; --
DECLARE ROWS_SKIPPED INTEGER DEFAULT 0; --
DECLARE ROWS_LOADED INTEGER DEFAULT 0; --
DECLARE ROWS_REJECTED INTEGER DEFAULT 0; --
DECLARE ROWS_DELETED INTEGER DEFAULT 0; --
DECLARE ROWS_COMMITTED INTEGER DEFAULT 0; --
DECLARE ROWS_PART_READ INTEGER DEFAULT 0; --
DECLARE ROWS_PART_REJECTED INTEGER DEFAULT 0; --
DECLARE ROWS_PART_PARTITIONED INTEGER DEFAULT 0; --
DECLARE MPP_LOAD_SUMMARY VARCHAR (1024); --
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
SET OUTCODE = SQLCODE;--
END;--
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING
BEGIN
SET OUTCODE = SQLCODE;--
SET OUTMSG = OUTMSG||'出错!!!!!';--
END;--
RESIGNAL;--
INSERT INTO EASTBS.LOG(LOG_ID, LOG_NAME, LOG_PROC, LOG_TIME, LOG_DESC) VALUES(CHAR(NEXTVAL FOR EASTBS.LOG_SEQ), OUTMSG, SP_NAME, CURRENT_TIMESTAMP, 'SQLCODE:'||CHAR(SQLCODE));--
END;--
SET OUTMSG = '开始执行: '||SP_NAME;--
INSERT INTO EASTBS.LOG(LOG_ID, LOG_NAME, LOG_PROC, LOG_TIME, LOG_DESC) VALUES(CHAR(NEXTVAL FOR EASTBS.LOG_SEQ), OUTMSG, SP_NAME, CURRENT_TIMESTAMP, 'SQLCODE:'||CHAR(SQLCODE));--
COMMIT;--
------将数据转换出入临时表
CALL SYSPROC.DB2LOAD (1,
'DECLARE C1 CURSOR FOR
select 字段很多,case when xxx='1' then '中文' when xxx=2 then '还是中文' else '其他' end from jyls',
'LOAD FROM C1 OF CURSOR REPLACE INTO EAST.JYLS_TMP',
SQL_CODE,
SQLMESSAGE,
ROWS_READ,
ROWS_SKIPPED,
ROWS_LOADED,
ROWS_REJECTED,
ROWS_DELETED,
ROWS_COMMITTED,
ROWS_PART_READ,
ROWS_PART_REJECTED,
ROWS_PART_PARTITIONED,
MPP_LOAD_SUMMARY);
COMMIT;--
---将临时表数据插入原表
CALL SYSPROC.DB2LOAD (1,
'DECLARE C2 CURSOR FOR
select * from jyls_tmp',
'LOAD FROM C2 OF CURSOR REPLACE INTO EAST.JYLS',
SQL_CODE,
SQLMESSAGE,
ROWS_READ,
ROWS_SKIPPED,
ROWS_LOADED,
ROWS_REJECTED,
ROWS_DELETED,
ROWS_COMMITTED,
ROWS_PART_READ,
ROWS_PART_REJECTED,
ROWS_PART_PARTITIONED,
MPP_LOAD_SUMMARY); --
COMMIT;--
SET OUTMSG = '完成'||SP_NAME;--
INSERT INTO EASTBS.LOG(LOG_ID, LOG_NAME, LOG_PROC, LOG_TIME, LOG_DESC) VALUES(CHAR(NEXTVAL FOR EASTBS.LOG_SEQ), OUTMSG, SP_NAME, CURRENT_TIMESTAMP, 'SQLCODE:'||CHAR(SQLCODE));--
COMMIT;--
SET OUTCODE = 1;--
RETURN OUTCODE;--
END
SQL_CODE,
SQLMESSAGE,
ROWS_READ,
ROWS_SKIPPED,
ROWS_LOADED,
ROWS_REJECTED,
ROWS_DELETED,
ROWS_COMMITTED,
ROWS_PART_READ,
ROWS_PART_REJECTED,
ROWS_PART_PARTITIONED,
MPP_LOAD_SUMMARY
这几个参数是一定要有的。
load游标的用法请参考