Hello,

I wanted to drop a table with a lot of data in it (about 1.9 GB). I
thought it would be a good idea to delete the data first. So I issued a
"delete from <table>" command - which took, not very surprisingly, a
long time. After a while, when I thought the table should be empty (I
didnot check it - shame on me), I tried to drop the table (via the
db2cc). After this I could not "talk" to my db2 anymore. I tried db2stop
(and db2stop force) but it did not help. I also rebooted the computer
the database is running on. Now i can start the database (db2start) and
get a "success", but I cannot connect to the database (connect to
<database>) it takes forever...
How can I find out in which state the database is and how can I force a
recovery (or at least a state where I can use my database again)?

Thanks.

Greetings, Jan

Jan Suchanek wrote:

> Hello,
>
> I wanted to drop a table with a lot of data in it (about 1.9 GB). I
> thought it would be a good idea to delete the data first. So I issued a
> "delete from <table>" command - which took, not very surprisingly, a
> long time. After a while, when I thought the table should be empty (I
> didnot check it - shame on me), I tried to drop the table (via the
> db2cc). After this I could not "talk" to my db2 anymore. I tried db2stop
> (and db2stop force) but it did not help. I also rebooted the computer
> the database is running on. Now i can start the database (db2start) and
> get a "success", but I cannot connect to the database (connect to
> <database>) it takes forever...
> How can I find out in which state the database is and how can I force a
> recovery (or at least a state where I can use my database again)?

What do you see in your db2diag.log?

I assume that the reboot might have resulted in a DB2 crash, and the
subsequent attempts to connect initiated the crash recovery mechanisms.
The db2diag.log should tell you that and also what happened before.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

Hello,

>
> What do you see in your db2diag.log?

Quite a lot.
>
> I assume that the reboot might have resulted in a DB2 crash, and the
> subsequent attempts to connect initiated the crash recovery mechanisms.
> The db2diag.log should tell you that and also what happened before.
>

I think you a right... and the log has a lot of entries since the crash.
But what can I do to fix it?

Crash recovery started. LowtranLSN 00000002D7BB800C MinbuffLSN
00000002D7BB800C


2004-09-27-17.09.15.523689 Instance:db2inst1 Node:000
PID:1426(db2agent (TISYS)) TID:16384 Appid:*LOCAL.db2inst1.0C5007150914
recovery manager sqlprecm Probe:125 Database:TISYS


Using parallel recovery with 3 agents 4 QSets 28 queues and 8 chunks




2004-09-27-17.09.15.712260 Instance:db2inst1 Node:000
PID:1435(db2agnsc (TISYS)) TID:16384 Appid:*LOCAL.db2inst1.0C5007150914
buffer pool services sqlbFreeUpSlot Probe:122 Database:TISYS


ADM6019E All pages in buffer pool "IBMHIDDENBP4K" (ID "4096") are in use.
Refer to the documentation for SQLCODE -1218.


PID:1435 TID:16384 Node:000 Title: Not available!
Could not fix page for objID=42, tbspaceID=2, objType=1, parentObjID=42
parentTbspaceID=2.
PID:1435 TID:16384 Node:000 Title: Not available!
Current size of bufferpool #4096: 16 slots.
2004-09-27-17.09.16.336416 Instance:db2inst1 Node:000
PID:1435(db2agnsc (TISYS)) TID:16384 Appid:*LOCAL.db2inst1.0C5007150914
index manager sqliRedo Probe:811 Database:TISYS


DIA0001E An internal error occurred. Report the following error code :
"ZRC=0x8502002C".


PID:1435 TID:16384 Node:000 Title: SQLP_LSN
0002 d7bb 8124 .....$


PID:1435 TID:16384 Node:000 Title: SQLI_LRH
028d 0200 2a00 0200 2a00 0100 1500 0000 ....*...*.......


2004-09-27-17.09.16.376109 Instance:db2inst1 Node:000
PID:1435(db2agnsc (TISYS)) TID:16384 Appid:*LOCAL.db2inst1.0C5007150914
data management sqldmrdo Probe:765 Database:TISYS


DIA0001E An internal error occurred. Report the following error code :
"ZRC=0x8502002C".

2004-09-27-17.09.16.392407 Instance:db2inst1 Node:000
PID:1435(db2agnsc (TISYS)) TID:16384 Appid:*LOCAL.db2inst1.0C5007150914
data management sqldmrdo Probe:765 Database:TISYS


Error during REDO of LSN: 0002 d7bb 8124
......$


2004-09-27-17.09.16.417418 Instance:db2inst1 Node:000
PID:1435(db2agnsc (TISYS)) TID:16384 Appid:*LOCAL.db2inst1.0C5007150914
data management sqldmrdo Probe:765 Database:TISYS


Error during REDO of log record:
028d 0200 2a00 0200 2a00 0100 1500 0000 ....*...*.......
1600 0000 0001 0000 0300 0000 0100 0400 ................
0402 0004 0000 43 ......C


2004-09-27-17.09.16.422757 Instance:db2inst1 Node:000
PID:1435(db2agnsc (TISYS)) TID:16384 Appid:*LOCAL.db2inst1.0C5007150914
recovery manager sqlpRecDbRedo Probe:160 Database:TISYS


REDO returns -2063466452 on log record: 2700 0000 4e00 0000 0002 d7bb
800c 0000 '...N...........


0008 a8b5 0000 0000 0000 0000 0000 0000 ................


2004-09-27-17.09.16.427703 Instance:db2inst1 Node:000
PID:1435(db2agnsc (TISYS)) TID:16384 Appid:*LOCAL.db2inst1.0C5007150914
recovery manager sqlpRecDbRedo Probe:160 Database:TISYS


extra info: 028d 0200 2a00 0200 2a00 0100 1500 0000 ....*...*.......
1600 0000 0001 0000 0300 0000 0100 0400 ................
0402 0004 0000 43 ......C


2004-09-27-17.09.16.433605 Instance:db2inst1 Node:000
PID:1435(db2agnsc (TISYS)) TID:16384 Appid:*LOCAL.db2inst1.0C5007150914
recovery manager sqlpPRecProcLog Probe:250 Database:TISYS


DIA0001E An internal error occurred. Report the following error code :
"ZRC=0x8502002C".


2004-09-27-17.09.16.435746 Instance:db2inst1 Node:000
PID:1435(db2agnsc (TISYS)) TID:16384 Appid:*LOCAL.db2inst1.0C5007150914
recovery manager sqlpPRecProcLog Probe:250 Database:TISYS


qEntry for 00000002D7BB8124entryFlags 1 queueId 1 waitOthers (nil)
numBlocked 0 lrHeader:
2700 0000 4e00 0000 0002 d7bb 800c 0000 '...N...........
0008 a8b5 0000 0000 0000 0000 0000 0000 ................

Jan Suchanek wrote:

>> I assume that the reboot might have resulted in a DB2 crash, and the
>> subsequent attempts to connect initiated the crash recovery mechanisms.
>> The db2diag.log should tell you that and also what happened before.
>
> I think you a right... and the log has a lot of entries since the crash.
> But what can I do to fix it?

Wait until the crash recovery is finished, I'd say. Given that you had the
DELETE operation for the 1.9GB of data, it could take quite a while. Grab
a cup of coffee. ;-)

Or, in case the data is not really important (like on a development system),
you could start over with a new database and restore the data from a
backup.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

Knut Stolze wrote:
> Jan Suchanek wrote:
>
>
>>>I assume that the reboot might have resulted in a DB2 crash, and the
>>>subsequent attempts to connect initiated the crash recovery mechanisms.
>>>The db2diag.log should tell you that and also what happened before.
>>
>>I think you a right... and the log has a lot of entries since the crash.
>>But what can I do to fix it?
>
>
> Wait until the crash recovery is finished, I'd say. Given that you had the
> DELETE operation for the 1.9GB of data, it could take quite a while. Grab
> a cup of coffee. ;-)

How long, would you say? I started the database yesterday and I still
cannot connect... Can I find out if the database makes any progress
(system load is at 0.00 - it doesnot seem that there is someone
working...). I am afraid we dont have such big cups :-(


>
> Or, in case the data is not really important (like on a development system),
> you could start over with a new database and restore the data from a
> backup.

Yes, it is a testing and development system, so dropping the database
would not be a problem (since I am trying to the an automated migration
from sapdb to db2 there will be some drops anyway for testing). But I
still have to figure out if it can cause such trouble just deleting and
dropping at the same time. This would be very embarrassing once we use
db2 as a production system...

Greetings, Jan

Here's a tip :

When you want to drop the contents of a table that large, you are going
to have a LOOONG wait, and the logs can fill up quite quickly. The next
time, "truncate" the table. Create an empty file called - say - empty .

Next, at the db2 CLI, type :

db2 "import from empty of del replace into sometablenametobedeleted"

This will replace the contents of the table with the empty file,
effectively "truncating" the table.

Saves you all this hassle and woe.

Mairhtin



Jan Suchanek <jan.suchanek@gmx.de> wrote in
news:415920a7$1@news.uni-ulm.de:

> Knut Stolze wrote:
>> Jan Suchanek wrote:
>>
>>
>>>>I assume that the reboot might have resulted in a DB2 crash, and the
>>>>subsequent attempts to connect initiated the crash recovery
>>>>mechanisms. The db2diag.log should tell you that and also what
>>>>happened before.
>>>
>>>I think you a right... and the log has a lot of entries since the
>>>crash. But what can I do to fix it?
>>
>>
>> Wait until the crash recovery is finished, I'd say. Given that you
>> had the DELETE operation for the 1.9GB of data, it could take quite a
>> while. Grab a cup of coffee. ;-)
>
> How long, would you say? I started the database yesterday and I still
> cannot connect... Can I find out if the database makes any progress
> (system load is at 0.00 - it doesnot seem that there is someone
> working...). I am afraid we dont have such big cups :-(
>
>
>>
>> Or, in case the data is not really important (like on a development
>> system), you could start over with a new database and restore the
>> data from a backup.
>
> Yes, it is a testing and development system, so dropping the database
> would not be a problem (since I am trying to the an automated
> migration from sapdb to db2 there will be some drops anyway for
> testing). But I still have to figure out if it can cause such trouble
> just deleting and dropping at the same time. This would be very
> embarrassing once we use db2 as a production system...
>
> Greetings, Jan
>