Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Location
    Italy
    Posts
    33

    Unhappy Unanswered: How to remove unused log files

    I'm using DB2 8.1 (FP3) Workgroup on Linux.

    I'm having troubles with the logfiles of a DB.
    Settings for this DB are:
    LOGFILSIZ = 60000
    LOGPRIMARY = 2
    LOGSECOND = 40

    I want to use this secondary logs to allow UPDATE of a big table (~ 4.000.000 of records) in a transaction.
    All is Ok, but after a commit, the disk space used is about 12G.
    Please note that a fresh restored DB is about 2G.
    It seems that the files used for the logs aren't freed.
    How can i recover this space?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can use the userexit. Find the sample that is close to what you need (disk, tape, tsm). For my example, I will use the disk version.
    Copy it to a working directory. Modify it, set the archive and restore directories to what you want (they can be the same). Create these directories, and make sure your instance owner has all rights to them.
    Compile as it states in the source code. Copy it to the directory specified. Set the DB config parameter for userexit. Restart the database.

    Form this point on, when a log becomes full, it will be copied to the Archive directory. The original will eventually be reused. You can then copy these elsewhere and then delete them as you see fit. If you need them for a ROLLFORWARD, you can copy them to the Restore directory first.

    HTH

    Andy

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Is there any way you can do intermediate commits (every 1000 updates or so)?

    If the update is a single SQL statement, can you limit the range with a where statement (you would need to create multiple SQL statements to cover all ranges)?

    If the statement fails while updating, and DB2 attempts rollback without any intermediate commits, you could be in for a long wait.

    If you can use the IMPORT command with INSERT_UPDATE feature then you can use the COMMITCOUNT n parm for intermediate commits.

  4. #4
    Join Date
    Aug 2003
    Location
    Italy
    Posts
    33
    Originally posted by Marcus_A
    Is there any way you can do intermediate commits (every 1000 updates or so)?

    If the update is a single SQL statement, can you limit the range with a where statement (you would need to create multiple SQL statements to cover all ranges)?

    If the statement fails while updating, and DB2 attempts rollback without any intermediate commits, you could be in for a long wait.

    If you can use the IMPORT command with INSERT_UPDATE feature then you can use the COMMITCOUNT n parm for intermediate commits.
    No, sorry some statements use a single UPDATE, setting a field for a whole table (without a WHERE clause)

Posting Permissions

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