pagesize increase or longvarchar

Naren Mehta

I have a table whose rowsize is nearing 4005 bytes and I want to
increase the length of one column. I know I have two ways to do this.
I can either increase the tablespace pagesize from 4K to 8K, or change
the column from being a varchar to longvarchar. Which is
better(performance wise)?

Also is it true that you cannot change the page sizes of tablespaces
on-the-fly -- you will have to export your data, drop all objects that
exist in the
tablespace, create new tablespaces with a larger page size, recreate
all
objects in the new tablespace, and the import your data.

Thanks in advance.

Nov 12 '05 #1

Pierre Saint-Jacques

If you're at V8.2, investigate whether or not the column change can be
done by looking at your docs. - I don't have them.
If you access the long varchar regularly thru your queries, you will pay
the following performance price.
2 I/O's per row. One to get the page, one to get the long varchar. That
one goes to agent private memory, not the buffer pool.

See below #######.
HTH, Pierre.

Naren Mehta wrote:

I have a table whose rowsize is nearing 4005 bytes and I want to
increase the length of one column. I know I have two ways to do this.
I can either increase the tablespace pagesize from 4K to 8K, or change
the column from being a varchar to longvarchar. Which is
better(performance wise)? ####### Yes this is true. Page size are fixed and cannot be altered. Also is it true that you cannot change the page sizes of tablespaces
on-the-fly -- you will have to export your data, drop all objects that
exist in the
tablespace, create new tablespaces with a larger page size, recreate
all
objects in the new tablespace, and the import your data.

Thanks in advance.

--
Pierre Saint-Jacques - Reply to: sescons at attglobal dot net
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.

Nov 12 '05 #2

Naren Mehta

Thanks a lot.

Pierre Saint-Jacques <se*****@attglobal.net> wrote in message news:<41**************@attglobal.net>...

If you're at V8.2, investigate whether or not the column change can be
done by looking at your docs. - I don't have them.
If you access the long varchar regularly thru your queries, you will pay
the following performance price.
2 I/O's per row. One to get the page, one to get the long varchar. That
one goes to agent private memory, not the buffer pool.

See below #######.
HTH, Pierre.

Naren Mehta wrote:

I have a table whose rowsize is nearing 4005 bytes and I want to
increase the length of one column. I know I have two ways to do this.
I can either increase the tablespace pagesize from 4K to 8K, or change
the column from being a varchar to longvarchar. Which is
better(performance wise)?

####### Yes this is true. Page size are fixed and cannot be altered.

Also is it true that you cannot change the page sizes of tablespaces
on-the-fly -- you will have to export your data, drop all objects that
exist in the
tablespace, create new tablespaces with a larger page size, recreate
all
objects in the new tablespace, and the import your data.

Thanks in advance.

Nov 12 '05 #3

Bob [IBM]

Pierre - one thing to remember is that LONG VARCHAR will be deprecated in upcoming
releases so it is not wise to continue using them unless absolutely necessary ... consider
the LOB type column of CLOB in that case if absolutely necessary.

--

Bob
Engagement Specialist - DB2 Information Management Software - IBM Software Group
IBM Toronto Lab
[My comments are solely my own and are not meant to represent an official IBM position -
ask my cat!]

"Pierre Saint-Jacques" <se*****@attglobal.net> wrote in message
news:41**************@attglobal.net...

If you're at V8.2, investigate whether or not the column change can be
done by looking at your docs. - I don't have them.
If you access the long varchar regularly thru your queries, you will pay
the following performance price.
2 I/O's per row. One to get the page, one to get the long varchar. That
one goes to agent private memory, not the buffer pool.

See below #######.
HTH, Pierre.

Naren Mehta wrote:

I have a table whose rowsize is nearing 4005 bytes and I want to
increase the length of one column. I know I have two ways to do this.
I can either increase the tablespace pagesize from 4K to 8K, or change
the column from being a varchar to longvarchar. Which is
better(performance wise)?

####### Yes this is true. Page size are fixed and cannot be altered.

Also is it true that you cannot change the page sizes of tablespaces
on-the-fly -- you will have to export your data, drop all objects that
exist in the
tablespace, create new tablespaces with a larger page size, recreate
all
objects in the new tablespace, and the import your data.

Thanks in advance.

--
Pierre Saint-Jacques - Reply to: sescons at attglobal dot net
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.

Nov 12 '05 #4