How to load chinese characters - RDBMS:5356

Database
Enthusiast

How to load chinese characters - RDBMS:5356

Greetings Experts,

In our Project, we are exporting and importing (Bteq) from one of the table (has chinese characters) which was working fine until now.  Now, we have  '0A'XC (hexadecimal representation  of Line Break or Line feed)  character in one of the column in that table and from unix we can see that the number of fields in exported file for all records are same except for this one due to the presence of '0A'XC.  We set the characterset to be UTF8 and  tried to update this field alone with the below command;

update table taba set col_chinese=OREPLACE(col_chinese,'0A'XC,'') where ...

But this doesn't work as it's showing the following error 

RDBMS:5356  One of the characters or code point does not exist within the specified repertoire

Tried again with UTF16 character set and it failed with same issue.  I guess it should be a character set issue which might require to set like kanji or some others;  Hence tried to check with dummy data as below in SQL Assistant and BTEQ.

.SET SESSION CHARACTERSET 'UTF8';
.LOGON TDPID/USERNAME,PWD;

CREATE VOLATILE TABLE ABC(COL1 VARCHAR(30) CHARACTER SET UNICODE)
ON COMMIT PRESERVE ROWS;

INSERT INTO ABC VALUES('李白《静夜思》');
INSERT INTO ABC VALUES('李白《静
夜思》'); -- Manually set \n with in the data

SELECT COL1, CHAR2HEXINT(COL1), OREPLACE(COL1,'0A'XC,'') COL2,
CHAR2HEXINT(COL2) FROM ABC;

It is failing with "Expected something between the | and ....." and displaying some weird symbols ("|" is not part of my script and it appears as part of weird symbols) in the statement which was not passed by me.

Faced the same issue in BTEQ as well

cmd> BTEQ -m -c 'UTF8' < input_script.txt > output.txt;

Checked in Bteq interactive mode with BTEQ -m -c 'utf8'   

INSERT INTO ABC VALUES('李白《静夜思》');

This statement becomes a bunch of "?" characters;  I am not able to figure it out;  How can I insert chinese characters into it;

FYI, I have tried in my local V15 virtual machine and it works fine (could insert the chinese chars and could eliminate '0A'xc);

Can  somebody help me with this please..

2 REPLIES
Teradata Employee

Re: How to load chinese characters - RDBMS:5356

Is it possible that there is a different (older) UDF installed named OREPLACE? Try qualifying with TD_SYSFNLIB.

Also, since this is a single character, I would use OTRANSLATE instead of OREPLACE.

Enthusiast

Re: How to load chinese characters - RDBMS:5356

Hi Fred,

Sorry, forgot to mention that the insert statement was failed which is below

INSERT INTO ABC VALUES('李白《静夜思》');
INSERT INTO ABC VALUES('李白《静
夜思》'); -- Manually set \n with in the data

Not sure what was the issue as I was able to run the same set of statements in my v15 virtual machine without any issues. Any other settings are to be made..