Migrating Databases with NUMERIC and DECIMAL Data Types

Go Up to Fixed-decimal Data Types

Read the “considering migration” section above to decide whether you have columns in a dialect 1 database that would be best stored as 64-bit INT values in a dialect 3 database. If this is the case, follow these steps for each column:

1. Back up your original database. Read the “migration” appendix in the Operations Guide to determine what preparations you need to make before migrating the database. Typically, this includes detecting metadata that uses double quotes around strings. After making necessary preparations as indicated in the migration chapter, back up the database using its current gbak version and restore it using the latest InterBase.
2. Use gfix -set_db_SQL_dialect 3 to change the database to dialect 3.
3. Use the ALTER COLUMN clause of the ALTER DATABASE statement to change the name of each affected column to something different from its original name. If column position is going to be an issue with any of your clients, use ALTER COLUMN to change the positions as well.
4. Create a new column for each one that you are migrating. Use the original column names and if necessary, positions. Declare each one as a DECIMAL or NUMERIC with precision greater than 9.
5. Use UPDATE to copy the data from each old column to its corresponding new column:

   UPDATE tablename
   SET new_col_name = old_col_name;
   

6. Check that your data has been successfully copied to the new columns and drop the old columns.