Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11

    Unanswered: delete performance

    db2 ese 9.5 fp8 on P/linux
    we have a large table that needs to be cleaned all the time
    this tables keeps requests for executions and needs to be cleaned if exec is finished
    we use delete from batch.message where id in (select id from batch.message where work_id=? fetch first 10000 rows only)
    we added the fetch otherwise we get -964 (logfull) and we already have many logfiles for this db
    the delete takes about 200 sec
    the table has been declared as volatile as the size is changing in size all the time and stats would never be up-to-date

    any good idea to speed-up this process ?
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Write a stored procedure to do the deletes. A cursor WITH HOLD option should be created, but using UR islolation level. Then delete rows that match your criteria (separate delete and not "where current of cursor"). Do commits often to minimize lock contention on the delete (original cursor will stay open if WITH HOLD is used).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    Thanks Marcus
    just a small remark : we have noticed when doing delete from store proc, although using intermediate commit the logfiles are not released - we could see more and more logfiles allocated and finally out of logfiles..
    it seems the call .. is seen as 1 uow..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I do not see a point in declaring a cursor.
    The delete can be made simpler like

    delete from (select * from batch.message where work_id=? fetch first 10000 rows only)

    and repeat till SQLCODE is 100.

    Interesting observation about logs not being released!

    Cheers
    Sathy
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Purpose of declaring cursor is:

    • to minimize lock contention by doing frequent intermediate commits
    • reduce active log size by doing frequent intermediate commits

    There should not be a problem with active log size if the stored proc is used with frequent commits, although you will have to prune the archive logs frequently.
    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
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by przytula_guy View Post
    we have a large table that needs to be cleaned all the time
    ...
    any good idea to speed-up this process ?
    Consider using an MDC, organized by work_id. It would be especially efficient in DB2 9.7 though, where you'd have asynchronous block roll-out and index maintenance.

Posting Permissions

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