Converting a Disk Table into an MOT Table

The direct conversion of disk tables into MOT tables is not yet possible, meaning that no ALTER TABLE statement yet exists that converts a disk-based table into an MOT table.

The following describes how to manually perform a few steps in order to convert a disk-based table into an MOT table, as well as how the gs_dump tool is used to export data and the gs_restore tool is used to import data.

Prerequisite Check
Check that the schema of the disk table to be converted into an MOT table contains all required columns.

Check whether the schema contains any unsupported column data types, as described in the Unsupported Data Types_ _section.

If a specific column is not supported, then it is recommended to first create a secondary disk table with an updated schema. This schema is the same as the original table, except that all the unsupported types have been converted into supported types.

Afterwards, use the following script to export this secondary disk table and then import it into an MOT table.

Converting
To covert a disk-based table into an MOT table, perform the following –

1. Suspend application activity.
2. Use gs_dump tool to dump the table’s data into a physical file on disk. Make sure to use the data only.
3. Rename your original disk-based table.
4. Create an MOT table with the same table name and schema. Make sure to use the create FOREIGN keyword to specify that it will be an MOT table.
5. Use gs_restore to load/restore data from the disk file into the database table.
6. Visually/manually verify that all the original data was imported correctly into the new MOT table. An example is provided below.
7. Resume application activity.

IMPORTANT Note – In this way, since the table name remains the same, application queries and relevant database stored-procedures will be able to access the new MOT table seamlessly without code changes. An additional method is to copy data from a regular (Heap) table into the new MOT table by using an “INSERT INTO SELECT” statement.

This method is subject to MOT transaction size limitation of less than 1 GB.

Conversion Example
Let's say that you have a database name benchmarksql and a table named customer (which is a disk-based table) to be migrated it into an MOT table.

To migrate the customer table into an MOT table, perform the following –

1. Check your source table column types. Verify that all types are supported by MOT, refer to section Unsupported Data Types.

1. Check your source table data.

1. Dump table data only by using gs_dump.

1. Rename the source table name.

1. Create the MOT table to be exactly the same as the source table.

1. Import the source dump data into the new MOT table.