Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Posts
    19

    Unanswered: Turn Transaction Log Off in DB2 UDB

    I have two version of DB2 UDB

    DB2 UDB 8.1 for Windows = How do I turn the Transaction Log off!

    DB2 UDB 7.26 for Solaris = How do I turn the Transaction Log off!

    Can any one help, I am at Complete lost!

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Not sure what you mean by "off" but you can't turn it off for normal insert, update, and delete SQL statements. Certain utilites can update DB2 without logging if no recovery is needed.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Sep 2003
    Posts
    19
    I don't require and recovery / rollback! I just need to insert/ update 500 GB worth of data and obviously when it reaches the max parameter; it fails the job saying the Transaction log is full! So the only other option since I don't care about rollback and recovery, I just want to insert and update this data without any logs. Any help would be greatly appreciated!
    Sundeep Sanghavi

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The log gets full because you are not committing the transactions often enough (apparently not at all). I don't know if you are using the load/import utility or an application program to do the inserts/updates, but this should be an easy problem to fix.

    I would recommend that you commit every 100 1000 updates.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Mar 2003
    Posts
    343
    Or autoload the data.

    Or if the table you are inserting into was created with the not logged initially option, then in a session alter it and activate not logged initially and do the insert with autocommit set to off.

    The following posting illustrates how to use the latter.

    http://www.tek-tips.com/gviewthread....178/qid/725298

  6. #6
    Join Date
    Sep 2003
    Posts
    19
    Thank you so much for the tip. That worked!

  7. #7
    Join Date
    Dec 2009
    Posts
    1

    Question Turn transaction log off

    Hello friends,

    I have a similar query, I want to delete rows from a particular table on basis of one particular id which has around 26 lacs rows in the same table. So I want to delete all these 26 lacs rows on the basis of this id with one sql (delete from tablename where id=? )command without writing the transaction log.

    So can i fire this query with any such command option which will commit after every one or 10 rows deleted such that transaction logs will be archived

    OR

    Can I fire the query without writing transaction logs such that even if this query breaks in between though it won't rollback the data.

    Appreciate your early response.

    Regards,
    Nishesh D.
    Websphere Administrator
    Birla Sun Life.

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You can use the ROW_NUMBER function to filter out only a subset of the rows, delete them with a single statement, then COMMIT this batch. Then you process the same batch by adjusting the predicates applied to the result of the ROW_NUMBER function.

    An alternative is to use NOT LOGGED INITIALLY on the table via the ALTER TABLE statement. However, if you happen to run into a problem during the execution of the DELETE statement or before you run a backup, the table is rendered useless and you have to drop it. The reason is that DB2 cannot guarantee a consistent state for the table if you did a not-logged operation. (Problem = any kind of error in a SQL statement.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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