Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665

    Unanswered: transaction logs

    Ok im very new to DB2, so I am having a bit of an issue. My transaction logs for my DB is full, now in SQL Server all I did was a back up. Is that the correct thing to do in DB2?
    Jim

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Please read "Read before posting". You need to state what OS and DB2 version. Also, for your particular problem, which type of loggin are you using?

    Andy

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Since you're not being specific about your error and what actions lead to it I'll give you my guess: try increasing the log size (primary and/or secondary). "Log full" condition is typically a result of a long-running transaction, which records all changes it's making to the log file(s). Backing up the database would not solve the problem because when you run your long transaction next time it'll fill the logs just the same.

    Check Administration guide with respect to the following database configuration parameters: logfilsiz, logprimary, logsecond

  4. #4
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    I am running IBM DB2 8.1 fixpack 5 on a Windows 2003 Enterprise Server. I am performing a data load into DB2 from Ascential Datastage where in the stage is doing a Clear table then Insert step.
    HTH
    Jim

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You are performing too many inserts in one Unit of Work (UOW). You either need to increase the logs (see n_i's post), or breakup the UOW--Use COMMIT more often.

    HTH

    Andy

  6. #6
    Join Date
    Aug 2002
    Location
    Charlotte NC
    Posts
    665
    Quote Originally Posted by ARWinner
    You are performing too many inserts in one Unit of Work (UOW). You either need to increase the logs (see n_i's post), or breakup the UOW--Use COMMIT more often.

    HTH

    Andy

    I increased my log size and the amount of the logs and now my job runs!!!
    thanx for the help
    Jim

  7. #7
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Question Log usage

    By the way - does anyone have an exhaustive listing
    of which types of operations that uses the log?

    - Its vital to our backup strategy
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Tank,
    Everything that modifies the DB. This includes INSERT, UPDATE, DELETE, CREATE TABLE, ALTER TABLE, RUNSTATS. There are too many to list them all. Let us just say that if it in any way changes the DB, it should be in the log. How else would you be able to recover the changes through the restore and rollforward process.

    Andy

  9. #9
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Thumbs up Select

    Hi Andy and thanks

    It was just that JPetruk mentioned that some form of
    select queries created temp tables.

    If they do this, I might run into problems doing an online
    backup, because It will require enormous storage resources
    to retain the necessary log.

    Cheers
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  10. #10
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by Tank
    Hi Andy and thanks

    It was just that JPetruk mentioned that some form of
    select queries created temp tables.

    If they do this, I might run into problems doing an online
    backup, because It will require enormous storage resources
    to retain the necessary log.

    Cheers
    Those temp tables I was talking about are temporary internal tables DB2 creates to "stage" data during processing... I can't recall if those would be logged, but I strongly suspect they are not.
    --
    Jonathan Petruk
    DB2 Database Consultant

  11. #11
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Smile But are they or are they not?

    Hi Jonathan

    Yes I know they are internal (you told us) - but the cruciality of
    the logging status remains, especially as I have several
    user doing very complex and large queries for statistic
    purposes - but what the heck I'm a good customer - I'll
    give the guys in Toronto a ring - Thanks for the input

    Cheers
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  12. #12
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    I was poking around the db2 docs, and it seems user declared temporary tables are not logged. It doesn't seem to have anything about system temp tables, but I'd be very surprised if they are logged. It would be silly, since the SELECTs aren't going to be redone during a RF, why re-do the work on the temporary tables?

    Confirm with the lab and let us know.
    --
    Jonathan Petruk
    DB2 Database Consultant

Posting Permissions

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