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 TIMESTAMP data type. In most cases, these were DATE data types in your pre-6 database. Decide whether you want it to be TIME, TIMESTAMP, or DATE in 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 DECIMAL or NUMERIC data type with a precision greater than 9. Consider whether or not you want that data stored as DOUBLE PRECISION or 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 INT64 alone for now.
7. At the beginning of the file, enter SET SQL DIALECT 3. On the next line, uncomment the CREATE DATABASE statement 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 gfix.
11. At this point, DECIMAL and NUMERIC columns 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.

Advance To:

• Migrating Older Databases