By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,743 Members | 804 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,743 IT Pros & Developers. It's quick & easy.

Column size for Multibyte data

P: n/a
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

May 9 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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
May 9 '07 #2

P: n/a
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.

--

May 9 '07 #3

P: n/a
On 9 Mai, 18:52, "Dave Hughes" <d...@waveform.plus.comwrote:
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

May 15 '07 #4

P: n/a
sameer_deshpande wrote:
On 9 Mai, 18:52, "Dave Hughes" <d...@waveform.plus.comwrote:
>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
May 15 '07 #5

P: n/a
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.

--

May 15 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.