| Author |
Message
|
| ma.eyal |
 Posted: Sun Apr 30, 2006 11:36 pm Post subject: CCSID Insert database - char conversion |
 |
|
Novice
Joined: 13 Sep 2005
Posts: 15
Location: Israel
|
Hello.
We had stambled into a problem with character conversions and inserting given data from a message into the database when working with hebrew and unicode.
Our Queue Manager (v5.31) is running under CCSID 1208, we work with a db2 on aix with CCSID 1208, and the message arriving is with 1208 code page (originated from a Z/OS system sent to the aix using SNA).
When the message arrives to the wbi (v5), we are trying to put a field into the database without any data manipulation.
What we encountered was that the data inserted into the database is invalid. The data is not readable and when selecting the hex of the field to check the data, we get that its not equal to the original value.
If the original value in hex was :
"2020d7a8d79e2020d7a2d795d7a02020d7aad7a8d791d797"
the one in the database is
"20207f7f20207f7f7f20207f7f7f7f"
We can fix this problem by putting te data as a blob into the database, which saves the original hex values. But then we cant watch or update th data.
Anyone has encountered this sort of a problem?
Thank you. |
|
| Back to top |
|
 |
| Kateel |
| Posted: Tue May 02, 2006 6:28 am Post subject: |
|
|
Novice
Joined: 01 May 2006
Posts: 17
|
| Since the message is coming from Z/OS, you need to convert the message from EBCDIC to ASCII. Does your sender channel from Z/OS has convert option set ? |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue May 02, 2006 4:34 pm Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| Kateel wrote: |
| Since the message is coming from Z/OS, you need to convert the message from EBCDIC to ASCII. Does your sender channel from Z/OS has convert option set ? |
Since this is inside Broker, Broker automatically converts all the data in the message tree into Unicode as it parses the message.
Using sender channel conversion is always a bad idea.
I do not understand the mention of SNA. Are your MQ channels running over SNA? What does you mean?
Are you sure that the messages are arriving to Broker with the proper Unicode characters for the hebrew characters you are trying to write?
Are you sure that you have properly configured your database to handle this data? I ran into a vaguely similar problem with WAS and Oracle where WAS needed to have NLS_LANGUAGE environment variables configured to prevent the Oracle client from converting the data improperly.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| ma.eyal |
| Posted: Wed May 03, 2006 10:28 pm Post subject: |
|
|
Novice
Joined: 13 Sep 2005
Posts: 15
Location: Israel
|
Hello.
I have done some investigation about our system.
The system sending the message from z/os is an event triggering sytem provided by IBM, which creates messages in a ccsid of 1208 and sending them formated in xml to the wbi (which sits on aix).
The system reads information from the db2 log, and does not convert the data from 424 to 1208.
So what I get is a message with a mix of UTF-8 base message with EBCDIC fields.
So if I cast the given field inside the message to ccsid 424 in the flow, I get its hex values correctly, but I still have a problem converting it to 1208, since the flow is running under 1208 code page.
I have tried all sort of conversions with CAST, but was unable to cast the field correctly to 1208. Anyone has encoutered this sort of a problem and can give some advice?
Thank you. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu May 04, 2006 5:23 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
If you reassign the fields that hold data in the 424 codepage with the values of the CAST result, then you will have converted the data into 1208.
Like so:
Set OutputRoot.XML.DB2Message.424Field1 = CAST(InputRoot.XML.Db2Message.424Field1 ENCODING InputRoot.Properties.Encoding CodedCharSetId 424);
So insert a Compute node to do this to all the fields, and then go to the parts of your flow that want to write stuff into your database.
Also, you're reading the db2 logs? Why?
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| ma.eyal |
| Posted: Thu May 04, 2006 5:41 am Post subject: |
|
|
Novice
Joined: 13 Sep 2005
Posts: 15
Location: Israel
|
Thank you, I will try that and post if I was able to fix the problem.
We are using IBM's Event Publisher, which follows log changes and generate a MQ message in UTF-8.
The problem with it is that it sends the data "as is" without any conversions.
Its a black box to us and unchangable.
Good day. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu May 04, 2006 8:33 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
AH! Okay, using Event Publisher is a bit different.
Open a PMR! 
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| ma.eyal |
| Posted: Sat May 06, 2006 11:04 pm Post subject: |
|
|
Novice
Joined: 13 Sep 2005
Posts: 15
Location: Israel
|
Thank you for the help jeff.
We tried the suggested and a few more conversion options but none of them worked.
We concluded that the information is send wrongly converted from the Z/Os because of the mismatch message data/ccsid coming from the Event Publiser. Using a small batch worked, but we need to use the Event Publisher...
I guess there is no other way except opening a PMR and hoping for the best  |
|
| Back to top |
|
|
|
|
