Try OpenEdge Now
skip to main content
DataServer for Microsoft SQL Server
The DataServer Tutorial : Independent OpenEdge MS SQL Server Schema Pull : Running the Independent schema pull utility in batch mode
 

Running the Independent schema pull utility in batch mode

Use the following steps to run the Independent schema pull utility 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 the table that follows.
Note: There are additional parameters required for the batch pull that are not required for the Update/Add Table Definitions Pull Down option. This is because the batch mode pull can be used to perform the duties of Create DataServer Schema if a corresponding schema holder database and logical database contains the schema image that does not already exist or is not already connected when the batch schema pull is executed.
Table 66. Independent Schema pull utility
Environment variable
Corresponding Interface element
Description
SHDBNAME
Schema Holder Database
Default: <blank>
Specify the new schema-holder name, in which the resultant logical database will reside.
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 Connectingthe 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.
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 value for MSSPDBNAME can be the same as PRODBNAME but must be different from SHDBNAME.
MSSDBNAME
ODBC Database Name
Default: <blank>
Specify the ODBC database name associated with the resultant schema image from the pull operation.
Dependencies: If MSSDBNAME is not set, the pull operation terminates with an error. The ODBC DSN must be properly specified and located in the ODBC Data Source Administration of your system.
MSSUSERNAME
User Name
Default: <blank>
Specify the user name for the target data source.
Dependencies: If MSSUSERNAME is not set, the pull operation terminates with an error.
MSSPASSWORD
User's Password
Default: <blank>
Specify the password of the user for the target data source.
Dependencies: If a password is required for authentication and MSSPASSWORD variable is not set, the pull operation terminates with an error.
SHCONPARMS
Conn Params for logical database
Default: current working database
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.
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.
MSSCODEPAGE
Codepage
Default: iso8859-1 when UNICODETYPES is disabled (that is, set to NO).
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 pull operation.
MSSCOLLNAME
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.
MSSCASESEN
Insensitive
Default: NO
Specifies if the code page you are matching on the foreign data source is or isn't case insensitive. Provide YES if your 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.
MSSOBJNAME
Object name
Default: *
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.)
MSSOBJOWNER
Object owner
Default: owner name of the connected user
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.)
MSSOBJQUALIFIER
Object qualifier
Default: *
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.)
MAPOEDATETIME
Default to OpenEdge DATETIME
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 MAPSOEDATETIME 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.
MAPTOLOB
Default to OpenEdge LOB for:
*CLOB
*BLOB
Default: NO
By default, all server LOB data types map to the CHARACTER data type in OpenEdge for backward compatibility. By changing this parameter from it's default value of NO, you are requiring that your ABL application be coded specifically to handle LOB objects using OpenEdge LOB operations.
Specify a value of "B" to map MS SQL Server VARBINARY(MAX), IMAGE and FILESTREAM data types to OpenEdge BLOBs.
Specify a value of "C" to map MS SQL Server VARCHAR(MAX) and TEXT data types to OpenEdge CLOBS.
Specify a value of "L" (for Lobs) to map both BLOB server types to OpenEdge BLOBs and CLOB server types to OpenEdge CLOBs.
For more information on mapping OpenEdge and MS SQL Server data types, see Support for OpenEdge ABL BLOB data type.
MAPTOROWID
Designate Primary/Clustered index as ROWID
Default: NO
Specifying YES designates the clustered index as ROWID if it qualifies for ROWID selection when the pulled server table has a defined clustered index and does not have a PROGRESS_RECID field defined in it.
Dependencies: For the clustered index to qualify for ROWID selection, it must be unique. For legacy purposes, if any server table is identified as having a PROGRESS_RECID field, this ROWID designation takes precedence over any other method of determining a ROWID designation.
GETBESTROWID
Select 'Best' ROWID Index using:
*OE Schema
*Foreign schema
Default: Yes or 1 or OE Schema
Uses either the OpenEdge Schema or Foreign Schema to determine the candidacy of index attributes for ROWID eligibility.
Select 'Best' ROWID Index using one of the following options:
*Select 1 or YES for OESchema: to try to 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 indexselection. If a Best index selection can be found in the OpenEdge Schema, it is designated as ROWID.
*Select 2 for Foreign Schema: to try to 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.
Dependencies: For legacy purposes, if the server table is identified as having a PROGRESS_RECID field, this designation for ROWID takes precedence over any other option for designating a ROWID selection for the schema image. Otherwise, if clustered and primary index ROWID mapping is also selected via the MAPTOROWID environment variable, then the MAPTOROWID environment variable will take precedence over the GETBESTROWID option for ROWID selection if GETBESTROWID is on.
When COMPATIBLE and all the OpenEdge 11.0 ROWID selection options are turned off, a value of 1 or YES for OE Schema provides the legacy equivalent of ROWID designation behavior for the tables from prior versions of OpenEdge (assuming the PROGRESS_RECID field is not present in the table to supersede this designation).
4. Execute the following command to set and export the above environment variables at the system prompt:
PRODBNAME=e
SHDBNAME=sholder
MSSDBNAME=vp_snc10
MSSPDBNAME=lholder
MSSUSERNAME=vp
MSSPASSWORD=vp
SOURCEDB=e
export PRODBNAME SHDBNAME MSSDBNAME MSSPDBNAME MSSUSERNAME MSSPASSWORD
.
.
.
5. Execute the following command in the Proenv command prompt to pull schema from MS SQL Server:.
pro -p prodict/mss/schpullmss.p -b