Column size for Multibyte data

[sameer_deshpande]

Hi,

I have DB2 UTF-8 database (codeset IBM-1252) on Windows 2000 Server. I
need to store multi-byte contents. How do I calculate size of the
column to store multi-byte information?

F.ex: I cant have a column with size VARCHAR(10) bytes to store 10
multi-byte characters, because its going to run out of column size.
Do I have to simply double/tripple the column size simply by guessing?

In case of Oracle UTF-8 database I can have a table defination like

CREATE TABLE T (name VARCHAR2(10 CHARACTER));

Here I have a guarantee of storing 10 multi-byte characters. How do I
do the same in DB2.

Thanks

Sameer

[Serge Rielau]

sameer_deshpande wrote:

Hi,

I have DB2 UTF-8 database (codeset IBM-1252) on Windows 2000 Server. I
need to store multi-byte contents. How do I calculate size of the
column to store multi-byte information?

F.ex: I cant have a column with size VARCHAR(10) bytes to store 10
multi-byte characters, because its going to run out of column size.
Do I have to simply double/tripple the column size simply by guessing?

In case of Oracle UTF-8 database I can have a table defination like

CREATE TABLE T (name VARCHAR2(10 CHARACTER));

Here I have a guarantee of storing 10 multi-byte characters. How do I
do the same in DB2.

Yes for VARCHAR you triple.
Alternatively you can use VARGRAPHIC which uses UCS-2 instead of UTF-8.
VARGRAPHIC(10) fits exactly 10 characters, but will always use 2 bytes
per character.
If you want to enforce a 10 character limit for VARCHAR you can use a
check constraint using the new character length function in DB2 9

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Dave Hughes]

sameer_deshpande wrote:

Hi,

I have DB2 UTF-8 database (codeset IBM-1252) on Windows 2000 Server. I
need to store multi-byte contents.

Are you sure? Codeset 1252 is Win-1252, basically a modification of the
ISO-8859-1 single byte codepage. UTF-8 is codeset 1208 in DB2. You'll
need codset 1208 if you want to use the GRAPHIC datatype as per Serge's
suggestion. If you created the database with something like:

CREATE DB MYDB CODESET UTF-8 TERRITORY ...

Then you should be fine. If you're not sure, check the default codepage
of the database with the following query:

SELECT CODEPAGE
FROM SYSCAT.DATATYPES
WHERE TYPESCHEMA = 'SYSIBM' AND TYPENAME = 'VARCHAR'

If the result isn't 1208, you've got a problem. As far as I know, you
can't change the codepage of an existing database.
HTH,

Dave.

--

[sameer_deshpande]

On 9 Mai, 18:52, "Dave Hughes" <[e-mail]:

sameer_deshpande wrote:

Hi,

I have DB2 UTF-8 database (codeset IBM-1252) on Windows 2000 Server. I
need to storemulti-bytecontents.

Are you sure? Codeset 1252 is Win-1252, basically a modification of the
ISO-8859-1 single byte codepage. UTF-8 is codeset 1208 in DB2. You'll
need codset 1208 if you want to use the GRAPHIC datatype as per Serge's
suggestion. If you created the database with something like:

CREATE DB MYDB CODESET UTF-8 TERRITORY ...

Then you should be fine. If you're not sure, check the default codepage
of the database with the following query:

SELECT CODEPAGE
FROM SYSCAT.DATATYPES
WHERE TYPESCHEMA = 'SYSIBM' AND TYPENAME = 'VARCHAR'

If the result isn't 1208, you've got a problem. As far as I know, you
can't change the codepage of an existing database.

HTH,

Dave.

--

You are right. Result of the codepage column is indeed 1208. I was
wrong to specify codeset IBM-1252.

In UTF-8 database if I create a VARGRAPHIC column with size 10, then I
am able to insert 10 multi-byte charaters. What is the max column
length allowed for VARGRAPHIC data type. I could go upto
VARGRAPHIC(16336). Is this correct - 16336?
Thanks

Sameer

[Serge Rielau]

sameer_deshpande wrote:

On 9 Mai, 18:52, "Dave Hughes" <[e-mail]:

>sameer_deshpande wrote:

>>Hi,
I have DB2 UTF-8 database (codeset IBM-1252) on Windows 2000 Server. I
need to storemulti-bytecontents.

Are you sure? Codeset 1252 is Win-1252, basically a modification of the
ISO-8859-1 single byte codepage. UTF-8 is codeset 1208 in DB2. You'll
need codset 1208 if you want to use the GRAPHIC datatype as per Serge's
suggestion. If you created the database with something like:

CREATE DB MYDB CODESET UTF-8 TERRITORY ...

Then you should be fine. If you're not sure, check the default codepage
of the database with the following query:

SELECT CODEPAGE
FROM SYSCAT.DATATYPES
WHERE TYPESCHEMA = 'SYSIBM' AND TYPENAME = 'VARCHAR'

If the result isn't 1208, you've got a problem. As far as I know, you
can't change the codepage of an existing database.

HTH,

Dave.

--

You are right. Result of the codepage column is indeed 1208. I was
wrong to specify codeset IBM-1252.

In UTF-8 database if I create a VARGRAPHIC column with size 10, then I
am able to insert 10 multi-byte charaters. What is the max column
length allowed for VARGRAPHIC data type. I could go upto
VARGRAPHIC(16336). Is this correct - 16336?

http://publib.boulder.ibm.com/infoce...c/r0001029.htm

Correct. Of course storing such beats will require a 32K page size and a
one column table...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Dave Hughes]

Serge Rielau wrote:

sameer_deshpande wrote:

[snip]

In UTF-8 database if I create a VARGRAPHIC column with size 10,
then I am able to insert 10 multi-byte charaters. What is the max
column length allowed for VARGRAPHIC data type. I could go upto
VARGRAPHIC(16336). Is this correct - 16336?

http://publib.boulder.ibm.com/infoce.../com.ibm.db2.u
db.admin.doc/doc/r0001029.htm

Correct. Of course storing such beats will require a 32K page size
and a one column table...

You may also wish to look into the DBCLOB data type[1] if you have a
large amount of double-byte character data to store (DBCLOB is to CLOB
as VARGRAPHIC is to VARCHAR).
[1] http://tinyurl.com/yseay6
HTH,

Dave.

--