Dropping tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ross Mallett - 21 Dec 2005 05:35 GMT

I have to drop large numbers of test tables (> 200) fairly often. This
operation seems to be very slow.

Is there some way to speed up the process of dropping tables?

Reply to this Message

Phil Sherman - 21 Dec 2005 06:36 GMT

Have you tried placing them in their own tablespace and dropping the
tablespace?

Phil Sherman

> I have to drop large numbers of test tables (> 200) fairly often. This
> operation seems to be very slow.
>
> Is there some way to speed up the process of dropping tables?

Reply to this Message

Ross Mallett - 11 Jan 2006 06:39 GMT

> Have you tried placing them in their own tablespace and dropping the
> tablespace?
[quoted text clipped - 5 lines]
>>
>> Is there some way to speed up the process of dropping tables?

Yes.

It doesn't seem to be any faster. However, maybe I should try putting
each table in its own tablespace.

Reply to this Message

Eugene F - 11 Jan 2006 07:00 GMT

DB2 (at least for LUW) doesn't allow to drop a table space if there is
one object (tables or index) allocated on it. So that souldn't going to
work.

-Eugene

Reply to this Message

Eugene F - 11 Jan 2006 07:00 GMT

DB2 (at least for LUW) doesn't allow to drop a table space if there is
one object (tables or index) allocated on it. So that souldn't going to
work.

-Eugene

Reply to this Message

Knut Stolze - 11 Jan 2006 10:35 GMT

> DB2 (at least for LUW) doesn't allow to drop a table space if there is
> one object (tables or index) allocated on it. So that souldn't going to
> work.

This statement is wrong. You can drop the tablespace and it will drop all
tables in it:

$ db2 "create tablespace ts managed by database using ( file 'ts' 1000 )"
DB20000I The SQL command completed successfully.
$ db2 "create table x ( a int ) in ts"
DB20000I The SQL command completed successfully.
$ db2 "select count(*) from syscat.tables where tabname = 'X'"

1
-----------
1

1 record(s) selected.

$ db2 "drop tablespace ts"
DB20000I The SQL command completed successfully.
$ db2 "select count(*) from syscat.tables where tabname = 'X'"

1
-----------
0

1 record(s) selected.

Signature

Knut Stolze
DB2 Information Integration Development
IBM Germany

Reply to this Message

Eugene F - 11 Jan 2006 19:06 GMT

Ok I'm wrong not being 100% exact because my opinion was based on a
"real-life" case scenario I had hit in the past: attemp to drop a
tablespace with a table(s) having an index in another tablespace which
(I just checked) caused the failure like this:

db2 => create table t(i int) in data_1 index in index_1
db2 => drop tablespace data_1
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0282N Table space "DATA_1" cannot be dropped because at least one
of the
tables in it, "DB2INST1.T", has one or more of its parts in another
table
space. SQLSTATE=55024

BTW, I can recall, dropping a large number of tables can be slow if the
DROPPED TABLE RECOVERY is ON for the tablespace where these tables
live.

-Eugene

Reply to this Message

Pierre Saint-Jacques - 11 Jan 2006 22:26 GMT

That worked as expected because the table components are split in two
tblspcs.
In that case you cannot drop the data or index tablespace singly.
What you can do though is:
db2 drop tablespace data_1, index_1

And that will drop everything.
HTH, Pierre.

Signature

Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515

> Ok I'm wrong not being 100% exact because my opinion was based on a
> "real-life" case scenario I had hit in the past: attemp to drop a
[quoted text clipped - 18 lines]
>
> -Eugene

Reply to this Message