473,581 Members | 3,096 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_TABL E>", 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.SQL05032 8011334260
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 3886

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_TABL E>", 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.SQL05032 8011334260
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.goo glegroups.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_TABL E>", 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.SQL05032 8011334260
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.co m> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.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.SQL05032 8011334260 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1940
by: Robert Nagle | last post by:
While playing around on the webmin mysql interface, I must have accidentally dropped the mysql database. Don't ask. I have backups of all the db's consisting of all the mysql statements (a few days out of sync; nothing terrible). I can import, but I was wondering. I can see var/lib/mysql has all my raw database files. If I...
1
1675
by: Matthew Crouch | last post by:
Any logs/auto backups that might help me recover from this?
3
3142
by: apple | last post by:
UDB v8 fp 6a on AIX 5.1.0.0 Below is a manual incremental recover from compressed backup datasets. With external compress backup datasets, can it be coded to do an automatic incremental recover? The manual incremental recover works just fine, but having trouble getting the automatic incremental recover to work. Thanks for your help #...
11
2319
by: MLH | last post by:
I don't know how it happened. I have hundreds of hours worth of work invested in a file I foolishly named DB9.mdb. I was intending on renaming the file soon. But I neglected to do so before getting caught up in a new project. When I did, I forgot about the filename I was working under. One day, and I don't know how long ago it was, I deleted...
5
28992
by: Grant | last post by:
Hi Is there a way to recover deleted records from a table. A mass deletion has occurred and Access has been closed since it happened Louis
0
3934
by: U S Contractors Offering Service A Non-profit | last post by:
Brilliant technology helping those most in need Inbox Reply U S Contractors Offering Service A Non-profit show details 10:37 pm (1 hour ago) Brilliant technology helping those most in need Inbox Reply from Craig Somerford <uscos@2barter.net> hide details 10:25 pm (3 minutes ago)
0
1787
by: mike_dba | last post by:
I have been testing the db2 recover command on a DB2 V8.2 Linux database. The database contains a single partition. I am not archiving logs but retaining them on disk. the backup image is to disk as well. I performed the following in order : 1. OFFLINE BACKUP <time#1> 2. Create table S.T (c1 smallint) 3. Insert several rows into S.T - ...
4
3438
by: DaveL | last post by:
hi, we have a database in simple recovery mode if a table were dropped Is there a way to recover the dropped table thanks DaveL
5
5101
by: emrezende | last post by:
I am running DB2 v9 in z/OS. I am trying to recovery a dropped table but I could not do it. I have the full and incremental image copies from before of the drop table command. The recover utility is working right and shows RC=0, but the table has not been recovered. When I try to recover another table that had all the rows deleted is working....
0
7788
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8139
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8299
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
8166
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
3799
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3813
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2298
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1398
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1127
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.