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 silently (batch mode)
 

Running the Incremental Schema Migration utility silently (batch mode)

Use the following steps to run the Incremental Schema Migration utility silently (in batch mode):
1. Create or use an existing target MS SQL Server database
2. Configure your ODBC driver to connect to your target data source.
3. On your client machine, pass parameters to the utility by setting the environment variables listed in described in the table below that correspond to the Delta df to MS SQL Server Conversion dialog.
Note: To troubleshoot any errors or warnings, you can view the deltasqlutil.log.
Table 62. Generate Delta.sql OpenEdge to MSS utility
Environment variable
Interface element
Description
DELTADFNAME
Delta DF File
Default: <blank>
Specify the Delta.sql file by selecting the file name and location.
SHDBNAME
Schema Holder Database
Default: <blank>
Specify the new schema-holder name, in which the resultant logical database will reside.
Note: Dependencies: If SHDBNAME is not set, the migration terminates with an error.
MSSCONPARMS
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 Connecting the 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.
MSSPDNAME
Logical name for MSS Database
Specify the logical database name configured to connect the foreign data source.
Dependencies: If MSSPDBNAME 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.
MSSUSERNAME
MSS Object Owner Name
Default: <blank>
Specify the user name for the target data source.
Dependencies: If MSSUSERNAME is not set, the migration terminates with an error.
VARLENGTH
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.
COMPATIBLE
Create RECID field
Default: Yes or 1
Determines if the migration must generate table definitions that provide compatibility with OpenEdge ROWID, RECID and with OpenEdge extent fields. When COMPATIBLE is set to NO, extent fields are not migrated, and array fields in OpenEdge databases are properly migrated to the server so that references to extend data types are properly mapped to the foreign data source and are handled the same as OpenEdge handles extents in the ABL.
When COMPATIBLE and GENROWID are both set to NO, PROGRESS_RECID is not generated to support OpenEdge ROWID and RECID and a version of the Select 'Best' ROWID Index algorithm is instead used to try to find existing indexes to support ROWID. For the Select 'Best' ROWID Index algorithm to support RECID in a backward compatible way, COMPATIBLE, GENROWID, MIGRATECONSTR, MAPOEPRIMARY and GENUNIQROWID options must all be unset. If any of these ROWID options are set in the conversion, the Select 'Best' ROWID Index algorithm will try to produce an index to support ROWID but that index may or may not support RECID.
When COMPATIBLE= 1 or Yes, it uses the legacy trigger behavior on the server to support PROGRESS_RECID.
When COMPATIBLE= 2, it uses the computed column approach to supporting PROGRESS_RECID on the server.
Dependencies:
*In releases prior to OpenEdge 11, when COMPATIBLE=YES, ROWID and RECID selection was resolved by adding the PROGRESS_RECID field to the server table definitions. Alternatively in prior releases, when COMPATIBLE=NO, a legacy version of the Select 'Best' ROWID Index option, used starting in OpenEdge 11, was used to find an existing index capable of supporting both ROWID and RECID.
Toggling the COMPATIBLE value on or off was equivalent to toggling the Create RECID Field option on or off in MS SQL Server DataServer versions prior to OpenEdge 11.
Beginning in OpenEdge 11.1, the legacy COMPATIBLE option is extended by GENROWID, MIGRATECONSTR, MAPOEPRIMARY and GENUNIQROWID options that are used to generate OpenEdge-compatible ROWID solutions that may or many not be compatible with OpenEdge RECID.
*You can simulate the behavior of OpenEdge releases prior to Version 11.0 by not setting the ROWID section options and by only setting the COMPATIBLE option to YES, 1 or 2.
The GENROWID option is active by default so that the migration does take advantage of the new ROWID features (that are not backward-compatible) by default. Explicitly unset GENROWID and all other ROWID options (GENROWID, MIGRATECONSTR, MAPOEPRIMARY and MAPOEPRIMARY) if you want backward compatible behavior.
*While behavior of the GENROWID option is similar to that of the COMPATIBLE option, the COMPATIBLE option also controls whether extent fields will be migrated and whether the trigger or computed column solution is used to support PROGRESS_RECID. Also, when COMPATIBLE is set to NO and all of the new ROWID options are unset (GENROWID, MIGRATECONSTR, MAPOEPRIMARY and MAPOEPRIMARY) a legacy version of the Select 'Best' ROWID Index option's algorithm for ROWID selection can be used. The Select 'Best' ROWID Index algorithm employed by the GENROWID option is advanced and is not backward compatible with the COMPATIBLE option as set in releases prior to OpenEdge 11.0.
For a detailed explanation of how COMPATIBLE and GENROWID options work together, see the NOTE section at the end of Table 60.
SHADOWCOL
Create Shadow Columns
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.
Dependencies: If MSSCASESEN is set to YES, the SHADOWCOL value is ignored.
CRTDEFAULT
Include Default
Default: NO
Specify YES to include OpenEdge initial values in the schema image definitions of the foreign data source in the schema holder database with fields migrated to columns in the foreign data source.
When CRTDEFAULT is off, the initial values are assigned out of the schema image to records created in the foreign data source by the OpenEdge DataServer.
When CRTDEFAULT is YES, the initial values are actually transferred and become part of the server definitions, not just the schema image definitions.
CRTDELTADF
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.
UNICODETYPES
Use Unicode Types
Default: NO
Maps OpenEdge character fields to MSS 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.
UNICODE_EXPAND
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.
MAPMSSDATETIME
Map to MSS Datetime Type
Default: Yes
Specifies whether DATE and DATETIME data types in OpenEdge must map to legacy DATETIME timestamp data types in MSS Server.
If set to Yes, it provides backward-compatibility to earlier version of OpenEdge and MSS Server. However, if your database target is MSS 2008 or above and MAPSMSSDATETIME is set to No, more accurate mapping takes place between OpenEdge data and time data types and the new data and time data types in MSS Server.
MSSREVSEQGEN
Use Generator
Default: NO
Determines if migration must use generator or the old sequence generator when migrating sequences to foreign data source. When MSSREVSEQGEN is set to NO, the legacy sequence generator is migrated instead of the revised version.
EXPANDX8
Expand x(8) to 30
Default: Yes
Determines whether character fields set to the default format size of x(8) should be expanded to a 30 character default. This expands the default length on the foreign data source.
Dependencies: The option only affects the batch conversion if SQLWIDTH is set to NO, which utilizes 4GL Format for character size translation to the foreign data source. If SQLWIDTH is set to YES, this option is ignored.
SQLWIDTH
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.
Dependencies: 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.
GENROWID
For Create RECID use:
Default: Yes or 1
Determines if the migration must generate table definitions that provide compatibility with OpenEdge ROWID and RECID. When COMPATIBLE and GENROWID are both set to no, PROGRESS_RECID is not generated to support OpenEdge ROWID and RECID.
When GENROWID= 1 or Yes, it is the equivalent of COMPATIBLE= 1 or Yes with regard to support for OpenEdge ROWID and RECID; except that its value does not determine whether the trigger or computed column approaches should be used to support PROGRESS_RECID. Only COMPATIBLE determines that behavior.
When GENROWID= 2, it is equivalent to setting GENROWID=1 except that when the PROGRESS_RECID index is created on the server and designated as the ROWID/RECID selection, it also creates a primary constraint over PROGRESS_RECID index key.
Dependencies:
*Whenever any of the OpenEdge 11 ROWID selection options are set along with or independent of the COMPATIBLE environment variable, the conversion process is incompatible with legacy conversion routines in terms of how the ROWID designation is selected.
*If COMPATIBLE=NO and GENROWID=NO, then GETBESTROWID will be set to YES whether it was specified in the environment variables or not. A legacy version of GETBESTROWID is set to YES by default when COMPATIBLE=NO and is GETBESTROWID is set to NO when COMPATIBLE=YES, 1 or 2.
Starting in OpenEdge 11.0 when GENROWID=NO, a new algorithm for GETBESTROWID is used by default. When GETBESTROWID=YES, it is mutually exclusive with the ROWID/RECID selection activities of both COMPATIBLE and GENROWID. But, the GETBESTROWID algorithm gets turned on and off like a toggle switch when COMPATIBLE is used for ROWID/RECID selection.
Whereas, when the GETBESTROWID algorithm is on starting in OpenEdge 11.0, its operations can be complimentary to other ROWID options available starting OpenEdge 11.0. Also, the existing index selected by the GETBESTROWID algorithm for ROWID starting in OpenEdge 11.0 may or may not be compatible with OpenEdge RECID functionality. Only when the ROWID index selected by the algorithm is a single component integer or Big integer key is it also compatible with RECID.
For a detailed explanation of how COMPATIBLE and GENROWID options work together, see the NOTE section at the end of Table 60.
4. Execute the following command to set and export environment variables at the system prompt:
DELTADFNAME=FileName.df
SHDBNAME=MyDatabase
export DELTADFNAME SHDBNAME
.
.
.
5. Execute the following command in the Proenv command prompt to perform an incremental update to MS SQL Server:
pro -b -p prodict/mss/deltasql.p
After running the utility, you must apply the SQL data 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 must also confirm that none of the data is lost.
The Incremental Schema Migration utility generates SQL that will create objects in the MS SQL Server database that are compatible with OpenEdge.
The following table shows that the utility creates the same objects as the OpenEdge DB to MS SQL Server migration utility.
Table 63. MS SQL Server equivalents of OpenEdge objects
OpenEdge object
MS SQL Server equivalents
Array
One column for each extent of the OpenEdge array.
Table
For new tables, if the Create RECID Field option was selected, a PROGRESS_RECID column and associated triggers are created. This indexed column provides a unique key on the MS SQL Server table.
The utility ensures that the migrated objects have names that are unique to the MS SQL Server 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 MS SQL Server 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.