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

SOS for long varchar

P: n/a
hello.
i have the next for create one table into db2

CREATE TABLE "MYSQUEMA"."TABLADEMO" (
"ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (
START WITH +0
INCREMENT BY +1
MINVALUE +0
MAXVALUE +2147483647
NO CYCLE
NO CACHE
NO ORDER ) ,
"TITULO" VARCHAR(250) ,
"COMENTARIO" LONG VARCHAR,
"IDIOMA" VARCHAR(5) NOT NULL ,
"ESTADO" VARCHAR(50) NOT NULL WITH DEFAULT 'true' )
IN "MISPACIO" ;

the problem is that i must to create one long varchar, or using oher type of
field, with 6000 for the max size.
is this posible? how?

i cannot use clob, blob... field type.
Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a

"James o'konnor" <jo*@thows.com> wrote in message
news:dk**********@domitilla.aioe.org...
hello.
i have the next for create one table into db2

CREATE TABLE "MYSQUEMA"."TABLADEMO" (
"ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (
START WITH +0
INCREMENT BY +1
MINVALUE +0
MAXVALUE +2147483647
NO CYCLE
NO CACHE
NO ORDER ) ,
"TITULO" VARCHAR(250) ,
"COMENTARIO" LONG VARCHAR,
"IDIOMA" VARCHAR(5) NOT NULL ,
"ESTADO" VARCHAR(50) NOT NULL WITH DEFAULT 'true' )
IN "MISPACIO" ;

the problem is that i must to create one long varchar, or using oher type of field, with 6000 for the max size.
is this posible? how?

i cannot use clob, blob... field type.

If you define a column as LONG VARCHAR, you can't set a maximum size, like
6000 characters. When a column is defined as LONG VARCHAR, DB2 sets the
length of the column to whatever space is available once the fixed-length
columns have been considered. In your case, all but your Comentario column
are fixed length and their lengths add up to 313 bytes. If you are using a
4K page size, the space available for your LONG VARCHAR will be roughly
4096 - 313 = 3783 (minus some overhead). Therefore, if you want to store up
to 6000 characters in the Comentario column, you will need to use a page
size of at least 8K.

If you use 8K pages, the space available for the LONG VARCHAR will be 8192 -
313 = 7779 (minus some overhead). This, of course, is more than the 6000
characters you expect to store. If you can live with this, then proceed. You
will waste quite a lot of space but that might not be a problem since disk
space is relatively inexpensive these days. However, you will also find that
having a LONG VARCHAR in a table makes it impossible to add new columns to
the table. The LONG VARCHAR effectively means that every row fills a page
all by itself and therefore, no columns can be added to the table since the
existing rows already fill the page.

If that is not acceptable, I suggest that you may want make the Comentario
column a VARCHAR(6000). Again, the page size you use will have to be at
least 8K, otherwise the creation of the table will fail.

Rhino
Nov 12 '05 #2

P: n/a
"Rhino" <no***********************@nospam.com> wrote in message
news:43********************@news20.bellglobal.com. ..
<snip>
If that is not acceptable, I suggest that you may want make the Comentario
column a VARCHAR(6000). Again, the page size you use will have to be at
least 8K, otherwise the creation of the table will fail.

Rhino

Regular varchar will hold almost up to 32K and will perform much better.
This is because long varchar cannot use bufferpools and all I/O is a direct
read/write from/to disk.
Nov 12 '05 #3

P: n/a
Rhino wrote:
"James o'konnor" <jo*@thows.com> wrote in message
news:dk**********@domitilla.aioe.org...
hello.
i have the next for create one table into db2

CREATE TABLE "MYSQUEMA"."TABLADEMO" (
"ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (
START WITH +0
INCREMENT BY +1
MINVALUE +0
MAXVALUE +2147483647
NO CYCLE
NO CACHE
NO ORDER ) ,
"TITULO" VARCHAR(250) ,
"COMENTARIO" LONG VARCHAR,
"IDIOMA" VARCHAR(5) NOT NULL ,
"ESTADO" VARCHAR(50) NOT NULL WITH DEFAULT 'true' )
IN "MISPACIO" ;

the problem is that i must to create one long varchar, or using oher type


of
field, with 6000 for the max size.
is this posible? how?

i cannot use clob, blob... field type.


If you define a column as LONG VARCHAR, you can't set a maximum size, like
6000 characters. When a column is defined as LONG VARCHAR, DB2 sets the
length of the column to whatever space is available once the fixed-length
columns have been considered. In your case, all but your Comentario column
are fixed length and their lengths add up to 313 bytes. If you are using a
4K page size, the space available for your LONG VARCHAR will be roughly
4096 - 313 = 3783 (minus some overhead). Therefore, if you want to store up
to 6000 characters in the Comentario column, you will need to use a page
size of at least 8K.

If you use 8K pages, the space available for the LONG VARCHAR will be 8192 -
313 = 7779 (minus some overhead). This, of course, is more than the 6000
characters you expect to store. If you can live with this, then proceed. You
will waste quite a lot of space but that might not be a problem since disk
space is relatively inexpensive these days. However, you will also find that
having a LONG VARCHAR in a table makes it impossible to add new columns to
the table. The LONG VARCHAR effectively means that every row fills a page
all by itself and therefore, no columns can be added to the table since the
existing rows already fill the page.

If that is not acceptable, I suggest that you may want make the Comentario
column a VARCHAR(6000). Again, the page size you use will have to be at
least 8K, otherwise the creation of the table will fail.

On which platform is this? On DB2 for LUW LONG VARCHAR is the precursor
of CLOB. It can be up to 32K big AFAIK consuming as much space as the
equivalent CLOB(32k) in the row).
The difference between CLOB and LONG VARCHAR is that LONG VARCHAR is
"stabilized" which is one step away from deprecated.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #4

P: n/a

"Mark A" <no****@nowhere.com> wrote in message
news:-_********************@comcast.com...
"Rhino" <no***********************@nospam.com> wrote in message
news:43********************@news20.bellglobal.com. ..
<snip>
If that is not acceptable, I suggest that you may want make the Comentario column a VARCHAR(6000). Again, the page size you use will have to be at
least 8K, otherwise the creation of the table will fail.

Rhino
Regular varchar will hold almost up to 32K and will perform much better.
This is because long varchar cannot use bufferpools and all I/O is a

direct read/write from/to disk.

Agreed. That's why I suggested VARCHAR(x) instead of LONG VARCHAR. The size
limit of 6000 is based on what the original poster said in his question; he
didn't think the value would ever exceed that size.

Rhino
Nov 12 '05 #5

P: n/a

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3t************@individual.net...
Rhino wrote:
"James o'konnor" <jo*@thows.com> wrote in message
news:dk**********@domitilla.aioe.org...
hello.
i have the next for create one table into db2

CREATE TABLE "MYSQUEMA"."TABLADEMO" (
"ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (
START WITH +0
INCREMENT BY +1
MINVALUE +0
MAXVALUE +2147483647
NO CYCLE
NO CACHE
NO ORDER ) ,
"TITULO" VARCHAR(250) ,
"COMENTARIO" LONG VARCHAR,
"IDIOMA" VARCHAR(5) NOT NULL ,
"ESTADO" VARCHAR(50) NOT NULL WITH DEFAULT 'true' )
IN "MISPACIO" ;

the problem is that i must to create one long varchar, or using oher
type
of
field, with 6000 for the max size.
is this posible? how?

i cannot use clob, blob... field type.

If you define a column as LONG VARCHAR, you can't set a maximum size, like 6000 characters. When a column is defined as LONG VARCHAR, DB2 sets the
length of the column to whatever space is available once the fixed-length columns have been considered. In your case, all but your Comentario column are fixed length and their lengths add up to 313 bytes. If you are using a 4K page size, the space available for your LONG VARCHAR will be roughly
4096 - 313 = 3783 (minus some overhead). Therefore, if you want to store up to 6000 characters in the Comentario column, you will need to use a page
size of at least 8K.

If you use 8K pages, the space available for the LONG VARCHAR will be 8192 - 313 = 7779 (minus some overhead). This, of course, is more than the 6000
characters you expect to store. If you can live with this, then proceed. You will waste quite a lot of space but that might not be a problem since disk space is relatively inexpensive these days. However, you will also find that having a LONG VARCHAR in a table makes it impossible to add new columns to the table. The LONG VARCHAR effectively means that every row fills a page all by itself and therefore, no columns can be added to the table since the existing rows already fill the page.

If that is not acceptable, I suggest that you may want make the Comentario column a VARCHAR(6000). Again, the page size you use will have to be at
least 8K, otherwise the creation of the table will fail.

On which platform is this? On DB2 for LUW LONG VARCHAR is the precursor
of CLOB. It can be up to 32K big AFAIK consuming as much space as the
equivalent CLOB(32k) in the row).


The question stated that he didn't expect the column to ever hold more than
6000 characters. That's why I proposed a definition of VARCHAR(6000).
The difference between CLOB and LONG VARCHAR is that LONG VARCHAR is
"stabilized" which is one step away from deprecated.

I haven't heard this term before. Should we be surprised if LONG VARCHAR
disappears in Version 9 or 10? I realize that you probably can't say
categorically that LONG VARCHAR will disappear at such and such a point
since the planners could always change their minds....

As for CLOBs, the original poster ruled them out without explaining why so I
confined myself to talking about LONG VARCHAR and VARCHAR(x).

Rhino
Nov 12 '05 #6

P: n/a
Rhino wrote:
column a VARCHAR(6000). Again, the page size you use will have to be at
least 8K, otherwise the creation of the table will fail.

On which platform is this? On DB2 for LUW LONG VARCHAR is the precursor
of CLOB. It can be up to 32K big AFAIK consuming as much space as the
equivalent CLOB(32k) in the row).

The question stated that he didn't expect the column to ever hold more than
6000 characters. That's why I proposed a definition of VARCHAR(6000).

Reread the post. You are right. I mistakingly thought you said it
required 8k pagesize for a LONG VARCHAR.
The difference between CLOB and LONG VARCHAR is that LONG VARCHAR is
"stabilized" which is one step away from deprecated.

I haven't heard this term before. Should we be surprised if LONG VARCHAR
disappears in Version 9 or 10? I realize that you probably can't say
categorically that LONG VARCHAR will disappear at such and such a point
since the planners could always change their minds....

Stabilized means that the property is not integrated into new SQL
features. Existing support remains, but it's not enhanced.
The same is true for e.g. REXX (much to the dismay of some in this group
as I know).
Deprecated is stronger than stabilized. I think DARI style procedures
are deprecated, meaning support will be removed in a future version.
I have no indication that the LONG types will be deprecated anytime soon.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #7

P: n/a

"Serge Rielau" <sr*****@ca.ibm.com> wrote in message
news:3t************@individual.net...
Rhino wrote:
column a VARCHAR(6000). Again, the page size you use will have to be at
least 8K, otherwise the creation of the table will fail.
On which platform is this? On DB2 for LUW LONG VARCHAR is the precursor
of CLOB. It can be up to 32K big AFAIK consuming as much space as the
equivalent CLOB(32k) in the row). The question stated that he didn't expect the column to ever hold more than
6000 characters. That's why I proposed a definition of VARCHAR(6000).

Reread the post. You are right. I mistakingly thought you said it
required 8k pagesize for a LONG VARCHAR.

Nope; I just meant that if he wanted a VARCHAR(6000) column, it wouldn't
work if his page size was 4K so he'd have to increase the page size to at
least 8K :-)
The difference between CLOB and LONG VARCHAR is that LONG VARCHAR is
"stabilized" which is one step away from deprecated.

I haven't heard this term before. Should we be surprised if LONG VARCHAR
disappears in Version 9 or 10? I realize that you probably can't say
categorically that LONG VARCHAR will disappear at such and such a point
since the planners could always change their minds....

Stabilized means that the property is not integrated into new SQL
features. Existing support remains, but it's not enhanced.


Ahh, interesting....
The same is true for e.g. REXX (much to the dismay of some in this group
as I know).
This is the first I'd heard about REXX support being stabilized. I have to
admit I'm a bit disappointed to hear that. I used REXX for the major
component of a project I did several years back and really came to love that
language; I loved it when I found out I could use DB2 with REXX. But I've
moved on to Java now so my disappointment about the status of REXX is really
just nostalgia; it won't really affect me at all.
Deprecated is stronger than stabilized. I think DARI style procedures
are deprecated, meaning support will be removed in a future version.
I have no indication that the LONG types will be deprecated anytime soon.

Thanks for explaining that.

I, for one, would not miss the disappearance of LONG datatypes one iota.
I've always disliked them due to the impossibility of adding columns to a
table that used LONG datatypes, as well as the inherent space wasted by
them. Frankly, I've never quite seen the point in having them in the first
place. Still, I suppose some people use them so it's good to know what the
future holds for them.

Rhino
Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.