To configure the OpenEdge DataServer for Microsoft SQL Server or Oracle, you must configure the Open Database Connectivity driver, create a schema holder, and create a target database.

Pro2 is designed to replicate data as one-way connection from the OpenEdge source database to the target database. To facilitate this one way connection, Pro2 assumes the target database is read-only. If you plan to make changes to your target database, be aware that these changes can be overwritten by Pro2 when a tables or row are updated one the source database.

Standard data-type conversions

Pro2 performs some data type conversions automatically to circumvent issues that would typically break other types of Open Database Connectivity (ODBC) data transfers from OpenEdge to a Microsoft SQL Server or Oracle database.
  • Date conversions— Some versions of Microsoft SQL Server and Oracle databases cannot handle dates with a value before January 1, 1753. To work around this issue, Pro2 preserves the month and the date and sets the year to 1753.
  • Column width—In OpenEdge databases, data is stored in variable length fields, regardless of the field width definition. However, in Microsoft SQL Server and Oracle databases, column width is fixed. This would cause the Microsoft SQL Server or Oracle database to have a larger data footprint than the source OpenEdge database. To work around this, Pro2 truncates data to the column width that is defined in the target schema.
  • Logical fields—The OpenEdge database recognizes three possible values for logical fields: True (1), False (0), and Unknown (?). The value Unknown is not recognized in other databases. To work around this, if a logical value is True, then Pro2 sets the target field to True, otherwise it is set it to False.

Data types on the target side can be the same or different from the source database. For example, precision of decimal types can be decreased, logical fields can be changed to character, or integers can be changed to logical.

If you do not need the standard data-types conversions, you can enable the TGT_DATATYPE_OVERRIDE property. This property retains the value of a data-type and does not convert it to adhere to the target schema requirements. However, you must recheck the validations as they may fail.

Table and column names on the target database can be the same or different from those on the source database. If they are the same, the Pro2 auto-map function on the mapping page of the user interface can be used. Otherwise, the tables need to be manually mapped. However, table mapping is required only once and can be reused in subsequent implementations.

If you want to use literal table and column names for your target SQL database, enable the TGT_USE_LITERAL_NAMES property. This property allows you to use the same naming convention as the source database for tables and columns without modifying them to meet the requirements of the target database.

Configure the ODBC DataServer

The ODBC connectivity must be set up before you configure the OpenEdge DataServer. This allows communication between the OpenEdge database and another database.

To configure the ODBC DataServer:

  1. Add a new system DSN using the Windows Data Sources tool by selecting the System DNS Tab in the Data Sources (ODBC) Administrative Tools section in the Control Panel.
  2. Click Add to add a new system DSN.

  3. For Microsoft SQL Server, choose the SQL Server driver. For Oracle, select the Microsoft ODBC for Oracle driver and click Finish.
  4. Specify the name (for example logical-db-nameODBC) for your data source and the server where it is located, then click Next.
  5. Provide your login information. If you are using Select SQL authentication, provide the login ID and password for the Microsoft SQL Server database, and click Next.
  6. From the Change the default database to drop-down, select the appropriate database, and click Next.
  7. Accept the defaults on the next dialog box, and click Next.
  8. Click the Test connection button to confirm that you are connected.
  9. Exit the Data Source Administration tool.

Create an OpenEdge schema holder

An empty OpenEdge database is created to act as the schema holder for the Microsoft SQL Server or Oracle database. The schema holder is the layer that allows ABL to interact with the Microsoft SQL Server or Oracle database as though it were a native OpenEdge database.

To create a schema holder:

  1. Open a Proenv window, and type prodb database name empty to create an empty schema holder database.
  2. Start a single user session by typing prowin database name -1.

    The Procedure Editor window appears.

  3. From the Procedure Editor, select Tools > Data Administration.
  4. Select DataServer > database type Utilities > Create DataServer Schema .

    The Create/Modify Record for Schema window appears.

  5. Specify Logical Database Name.
  6. If the source database is a SQL server, specify the ODBC Data Source Name. Click Ok.
  7. Enter the Login ID and Password if required in the next dialog box and click OK.
  8. Enter criteria for Object Name, Object Owner (typically, dbo), and Qualifier.
  9. Select the Default to OpenEdge DATETIME option.
  10. If you are using LOB fields in replication, ensure that the Default to OpenEdge LOB for BLOBs option is selected along with the CLOBs option. This forces the data server to automatically consider certain target side data types to be treated as LOBs.

    By selecting CLOB, varchar(max)/nvarchar(max) data types are automatically converted to CLOB. By selecting BLOB the data server is forced to consider all varbinary(max) as BLOB. On source fields that are character types, Pro2 may convert some types to varchar(max) when the source width is wider than the MAX_CHAR_WIDTH property setting. This conversion has the added benefit of avoiding row size limits for SQL Servers. However, varchar(max)/nvarchar(max) are not necessarily treated as CLOBS.



  11. Click OK to finish.

Target Side Only Tables and Fields

The following tables are added to the target database for Pro2 use:

Pro2SQL – used by Pro2 to verify connection to target database.

PRROWID – Unique key that corresponds to the source Progress ROWID.

Pro2created – Date/timestamp of the record was first created.

Pro2modified – Date/timestamp of the record was last modified.

Pro2SrcPDB – Name of the source database.

Progress_RECID – Used by Pro2ORA. Required for Pro2SQL with source databases before OpenEdge release 10.2.

Progress_RECID_Ident – Required for Pro2ORA and Pro2SQL with source databases before OpenEdge release 10.2.

Indexes in the SQL Target

Pro2 generates a script to copy all source side indexes to the target database with the exception that any unique keys are translated as non-unique. The source side indexes are not used by Pro2 and are not required. Not creating or dropping unused or unnecessary indexes on the target side can optimize Pro2 performance as there will be no overhead for index maintenance.

One of the benefits of the Pro2aql table is the that it enables you to add indexes on the SQL target database. Most OpenEdge applications have an index structure for Online Transaction Processing (OLTP), however many reporting tools operate more efficiently with an index structure for Online Analytical Processing (OLAP). Adding indexes for OLAP to the SQL target database is useful and recommended, but there is one caveat. The only unique index allowed on the SQL target database is the index on the PRROWID field. If there are UNIQUE indexes other than the PRROWID index, then replication may fail due to a UNIQUE key constraint. There is little value of a UNIQUE index in an OLAP environment, so all additional indexes should not be UNIQUE. If unique index is required, it is strongly recommended that the PRROWID column be part of that index.

Create target Microsoft SQL Server or Oracle database

If you have not done so already, create a target database for you replicated data. The Target database can be either a Microsoft SQL server, Oracle, or OpenEdge database. Creating a target database will depend largely on they type of database you want to use and your development tooling. The following procedure lists the general steps to creating your target database.

  1. In your database admin tool, like Microsoft SQL server Management Studio, create a new database and name it according to your needs.
  2. Create a new database query, and load the schema file into the query.

For more information about creating a Microsoft SQL Server database, see Microsoft SQL documentation.

For more information about creating an Oracle database, see Oracle Database Documentation

You can now configure the replication process and proceed with the replication.