Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    190

    Unanswered: transaction logs full issue

    I have a situation where a developer's code does the following:

    1. Declare temp table
    2. Insert data into temp table (no commit).
    3. Using common table expressions and lots of joins, a long running query is run.

    Now the part I'm not sure about. Several hours into the long running query, I get a SQL0965C transaction full msg pointing to this process. This process is only running a select statement at this point. My question is would this process be pointed to by db2 as the culprit, even though it only had a small number of inserts before the select statement, but didn't do a commit for about 2 hours? If this is now the case, why would I get a SQL0965C on a process performing a select statement (a long running query). I would expect to run out of temp space in this case.

    DB21085I Instance "db2inst3" uses "64" bits and DB2 code release "SQL09053"
    with level identifier "06040107".
    Informational tokens are "DB2 v9.5.0.3", "special_22945", "U818976_22945", and
    Fix Pack "3".
    Product is installed at "/cs/IBM/db2/V9.5".


    Thanks

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Here is what may be happening. As an example, I will use 3 Primary and 2 Secondary logs (and assume you have archive logging and it is not infinite).

    Start with Log 1.
    Problem user does Insert without a Commit. There is an open transaction in the Log 1.
    Other processes do Changes and commits and the Log 1 fills up.
    DB2 goes to Log 2.
    Other processes do Changes an commits and Log 2 fills up.
    DB2 goes to Log 3.
    Other processes do Changes an commits and Log 3 fills up.
    At this point DB2 would close Log 1 as it should have 3 Primary logs open but it can't since there is still an uncommitted transaction in it.
    DB2 goes Log 4 (as Secondary 1).
    Other processes do Changes an commits and Log 4 (Secondary 1) fills up.
    DB2 still can't close Log 1.
    DB2 goes to Log 5 (as Secondary 2).
    Other processes do Changes an commits and Log 5 (Secondary 2) fills up.
    DB2 still can not close Log 1 because of the uncommitted transaction.
    But it has run out of Primary and Secondary logs.

    You now have a Transaction logs full situation.

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I have encountered the scenario stealth_dba describes a number of times.
    You can use the database snapshot to understand which application is holding the oldest transaction etc.

    db2 get snapshot for database on <dbname>

    Look up for
    Log space used by the database (Bytes)
    Secondary logs allocated currently
    Appl id holding the oldest transaction

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Oct 2007
    Posts
    246
    to be more sure on ur question you can post the user temp tablespace describe
    like why the temp space was not full and when you got this error related to transaction log was there enought space in your temp tablespace file system.
    regds
    Paul

  5. #5
    Join Date
    Apr 2004
    Posts
    190
    Will this scenario happen as described, if using a temp table created with the NOT LOGGED statement.

Posting Permissions

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