How to Delete Retain 2 Tables from Retain 3 Database

  • 7019246
  • 18-Sep-2013
  • 07-Aug-2017

Environment


Systems migrated from Retain 2.x to Retain 3.x

Situation


After successfully migrating from Retain 2.x to Retain 3.x, I would like to delete the old 2.x tables in order to free up database space.

Resolution


This should only be done after verifying a successful migration to 3.x and after backing up the database.

Note that the table names are case sensitive in Linux.

DROP TABLE retain.attachment;
DROP TABLE retain.Document;
DROP TABLE retain.t_msg_properties;
DROP TABLE retain.t_recp_properties;
DROP TABLE retain.t_recipients;
DROP TABLE retain.Email;
DROP TABLE retain.Node;

*Note: When these tables get deleted, it does NOT affect your drive space, (UNLESS you are using specific switches, see below.). It simply frees up room in the database that will be used up for the new information; thus, for some time, your database size will not grow at all until that free space is used up.

Your DBA can compress the database, but that would be inefficient and will impact database performance because now the database will have to grow with each additional record.

If you're running MySQL and have taken advantage of the innodb_file_per_table switch (imported the database or created the retain database after putting that switch into place) then you can use the truncate command to free up space.  MySQL 5.6 has this on by default.
Example: TRUNCATE attachment;

Additional Information

This article was originally published in the GWAVA knowledgebase as article ID 2208.