Method One: In-place Migration

Go Up to Migrating Databases to Dialect 3

1. If you have not migrated the database to version 6 and later, dialect 1, do so first. Back up the database again.
2. Extract the metadata from the database using isql -x. If you are migrating legacy databases that contain GDML, see Migrating Older Databases.
3. Prepare an empty text file to use as a script file. As you fix data structures in the metadata files, you will copy them to this file to create a new script.
   Note:
   You could also proceed by removing unchanged SQL statements from the original metadata file, but this is more likely to result in problems from statements that were left in error. Embarcadero recommends creating a new script file that contains only the statements that need to be run against the original database.

For the remaining steps, use a text editor to examine and modify the metadata and script files. Place copied statements into the new script file in the same order they occur in the metadata file to avoid dependency errors.

4. Search for each instance of double quotes in the extracted metadata file. These can occur in triggers, stored procedures, views, domains, table column defaults, and constraints. Change each double quote that delimits a string to a single quote. Make a note of any tables that have column-level constraints or column defaults in double quotes.
   Copy each changed statement to your script file, but do not copy ALTER TABLE statements whose only double quotes are in column-level constraints or column defaults.
   Important:
   When copying trigger or stored procedure code, be sure to include any associated SET TERM statements.

   Quoted quotes If there is any chance that you have single or double quotes inside of strings, you must search and replace on a case-by-case basis to avoid inappropriate changes. The handling of quotation marks within strings is as follows:

   String:	In "peg" mode
   Double-quoted:	"In ""peg"" mode"
   Single-quoted:	'In "peg" mode'
   String:	O'Reilly
   Double-quoted:	"O'Reilly"
   Single-quoted:	'OReilly'

5. In the new script file, search for occurrences of the TIMESTAMP data type. In most cases, these were DATE data types in your pre-6 database. For each one, decide whether you want it to be TIME, TIMESTAMP, or DATE in your dialect 3 database. Change it as needed.
6. Repeat step 5 in the metadata file. Copy each changed statement to your new script file.
7. In the new script file, search for occurrences of reserved words that are used as object names and enclose them in double quotes; that makes them delimited identifiers.
8. Repeat step 7 in the metadata file. Copy each changed statement to your new script file.
9. In each of the two files, search for each instance of a DECIMAL or NUMERIC data type with a precision greater than 9. Consider whether or not you want data stored in that column or with that domain to be 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 to be stored as INT64 alone for now. Copy each changed statement that occurs in the metadata file to your new script file.

Perform the following steps in your new script file:

10. Locate each CREATE TRIGGER and CREATE DOMAIN statement and change it to ALTER TRIGGER or ALTER DOMAIN as appropriate.
11. Locate each CREATE VIEW statement. Precede it by a corresponding DROP statement. For example, if you have a CREATE VIEW <foo> statement, put a DROP VIEW <foo> statement right before it, so that when you run this script against your database, each view first gets dropped and then re-created.
12. If you have any ALTER TABLE statements that you copied because they contain named table-level constraints, modify the statement so that it does nothing except drop the named constraint and then add the constraint back with the single quotes.
13. Check that stored procedure statements are ALTER PROCEDURE statements. This should already be the case.
14. At the beginning of the script, put a CONNECT statement that connects to the original database that you are migrating.
15. Make sure your database is backed up and run your script against the database.
16. Use gfix to change the database dialect to 3.

    gfix -sql_dialect 3 <database.ib>

    Note:
    To run gfix against a database, you must attach as either the database owner or SYSDBA.
17. 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.
18. Validate the database using either IBConsole or gfix.

That’s it. You have got a dialect 3 database. There is a little more work to do if you want your NUMERIC and DECIMAL columns with a precision of greater than 9 to be stored as INT64. At this point, they are still stored as DOUBLE PRECISION. To decide whether you want to change the way data is stored in these columns, read Do you really need to migrate your NUMERIC and DECIMAL Data Types? and Migrating NUMERIC and DECIMAL Data Types.

In addition, there are some optional steps you can take that are described in the following sections, Column Defaults and Column Constraints and Unnamed Table Constraints.

Important:
If you ever extract metadata from the dialect 3 database that you created using the steps above, and if you plan to use that metadata to create a new database, check to see if the extracted metadata contains double quotes delimiting string constants in column defaults, column constraints, or unnamed table constraints. Change any such occurrences to single quotes before using the metadata to create the new database.

Topics

• Column Defaults and Column Constraints
• Unnamed Table Constraints
• About NUMERIC and DECIMAL Data Types

Advance To:

• Column Defaults and Column Constraints