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?

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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