Dropping tables

[Ross Mallett]

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?

[Phil Sherman]

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

Phil Sherman
Ross Mallett wrote:

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?

[Ross Mallett]

Phil Sherman wrote:

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

Phil Sherman
Ross Mallett wrote:

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?

Yes.

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

[Eugene F]

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

[Eugene F]

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

[Knut Stolze]

Eugene F wrote:

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.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Eugene F]

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

[Pierre Saint-Jacques]

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.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"Eugene F" <ro********@yahoo.com> a écrit dans le message de news:
11*********************@o13g2000cwo.googlegroups.c om...

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