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!

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.

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

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.

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

Thank you so much for the tip. That worked!

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.

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.)