SQL1139N The total size of the table space is too big.

[pravin.prabhakar]

Hello,

Our 4K index tablespace has grown to 64GB. It has maxed out. I want to
increase the page size from 4 to 16K. What will be the safest method?
Any suggestions? We are in DB2 UDB 8.2 Sun Solaris 8.

Thanks.

[Mark A]

<pr**************@gmail.comwrote in message
news:e5**********************************@72g2000h su.googlegroups.com...

Hello,

Our 4K index tablespace has grown to 64GB. It has maxed out. I want to
increase the page size from 4 to 16K. What will be the safest method?
Any suggestions? We are in DB2 UDB 8.2 Sun Solaris 8.

Thanks.

You would have to drop the tables and recreate them so that the indexes
point to a different tablespace. Obviously, you would need to unload the
data before hand, and then reload it afterwards.

For an OLTP system, you would better off dropping just some of the larger
tables (with large indexes) and specifying a new 4K tablespace for the
indexes in those tables, since a 4K tablespace (and bufferpool) are probably
advisable over a larger page size.

If you have a data warehouse, then you may want to use a larger page size.

[pravin.prabhakar]

Thanks for the reply. Since the application is Siebel, I dont think we
will have the flexibility of dropping the table...

On Feb 24, 9:20*am, "Mark A" <[e-mail]:

<[e-mail] in message

news:e5**********************************@72g2000h su.googlegroups.com...

Hello,

Our 4K index tablespace has grown to 64GB. It has maxed out. I want to
increase the page size from 4 to 16K. What will be the safest method?
Any suggestions? We are in DB2 UDB 8.2 Sun Solaris 8.

Thanks.

You would have to drop the tables and recreate them so that the indexes
point to a different tablespace. Obviously, you would need to unload the
data before hand, and then reload it afterwards.

For an OLTP system, you would better off dropping just some of the larger
tables (with large indexes) and specifying a new 4K tablespace for the
indexes in those tables, since a 4K tablespace (and bufferpool) are probably
advisable over a larger page size.

If you have a data warehouse, then you may want to use a larger page size.

[Mark A]

><pr**************@gmail.comwrote in message

>news:786e340b-8218-4ac6-966f->7e**********@62g2000hsn.googlegroups.com...
Thanks for the reply. Since the application is Siebel, I dont think we
will have the flexibility of dropping the table...

Read my lips. You have no choice.

You cannot move the table or indexes to a new tablespace unless you drop and
recreate the table. So long as you do this when the application is not
running (and you have a competent DB2 DBA who will take into account the
referential integrity issues) it does not matter whether the application is
Siebel or anything else.

[pravin.prabhakar]

On Feb 24, 10:31*am, "Mark A" <[e-mail]:

<[e-mail] in message
news:786e340b-8218-4ac6-966f->7eea6112b__BEGIN_MASK_n#9g02mG7!__...__********** ************@62g2000hsn.googlegroups.com...
Thanks for the reply. Since the application is Siebel, I dont think we
will have the flexibility of dropping the table...

Read my lips. You have no choice.

You cannot move the table or indexes to a new tablespace unless you drop and
recreate the table. So long as you do this when the application is not
running (and you have a competent DB2 DBA who will take into account the
referential integrity issues) it does not matter whether the application is
Siebel or anything else.

Thanks for your reply. Greatly appreciated. I guess we dont have other
options.

[pravin.prabhakar]

Thanks for the reply. Your response is greatly appreciated. All tables
have multi million rows....

On Feb 24, 10:31*am, "Mark A" <[e-mail]:

<[e-mail] in message
news:786e340b-8218-4ac6-966f->7eea6112b__BEGIN_MASK_n#9g02mG7!__...__********** ************@62g2000hsn.googlegroups.com...
Thanks for the reply. Since the application is Siebel, I dont think we
will have the flexibility of dropping the table...

Read my lips. You have no choice.

You cannot move the table or indexes to a new tablespace unless you drop and
recreate the table. So long as you do this when the application is not
running (and you have a competent DB2 DBA who will take into account the
referential integrity issues) it does not matter whether the application is
Siebel or anything else.

[Lew]

A standard practice we used to use for situations like this for moving
tables to a new tablespace is to create a table with the exact same
definition and indexes in a new tablespace with a different name.
Then we would do a load from cursor into the new table from the
existing table, rename the old table to something and then rename the
new table to the proper table name.

create table tab1_new in ts_new
declare cursor mycur for select * from tab1
load from mycur of cursor insert into tab1_new
rename table tab1 to tab1_old
rename table tab1_new to tab1
drop table tab1_old

This might make it a bit less painful