| Author |
Message
|
| hellobond070 |
 Posted: Fri Nov 27, 2009 3:42 am Post subject: Data Not getting Inserted in Database : Special Character |
 |
|
Centurion
Joined: 18 Nov 2009
Posts: 118
|
I am facing a weird issue. The incoming data with special character comes to the broker flow and it tries to update the database table throwing an exception.
When I try manually to update the table this data with special character gets uploaded in the table.
Details :
- The Special character is Ã
- Broker and DB2 database on AIX platform
- SQL error : [IBM][CLI Driver] CLI0109E String data right truncation. SQLSTATE=22001
Kindly guide me.
Thanks |
|
| Back to top |
|
 |
| AkankshA |
| Posted: Fri Nov 27, 2009 3:50 am Post subject: |
|
|
Grand Master
Joined: 12 Jan 2006
Posts: 1494
Location: Singapore
|
what is the CCSID of your broker...
is it compatible ??
which driver are you using ??
_________________
Cheers |
|
| Back to top |
|
|
| hellobond070 |
| Posted: Fri Nov 27, 2009 4:07 am Post subject: |
|
|
Centurion
Joined: 18 Nov 2009
Posts: 118
|
Is there any way i can find it..the ccsid of the broker
What I understand from the issue is the problem doesn't lie in the database part, because manually the same data is getting uploaded.
The problem has to be with the WBIMB. May be we need to cast the data with a ccsid and try updating the table.
Any suggestions. |
|
| Back to top |
|
|
| harish_td |
| Posted: Fri Nov 27, 2009 10:47 pm Post subject: |
|
|
Master
Joined: 13 Feb 2006
Posts: 236
|
| hellobond070 wrote: |
| Is there any way i can find it..the ccsid of the broker |
mqsiservice <Broker-Name> |
|
| Back to top |
|
|
| hellobond070 |
| Posted: Sat Nov 28, 2009 7:12 am Post subject: |
|
|
Centurion
Joined: 18 Nov 2009
Posts: 118
|
819 is the broker ccsid
I tired using the PASSTHRU statement, but in vain. This did not help.
M still getting driver error.
Any solutions would be highly appreciated. |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Sat Nov 28, 2009 9:20 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi hellobond070,
| hellobond070 wrote: |
- The Special character is Ã
- Broker and DB2 database on AIX platform
- SQL error : [IBM][CLI Driver] CLI0109E String data right truncation. SQLSTATE=22001 |
Are you sure this is a CCSID issue ? I would assume data right truncation to be an issue with the data length being greater than the accepted column size...I could be wrong
Usually even if the characters are unrecognizable, they do get inserted as junk into tables.
Regards. |
|
| Back to top |
|
|
| sirsi |
| Posted: Sat Nov 28, 2009 5:58 pm Post subject: |
|
|
Disciple
Joined: 11 Mar 2005
Posts: 177
|
| as asked, whats DB2 driver version, codepage supported by your DB2 |
|
| Back to top |
|
|
| sirsi |
| Posted: Sat Nov 28, 2009 6:08 pm Post subject: |
|
|
Disciple
Joined: 11 Mar 2005
Posts: 177
|
|
| Back to top |
|
|
| hellobond070 |
| Posted: Sun Nov 29, 2009 12:09 am Post subject: |
|
|
Centurion
Joined: 18 Nov 2009
Posts: 118
|
hi all,
Operating System: AIX
DBMS Name : DB2/6000
DBMS Version : 08.02.0009
Driver name : DB2CLI.dll
Driver Version : 08.02.0004
Could you please let me know the way to find out CCSID used by DB2
The data character length has already been checked. I could insert the same data with special character directly into the table from command prompt. But the same job cud not be done by the broker.
Please help.
Thanks |
|
| Back to top |
|
|
| hellobond070 |
| Posted: Sun Nov 29, 2009 12:38 am Post subject: |
|
|
Centurion
Joined: 18 Nov 2009
Posts: 118
|
hi Sirsi,
The link you have provided is for ZOS. We r using AIX.
Also its a character issue, so will Encoding help ?? or CCSID should suffice ?? |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Mon Nov 30, 2009 1:39 pm Post subject: Re: Data Not getting Inserted in Database : Special Characte |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| hellobond070 wrote: |
Details :
- The Special character is Ã
- Broker and DB2 database on AIX platform
- SQL error : [IBM][CLI Driver] CLI0109E String data right truncation. SQLSTATE=22001 |
DB2 string functions count bytes, not characters. Be sure to calculate string lengths and column widths accordingly, particularly for strings that contain multi-byte characters.
Consult the documentation for more details. |
|
| Back to top |
|
|
| agrawalamit166 |
| Posted: Mon Nov 30, 2009 1:40 pm Post subject: |
|
|
Voyager
Joined: 17 Aug 2009
Posts: 78
Location: NY, US
|
|
| Back to top |
|
|
| hellobond070 |
| Posted: Tue Dec 01, 2009 3:12 am Post subject: |
|
|
Centurion
Joined: 18 Nov 2009
Posts: 118
|
| Quote: |
agrawalamit166 Posted: Mon Nov 30, 2009 1:40 pm Post subject:
R U using dynamic Query? |
- Yes its a dynamic query. The flow reads the data from incoming file and updates the database if not already present.
| Quote: |
DB2 string functions count bytes, not characters. Be sure to calculate string lengths and column widths accordingly, particularly for strings that contain multi-byte characters.
Consult the documentation for more details. |
- I went through the documentation and it gives good information. One of them being
DB2 string functions use byte indexing and not character indexing. This means that for Unicode data, the meaning of certain functions differs from the WebSphere Message Broker functions, even though they can be ‘interpreted’.
So the special character in my data is being interpreted at Message broker as well as db2 end. But I guess, the way it is represented at both ends differs.
But this documentation doesn't give me the solution.
The DB2 uses 819 ccsid which is the same as Broker ccsid in my case.
Please advise !!!!
Thanks |
|
| Back to top |
|
|
| sirsi |
| Posted: Tue Dec 01, 2009 8:17 am Post subject: |
|
|
Disciple
Joined: 11 Mar 2005
Posts: 177
|
| kindly tell us the flow logic, i mean input file is modelled in MRM? how are u accessing the field which you are inserting into db...etc |
|
| Back to top |
|
|
| hellobond070 |
| Posted: Tue Dec 01, 2009 8:36 am Post subject: |
|
|
Centurion
Joined: 18 Nov 2009
Posts: 118
|
Hello Sirsi,
We have a flat file coming in which is parsed by a message set in MRM domain. The whole file needs to be updated in the database.
The error occurs exactly at the point of insertion by the sql quesry written in the esql file into the database.
As previously said, if I try to enter the special character into the database table directly it does get inserted without issues. The message broker is able to read the character correctly since I dont see issues if I get it printed using the trace node.
Any suggestions guys or anything that I can try to see if I can get this working. Looks challenging. |
|
| Back to top |
|
|
|
|
