Migrating Databases with NUMERIC and 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:
- 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
gbakversion and restore it using the latest InterBase.
gfix -set_db_SQL_dialect 3to change the database to dialect 3.
- Use the
ALTER COLUMNclause of the
ALTER DATABASEstatement 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 COLUMNto change the positions as well.
- 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
NUMERICwith precision greater than 9.
UPDATEto copy the data from each old column to its corresponding new column:
UPDATE tablename SET new_col_name = old_col_name;
- Check that your data has been successfully copied to the new columns and drop the old columns.