How to truncate Chinese Char ?

anu123 · Post by **anu123** » Fri Mar 08, 2013 12:16 am

Hi All,

Is there any function or method we have in DataStage to truncate the value of Chinese Char from 100 to 50 ?

Please suggest me .

chulett · Post by **chulett** » Fri Mar 08, 2013 12:20 am

Suggest you clarify your requirement. Do you mean you need to substring off the first 50 characters of a 100 character string?

anu123 · Post by **anu123** » Fri Mar 08, 2013 8:36 am

Yes , i want first 50 positions of a 100 character string. I tried with substr & [1,50], but its not working.

sudha03_vpr · Post by **sudha03_vpr** » Fri Mar 08, 2013 11:27 am

Does your NLS Char set in Datastage support the Chinese character set ? What is the error that you get when you use column1[1,50] ?

sudha03_vpr · Post by **sudha03_vpr** » Fri Mar 08, 2013 11:30 am

Instead of doing this in the Transformer you can extract only those 50 characters from the database.

Eg : SELECT SUBSTR(NAME,1,10) FROM CUSTOMER

anu123 · Post by **anu123** » Fri Mar 08, 2013 12:24 pm

I am reading from .txt file and trying to load in to DB2
We have NLS as UTF-8

Error : [IBM][CLI Driver][DB2/AIX64] SQL0302N The value of a host variable in the EXECUTE or OPEN statement is out of range for its corresponding use. SQLSTATE=22001

sudha03_vpr · Post by **sudha03_vpr** » Fri Mar 08, 2013 12:35 pm

Instead of table, can you load the data to the flat file and check if you have any special/ chinese characters .

ray.wurlod · Post by **ray.wurlod** » Fri Mar 08, 2013 7:31 pm

The inbuilt functions that you are likely to use, like [1,50] or Substring(), or Left(), all operate on characters (not on bytes). So, provided you have specified the correct character map (the one used to encode the data, for example BIG5 or GB2312) then all should be well.

The error message does not appear to be about characters and substringing. Check your DB2 error manual to understand more fully what error code SQL0302N means and its probable resolution.

Please post your findings back here.

anu123 · Post by **anu123** » Sat Mar 09, 2013 12:59 am

Hi Ray,

I found the info from the link below and tried by changing the field type as nvarchar in DS and not changed in the table , so that could be a reason for failing again with same error .

"To resolve this issue, you can triple the original varchar precision and size, or you can change the data type to nvarchar."

http://pic.dhe.ibm.com/infocenter/caapp ... iable.html

ray.wurlod · Post by **ray.wurlod** » Sat Mar 09, 2013 1:56 am

That, in turn, really depends on whether the database specifies characters or bytes for storage. If bytes, then a multiplier is definitely indicated, and will usually occur automatically when the table definition is imported. We found with most encodings of Japanese data that a factor of 3 was correct; however if there is a prevalence of double-width characters in your Chinese data a factor of 4 may be more appropriate.