Method Two: Migrating to a New Database
Go Up to Migrating Databases to Dialect 3
If you can create a data transfer utility that copies data between databases, the process of migrating a database to dialect 3 is considerably simplified.
Overview Extract the metadata from your database, examine it for problem areas, and fix the problems. Use the modified metadata file to create a new dialect 3 database and use an application to transfer data from the old database to the new.
- 1. If you have not migrated the database to version 6, dialect 1, do so first. Back up the database again.
- 2. Extract the metadata from the database using
isql. If you are migrating a database that contains data structures created with GDML, see Migrating Older Databases.
For the following steps, use a text editor to examine and modify the metadata file.
- 3. Search for each occurrence of the
TIMESTAMPdata type. In most cases, these were
DATEdata types in your pre-6 database. Decide whether you want it to be
DATEin your dialect 3 database. Change it as needed.
- 4. Find all instances of reserved words that are used as object names and enclose them in double quotes to make them delimited identifiers.
- 5. Search for each instance of double quotes in the extracted metadata file. These can occur in triggers, stored procedures, views, domains, exceptions, table column defaults, and constraints. Change each double quote to a single quote.
- 6. Search for each instance of a
NUMERICdata type with a precision greater than 9. Consider whether or not you want that data stored as
INT64. See Do you really need to migrate your NUMERIC and DECIMAL Data Types? for a discussion of issues. For occurrences that should be stored as
DOUBLE PRECISION, change the data type to that. Leave occurrences that you want stored as
INT64alone for now.
- 7. At the beginning of the file, enter
SET SQL DIALECT 3. On the next line, uncomment the
CREATE DATABASEstatement and edit it as necessary to create a new database.
- 8. Run the metadata file as a script to create a new database.
- 9. Use your data transfer utility to copy data from the old database to the new dialect 3 database. In the case of a large database, allow significant time for this.
- 10. Validate the database using
- 11. At this point,
NUMERICcolumns with a precision greater than 9 are still stored as
DOUBLE PRECISION. To store the data as
INT64, read Do you really need to migrate your NUMERIC and DECIMAL Data Types? and, if necessary, follow the steps in Migrating NUMERIC and DECIMAL Data Types.