12.21.4 How to convert an existing table to a compressed table

Organization of this subsection
(1) Converting columns in an existing table to compressed columns
(2) Adding a compressed column at the end of an existing table

(1) Converting columns in an existing table to compressed columns

The column attribute change definition (CHANGE column-name) of the ALTER TABLE definition SQL statement cannot be used to change a column to a compressed column. Instead, you must use the procedure below to convert columns in an existing table to compressed columns.

To convert columns to compressed columns:

  1. Unload the existing table.
    Unload the existing table.
  2. Delete the existing table.
    Use DROP TABLE to delete the existing table.
  3. Redefine the table.
    Use CREATE TABLE to redefine the table with the compression specification made for each column that is to be changed to a compressed column. Do not make any changes other than to add the compression specification.
  4. Reload the table.
    Reload the unload data file that was unloaded in step 1 to the table that was redefined in step 3.

For details about unloading and reloading data, see the manual HiRDB Version 9 Command Reference.

Reference note
When the column to be changed to a compressed column is the last column of a table, steps 2 and 3 above can be replaced with the following step:
  • Delete the existing column and add a compressed column
    Use PURGE TABLE to remove all data from the table and then the column deletion definition (DROP column-name) of ALTER TABLE to delete the column that is to be changed to a compressed column. Next, use the column addition definition (ADD column-name) of ALTER TABLE to redefine (add) the table using the compression specification for the column that was deleted.

(2) Adding a compressed column at the end of an existing table

Use the column addition definition (ADD column-name) of the ALTER TABLE definition SQL statement to add a compressed column with the compression specification. Then load data into the compressed column. The data is compressed and stored in the column.

Reference note
The column addition definition of ALTER TABLE adds a column at the end of a table. Therefore, a compressed column can be added only at the end of a table.