Try OpenEdge Now
skip to main content
DataServer for Microsoft SQL Server
The DataServer Tutorial : Migrating an OpenEdge database to MS SQL Server : Running the OpenEdge DB to MS SQL Server utility
 

Running the OpenEdge DB to MS SQL Server utility

The OpenEdge DB to MS SQL Server utility runs in Windows with a DataServer accessing MS SQL Server through an ODBC driver.
You can run the utility interactively from Windows or UNIX, or in batch mode. All the logs are logged at protomss.log.
To run the OpenEdge DB to MS SQL Server utility interactively:
1. Create a target MS SQL Server. You must use an empty target data source when you run the OpenEdge DB to MS SQL Server utility.
2. Configure your ODBC driver to connect to your new target data source.
3. Start the OpenEdge client and connect to the OpenEdge database that you want to migrate to the target data source.
Note: For a DBE (double-byte enabled) DataServer application, you must specify the Internal Code Page (-cpinternal) and Stream Code Page (-cpstream) parameters when you start the OpenEdge client. The values that you specify for these parameters must match the code page that the target data source uses.
4. Start the utility using one of the following methods:
*From the Data Administration, choose DataServer > MS SQL Server Utilities > Schema Migration Tools > OpenEdge DB to MS SQL Server.
*Independently run the OpenEdge DB to MS SQL Server utility in the system prompt using the protomss utility:
pro -p prodict/mss/protomss.p
All the warnings, errors, or causes of termination in the conversion process can be found in the log file, protomss.log.
The following screen appears:
Figure 26. OpenEdge to MS SQL Server Conversion dialog box
5. Enter the information required.
The following table describes the information you are prompted to enter.
Table 56. OpenEdge-to-MS SQL Server Conversion utility
Interface element
Description
Original OpenEdge Database
Default: <blank>
Specifies a source OpenEdge database to be migrated.
If this interface element is unset and the user has already set a current working database, the conversion utility uses the current working database to set the value for the original OpenEdge database.
Dependencies: If this element is unset, and no other working database is pre-connected, the migration cannot proceed.
Connect parameters for OpenEdge
Default: current working database parameters
Specifies startup parameters used to connect the Original OpenEdge Database.
Dependencies: It is required that the Original OpenEdge Database is started in single-user or read-only mode. Therefore, if you provide your own connect parameters for OpenEdge, you must include settings to connect to the Original OpenEdge Database in the single-user mode or read-only mode to avoid connection failure.
Name of Schema Holder Database
Default: <blank>
Specify the new schema-holder name, in which the resultant logical database will reside.
Dependencies: If a schema holder database is not set, the migration cannot proceed.
Logical Database Name
Default: <blank>
Specify the logical database name associated with the schema image of the foreign data.
Dependencies: If the logical database name is not set, the migration cannot proceed.
The Logical Database Name must be different from the Name of the Schema Holder Database.
ODBC Data Source Name
Default: <blank>
Specify the ODBC Data Source name used to configure the OEDB connection to the foreign data source.
Dependencies: If the ODBC Data Source Name is not set, the migration cannot proceed.
The value for ODBC Data Source Name can be the same as Original OpenEdge Database name but must be different from the Name of the Schema Holder Database.
The ODBC DNS must be properly specified in the ODBC Data Source Administrator of your system. If the Load SQL interface element is set to YES, ODBC DNS must also be properly configured to connect to the foreign Data Source.
Username
Default: <blank>
Specify the user name for the target data source.
Dependencies: If you do not set the user name required for authentication, the migration terminates with an error.
User's Password
Default: <blank>
Specify the password of the user for the target data source.
Dependencies: If you do not set the password required for authentication, the migration terminates with an error.
Connect parameters for the DataServer connection
Default: <blank>
Specifies the startup parameters for the DataServer and the target foreign data source.For more information on connection parameters, see Connecting the DataServer for connection parameters.
Dependencies: Connect parameters are not mandatory. If supplied, they must be formatted as a comma-separated list of names or name-value pairs, without white-spaces, that comprise the -Dsrv parameter value passed to the DataServer connection. Some of the parameters are parsed into the DataServer run-time, while others in the list are forwarded onto the target foreign data source.
If an invalid or improperly formatted connection parameter is parsed during migration, the migration terminates with an error.
MaximumVarchar Length
Default: The unconditioned default is 8000. It ranges from 4000 to 8000.
Specify the maximum number of characters for a VARCHAR type. When a single-byte character set is utilized (as specified by the Code Page interface element), it must be a positive value less than or equal to 8000. The default threshold value of 8000 is the maximum number of characters that can be stored in a VARCHAR data type in MS SQL Server. When Unicode character data is utilized by the application, the default threshold value is limited to 4000. This value can also be overridden. During OpenEdge to MS SQL Server migration, whenever the column length of character data exceeds the value specified for VARLENGTH, the column is instead migrated as a Large Object (LOB) data type that can store data that far exceeds the data that can be stored in a VARCHAR data type.
Dependencies: When MSSCODEPAGE is set to utf-8, the value set for VARLENGTH must be a positive value less than or equal to 4000 as it is the maximum number of characters that can be stored in a NVARCHAR data type in MS SQL Server due to UCS-2 character expansion. And, character lengths exceeding the threshold value set for VARLENGTH are converted to Unicode Large Object types.
When an OpenEdge database stores UTF-8 data, each OpenEdge character can be as large as 4 bytes, potentially reducing the Maximum Varchar Length to 2000 characters on the server. But generally, MS SQL Server only supports the UCS-2 character set that allows 4000 UTF-8 characters in the UCS-2 range to be migrated into a Varchar column on the server.
Codepage
Default: iso8859-1 when UNICODETYPES is disabled (that is, set to NO). Else, the default is utf-8.
Specifies the corresponding OpenEdge name for the code page with which the MSS Database is compatible. Use UTF-8 if Unicode support is desired.
If you use a code page that OpenEdge does not support, you must supply a conversion table that translates between the OpenEdge client code page and the code page that your data source uses. For a complete discussion of code pages, see OpenEdge Development: Internationalizing Applications.
It is not mandatory to pass this value during batch migration. You can use Change DataServer Schema Code Page utility to add the code page information to the schema holder later but before you start using the DataServer to read and write data. For more information on changing code page, see Changing a code page in a schema holder.
Dependencies: The specified codepage must exist in the convmap.cp file of your Progress environment in order to be used during the migration.
Collation
Default: Basic
Specifies the collation name with which the OpenEdge client must collate and weigh code page values.
Dependencies: The specified collation must exist in the convmap.cp file of your Progress environment in order to be used during the migration unless you are using a Unicode code page. In case you are using a Unicode code page, then the code page name can correspond to one of the International Components for Unicode (ICU) collations that provide linguistic sorting of Unicode data based on the Unicode Collation Algorithm.
Insensitive
Default: NO
Specifies if the code page that you are matching on the foreign data source is or isn't case insensitive. Provide YES if your server code page is case insensitive, else retain the default value NO.
Dependencies: By default, the code pages in MS SQL Server are case-insensitive. This matches the default sensitivity in OpenEdge. So, if you are using a case-insensitive code page in MS SQL Server, Progress recommends setting this value to YES.
Load SQL
Default: Not selected (NO)
Allows you to specify whether you want the utility to execute the SQL script that is generated in order to create the schema in the target empty MS SQL Server database on the server that was configured through your ODBC Data Source Name. Specify YES to enable this behavior.
Move Data
Default: Not selected (NO)
Allows you to specify whether to populate the foreign data source with your OpenEdge data after the schema has been migrated. Specify YES to dump and load data or NO to not populate the database. For example, you might specify NO if your database is large, and you want to dump and load data at a more convenient time.
Dependencies: Load SQL must be selected in order to move data.
Genenerate Rank Report
Default: Not selected (NO)
When selected, this option generates a ranking selection report, rnkreppdb.out. This report contains details of the selections you made in the OpenEdge-to-MS SQL Server Conversion utility.
Advanced
Click Advanced to open the OpenEdge DB to MS SQL Server Conversion Advanced Options dialog box. The purpose of this dialog box is to reduce clutter on the main dialog box and provide logical grouping of fields on it. See Table 57 for the details you must enter.
6. Click Advanced to edit the advanced settings.
The OpenEdge DB to MS SQL Server Conversion dialog box appears:
Figure 27. OpenEdge DB to MS SQL Server Conversion Advanced Options
7. Enter the information required.
The following table describes the information you are prompted to enter.
Table 57. OpenEdge DB to MS SQL Server Conversion Advanced Options
Interface Elements
Description
Migrate Constraints
Selecting this option enables you to migrate any constraint definitions defined using the procedures defined in Overview of foreign database constraint definitions.
Now, the Migrate Constraints box is not selected by default in order to maintain backward compatibility with previous releases.
Note: In OpenEdge Version 11.0 through 11.2, Migrate Constraints was selected by default and could still maintain backward compatibility. OpenEdge now has a new migration algorithms may be invoked as soon as a non-default option is selected from the ROWID-related options of the Advanced menu.
ROWID sub-section
Select to specify ROWID and RECID. See ROWID sub-section of MS SQL Server Conversion for more information.
Note: While the Migrate Constraints option is not a direct mechanism for establishing ROWID and RECID, in contrast to the options in the ROWID sub-section, the presence of a Primary or Clustered constraint migrated from OpenEdge supersede the specifications for ROWID and RECID selected in the ROWID sub-section that might have otherwise derived a Primary and/or clustered index corresponding to a ROWID/RECID designation.
Map to MSS Datetime Type
Selected by default to provide backward compatibility to legacy DATETIME data types in MS SQL Server.
Uncheck box to map to datetime server data types with time zone components that support time zone component with a stronger sub-section time precision. For details, see DataServer for MS SQL Server support for datetime data types.
Create Shadow Columns
When SQL Server is configured with a case sensitive code page, this box is available for selection. Selecting it provides case insensitivity compatible with the behavior of an OpenEdge database.
Note: If Create Shadow Columns is not selected with a case-sensitive code page, then case-insensitivity in the OpenEdge schema supports the UPPER() function in run-time SQL translation. Ensure that you use this option appropriately because the inclusion of the UPPER() function in SQL translation might negatively impact server performance.
Dependencies: MS SQL Server uses case insensitive by default, which is why the Insensitive option on the main menu of the Conversion options is YES by default. If the Insensitive option remains equal to YES, then the Create Shadow Column option is disables and ignored.
Use generator
Selected by default. Enables revised generator. Not selecting the option retains older sequence generator, particularly if SET-VALUE sequence statement is used extensively in your program.
Use Unicode Types
Maps OpenEdge character fields to MS SQL Server Unicode data types.
Dependencies: Selecting this option changes the default code page to UTF-8 and directs the schema migration to convert all character and CLOB data types in the OpenEdge database to Unicode data types on the server.
Expand width (utf-8)
Doubles the size of character fields to accommodate Unicode expansion of single-byte data based on specified field width criteria.
For field widths use:
When pushing fields to a foreign data source, you can select one of two character field formatting options:
*Width — Uses the value of the _width field in the _field record.
Note: Recommended for Unicode implementations. For more information on column width, see Column width.
*4GL Format — Compiles with the current default width specified. (default)
If you select the 4GL 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.
Dependencies: The Width and 4GL Format options are mutually exclusive options and you cannot use the Expand x(8) to 30 setting with the Width option.
Apply Uniqueness as
The Apply Uniqueness as allows you to specify how uniqueness is defined. The default, Index Attributes, provides backward compatibility by marking the UNIQUE attribute on a created index. The Constraints option defines named constraint definitions for uniqueness independent of the index definition.
Dependencies: Existing UNIQUE constraints defined on the server always take precedence over any constraint definitions that would otherwise be redundantly generated using this option.
Include Defaults
The Include Defaults check box is indicates whether or not, as part of migration, you want to include OpenEdge initial values in the fields of the foreign data source's column schema. The Apply Default as option further enables you to decide how the initial values must be expressed in your database and application environment.
Apply Defaults as
The Apply Default as enables you to specify how defaults must be applied to the DataServer implementation.
The Include Defaults check box is enhanced with an Apply Default as radio set that enables you to specify if your defaults must be defined as Field Attributes (default) or Constraints. The Field Attributes option provides backward compatibility. The Constraints option specifies constraint definitions if a constraint definition doesn't already exist. If the user selects Constraints, then after the migration, all the initial values defined for migration become named default constraint definitions on the server.
To perform a complete migration of your OpenEdge database to a target data source, you must enter information in all appropriate fields and select all appropriate check boxes.
The utility creates a schema holder, updates the empty target data source that you created to contain the objects stored in your OpenEdge database, and creates a startup procedure that you can use to connect your schema holder. The startup procedure derives its name from the logical name for your target database. For example, if you specified "sports" as the logical data source name, the utility creates the csports.p startup procedure.
* Running the conversion in batch mode