Page 1 of 1
How to truncate Chinese Char ?
Posted: Fri Mar 08, 2013 12:16 am
by anu123
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 .
Posted: Fri Mar 08, 2013 12:20 am
by chulett
Suggest you clarify your requirement. Do you mean you need to substring off the first 50 characters of a 100 character string?
Posted: Fri Mar 08, 2013 8:36 am
by anu123
Yes , i want first 50 positions of a 100 character string. I tried with substr & [1,50], but its not working.
Posted: Fri Mar 08, 2013 11:27 am
by sudha03_vpr
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] ?
Posted: Fri Mar 08, 2013 11:30 am
by sudha03_vpr
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
Posted: Fri Mar 08, 2013 12:24 pm
by anu123
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
Posted: Fri Mar 08, 2013 12:35 pm
by sudha03_vpr
Instead of table, can you load the data to the flat file and check if you have any special/ chinese characters .
Posted: Fri Mar 08, 2013 7:31 pm
by ray.wurlod
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.
Posted: Sat Mar 09, 2013 12:59 am
by anu123
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
Posted: Sat Mar 09, 2013 1:56 am
by ray.wurlod
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.