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

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

P: n/a
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.
Feb 24 '08 #1
Share this Question
Share on Google+
6 Replies


P: n/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.
Feb 24 '08 #2

P: n/a
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" <nob...@nowhere.comwrote:
<pravin.prabha...@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.
Feb 24 '08 #3

P: n/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.
Feb 24 '08 #4

P: n/a
On Feb 24, 10:31*am, "Mark A" <nob...@nowhere.comwrote:
<pravin.prabha...@gmail.comwrote 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.
Feb 24 '08 #5

P: n/a
Thanks for the reply. Your response is greatly appreciated. All tables
have multi million rows....

On Feb 24, 10:31*am, "Mark A" <nob...@nowhere.comwrote:
<pravin.prabha...@gmail.comwrote 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.
Feb 24 '08 #6

P: n/a
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
Feb 27 '08 #7

This discussion thread is closed

Replies have been disabled for this discussion.