Since compression (or decompression) only affects pages that reach capacity after compression is turned on (or off), an existing database is not compressed (or decompressed) just by turning on the setting. In order to create a compressed (or decompressed) copy of existing database, use one of the following methods:
▪ Export / Import: To use the export/import method, complete the following steps:
1 Create an SQL script that contains data definition statements describing the structure of the database, see solidDB Data Dictionary (soldd).
2 Create an empty database (with compression enabled if required, by using the COMPRESSED property with the CREATE TABLE statement, see CREATE TABLE).
3 Run the SQL script to create database structure in the new database.
For large databases, this is a time consuming operation possibly leading to out-of service time.
▪ Temporary tables method: This method is logically very similar to export/import. To use the temporary tables method, complete the following steps:
1 Enable (or disable) compression on the database by using the properties, COMPRESSED or NONCOMPRESSED, with the ALTER TABLE statement, see ALTER TABLE.
2 For each table, populate a copy table by completing the following steps:
a Execute the following statement:
INSERT INTO new_table (SELECT * FROM old_table)
b Drop the old table and renaming the new table to the name of the old table.
This approach requires reorganizing the database to benefit from the released disk blocks.
Note In both methods, getting the import order right to avoid foreign key violation might require engineering effort.