RDBMS - 6706 - Untranslatable character

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Highlighted
Enthusiast

RDBMS - 6706 - Untranslatable character

Greetings Experts,

We have a datastage job which reads from a different server TD_PROD_SERVER_A.TABLE_A and loads to another server TD_PROD_SERVER_B.TABLE_B.  The DDLs are same for each of these tables in 2 different servers.  The job had failed with RDBMS: 6706 Untranslatable character issue;  Source_count=1000, Target_count=996, 4 rows are not-loaded.  When looked at the _ET table, could see that all 4 rows correspond to a single column COLUMN1 whose definition is

COLUMN1 VARCHAR(15) CHARACTER SET LATIN NOT CASE SPECIFIC same in both tables of different server.  What I don't understand is how it got loaded at into the source table TD_PROD_SERVER_A.TABLE_A.COLUMN1  when it is a LATIN character set.  When I ran the

select column1, char2hexint(column1) HEX_VALUE from TD_PROD_SERVER_A.TABLE_A where translate_chk(column1 using unicode_to_latin) <> 0

 could see 4 rows as

 

COLUMN1            HEX_VALUE

  Y                1A1A8D59

 Y                 1A1A1A59

When I create a volatile table and insert a record

 

CREATE VOLATILE TABLE ABC (COL1 VARCHAR(15) CHARACTER SET LATIN NOT CASE SPECIFIC) ON COMMIT PRESERVE ROWS;

INSERT INTO ABC VALUES('1A1A8D59'XC);

it fails with same error "6706 Untranslatable character" error.  Can someone please suggest why at the source these values which fails in our server is successfully inserted eventhough the DDL is same.  Does any kind of datastage settings like the character-set which loads into source server table and target server table might be different which raises this issue.  Awaiting your valuable inputs.  Thanks.


Accepted Solutions
Teradata Employee

Re: RDBMS - 6706 - Untranslatable character

Your DataStage instance and/or your connection settings are requesting translation from internal LATIN to external Unicode (e.g. UTF8) and back to LATIN. The x'1A' in effect it means "a previous translation failed, and this is just a placeholder recording the error" so the database doesn't know how to translate that character. Many tools and some other databases use x'1A' in this way, not just Teradata. (For double-byte character sets / Teradata UNICODE, the value x'FFFD' is typically used in a similar manner.)

 

The data was likely loaded in the original system using "ASCII" session character set. Using ASCII to load a LATIN column does not invoke any translation, so the x'1A' would go undetected.

1 ACCEPTED SOLUTION
4 REPLIES
Enthusiast

Re: RDBMS - 6706 - Untranslatable character

Please ignore the sample values that I had provided as ' Y' and ' Y'.  They are directly typed by me as couldn't copy/paste the  exact value.  But the hexa-decimal value is correctly provided for others to check and assist.

Teradata Employee

Re: RDBMS - 6706 - Untranslatable character

Your DataStage instance and/or your connection settings are requesting translation from internal LATIN to external Unicode (e.g. UTF8) and back to LATIN. The x'1A' in effect it means "a previous translation failed, and this is just a placeholder recording the error" so the database doesn't know how to translate that character. Many tools and some other databases use x'1A' in this way, not just Teradata. (For double-byte character sets / Teradata UNICODE, the value x'FFFD' is typically used in a similar manner.)

 

The data was likely loaded in the original system using "ASCII" session character set. Using ASCII to load a LATIN column does not invoke any translation, so the x'1A' would go undetected.

Enthusiast

Re: RDBMS - 6706 - Untranslatable character

You can try using the following update statement on the column and replace the chars in question to spaces etc. with REGEXP_REPLACE:

UPDATE TD_PROD_SERVER_A.TABLE_A

 SET column1 =REGEXP_REPLACE(TRIM(Upper(TRIM(REGEXP_REPLACE(
TRANSLATE(column1 USING LATIN_TO_UNICODE WITH ERROR) , '[^a-z0-9]', ' ',1,0,'i')))
), '[[:space:]]+',' ',1,0,'i')
WHERE
 TRANSLATE_CHK(column1 USING LATIN_TO_UNICODE) <> 0;

Thanks,

 

Enthusiast

Re: RDBMS - 6706 - Untranslatable character


Thank you for your attention Fred and Jobbazar.  Sincerely apologize for the late reply.

Even after changing our column to Unicode, the Datastage job in Multiload still failed with the same issue.  We checked with DBAs and they confirmed that on the source server they had made a DBS setting change recently to allow special characters (104:  AcceptReplacementCharacters=True). 

I had exported the failed rows from the source server into a flat file with delimiter "|" and loaded from that file to our target server , same table , column in character set Latin using TPT script and strangely all rows got loaded.  Not sure why TPT is able to load when Multi-load is failing.  I used UTF-8 in the TPT script and the same UTF-8 was used in the Datastage.  Not sure why the datastage job was failing while TPT is able to successfully overcome the "Untranslatable character" issue.  Can you please help me to understand this behaviour.  Thank you for your time.

Yes it was x'FFFD'.