469,646 Members | 1,567 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,646 developers. It's quick & easy.

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

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

Nov 12 '05 #1
7 3627

cbiel...@gmail.com 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

Nov 12 '05 #2
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

Nov 12 '05 #3
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?

Nov 12 '05 #4

cbiel...@gmail.com 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.

Nov 12 '05 #5

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

cbiel...@gmail.com 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.
Nov 12 '05 #6
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
Nov 12 '05 #7
> Probably the primary key

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

ken

Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Robert Nagle | last post: by
1 post views Thread by Matthew Crouch | last post: by
3 posts views Thread by apple | last post: by
5 posts views Thread by Grant | last post: by
reply views Thread by U S Contractors Offering Service A Non-profit | last post: by
reply views Thread by mike_dba | last post: by
4 posts views Thread by DaveL | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.