problem when reading Chinese character in complex flat file
Posted: Thu Nov 04, 2010 5:08 am
I need to read one complex file and load the data into DB2.
THE COPYBOOK is like as follow:
1:RCZSCB3A:-REC.
05 :RCZSCB3A:-NAME X(10).
And The Table:
A(
NAME CHAR(10)
)
Some Chinese characters may be in the column , so I set the property "extended" : "unicode"
"nls map" : "ibm-1383"
and set "Field width" : 10.
In the EBCDIC file , there is only one record :
0x0E 0x49 0xE1 0x55 0xD3 0x0F 0x40 0x40 0x40 0x40
(0x49 0xE1 0x55 0xD3 means "测试")
after insert the record by DB2 ENTERPRISE SATAGE I
select * from A where NAME="测试",it returns nothing。
AND ASCII(SUBSTR(NAME,9,1))=0 ;
ASCII(SUBSTR(NAME,10,1))=20 ;
LENGTH(TRIM(NAME)) = 9
I GUESS IT IS CAUSED BY "PAD CHARACTER" WHICH I SET 0X20
If I use "connector stage" to insert the record
the result is fine.the sql statament above can return one record and
ASCII(SUBSTR(NAME,9,1))=20 ;
ASCII(SUBSTR(NAME,10,1))=20 ;
LENGTH(TRIM(NAME)) = 4
What should I do to make the dB2 enterprsie stage to insert
"测试0x20 0x20 0x20 0x20 0x20 0x20"
but not "测试0x20 0x20 0x20 0x20 0x00 0x20"
THE COPYBOOK is like as follow:
1:RCZSCB3A:-REC.
05 :RCZSCB3A:-NAME X(10).
And The Table:
A(
NAME CHAR(10)
)
Some Chinese characters may be in the column , so I set the property "extended" : "unicode"
"nls map" : "ibm-1383"
and set "Field width" : 10.
In the EBCDIC file , there is only one record :
0x0E 0x49 0xE1 0x55 0xD3 0x0F 0x40 0x40 0x40 0x40
(0x49 0xE1 0x55 0xD3 means "测试")
after insert the record by DB2 ENTERPRISE SATAGE I
select * from A where NAME="测试",it returns nothing。
AND ASCII(SUBSTR(NAME,9,1))=0 ;
ASCII(SUBSTR(NAME,10,1))=20 ;
LENGTH(TRIM(NAME)) = 9
I GUESS IT IS CAUSED BY "PAD CHARACTER" WHICH I SET 0X20
If I use "connector stage" to insert the record
the result is fine.the sql statament above can return one record and
ASCII(SUBSTR(NAME,9,1))=20 ;
ASCII(SUBSTR(NAME,10,1))=20 ;
LENGTH(TRIM(NAME)) = 4
What should I do to make the dB2 enterprsie stage to insert
"测试0x20 0x20 0x20 0x20 0x20 0x20"
but not "测试0x20 0x20 0x20 0x20 0x00 0x20"