Column size for Multibyte data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sameer_deshpande - 09 May 2007 10:08 GMT

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

Reply to this Message

Serge Rielau - 09 May 2007 12:41 GMT

> Hi,
>
[quoted text clipped - 12 lines]
> 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

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Dave Hughes - 09 May 2007 17:52 GMT

> 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.

--

Reply to this Message

sameer_deshpande - 15 May 2007 10:34 GMT

> > Hi,
>
[quoted text clipped - 23 lines]
>
> --

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

Reply to this Message

Serge Rielau - 15 May 2007 13:23 GMT

>>> Hi,
>>> I have DB2 UTF-8 database (codeset IBM-1252) on Windows 2000 Server. I
[quoted text clipped - 29 lines]
> length allowed for VARGRAPHIC data type. I could go upto
> VARGRAPHIC(16336). Is this correct - 16336?

http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.d
oc/doc/r0001029.htm

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

Cheers
Serge

Signature

Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to this Message

Dave Hughes - 15 May 2007 16:02 GMT

[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
[quoted text clipped - 5 lines]
> 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.

--

Reply to this Message