hi all,

i get this message in db2diag.log when i try to delete 800.000 rows from a table


ADM5502W The escalation of "510806" locks on table "xx .yyy" to lock
intent "X" was successful.


and these are the parameters for locks

(LOCKLIST) = 10000
(MAXLOCKS) = 50
(LOCKTIMEOUT) = 20


what can i do? more LOCKLIST? is the above value not enough?




another question:

how can observe what isolation level an application uses?
an application can switch the isolation level ... how can i find it out?




and the last question is:

how can i delete "delete from table .... " without loging? i heard about "NO LOG" option ... is that right ...?



thanks a lot for any help

bab

This is not a error ... This message is provided for information ... But, this escaltion has performance implications ...

It is not surprising to see a lock escalation when trying to delete 800000 records .... IF this is one off, don't worry ... If you will be doing it periodically, then you may consider

db2 +c lock table xx.yyy in exclusive mode
db2 +c delete from xx.yyy where col1=0

Hold on ...

Should you be using DELETE to remove 800000 records ???

You should consider using Not logged intially option (But remember, there are recovery implications when using this)

db2 "CREATE TABLE TAB1 ..... NOT LOGGED INTIALLY"

db2 +c "ALTER TABLE TAB1 ACTIVATE NOT LOGGED INITIALLY"
db2 +c "DELETE FROM TAB1 where col1=0"
db2 +c commit

HTH

Sathyaram

You could also split the DELETE up into smaller DELETE sets...

delete from t1 where id in (select id from t1 fetch
first 1000 rows only);

(assuming id is unique, and add your actual search condition)

Repeat this over and over until you get:
SQL0100W No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE=02000

Only works in v8 with some fixpacks, though, otherwise you'll have to split up the ranges in a script/app.

hi sathyaram_s + Petruk

thanks a lot.


sathyaram,

and how can i undo the "NOT LOGGED INITIALLY" after delete is finished?



thanks

bab

The "NOT LOGGED INITIALLY" is reversed when the first commit happens. You must not use autocommit in a CLP script (use the +c parm) and then issue an explicit commit.

hi sathyaram and marcus,

i have done what you said and deleted the table with "NOT LOGGED INITIALLY" but the snapshot for databse "space log" shows that log space grows and grows and grows like before ... hmmm
what can be the reason?



thanks

bab

There may be cascading deletes happening in other tables due to RI constraints or triggers.

hi,

thank you.

but there is no constraints and no triggers on this table.

but is it first mandatory to create the table with this option?

thanks

bab

Yes, the table has to be created with "NOT LOGGED INITIALLY" option in order for you to "ACTIVATE" it later.

ok, but means that, that once created a table with this option there is no way to remove this option from table? hmmm ...

thanks

bab

There is no impact on the table under normal circumstances even if you create the table with this option ...

It gets activated only if you alter the table ....

Cheers
Sathyaram

Someone here mentionned that this restriction goes away in one of the v8 fixpacks, ie. you no longer need to create the table with NOT LOGGED INITIALLY to be able to activate it with ALTER TABLE.

Couldn't find it in the release notes, though, maybe it's part of Stinger?

Yes, I have.

DB2 8.1.4 on AIX 5.2

We have used the: 'alter table activate not logged initially',
on several tables that were surely created before I even
knew there was a thing called logging ;-)

BOW