Oops, dropped an rlv that was in use... Need to recover...

[cbielins]

So yea... I rm-ed a rlv that our TS_FACT2 tblspace was using. So our
db went to the crapper. The tblspace didn't have any pertinent info,
so I'm ok with dropping the tblspace and starting over.

These are the commands and errors that I have found:

--db2 => restart database sware drop pending tablespace (TS_FACT2)
DB20000I The RESTART DATABASE command completed successfully.

--db2 => list tablespaces
Tablespace ID = 4
Name = TS_FACT2
Type = Database managed space
Contents = Any data
State = 0x1000c000
Detailed explanation:
DMS rebalancer is active
Offline
Drop Pending

db2 => drop tablespace TS_FACT2
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 "TS_FACT2" cannot be dropped because at least one
of the
tables in it, "SWARE.<DB_TABLE>", has one or more of its parts in
another
table space. SQLSTATE=55024

Ok so the above table, SWARE.<DB_TABLE>, had an index in our
TS_INDEXES. So I try and drop the table:

db2 => drop table sware.<DB_TABLE>
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0290N Table space access is not allowed. SQLSTATE=55039

I think to drop the index first, which I found from the syscat.indexes
table:

db2 => drop index sysibm.SQL050328011334260
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0669N A system required index cannot be dropped explicitly.
SQLSTATE=42917
Now I am lost and don't know what to do. Any help would be
appreciated.
Thanks,
Chris

[Keith]

[e-mail] wrote:

So yea... I rm-ed a rlv that our TS_FACT2 tblspace was using. So our
db went to the crapper. The tblspace didn't have any pertinent info,
so I'm ok with dropping the tblspace and starting over.

These are the commands and errors that I have found:

--db2 => restart database sware drop pending tablespace (TS_FACT2)
DB20000I The RESTART DATABASE command completed successfully.

--db2 => list tablespaces
Tablespace ID = 4
Name = TS_FACT2
Type = Database managed space
Contents = Any data
State = 0x1000c000
Detailed explanation:
DMS rebalancer is active
Offline
Drop Pending

db2 => drop tablespace TS_FACT2
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 "TS_FACT2" cannot be dropped because at least one of the
tables in it, "SWARE.<DB_TABLE>", has one or more of its parts in
another
table space. SQLSTATE=55024

Ok so the above table, SWARE.<DB_TABLE>, had an index in our
TS_INDEXES. So I try and drop the table:

db2 => drop table sware.<DB_TABLE>
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0290N Table space access is not allowed. SQLSTATE=55039

I think to drop the index first, which I found from the syscat.indexes table:

db2 => drop index sysibm.SQL050328011334260
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0669N A system required index cannot be dropped explicitly.
SQLSTATE=42917
Now I am lost and don't know what to do. Any help would be
appreciated.
Thanks,
Chris

I think you have two options:

1. Restore the tablespace (since you have indexes for this
sware.db_table in ts_indexes, you will need to restore it as well).
If there are other tables in other tablespaces with indexes in
ts_indexes, then you might well resotre db.

or

2. drop tablespaces TS_FACT2, ts_indexes (drop both from the same
command - if you do it in two separate commands, you cannot drop
them, as you've seen)
Again, you can do this only if no other tables have indexes in
ts_indexes or you will need to drop all the indexes and drop TS_FACT2,
ts_indexes tablespaces. Then re-create all dropped indexes. This is no
fun and you might as well restore.

May be someone else have better idea???

HTH
Keith Ponnapalli
IBM Certified Advanced Database Administrator - DB2 UDB V8.1 for Linux,
UNIX, and Windows
INFORMIX Certified Database Administrator

[Fan Ruo Xin]

db2 drop tablespace ts_fact2, ts_indexes
db2 commit

<cb******@gmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

So yea... I rm-ed a rlv that our TS_FACT2 tblspace was using. So our
db went to the crapper. The tblspace didn't have any pertinent info,
so I'm ok with dropping the tblspace and starting over.

These are the commands and errors that I have found:

--db2 => restart database sware drop pending tablespace (TS_FACT2)
DB20000I The RESTART DATABASE command completed successfully.

--db2 => list tablespaces
Tablespace ID = 4
Name = TS_FACT2
Type = Database managed space
Contents = Any data
State = 0x1000c000
Detailed explanation:
DMS rebalancer is active
Offline
Drop Pending

db2 => drop tablespace TS_FACT2
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 "TS_FACT2" cannot be dropped because at least one
of the
tables in it, "SWARE.<DB_TABLE>", has one or more of its parts in
another
table space. SQLSTATE=55024

Ok so the above table, SWARE.<DB_TABLE>, had an index in our
TS_INDEXES. So I try and drop the table:

db2 => drop table sware.<DB_TABLE>
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0290N Table space access is not allowed. SQLSTATE=55039

I think to drop the index first, which I found from the syscat.indexes
table:

db2 => drop index sysibm.SQL050328011334260
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0669N A system required index cannot be dropped explicitly.
SQLSTATE=42917
Now I am lost and don't know what to do. Any help would be
appreciated.
Thanks,
Chris

[cbielins]

How will dropping TS_INDEXES affect the existing tables in other
tblspaces? I would not want to have the other table with indexes be
affected if at all possible?

[Keith]

[e-mail] wrote:

How will dropping TS_INDEXES affect the existing tables in other
tblspaces? I would not want to have the other table with indexes be
affected if at all possible?

This is what I mentioned in #2. If there are other tables (in some
other tablespaces) with indexes in ts_indexes, then you will not be
able to drop it.

[Fan Ruo Xin]

"Keith" <pr***@yahoo.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

[e-mail] wrote:

How will dropping TS_INDEXES affect the existing tables in other
tblspaces? I would not want to have the other table with indexes be
affected if at all possible?

This is what I mentioned in #2. If there are other tables (in some
other tablespaces) with indexes in ts_indexes, then you will not be
able to drop it.

In this case, you'd better run a restore.

[James Campbell via DBMonster.com]

The index sysibm.SQL050328011334260 was created by DB2 to give effect to
something you specified which required an index. Probably the primary key.
Look at its syscat.indexes.SYSTEM_REQUIRED value to see what.

Drop the primary key (or whatever it was) and the index will be
automatically dropped.

James Campbell

--
Message posted via http://www.dbmonster.com

[kenfar]

> Probably the primary key

I'll bet it could also be a block index for mdc tables. That'll be
harder to fix.

ken