Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2012
    Posts
    8

    Unanswered: transaction log full problem

    Hi i'm new to DB2. I created a test database and am trying to run a delete statement but keep running into problems with the transaction log being full. So I made some changes to fix that based on what I googled:
    Log file size (4KB) (LOGFILSIZ) = 20000
    Number of primary log files (LOGPRIMARY) = 50
    Number of secondary log files (LOGSECOND) = 100

    despite these changes, the log seems quite small still.
    -rw-------. 1 db2inst1 db2iadm1 4,202,496 Sep 28 13:39 S0000808.LOG

    Can anyone tell me what i'm doing wrong here? Obviously something is not right because the delete does not affect very many rows.

    thanks -

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Are you sure that the delete is only deleting a small amount of rows?

    You need to restart the database to get the changes to take effect:

    20,000 * 4096 = 81,920,000 bytes


    Andy

  3. #3
    Join Date
    Sep 2012
    Posts
    8
    Hey Andy

    I just restarted the DB, but the logfile size remains the same.
    RESTART DATABASE CDR
    DB20000I The RESTART DATABASE command completed successfully.
    Query execution time => 1 s: 408 ms

    -rw-------. 1 db2inst1 db2iadm1 4202496 Sep 28 15:19 S0000927.LOG

    seems strange. My config changes aren't taking effect. ??

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    That is not how you need to restart the database. You need to have all connections terminated and the database deactivated (if it was explicitly activated).

    Andy

  5. #5
    Join Date
    Sep 2012
    Posts
    8
    heh. Pardon my ignorance. My restart is not actually a restart. No wonder it doesn't work. Uh, so how do I "have all connections terminated and the database deactivated"

    I spent quite a bit of time googling these log issues, but never cam across anything that simply mentions "restart" the database. Suppose I can look again.

  6. #6
    Join Date
    Sep 2012
    Posts
    8
    looks like db2stop/db2start will work right? but that stops everything not just my test database I think. There's production databases running as part of the instance so that wont work for me.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by qwarty1 View Post
    how do I "have all connections terminated and the database deactivated"
    If you have no control over the the applications connected to the database, that is, you cannot shut them down, you can force those connections off:

    db2 "force application (appl_id_1, appl_id_2, etc)"

    You'll need to list all application IDs that are connected to this database. Obtain them by running:

    db2 list applications

    Another option is to quiesce the database:

    db2 quiesce db force connections

    then

    db2 deactivate db <db_name>

    then activate, connect to it, and unquiesce.
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Sep 2012
    Posts
    8
    thanks! this worked and my new settings are in place.

Tags for this Thread

Posting Permissions

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