473,327 Members | 2,007 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

SOS for long varchar

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

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

"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

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

"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Steve Thorpe | last post by:
Hi I have have two linked SQL Servers and I am trying to get remote writes working correctly (fast). I have configured the DB link on both machines to: Point at each others DB. I have security...
1
by: Uthuras | last post by:
Greetings, Machine : Pentium IV Os Windows 2000 server Product : DB2 UDB Release : 7.2 We are fail to load the following data file format into db2 database table that has long varchar...
2
by: Alexandre H. Guerra | last post by:
I needed to log all statements executed during a period of time and now i need ordering the long varchar column in the statements monitor table (STMT_TEXT) Is there any flag to set to release...
1
by: Scav | last post by:
Helpful folks, I recently applied fixpacks to our test server to bring it from V8.1.3 up to V8.2.2. Everything seemed to work fine except a DCOM component that deals with Long Varchar column on...
0
by: JohnO | last post by:
Thanks to Serge and MarkB for recent tips and suggestions. Ive rolled together a few stored procedures to assist with creating audit triggers automagically. Hope someone finds this as useful as...
2
by: John Smith | last post by:
What is the difference? Is LONG VARCHAR only 28 bytes longer then VARCHAR? Is this the only difference?
7
by: Tzanko | last post by:
As we all know, there is a 8060 bytes size limit on SQL Server rows. I have a table which requires a number of text fields (5 or 6). Each of these text fields should support a max of 4000...
3
by: Okonita | last post by:
Hi everyone, I have been building a set of tables for a new database and having problem with tables having columns defined as Long Varchar. Can someone look this over and tell me what I am doing...
6
by: jephperro | last post by:
Hi there, I'm having a really tough time with a SQL statement and I am wondering if someone is able to help out or point me in the right direction. I have a table of names which can be very...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.