Try OpenEdge Now
skip to main content
DataServer for Microsoft SQL Server
The DataServer Tutorial : Delta df to MS SQL Server Incremental Schema Migration utility : Running the Incremental Schema Migration utility interactively
 

Running the Incremental Schema Migration utility interactively

Use the following steps to run the Incremental Schema Migration utility interactively:
1. Generate delta.sql using one of the following methods:
*From the Data Admin main menu, choose DataServers > MS SQL Server Utilities > Schema Migration Tools > Generate Delta.sql OpenEdge to MSS.
All the selected options, errors, and warning messages are are logged at deltasqlutil.log.
*Execute the following command in the command prompt to Independently perform an incremental update to MS SQL Server:
pro -p prodict/mss/deltasql.p
All the selected options, errors, and warning messages are logged at deltasql.log.
The Delta df to MS SQL Server Conversion dialog box appears:
2. Provide the information described in the following table.
Table 61. Generate Delta.sql OpenEdge to MSS utility
Interface element
Description
Delta DF File
Default: <blank>
Specify the Delta.sql file by selecting the file location.
Schema Holder Database
Default: <blank>
Specify the new schema-holder name, in which the resultant logical database must reside.
Note: If this is not set, the migration terminates with an error.
Connect parameters for Schema
Default: <blank>
Specifies the startup parameters for the DataServer and the target foreign data source.
For more information on connection parameters, see Connectingthe DataServer for connection parameters.
Note: 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.
Logical name for MSS Database
Specify the ODBC data source name configured to connect the foreign data source.
Dependencies: If this option is not set, the migration terminates with an error. The ODBC DSN must be properly specified and located in the ODBC Data Source Administration of your system.
MSS Object Owner Name
Default: <blank>
Specify the user name for the target data source.
Dependencies: If this option is not set, the migration terminates with an error.
Maximum Varchar 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 determined by the selected MSSCODEPAGE value, this 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 virtue of a Unicode CODEPAGE environment variable selection, 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.
Create RECID field
Default: YES or 1 (Trigger)
Set it to 1 to create PROGRESS_RECID field using trigger approach to RECID or ROWID construction.
Select either 1 (Trigger) or 2 (Computed Column).
Select Trigger if you want the migrated table to designate RECID and ROWID.
Select Computed Column if you want the migrated table to make ROWID unique. Computed Column is preferred as it offers greater reliability, but it is only available for MS SQL Server 2005 and later
.
Include Default
Default: NO
Determines if shadow columns must be added to the record layout to represent data in upper case form for OpenEdge fields marked case-insensitive. Therefore, if MSSCASESEN is set to YES, the SHADOWCOL value is ignored.
Create Shadow Columns
Default: NO
Specify YES to include OpenEdge initial values with fields migrated to columns in the foreign data source.
Dependencies: When SQL Server is configured with a case sensitive code page, the OpenEdge fields marked as case insensitive are migrated to MSS as case insensitive records in MSS. In this process, a shadow column is added to the record.
Create schema holder delta df
Default: NO
Specify YES 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 check box is selected.
Use Unicode Types
Default: NO
Maps OpenEdge character fields to MSS Server unicode data types.
Set to YES to create character columns with unicode specification, and NO otherwise.
Expand Width (utf-8)
Default: NO
Set to YES to double the length of fields on conversion, and NO otherwise.
If set to YES, the single-byte characters receive adequate size as double-byte characters in MSS UCS-2 format.
Map to MSS Datetime Type
Default: YES
Specifies if OpenEdge DATE and DATATIME data types must be mapped to legacy DATETIME timestamp data types in MSS. Therefore, if set to YES, it provides backward compatibility to the previous versions of OpenEdge and MSS.
Deselect the check box if your database target is MS SQL Server 2008 or above. For details, see DataServer for MS SQL Server support for datetime data types.
Use Generator
Default: YES
Specifies weather to use the generator instead of the legacy sequence generator when migrating sequences to the foreign data source.
When set to NO, the legacy sequence generator is migrated. Progress recommends that you use the revised generator as it provides better performance.
Expand x(8) to 30
Default: YES
Set to YES to double the length of fields on conversion, and NO otherwise.
If set to YES, the single-byte characters receive adequate size as double-byte characters in MSS UCS-2 format. Specify NO if you want the column size of the server calculated directly from the x(8) format value.
Dependencies: This option is ignored if SQLWIDTH is set to YES.
For field widths use:
*Width
*ABL format
Default: NO
Specifies if the migration must use the Width field to calculate column size instead of using the format field.
Specify YES to use the Width field in the column's schema image to calculate column size in place of the default format field, else ABL format is used to size migrated column.
Dependencies: You cannot use the Expand x(8) to 30 setting with the Width option.
3. Choose OK. The Pre-selection utility generates a delta.sql file and, optionally, a delta.df file.
When this step is performed for an MS SQL Server DataServer, and the incremental update procedure includes changing an existing field from INTEGER to INT64 in the schema holder, the DataServer supports the update process through the ALTER TABLE ALTER COLUMN statement. If a column is part of an index, the incremental schema migration utility generates a script to drop any indexes that contain the column, change the column's data type to BIGINT, and then re-adds the indexes. During this process, the utility uses the index definitions defined in the schema holder.
4. After running the utility, you must apply the SQL that the utility generates to the MS SQL Server database and load the new delta.df file into the original schema holder so that it is synchronized with the modified MS SQL Server database.
Note: Note that if you updated an existing field from INTEGER to INT64, you also must confirm that none of the data is lost.