Try OpenEdge Now
skip to main content
DataServer for Microsoft SQL Server
The DataServer Tutorial : Preparing to create demonstration databases
 

Preparing to create demonstration databases

Subsequent sections contain tutorials that illustrate using the DataServer utilities to maintain your schema holder. Before you use these tutorials, you must perform some preparatory steps, including creating the demonstration database and schema holder and accessing the DataServer's MS SQL Server Utilities menu.
The following lists identifies the basic preliminary steps you must complete to create demonstration databases:
*Install and start your data source. This specific step depends on your unique environment. As necessary, refer to your data source documentation.
*Install your DataServer drivers.
*Install the OpenEdge client. As necessary, refer to the OpenEdge client-related documentation.
Use the following steps to create the demonstration database and schema holder using the OpenEdge DB to MS SQL Server utility:
1. Start the ODBC Data Source Administrator tool for your data source. Refer to Configuring an ODBC driver and registering the data source.
2. Select the appropriate ODBC driver for the schema holder.
3. Select the target ODBC data source for the schema holder. Refer to Configuring a local DataServer and Configuring a remote DataServer.
4. Make any additional selections required by your data source.
5. Specify mysport as the data source name. OpenEdge recognizes the name that you use to register a data source.
You are not required to specify the data source name as mysport. However, identifying the data source name, using this suggested data source name or another one you choose, can help you to distinguish your tutorial data source from all other data sources you set up.
6. From the Start menu on the task bar, select OpenEdge and select the specific product you want to use to begin your OpenEdge session or type the following command line in Windows:
install-path\dlc\bin\prowin32
Note: Starting a local OpenEdge session also automatically starts the local DataServer.
7. Access the Data Administration. Create a copy of the sports database and connect to it.
8. Select DataServer > MS SQL Server Utilities > Schema Migration Tools > OpenEdge DB to MS SQL Server to start the OpenEdge DB to MS SQL Server utility.
9. Specify OpenEdge DB to MS SQL Server parameter values as described in the following table.
Table 48. OpenEdge-to-MS SQL Server Conversion utility
Interface element
Description
Original OpenEdge Database
Accept the default value; it is the name of the connected data source. For example, if you copied the sports database to create a demonstration database, the default value sports displays.
Connect parameters for OpenEdge
Accept the default value, which specifies the current working database.
Name of Schema holder Database
Type myholder, or another schema holder name of your choice.
Logical Database Name
Type the name that OpenEdge uses to identify the MS SQL Server database.
ODBC Data Source Name
Type the name you used when registering the data source. For example, if you accepted the ODBC Data Source Name suggested earlier in Preparing to create demonstration databases, type mysports. Otherwise, type the ODBC Data Source name you previously set up.
Username
Type the user ID for MS SQL Server.
User's Password
Type the password of the user.
Connect parameters
See Connecting the DataServer for connection parameters.
MaximumVarchar Length
8000 characters. The server LOB data types (text and varchar(max)) can be greater than 8000 bytes. If you are using a single byte character set and your character field display format or SQL width is less than 8000, a VARCHAR data type will be used. If the value is greater than 8000, then a server LOB type will be used.
NOTE: When Use Unicode Types is selected, the Unicode variable character type, NVARCHAR, is used when the maximum length is 4000 or less. The maximum length must now be set at 4000, the adjusted default, or less. If your character field display format or SQL width is greater than 4000, then a Unicode server LOB data type is used. Keep in mind that because UTF-8 character encoding can be up to four bytes, only 2000 characters are guaranteed to fit in a Unicode field.
Codepage
Accept the default code page value.
Collation
Accept the default value.
Insensitive
Accept the default value to retain case-insensitivity.
Load SQL
This check box is select by default, to specify that the generated SQL script loads into the data source. Leave it selected.
Move Data
Select the check box to dump and load data from OpenEdge to MS SQL Server.
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 the next table for the details you are prompted to enter.
Table 49. OpenEdge DB to MS SQL Server Conversion Advanced Options
Interface Elements
Description
Migrate Constraints
By default, the Migrate Constraints option is selected. This enables you to migrate constraint definitions defined using the procedures defined in Overview of foreign database constraint definitions.
ROWID sub-section
The ROWID sub-section groups selections for specifying ROWID and RECID. See ROWID sub-section of MS SQL Server Conversion for more information.
Map to MSS Datetime Type
Selected by default. Uncheck box to map to datetime data types with time zone components. For details, see DataServer for MS SQL Server support for datetime data types .
Create Shadow Columns
MS SQL Server is case-insensitive by default and, when used with a case-insensitive code page, this box is unmodifiable. 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.
Use Revised Sequence generator
Select to enable revised generator. Clear this option to retain older sequence generator, particularly if SET-VALUE sequence statement is used extensively in your program.
Try Native Sequence
Select to migrate OpenEdge Sequences to MS SQL sequences.
Note: This option selection will be successful only if the target MS SQL databse is MS SQL 2012 or newer.
Cache size
If the Try Native Sequence option is selected, then the Cache Size option is made available for entry. This determines the size of the cache that should be used on the server for the cache size. The following three values are applicable:
*0 = server (the cache size is determined by the server based on server criteria)
*"?" = No Cache("NO CACHE" option is appended to the native sequence creation on the server)
*Positive integer(1 or >1) - The number of sequences stored in the cache(Can range between 1 to the maximum value of the data type of the native sequence)
Use Unicode Types
Changes default code page to UTF-8 and directs the schema migration to convert all character data types in OpenEdge database to Unicode data types.
Expand width (utf-8)
Doubles the size of character fields based on specified field width criteria.
For field widths use:
When pushing fields to a foreign data source, you can select one of two primary field format 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 option is selected on by default to indicate that the format for the character fields defined as x(8) will be created as 30 characters.
You cannot use the Expand x(8) to 30 setting with the Width option.
Apply Uniqueness as
The Apply Uniqueness as option allows to you specify how uniqueness is defined. The default, Indexes, provides backward compatibility. The constraints option defines constraint definitions for uniqueness, but existing constraint definitions always take precedence over any constraint definition generation from this migration option.
Include Defaults
The Include Defaults includes an Apply Default as option that allows you to specify if you want your defaults defined as Field Attributes (default) or constraints. The Field Attributes option provides backward compatibility. The Constraints option only defines constraint definitions if a constraint definition doesn't already exist. If you select Constraints, then after the migration, all the initial values defined for migration become default constraint definitions on the server.
Note: For a complete description for running while in batch mode, see Running the OpenEdge DB to MS SQL Server utility.
Running the utility creates and connects a schema holder and the MS SQL Server database. It operates as follows:
a. SQL script is generated.
b. SQL that creates the schema is sent to the MS SQL Server data manager.
c. The schema is pulled back to the schema holder.
d. The schema holder and the OpenEdge database are compared and all information needed by OpenEdge is applied to the schema holder.
e. Data is loaded if selected.
f. The schema holder is disconnected.
g. A message is displayed that tells the user which startup procedure to use to connect. Additionally, the entered connect information is also stored in the db record which the DataServer can use to automatically connect.