Progress-to-ORACLE Incremental Schema Migration Utility
The Incremental Schema Migration utility allows you to migrate schema changes from Progress to an ORACLE database. For example, in the process of developing an application in Progress that you will migrate to ORACLE, you might want to make and test schema changes in the Progress database that you want reflected in the ORACLE database. The utility reads a Progress delta.df
file that has been created using the standard incremental dump procedure. and creates a delta.sql
file that contains the SQL DDL for making the changes and a delta.df
file. You can then load the delta.df
file into the schema holder. You can then apply the delta.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 what the ORACLE definitions are.
Follow these steps to run the utility:
- From the Data Admin main menu, choose DataServers
ORACLE Utilities
Schema Migration Tools
Generate Delta.sql Progress to ORACLE. The following dialog box appears:
- Provide the information as described in Table 5–11.
Table 5–11: Generate Delta.sql Progress-to-ORACLE Utility
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 Progress databases. You can browse for the file name by choosing the Files button.
|
Schema Holder Database
|
The name of the schema holder. The schema information must match the schema of the ORACLE database to be updated. Progress Software recommends that you are connected to this database before running the utility. If you are not connected to the database, you can connect to it at this point using the Connect Parameters.
|
Connect parameters for Schema
|
Specify these parameters to connect to the ORACLE database to be updated if you are not already connected to the schema holder.
|
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 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.
|
Progress 4GL Compatible Objects
|
Check this toggle box to create a target database that supports arrays, case-insensitive indexes, backward and forward scrolling, and the Progress record identifier. These objects result in additional columns added to the ORACLE database’s tables.
|
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.
|
- Choose OK. The utility generates a
delta.sql
. file and, optionally, a delta.df
file.
- 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 that are compatible with Progress. It creates the same objects as the Progress-to-ORACLE Migration utility. For example, Progress indexes are case-insensitive. To create this equivalent functionality in the ORACLE database, for an index defined in the Progress database on a CHARACTER field, the utility generates SQL to create two columns in ORACLE: the second column is the uppercase shadow of the first. Table 5–12 describes the ORACLE equivalents of Progress object types.
Table 5–12: ORACLE Equivalents of Progress Objects
Progress Object
|
ORACLE Equivalents
|
Case-insensitive Index
|
Two indexed columns: the first column is the uppercase equivalent of the second column and is named U##first-column-name.
|
Array
|
One column for each extent of the Progress array. The columns are named field-name##extent-number. For example, a Progress field called monthly-amount with an extent of 12 will have 12 columns in ORACLE with names such as MONTHLY_AMOUNT##1 through MONTHLY_AMOUNT##12.
|
Table
|
A PROGRESS_RECID column. This indexed column provides a unique key on the ORACLE table.
A sequence named table-name_SEQ. This sequence populates the PROGRESS_RECID column for each row in the ORACLE table.
|
Obsolete Field
|
A view with a name ending in _V##. When you delete a field from the Progress database, the utility identifies this obsolete field and generates SQL to create an ORACLE view of the table that excludes the obsolete column because ORACLE does not allow the explicit deletion of a column from a table. Whenever Progress programs reference the table, the DataServer references the ORACLE view that contains the correct columns.
|
Table 5–13 shows how the fields of a Progress table convert to ORACLE equivalents.
Table 5–13: Sample Object Equivalents
Progress State Table
|
ORACLE STATE Table
|
Case-insensitive index: State
|
U##STATE
STATE
|
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.