Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2006
    Posts
    3

    Unanswered: deletion of records as smaller chunks in DB2???

    I have a table consists of 10000 records. i want to delete these records, if i tried to delete those records at a time, database fail out of log space.
    So, I want to delete these 10000 records as smaller chunks.
    i am using DB2.

    Can anyone help me to delete the records in smaller chunks?

    thanks,
    ramakanth

  2. #2
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    Please use WHERE clause in your DELETE statement.

    Thank You,

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by ramakanthboga
    ... if i tried to delete those records at a time, database fail out of log space.
    Do you mean lock space?
    The following could work in this case:
    Code:
    LOCK TABLE tablename IN EXCLUSIVE MODE ;
    DELETE FROM tablename ;
    COMMIT ;
    or something similar.
    The essential idea is to avoid row-at-a-time locking by forcing a table lock before starting the delete process.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Jun 2006
    Posts
    3
    hi JAYANTA_DATTA,
    thanks for the reply, if i use the where clause it can able to delete the records certain amount of records. i need something which can able to delete the records in loop(as a smaller chunks, say 100records) till the end.
    if you could tell me the syntax for this then it would be great.
    thanks,
    ramakanth boga


    hi Peter.Vanroose,
    i am sorry to tell you its not lock space. its log space i.e database fail out of trasaction log space.
    i need something which can able to delete the records in loop(as a smaller chunks, say 100records) till the end.
    thanks
    ramakanth boga

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    In that case you can write a loop around a cursor fetch, with a COMMIT every 100 rows, something like:
    Code:
    DECLARE c CURSOR FOR
    SELECT 1 FROM my_table WHERE [*condition*] FOR UPDATE ;
    OPEN c
    and then in a loop:
    Code:
    FETCH c;
    DELETE FROM my_table WHERE CURRENT OF c
    and issue a COMMIT every 100th iteration.

    Alternatively, in DB2 version 8 and beyond, you may use multi-row fetch and positioned delete, with a commit after every delete:
    Code:
    FETCH NEXT ROWSET FROM c FOR 100 ROWS ;
    DELETE FROM my_table WHERE CURRENT OF c ;
    COMMIT WORK
    Last edited by Peter.Vanroose; 07-01-06 at 11:33.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Jun 2006
    Posts
    3
    Peter.Vanroose,
    thanks for the response. i'll try to do this, if any problems comes i'll c u.

    ramakanth

Posting Permissions

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