Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

migrated from Dec 13 '12 at 9:26

This question came from our site for professional and enthusiast programmers.

It be better to export the schema database and recreate the whole database. :) – user16166 Dec 13 '12 at 9:35

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.

EDIT: You can indeed get some security with db2look. These are usually the -x and -xdep parameters.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.