去年做了一个数据报送的项目,项目要求导出表文件,并且用“|”号隔开,开始用DB2forToad 工具直接导,开始还可以,但是后来数据量上千万级别后,工具根本就导不了,后来想到有命令导出,命令如下:
EXPORT TO 'E:\xxx.txt' OF DEL
MODIFIED BY codepage=1208 NOCHARDEL COLDEL|
SELECT
A.TRANSACTIONNO,
A.COMPANYCODE,
A.GRPPOLICYNO,
A.POLICYNO,
A.PRTNO,
A.GPFLAG,
FROM EDW.TEST A
WHERE BATCHNO1 =WITH UR;
但是这个这个语句会有一个问题:
导出的数据会在10进制数据前面加上很多带正负号的0。
所以改进语句:
导出部分改为:
EXPORT TO 'E:\xxx.txt' OF DEL
MODIFIED BY codepage=1208 NOCHARDEL COLDEL| DECPLUSBLANK STRIPLZEROS
这个语句执行后会去掉前面带正负号的0部分,但是也会出现个问题,去掉的部分会有一个空格。
这样对于需要报送的数据,格式是有严格要求的,所以不能有空格。所以最后只能放大招了,用To_char()函数。
最终完美解决方法:
EXPORT TO '1001.txt' OF DEL
MODIFIED BY codepage=1208 NOCHARDEL COLDEL| timestampformat="yyyy-mm-dd hh:mm:ss"
SELECT
A.TRANSACTIONNO,
A.COMPANYCODE,
A.GRPPOLICYNO,
A.POLICYNO,
TO_CHAR(A.APPNTBIRTHDATE,'yyyy-mm-dd') AS APPNTBIRTHDATE,
TO_CHAR(A.SIGNDATE,'yyyy-mm-dd') AS SIGNDATE,
A.CURRENCY,
TO_CHAR(A.COPIES) AS COPIES,
TO_CHAR(A.PREMIUM) AS PREMIUM,
TO_CHAR(A.SUMINSURED) AS SUMINSURED,
TO_CHAR(A.CASHVALUE) AS CASHVALUE,
TO_CHAR(A.POLBALANCE) AS POLBALANCE,
TO_CHAR(A.ACCUMPREMIUM) AS ACCUMPREMIUM,
TO_CHAR(A.ACCUMLOANMONEY) AS ACCUMLOANMONEY,
TO_CHAR(A.APLMONEY) AS APLMONEY,
TO_CHAR(A.CURPAIDTODATE,'yyyy-mm-dd') AS CURPAIDTODATE,
TO_CHAR(A.PAYBEGINDATE,'yyyy-mm-dd') AS PAYBEGINDATE,
TO_CHAR(A.EFFDATE,'yyyy-mm-dd') AS EFFDATE,
TO_CHAR(A.UWDATE,'yyyy-mm-dd') AS UWDATE,
A.UWTYPE,
TO_CHAR(A.POLAPPLYDATE,'yyyy-mm-dd') AS POLAPPLYDATE,
TO_CHAR(A.CUSTOMGETPOLDATE,'yyyy-mm-dd') AS CUSTOMGETPOLDATE,
A.POLSTATUS,
A.BANKCODE,
A.BANKNAME,
A.BANKACCNO,
A.ACCNAME,
A.POLICYTYPE,
TO_CHAR(A.POLICYENDDATE,'yyyy-mm-dd') AS POLICYENDDATE,
TO_CHAR(A.TERMINATIONDATE,'yyyy-mm-dd') AS TERMINATIONDATE,
TO_CHAR(A.SUSPENDDATE,'yyyy-mm-dd') AS SUSPENDDATE,
TO_CHAR(A.RECOVERDATE,'yyyy-mm-dd') AS RECOVERDATE,
A.TERMINATIONREASON
FROM EDW.TEST A
WHERE BATCHNO1=@
WITH UR;
这个是数据导出遇到的一点问题,分享下解决方案给大家。