DB2 Universal Language support

[Kieran Green]

Greetings,

We are building an application written for Windows in C++ which uses
OLEDB to connect to AIX DB2 8.2. Our app stores all string data in
the wchar_t datatype, which generates dynamic SQL, typically with
bound parameters DBTYPE_WSTR, and so is a Unicode app.

We don't know whether to use the vargraphic datatype for storing
strings, or varchar, and which database character sets to support.

Option a - support only a UTF-8 database and use vargraphics.
Performance should be better since the app stores Unicode strings in
Unicode columns (UCS-2). However from a product perspective this is
limiting because customers may want to use, say IBM-1252, because they
want to! But they could be told that UTF-8 will store their data just
fine. Yes, we could also use vargraphics in any mbcs, but there are
conversion issues (control char's convert to SUB's in IBM-943).

Option b - support any database character set, and use varchar string
columns. This requires us to use the multiplier factor (e.g. 3 or 4)
for the column length (to support asian lang's when UTF-8 is chosen),
which heavily devours the limited rowsize in DB2 (you get deducted on
column creation time from the pagesize). Also going with a large
pagesize like 32K may hurt performance. Yes, we could make our code
choose whether to multiply or not depending on the language, but for
simplicity we don't. The UTF-8 is nice because it doesn't store a lot
of extra bytes as for vargraphics, when ascii is primarily stored
(saves disk space).

Our concern is that we support the most popular character set that
real people use. What is most prevalent? If we choose option a, will
there be a customer that balks because they want IBM-943?
Specifically: Will customers be perfectly happy with option a, or
will some demand to use other mbcs's (such as IBM-943), or would they
prefer to have the varchar's?

Thanks,
Kieran

[Mark Yudkin]

vargraphic is for DBCS, not Unicode. Use a Unicode databases with
(var)chars. (Var)chars in a Unicode db are actually ucs-2, although it
appears counterintuitive!

"Kieran Green" <ki***********@yahoo.com> wrote in message
news:ed*************************@posting.google.co m...

Greetings,

We are building an application written for Windows in C++ which uses
OLEDB to connect to AIX DB2 8.2. Our app stores all string data in
the wchar_t datatype, which generates dynamic SQL, typically with
bound parameters DBTYPE_WSTR, and so is a Unicode app.

We don't know whether to use the vargraphic datatype for storing
strings, or varchar, and which database character sets to support.

Option a - support only a UTF-8 database and use vargraphics.
Performance should be better since the app stores Unicode strings in
Unicode columns (UCS-2). However from a product perspective this is
limiting because customers may want to use, say IBM-1252, because they
want to! But they could be told that UTF-8 will store their data just
fine. Yes, we could also use vargraphics in any mbcs, but there are
conversion issues (control char's convert to SUB's in IBM-943).

Option b - support any database character set, and use varchar string
columns. This requires us to use the multiplier factor (e.g. 3 or 4)
for the column length (to support asian lang's when UTF-8 is chosen),
which heavily devours the limited rowsize in DB2 (you get deducted on
column creation time from the pagesize). Also going with a large
pagesize like 32K may hurt performance. Yes, we could make our code
choose whether to multiply or not depending on the language, but for
simplicity we don't. The UTF-8 is nice because it doesn't store a lot
of extra bytes as for vargraphics, when ascii is primarily stored
(saves disk space).

Our concern is that we support the most popular character set that
real people use. What is most prevalent? If we choose option a, will
there be a customer that balks because they want IBM-943?
Specifically: Will customers be perfectly happy with option a, or
will some demand to use other mbcs's (such as IBM-943), or would they
prefer to have the varchar's?

Thanks,
Kieran

[Kieran Green]

MS SQL Server uses nvarchar for Unicode. Isn't the analogy: MSSQL's
nvarchar = DB2's vargraphic? Assuming basic "Unicode" is two bytes,
and there are schemes to encode Unicode, such as UTF-8, wouldn't
MSSQL's nvarchar and DB2's vargraphic both store double-byte Unicode
in the basic form of UCS-2?

The DB2 docs state: "When a Unicode database is created, CHAR,
VARCHAR, [etc] data are stored in UTF-8, and GRAPHIC, VARGRAPHIC,
[etc] data are stored in UCS-2." It would seem that when our Unicode
OLEDB app inserts into a varchar column (DB2 database is created as
UTF-8), the Unicode data gets encoded and stored as UTF-8. Is that
right?

I've heard of "pure DBCS", in reference to IBM Asian character sets.
By "DBCS", do you mean one of the CCSID numbers relating to specific
IBM language encodings in double-byte? If so, then vargraphics would
be great for "DBCS", but if the vargraphic is UCS-2, isn't it as good
a receptical to store Unicode, as is MSSQL's nvarchar?

I'm also concerned about glossing over subtleties with language
encodings if we employ Option a or b, such as loss of support of
characters. So if anyone has some real expertise in use-cases in
Options a or b, that would be useful.

Much Thanks!
Kieran

"Mark Yudkin" <my***********************@boing.org> wrote in message news:<co**********@ngspool-d02.news.aol.com>...

vargraphic is for DBCS, not Unicode. Use a Unicode databases with
(var)chars. (Var)chars in a Unicode db are actually ucs-2, although it
appears counterintuitive!

"Kieran Green" <ki***********@yahoo.com> wrote in message
news:ed*************************@posting.google.co m...

Greetings,

We are building an application written for Windows in C++ which uses
OLEDB to connect to AIX DB2 8.2. Our app stores all string data in
the wchar_t datatype, which generates dynamic SQL, typically with
bound parameters DBTYPE_WSTR, and so is a Unicode app.

We don't know whether to use the vargraphic datatype for storing
strings, or varchar, and which database character sets to support.

Option a - support only a UTF-8 database and use vargraphics.
Performance should be better since the app stores Unicode strings in
Unicode columns (UCS-2). However from a product perspective this is
limiting because customers may want to use, say IBM-1252, because they
want to! But they could be told that UTF-8 will store their data just
fine. Yes, we could also use vargraphics in any mbcs, but there are
conversion issues (control char's convert to SUB's in IBM-943).

Option b - support any database character set, and use varchar string
columns. This requires us to use the multiplier factor (e.g. 3 or 4)
for the column length (to support asian lang's when UTF-8 is chosen),
which heavily devours the limited rowsize in DB2 (you get deducted on
column creation time from the pagesize). Also going with a large
pagesize like 32K may hurt performance. Yes, we could make our code
choose whether to multiply or not depending on the language, but for
simplicity we don't. The UTF-8 is nice because it doesn't store a lot
of extra bytes as for vargraphics, when ascii is primarily stored
(saves disk space).

Our concern is that we support the most popular character set that
real people use. What is most prevalent? If we choose option a, will
there be a customer that balks because they want IBM-943?
Specifically: Will customers be perfectly happy with option a, or
will some demand to use other mbcs's (such as IBM-943), or would they
prefer to have the varchar's?

Thanks,
Kieran

[Mark Yudkin]

DB2 does not have an equivalent to MS SQL's nvarchar - DB2 does not have a
Unicode data type. I too would like to see such a solution, but that's not
the way IBM decided to do things. Vargraphic is not Unicode, it is DBCS, an
earlier standard for handling CJK languages.

Provided your database has a Unicode code page, the data will be Unicode.
The internal encoding is not really important.

As I implied, I don't recommend either of your options a or b.

"Kieran Green" <ki***********@yahoo.com> wrote in message
news:ed*************************@posting.google.co m...

MS SQL Server uses nvarchar for Unicode. Isn't the analogy: MSSQL's
nvarchar = DB2's vargraphic? Assuming basic "Unicode" is two bytes,
and there are schemes to encode Unicode, such as UTF-8, wouldn't
MSSQL's nvarchar and DB2's vargraphic both store double-byte Unicode
in the basic form of UCS-2?

The DB2 docs state: "When a Unicode database is created, CHAR,
VARCHAR, [etc] data are stored in UTF-8, and GRAPHIC, VARGRAPHIC,
[etc] data are stored in UCS-2." It would seem that when our Unicode
OLEDB app inserts into a varchar column (DB2 database is created as
UTF-8), the Unicode data gets encoded and stored as UTF-8. Is that
right?

I've heard of "pure DBCS", in reference to IBM Asian character sets.
By "DBCS", do you mean one of the CCSID numbers relating to specific
IBM language encodings in double-byte? If so, then vargraphics would
be great for "DBCS", but if the vargraphic is UCS-2, isn't it as good
a receptical to store Unicode, as is MSSQL's nvarchar?

I'm also concerned about glossing over subtleties with language
encodings if we employ Option a or b, such as loss of support of
characters. So if anyone has some real expertise in use-cases in
Options a or b, that would be useful.

Much Thanks!
Kieran

"Mark Yudkin" <my***********************@boing.org> wrote in message
news:<co**********@ngspool-d02.news.aol.com>...

vargraphic is for DBCS, not Unicode. Use a Unicode databases with
(var)chars. (Var)chars in a Unicode db are actually ucs-2, although it
appears counterintuitive!

"Kieran Green" <ki***********@yahoo.com> wrote in message
news:ed*************************@posting.google.co m...

> Greetings,
>
> We are building an application written for Windows in C++ which uses
> OLEDB to connect to AIX DB2 8.2. Our app stores all string data in
> the wchar_t datatype, which generates dynamic SQL, typically with
> bound parameters DBTYPE_WSTR, and so is a Unicode app.
>
> We don't know whether to use the vargraphic datatype for storing
> strings, or varchar, and which database character sets to support.
>
> Option a - support only a UTF-8 database and use vargraphics.
> Performance should be better since the app stores Unicode strings in
> Unicode columns (UCS-2). However from a product perspective this is
> limiting because customers may want to use, say IBM-1252, because they
> want to! But they could be told that UTF-8 will store their data just
> fine. Yes, we could also use vargraphics in any mbcs, but there are
> conversion issues (control char's convert to SUB's in IBM-943).
>
> Option b - support any database character set, and use varchar string
> columns. This requires us to use the multiplier factor (e.g. 3 or 4)
> for the column length (to support asian lang's when UTF-8 is chosen),
> which heavily devours the limited rowsize in DB2 (you get deducted on
> column creation time from the pagesize). Also going with a large
> pagesize like 32K may hurt performance. Yes, we could make our code
> choose whether to multiply or not depending on the language, but for
> simplicity we don't. The UTF-8 is nice because it doesn't store a lot
> of extra bytes as for vargraphics, when ascii is primarily stored
> (saves disk space).
>
> Our concern is that we support the most popular character set that
> real people use. What is most prevalent? If we choose option a, will
> there be a customer that balks because they want IBM-943?
> Specifically: Will customers be perfectly happy with option a, or
> will some demand to use other mbcs's (such as IBM-943), or would they
> prefer to have the varchar's?
>
> Thanks,
> Kieran