Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Location
    Phoenix, AZ
    Posts
    42

    Unanswered: Logging off question during update

    Hi,

    I want to update a large number of rows in a table and I keep filling up the logs. Rather than increase the log size (space unavailable), I have decided that I want to turn logging off on the table(s) that I am running the updates in while the table is being updated.

    I understand that when logging is turned off that there will be no way to get back the data if needed unless the table is backed up prior. That will be covered either with a table export or full db backup.

    Is this the correct syntax to turn logging off temporarily while updates are performed? Can this be done when you are not creating the table initially?

    "Alter table lawson.actrans activate not logged initially;"
    "Update lawson.actrans set xxxxx = xxxx where ......"
    commit;

    What is the syntax to turn logging back on for the table afterwards?

    Thanks in advance

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    The syntax looks OK ... On committing, the logging will start ...

    If you are on Version 8, you do not have to create the table with NLI option for ALTER TABLE to work ... In Version 7, you will have to

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

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The first commit (either explicit or due to AUTOCOMMIT being ON) will re-enable logging.

  4. #4
    Join Date
    Jan 2004
    Location
    Phoenix, AZ
    Posts
    42
    Great, Thanks! I will be sure not to issue a commit statement.

    So, will the syntax to re-enable logging for that table be:

    "Alter table lawson.actrans activate logged initially;" ???

    Thx

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You will want to use a commit after you update statement. You will also need to turn off auto-commit in your CLI interface by using the -c option (-c -tvf ...)
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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