Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    33

    Unanswered: DB2 - transaction log is full error

    Hello,

    I am trying to delete all records in a talbel that has about more than 25,000 records in there. When i execute the SQL statement (delete from table), i have a transaction log full error. How does this problem solve? I know the transaction log needs to be increased, but until to how big that would not be a problem in the future. Thanks for your help.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I'm afraid you'll have to try and see. You can monitor log space usage by looking at the snapshot:
    Code:
    db2 get snapshot for application agentid <whatever> | grep "log space used"
    The UOW monitor switch must be ON for this to work ("db2 update monitor switches using uow on").

  3. #3
    Join Date
    Mar 2004
    Posts
    448
    All the transaction uses logprimary+logsecondary to the size of logfilsiz db
    parameter.A transaction uses the log primary, if it need more then that it creates
    log second file and uses it, once it uses all the log second then you will get
    trasaction log full error.
    As n_i told you , you can use it to set these sizes, remember that you can
    change the number of log second dynamically , but not the other 2 parameters.

    Also every time a database activate it creates all the primary log files, but
    log second on demand.
    I always try to fill my transaction in primary and only try to use second for
    batch jobs.

    regards,

    mujeeb

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    In addition to the above info, remember that logprimary+logsecondary is for the database and not for the transaction ..

    In other words, if logprimary+logsecondary is 100 and if one transaction is using up 90 log files, then the other transaction can use only 10 files

    If you are on V8, you may be able to set logsecond to -1, but generally it is not recommended ..
    To set logsecond to -1 the userexit configuration parameter must be set to yes.

    If you set userexit to yes, there is more config work to do and also additional resources , like storage (TSM or Disk) , CPU is involved



    HTH

    Sathyaram

Posting Permissions

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