SEO

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.

"James o'konnor" <jok@thows.com> wrote in message
news:dkgfli$4nv$1@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

"Rhino" <no.offline.contact.please@nospam.com> wrote in message
news:437bf.14807$J14.604861@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.

Rhino wrote:
> "James o'konnor" <jok@thows.com> wrote in message
> news:dkgfli$4nv$1@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

"Mark A" <nobody@nowhere.com> wrote in message
news:-_ydnetQ6tUUY_HeRVn-pA@comcast.com...
> "Rhino" <no.offline.contact.please@nospam.com> wrote in message
> news:437bf.14807$J14.604861@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

"Serge Rielau" <srielau@ca.ibm.com> wrote in message
news:3t4vveFqtvcoU1@individual.net...
> Rhino wrote:
> > "James o'konnor" <jok@thows.com> wrote in message
> > news:dkgfli$4nv$1@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

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

"Serge Rielau" <srielau@ca.ibm.com> wrote in message
news:3t7jpjFrd8emU1@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