Disable DB2 Logging

Posted on 2004-10-30
Last Modified: 2009-07-29
We have an ERP system, which has a logging utility for audit purposes.
Every INSERT, UPDATE and DELETE is logged into its log table with for example username and date information.
This process adds some overhead to running system.
Also DB2 logs our log process too.
How can I disable DB2 log for our ERP system log?
I hope to increase performance in this way since
log table is getting bigger every day.
Question by:ilevent
    LVL 50

    Accepted Solution

    you can't basically , and anyway the logs are for different purposes, don't confuse them

    you log you activity against the tables to provide an audit trail of business transactions....
    this type of log is typically required for 5-7years for Legal reasons, to satisfy your accountants that your internal processes are rigorous and can be used to detect fraud...  

    db2 logs your transactions to ensure that it can provide a recovery solution in the event of  database/hardware problems which maybe encountered during normal operation. This type of log is normally kept for months at most as part of your general backup/recovery (& disater recovery) procedures.  You do not normally attempt to process these logs yourself.


    You can use the ALTER NOT LOGGED INITAILLY facility of the tables to stop db2 logging
    database/table activity for the table , but this is only really intended for the bulk loading of data to your tables for which you intended to take a full backup after the event anyway. The scope of the NOT LOGGED is for the current transaction only... Use of NOT logged is dangerous as any errors encountered during its scope cannot be recovered by DB2 and so the whole tables(s) will be lost (have to be recreated from scratch)...

    LVL 45

    Expert Comment


    Let me second lowfatspread's comment and throw in another thought.

    ERP's are big, massive, inefficient "be everything to everyone" kinds of applications.  There are entirely too many moving parts for any one person to know everything that's going on within one of them.  Don't take dangerous steps with an ERP thinking that if the worst happens, you're only going to effect a small portion of the system.

    If you've got a performance problem with one (and every ERP experiences them at some point), get a GOOD DB2/UDB systems programmer to tune up the database or buy some more hardware.  And get used to the idea of "buy more hardware".  I'm convinced that ERP's were invented to help meet sale's quotas in this day of declining mainframes.

    LVL 13

    Expert Comment

    lol kdo
    LVL 13

    Expert Comment

    of course, if you would like to be brave, drop the triggers that create the transaction logging, or stop db2's audit facility, however I would check with management first before doing this, unless 20 years in prison appeals to you.
    If you do get the go ahead, make sure you have some written document signed by somebody who is AUTHORIZED TO SIGN THE DOCUMENT. normally this would be the firm that handles the companys audit or senior management level, ie CFO or CIO or CEO.
    If your problem is performance related, could you state what level of performance you see has been degraded?

    Author Comment

    This is what I want;
    thanks for any comment.
    LVL 45

    Expert Comment

    Hi ilevent,

    Use caution here.  The "NOT LOGGED INITIALLY" phrase applies only to the next update/transaction.  It's not intended for "general" use.

    LVL 13

    Expert Comment


    if you rollback the transaction implicitly or because of some error, the table can only be dropped, so I would be careful in using this. Make sure that you always have a very recent backup of the database in case something should go wrong.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
    Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
    Want to pick and choose which updates you receive? Feel free to check out this quick video on how to manage your email notifications.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    933 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now