Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2004
    Posts
    37

    Unanswered: db2 8.1 --> locks , isolation levels , ...

    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

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: db2 8.1 --> locks , isolation levels , ...

    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



    Originally posted by bab
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    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.
    --
    Jonathan Petruk
    DB2 Database Consultant

  4. #4
    Join Date
    Feb 2004
    Posts
    37
    hi sathyaram_s + Petruk

    thanks a lot.


    sathyaram,

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



    thanks

    bab

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Feb 2004
    Posts
    37
    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

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by bab
    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.

  8. #8
    Join Date
    Feb 2004
    Posts
    37
    Originally posted by n_i
    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

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by bab
    but is it first mandatory to create the table with this option?

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

  10. #10
    Join Date
    Feb 2004
    Posts
    37
    Originally posted by n_i
    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

  11. #11
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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

    Originally posted by bab
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  12. #12
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    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?
    --
    Jonathan Petruk
    DB2 Database Consultant

  13. #13
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220
    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
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •