Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2005

    Unanswered: Transaction Log Full

    I have a table with over 120 million records. In order to keep the number of records down to a minimum, I have to find a way to export summary data and then delete records on a monthly basis from this table. Supporting tables don't have any where near as many records and I only have to delete from this one table. Currently using circular logging.
    Records do not get edited.... simply post a record, pull summary data, export summary data and delete records when old.

    My Log File Size is set to 50000 and my number of primary logs was set to 25... I just upped it to 50 today. Secondary Log files is set to 0.

    My delete query isn't pretty, but it's simple -

    From MetricsData
    Where Month(Timekey_dttm) = 11 and Day(Timekey_dttm) < 5;


    My script repeats this and increments the Day(Timekey_dttm) < 10... 15.. etc., until I've covered all days in the month.

    Once I'm able to successfully delete records in this manner, I'll clean up the query/script to address year changes, etc.

    Can someone explain why I'm still getting Transaction Log File Full msgs? I'm now allocating 10 gig to my Transaction Logs. My backups aren't that big.

    Should I look at an alternative delete solution?

    Thanks in advance.

  2. #2
    Join Date
    Nov 2004

    alter table

    you could : alter table aCTIVATE NOT LOGGED INITIALLY and delete records in same unit of work and commit...
    Best Regards, Guy Przytula
    DB2/ORA/SQL Services
    DB2 DBA & Advanced DBA Certified
    DB2 Dprop Certified

  3. #3
    Join Date
    Mar 2004
    It seems you do a periodic(monthly?) purge of records from this table.
    Doing it using deletes will certainly log the activity(deletes).

    One suggestion for both performance and to reduce logging activity is to export the (required data out) and reload it into the table using db2 load.

    db2 "export to <file-name> of del select * from <table> where <search-condition-for-data-to-preserve>"
    db2 "drop table <table>"
    db2 "create table <table>...."
    db2 "load from <file-name of del ......(non-recoverable, etc.)"

    Another option is to create another (new) table, load that new table with data to be retained. Then drop the current table and rename the new table to the new table.

Posting Permissions

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