Creating a schema holder

To create the schema holder:

  1. From the Data Administration main menu, select DataServer > MS SQL Server Utilities > Create DataServer Schema.

    The following dialog box appears:



  2. In the Logical Database Name field, type the name that you will use to connect to your data source and refer to it in your programming applications. This name must be different from the schema holder name. For more information on database names, see the database access chapter in Develop ABL Applications.
    Note: If you place the schema from a second MS SQL Server database into a schema holder, the second schema must have a different logical database name from the first schema. The schema holder has one physical name, but each schema that it contains must have a different logical name.
  3. In the Code Page field, type the name of the code page for the schema holder. The name must be the OpenEdge name for the code page that the data source uses. The default is iso8859-1. If you choose UTF-8 as your schema image code page, your schema holder's code page must also be UTF-8.

    The following table lists the most common MS SQL Server database code pages and the equivalent OpenEdge names.

    Table 1. MS SQL Server and OpenEdge code pages
    MS SQL Server Code page OpenEdge equivalent
    iso_1 iso8859-1 (default schema-holder code page)
    cp850 ibm850

    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.

  4. In the Collation field, enter the name of the collation rule to use. The default is Basic. See Code pages for a discussion of collation issues to consider.
  5. In the Case Insensitive field, the default value yes indicates that MS SQL Server's case insensitivity feature is in use. To change this value, type no.
  6. Type the connection parameters in the Connection Parameters field.
    See Connecting the DataServer for a description of the required and optional connection parameters.
  7. In the ODBC Data Source Name field, type the name that you used when you registered the data source with the ODBC administration tool.
  8. Choose OK.
    The utility prompts you for your data source user ID and password. If they are required by the MS SQL Server data source and you did not provide them in the Connection Parameters field (see #rfi1436298960224__i84641), enter a data-source user ID and password combination that has SELECT privileges for the system objects listed in Authorization and authentication and read access to other database objects that the schema holder will include.
  9. Choose OK. When the DataServer connects to the MS SQL Server database, it reads information about data source objects. The Pre-Selection Criteria For Schema Pull dialog box appears:


  10. Provide the information described in the the following table:
    Table 2. Pre-Selection Criteria For Schema Pull
    Interface element Description
    Object name

    Specify an Object name qualifier for objects to be pulled for the server. The wild card default selects all objects. For example, you can specify A* in the Object Name field to list all the tables whose names begin with A or a.

    Note: You should not specify an entry that consists exclusively of wild cards for each of the three entry fields in the dialog box. An entry that consists exclusively of wild cards might degrade the performance of the database when you perform a schema pull. (It will include system catalog files from the data source not typically included in user databases.)
    Owner information

    Specify an Object owner to be pulled for the server. The wild card default selects all objects. For example, you can specify DS* in the Owner information field to list all the tables whose names begin with A or a.

    Note: You should not specify an entry that consists exclusively of wild cards for each of the three entry fields in the dialog box. An entry that consists exclusively of wild cards might degrade the performance of the database when you perform a schema pull. (It will include system catalog files from the data source not typically included in user databases.)
    Qualifier

    Specify an Object's qualifying database name to be pulled for the server. The wild card default selects all objects.

    Note: You should not specify an entry that consists exclusively of wild cards for each of the three entry fields in the dialog box. An entry that consists exclusively of wild cards might degrade the performance of the database when you perform a schema pull. (It will include system catalog files from the data source not typically included in user databases.)
    Default to OpenEdge DATETIME

    Select Default to OpenEdge DATETIME to automatically map MS SQL Server timestamp data types to the associated OpenEdge equivalent DATETIME data type.

    If this check box is not selected, server timestamp data types map to DATE data type in OpenEdge for backward compatibility.
    Default to OpenEdge LOB for:

    If you have modified your client application to handle LOB data types, select Default to OpenEdge LOB for: CLOBs and/or BLOBs to map the OpenEdge LOB data type to MS SQL Server VARBINARY(MAX), IMAGE and FILESTREAM data types.

    If you do not select this option, all server LOB data types map to the CHARACTER data type in OpenEdge for backward-compatible.

    For more information on mapping OpenEdge and MS SQL Server data types, see Support for OpenEdge ABL BLOB data type.
    Designate Primary/Clustered index as ROWID Specifying Designate Primary/Clustered index as ROWID for a table with a defined clustered index and without a defined PROGRESS_RECID field designates the clustered index as ROWID if it qualifies for ROWID selection.

    For the clustered index to qualify for ROWID selection, it must be unique. This designation for ROWID takes precedence over any other options selected for the schema pull operation but does not overwrite a legacy ROWID designation that is based on the presence of PROGRESS_RECID.

    Note: The Designate Primary/Clustered index as ROWID option may produce some or all of the "natural" key selections available from the foreign table's indexes. These can be considered alternatives to the PROGRESS_RECID column or other ROWIDs previously designated.
    Select 'Best' ROWID Index

    Selecting Select 'Best' ROWID Index during migration provides the legacy equivalent of ROWID designation behavior for the tables from prior versions of OpenEdge where the PROGRESS_RECID column was not present in a table.

    When selected in conjunction with new process flow options for ROWID migration, this option plays a secondary role in the designation of ROWID indexes deferring to the Designate Primary/Clustered index as ROWID option as the first choice. This option searches for a viable index for ROWID when an PROGRESS_RECID column does not exist and other process flow options that take precedence do not render a viable index.

    Select 'Best' ROWID Index using one of the following options:

    • OESchema: if you must locate an existing index to be used as ROWID that meets the requirements of the OpenEdge Schema. The OpenEdge Schema criteria is described in Best index selection. If a Best index selection can be found in the OpenEdge Schema, it is designated as ROWID.

      When this option is used in conjunction with the Designate Primary/Clustered index as ROWID option, this option specifies a secondary path in the search for the ROWID index. The primary and clustered selections associated with those options will take priority in ROWID selection as does the presence of a PROGRESS_RECID column.

    • Foreign Schema: if you must locate an existing index to be used as ROWID from the ODBC API that evaluates best index on the server. If a Best Index selection can be found through the ODBC API, it is designated as ROWID.

      If this option is selected in conjunction with the Designate Primary/Clustered index as ROWID option, then a warning is generated with respect to both options. When both options are selected, this option becomes a secondary path in the search for the ROWID index. The primary and clustered selections associated with those options takes priority in ROWID selection as does the presence of a PROGRESS_RECID column.
  11. Choose OK. OpenEdge displays a list of the data source objects that you can include in the schema holder, as shown:


    If you specified all wild cards as your table-selection criteria, the list might also include system-owned objects, which you do not have to include in the schema holder.

  12. Click the option appropriate to the action you want to perform:
    • Select Some — Displays the Select by Pattern Match dialog box on which you can specify object information used to select objects
    • Deselect Some — Displays the Deselect by Pattern Match dialog box on which you can specify object information used to deselect objects

    You can also elect to select and deselect individual objects by clicking and double-clicking on an object. An asterisk appears next to an object that has been selected; double-click an object to remove the asterisk and identify that the object is now deselected.

  13. Choose OK after you have identified all the objects you want to include in the schema holder. The DataServer reads information about the objects that you select and loads their data definitions into the schema holder. The time that this process takes depends on the size and number of objects that you select.

    For each table, the DataServer attempts to select an index to support the OpenEdge ROWID. If an appropriate index does not exist, the DataServer issues the warning, Please check errors, warnings and messages in the file ds_upd.e. The ds_upd.e file lists the objects that do not support ROWID. You can change the DataServer's selection of an index to support ROWID by using the Data Dictionary. See Defining the ROWID for instructions. For additional information, see Indexes and sorting .