DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
This topic has been marked "Resolved."
Author Message
joycerecacho
Participant



Joined: 26 Aug 2008
Posts: 298

Points: 5485

Post Posted: Thu Nov 21, 2019 6:42 am Reply with quote Back to top

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Hi everybody.
I am reading an XML file on which is expected to use encoding UTF8.
DataStage can read special characters perfectly, however the number of bytes is incorrect.

It follows an example:

Text field contend: "N3600"
Text field Size: Varchar(6)

I expected to count 6 bytes, however, datastage reads 7 bytes cause the ordinal character is considered 2 bytes.
Trying the Insert at Exadata, it reports that the contend exceeds the size of the field (Varchar(6)).

How can I solve this issue?

In theory, this text has only 6 bytes and should not cause abort.

Ive changed NLS options at the job level but it did not work neither.

Id really appreciate any tip.

Best regards,

_________________
Joyce A. Recacho
So Paulo/SP
Brazil
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 43053
Location: Denver, CO
Points: 222300

Post Posted: Thu Nov 21, 2019 11:58 am Reply with quote Back to top

Six bytes and six characters may or may not be equivalent when dealing with multi-byte charactersets. Don't know Exadata but Oracle does both "byte" and "character" semantics. Meaning, if you define a field as VARCHAR(6) BYTES then it won't hold your six characters because... seven bytes. VARCHAR(6) CHAR would hold them since it holds six characters not restricted by byte count. And for some reason, BYTE is the default in Oracle. Been bit in the butt before over that.

Something to look into.

_________________
-craig

"May the bridges I burn light my way forward"
Rate this response:  
Not yet rated
joycerecacho
Participant



Joined: 26 Aug 2008
Posts: 298

Points: 5485

Post Posted: Sun Nov 24, 2019 6:24 am Reply with quote Back to top

Chulett,
Your are completely right.
The fields were created as varchar instead of nvarchar (supports unicode). It required a greater number of bytes to store special characters.

Weve just changed the table structure and it worked out.

Thank you very much for your help.

Best Regards,

_________________
Joyce A. Recacho
So Paulo/SP
Brazil
Rate this response:  
Not yet rated
Display posts from previous:

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours