Try OpenEdge Now
skip to main content
DataServer for Oracle
The DataServer Tutorial : OpenEdge DB-to-Oracle Incremental Schema Migration utility
 

OpenEdge DB-to-Oracle Incremental Schema Migration utility

The Incremental Schema Migration utility allows you to migrate schema changes from an OpenEdge database to an Oracle database. For example, in the process of developing an application in ABL that you will migrate to Oracle, you might want to make and test schema changes in your OpenEdge database that you want reflected in the Oracle database. The utility reads a delta.df file that has been created using the standard incremental dump procedure, and creates a delta SQL file, named <Oracle-logical-db>.sql, that contains the SQL DDL for making the changes and a new delta .df file, named <schema-holder-name>.df. You can then load the .df file into the schema holder and apply the SQL file to the Oracle instance to complete the migration process.
Note that you do not make schema changes directly in the schema holder, which must remain synchronized with the Oracle database. The utility uses the schema holder to determine the Oracle definitions.
To run the Incremental Schema Migration utility:
1. From the Data Admin main menu, choose DataServers > ORACLE Utilities > Schema Migration Tools > Generate Delta.sql OpenEdge to Oracle . The following dialog box appears:
2. Provide the information as described in the following table.
Table 68. Delta df to Oracle Conversion UI elements
Interface element
Description
Delta DF File
The name of the delta.df file that was created when you ran the incremental dump routine against two OpenEdge databases. You can browse for the filename by choosing the Files button.
Schema Holder Database
The name of the schema holder.
Connect parameters for Schema
By default, the current working database is specified. To connect to a different database, specify connection parameters for the Oracle schema holder to be updated.
Logical name for Oracle database
Specify the Oracle database logical name, that is, the name by which you refer to the Oracle database in your application.
Oracle Object Owner Name
Enter the name of the owner.
Oracle tablespace for Tables
Enter the names of any tablespaces to be used here.
Oracle tablespace for Indexes
Enter the names of any tablespaces to be used for indexes here.
Maximum char length
Enter a positive value up to and including 4000. This value is defaulted based on the values of other Unicode-specific settings in your migration. See Handling character length during database migration for details.
Create RECID Field
Check this toggle box if your Oracle database currently contains the PROGRESS_RECID field. Selecting this option will maintain the use of PROGRESS_RECID in any new tables added by this utility.
Include Default
Check this toggle box to include initial values in column definitions.
Create schema holder delta df
Check this toggle box if you want the utility to generate a .df file that includes the incremental schema information. You can then load this .df file into the schema holder. By default, this toggle box is checked.
Create Shadow Columns
Check this toggle box to support case-sensitive columns with shadow columns. By default, this option is not checked, allowing the utility to automatically support ABL case insensitivity via Oracle function-based indexes.
Note: Function-based indexes and shadow columns can coexist in a schema holder to support case insensitive indexes.
Char semantics
Check this toggle box to set the unit of measure for length to character semantics when migrating OpenEdge file fields to Oracle table columns. Length values for the migration are derived from the For field widths use setting. The Char semantics option is only enabled for Unicode migrations. To enable it, the schema image code page must be set to utf-8 and you must specify an Oracle Version of 9 or later.
Use Unicode Types
Maps character fields to Unicode data types. Only applies if schema image's code page is UTF-8. You must specify 9 or higher (corresponding to an Oracle version of 9i or later) to enable this option.
For field widths use
When pushing fields to a foreign data source, you can select one of two primary field format options:
*Width — Uses the value of the _width field in the _field record. Recommended especially for Unicode implementations. For more information on field widths, see Adjusting field widths during migration.
*ABL Format — Compiles with the current default width specified. (default)
If you select the ABL Format option, you have an additional setting to define:
*Expand x(8) to 30 — This setting is on by default to indicate that the format for the character fields defined as x(8) will be created as 30 characters.
Note: You cannot use the Expand x(8) to 30 setting with the Width option.
3. Choose OK. The utility generates a delta.sql file and, optionally, a delta.df file.
4. After running the utility, you must apply the SQL it generates to the Oracle database and load the new delta.df file into the original schema holder so that it is synchronized with the modified Oracle database.
The utility generates SQL that will create objects in the Oracle database. It creates the same objects as the OpenEdgeDB-to-Oracle Migration utility. For example, OpenEdge indexes are case-insensitive. To create this equivalent functionality in the Oracle database, for an index defined in the OpenEdge database on a CHARACTER field, the utility generates SQL to use the UPPER function for the index. The following table describes the Oracle equivalents of OpenEdge object types.
Table 69. Oracle equivalents of OpenEdge objects
OpenEdge object
Oracle equivalent objects
Case-insensitive Index
The index definition uses the UPPER function.
Array
One column for each extent of the OpenEdge array. The columns are named field-name##extent-number. For example, an OpenEdge field called monthly-amount with an extent of 12 has 12 columns in Oracle with names such as MONTHLY_AMOUNT##1 through MONTHLY_AMOUNT##12.
Table
If Create RECID Field, is selected, for any new table, a PROGRESS_RECID column is added. This indexed column provides a unique key on the Oracle table.A sequence named table-name_SEQ is also added. This sequence populates the PROGRESS_RECID column for each row in the Oracle table.
Deleted Field
The column is dropped from Oracle.
Not all OpenEdge objects can be converted to Oracle by this utility. The following table details restrictions on the update.
Table 70. Database modifications not converted to Oracle
Database object
Modification in OpenEdge
Action
Sequence
Starting value altered
None. Oracle does not allow the starting value of a sequence to be altered. You must manually drop and add the sequence to implement this change.
Trigger
Any
Applied to schema holder. ABL triggers are not converted to Oracle SQL.
Character field
Format altered
None. Oracle's restrictions on the alteration of character fields, such as knowing if all fields are NULL prior to decreasing the width, cannot be accommodated by this utility, therefore no actions are implemented.
The following table shows how the fields of an OpenEdge table convert to Oracle equivalents.
Table 71. Sample object equivalents
OpenEdge state table
Oracle STATE table
Character field: State-Name
STATE_NAME
Array with 3 Extents: State-Fact
STATE_FACT##1 STATE_FACT##2 STATE_FACT##3
Default record identifier object
STATE##PROGRESS_RECID STATE_SEQ
The utility ensures that the migrated objects have names that are unique to the Oracle database. If you have given the object a name that is not unique, it drops characters from the end of the name and appends numbers until it creates a unique name. Since Oracle requires that index names be unique to the database, the utility appends the table name to the indexed column name to create a unique name.
* Adjusting field widths during migration
* Updating the Oracle database