Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    24

    Question Unanswered: Delete with commit

    Hi All,

    I am trying to delete some unwanted records from a table in db2 and want to commint in between the records like say for every 1000 recrods I want to do a commit on the table just to avoid the filling of the logs.

    Thanks in advance.

    Srinivas.
    Thanks
    Srinivas chityala

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Srinivas,
    You cannot do that. All you can do is use the WHERE clause and run
    the delete multiple times.

    Instead of: DELETE FROM MyTable
    You can try:
    DELETE FROM MyTable where myUniqueCol between (0 and 10000)
    DELETE FROM MyTable where myUniqueCol between (10000 and 20000)
    DELETE FROM MyTable where myUniqueCol between (20000 and 30000)
    DELETE FROM MyTable where myUniqueCol between (30000 and 40000)
    DELETE FROM MyTable where myUniqueCol between (40000 and 50000)
    etc.

    HTH

    Andy

  3. #3
    Join Date
    Feb 2004
    Posts
    24
    Thanks Andy for the quick respond. I also thought of another way like
    delete from table_name where col_name in (select col_name from table_name
    fetch first 1000 rows only);

    commit;

    and repeat the same for multiple times.

    Thanks
    Srinivas
    Thanks
    Srinivas chityala

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Alter table table-name ACTIVATE NOT LOGGED INITIALLY;
    Delete from table-name where ...;
    commit;

    You must execute the above script with auto-commit turned off (+c). The delete will not be logged. Make sure you have a backup, because in the event of a system failure the table will be corrupted.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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