CCSID conversion with JDBC

We have an application that resides on the iSeries that saves data to the DB2.
This application is running on many systems in multiple countries. For all
instances, the fields in DB2 were created with a CCSID of 37. When the
application reads from / saves to the DB2, it converts the characters to the
system's locale. So if the application is residing in Germany (CCSID 273 - <a
class="jive-link-external"
href="http://www-03.ibm.com/systems/i/software/globalization/pdf/cp00273z.p
df">http://www-03.ibm.com/systems/i/software/globalization/pdf/cp00273z.
pdf</a>) it saves the "{" character in hex as "43".
<br />
<br />
Our problem now is we are attempting to access this data through a java
application residing on a Windows server. Because the DB2 field is setup as
CCSID 37 (<a class="jive-link-external"
href="http://www-03.ibm.com/systems/i/software/globalization/pdf/cp00037z.p
df">http://www-03.ibm.com/systems/i/software/globalization/pdf/cp00037z.
pdf</a>), our JDBC query returns that hex "43" value as a
different character instead of the "{". <br />
<br />

Have you considered changing the column definition to a Unicode encoding to 
match Java's unicode-based character processing?

You can use the CAST function to override the CCSID value:   cast(col1 as 
char(3) ccsid 1208)

rhaman@yahoo.com wrote:
> We have an application that resides on the iSeries that saves data to the
DB2. This application is running on many systems in multiple countries. For all
instances, the fields in DB2 were created with a CCSID of 37. When the
application reads from / saves to the DB2, it converts the characters to the
system's locale. So if the application is residing in Germany (CCSID 273 - <a
class="jive-link-external"
href="http://www-03.ibm.com/systems/i/software/globalization/pdf/cp00273z.p
df">http://www-03.ibm.com/systems/i/software/globalization/pdf/cp00273z.
pdf</a>) it saves the "{" character in hex as "43".
<br />
> <br />
> Our problem now is we are attempting to access this data through a java
application residing on a Windows server. Because the DB2 field is setup as
CCSID 37 (<a class="jive-link-external"
href="http://www-03.ibm.com/systems/i/software/globalization/pdf/cp00037z.p
df">http://www-03.ibm.com/systems/i/software/globalization/pdf/cp00037z.
pdf</a>), our JDBC query returns that hex "43" value as a
different character instead of the "{". <br />
> <br />

   It seems the problem is, that the data has been getting stored 
incorrectly.  If so, there is no fix except to correct the data. 
Attempting anything else will almost surely just further compound the 
data problem that already exists.

   The code point 0x43 stored in a CCSID 037 field is the character 
LA170000 "ä", not the character SM110000 "{" which is code
point 0xC0 in 
the CCSID 037.  Thus hopefully the JDBC request is returning the 
character LA170000 "ä" which is the code point 0x7B [in for example,

CCSID 437]
http://www-03.ibm.com/systems/i/software/globalization/pdf/cp00437z.pdf

   Assuming all similar instances of variant characters were converted 
by the application into CCSID &L [where &L is the proper CCSID for the 
locale], then copying all of the character data without any conversion 
into fields with the CCSID &L would have the proper data.  Then the data 
could optionally be copied again, but with conversion, back into the 
fields with the CCSID 037; thus effecting conversion of the variant 
character data, to properly match the CCSID of the field.  A /cheat/ may 
be available [again given already converted variant characters are the 
only problem], by CHGPF CCSID(&L) to the existing file.

   Regardless of what is done, the application needs to be corrected to 
function in a manner that honors the CCSID of the field into which the 
data is stored.  No attempt should be made to override the JDBC into 
giving the appearance of functioning when in fact it is not, even though 
there is a way to do so.  The /trick/ to effect such an override is 
actually a horrible idea; making the fields hex/ccsid-65535 which 
indicates no conversion should occur, and then configuring the data 
source to effect conversion between the EBCDIC and ASCII by /pretending/ 
the data is in the CCSID &L regardless that it is defined to transport 
unchanged.

Regards, Chuck
-- 
  All comments provided "as is" with no warranties of any kind 
whatsoever and may not represent positions, strategies, nor views of my 
employer

rhaman@yahoo.com wrote:
> We have an application that resides on the iSeries that saves data to
> the DB2. This application is running on many systems in multiple
> countries. For all instances, the fields in DB2 were created with a
> CCSID of 37. When the application reads from / saves to the DB2, it
> converts the characters to the system's locale. So if the application
> is residing in Germany CCSID 273 - 
> http://www-03.ibm.com/systems/i/software/globalization/pdf/cp00273z.pdf
> it saves the "{" character in hex as "43". 
> Our problem now is we are attempting to access this data through a java
> application residing on a Windows server. Because the DB2 field is
> setup as CCSID 37 - 
> http://www-03.ibm.com/systems/i/software/globalization/pdf/cp00037z.pdf
> our JDBC query returns that hex "43" value as a different
character
> instead of the "{".
> Is there a way to override the CCSID value that is being used with the

Thanks for your help. Due to time constraints we came up with a workaround for
now which involved getting the system CCSID and then using IBM's CharConverter
class on the column that was giving us the most issues. We are considering
changing table structures in the next version so we will probably include data
conversions in our plan.

Thanks again for your help.