IBM DB2 9.7, possible to temporarily disable transaction logs?

Question

I want to clean the whole database, so I am dropping all tables and DB objects in two FOR loops, within one transaction. This makes the transaction logs become full. I could increase the transaction log size, but I would much rather drop the database without anything going into the transaction logs. Is this possible to do?

Answer 1

Dropping tables will engage the transaction logs. Actually dropping pretty much everything (except a tablespace or a bufferpool) engages the transaction logs.

Couple things that may help you

• Using truncate on each table will drop the data from the table without logging. This only works for tables. Dropping tables and other objects will still log, but truncate would help you cut down on logging.
• Dropping a tablespace (pending you are using your own and not the default IBM provided ones) will automatically drop ANY and ALL objects associated with that tablespace (usually tables), but drops the tables and the data in a way that is not logged. (There is a way to mark a tablespace for recoverable tables, but for the most part you get a clean database table-wise). NOTE: you still have to drop other objects, which will log some.
• Dropping the whole database. This will drop ALL objects immediately without logging.

I guess depending on what you are doing, the second or third points will get you there the quickest. Just remember that anything dropped that does not engage the transaction logs is not recoverable from, without restoring from a backup.

I'd recommend taking a backup before you try any of these as a best practice. You could also use db2look to get the schema changes and db2move to get the schema with data (although I don' think you get any security with either of those). Again, I'd stick to a backup before you try dropping anything.