skip to main content
Using the Driver : Creating and Customizing Schemas Using the DataDirect Schema Tool : Starting the Schema Tool : Starting the Schema Tool on Windows
  

Try DataDirect Drivers Now
Starting the Schema Tool on Windows
To create or modify a schema definition, follow these steps:
1. From the Progress DataDirect program group, click ODBC Administrator.
2. Select a tab:
*User DSN: If you are configuring an existing user data source, select the data source name and click Configure to display the driver Setup dialog box.
If you are configuring a new user data source, click Add to display a list of installed drivers. Select the driver and click Finish to display the driver Setup dialog box.
*System DSN: If you are configuring an existing system data source, select the data source name and click Configure to display the driver Setup dialog box.
If you are configuring a new system data source, click Add to display a list of installed drivers. Select the driver and click Finish to display the driver Setup dialog box.
*File DSN: If you are configuring an existing file data source, select the data source file and click Configure to display the driver Setup dialog box.
If you are configuring a new file data source, click Add to display a list of installed drivers; then, select a driver. Click Advanced if you want to specify attributes; otherwise, click Next to proceed. Specify a name for the data source and click Next. Verify the data source information; then, click Finish to display the driver Setup dialog box.
3. Select the driver you want to use from the Create New Data Source window. Then click Finish.
The the General tab of the driver setup dialog box appears.
The General tab of the MongoDB driver setup dialog
4. In the General tab, enter the appropriate information for each connection option; then, click Apply. The following table provides a short description of each. For more information on these and other connection options, refer to the "Connection Option Descriptions" chapter in your driver documentation.
Table 9. Summary: Setup Dialog Connection Options
Option
Characteristic
Data Source Name
Specifies the name of a data source in your Windows Registry or odbc.ini file.
Description
Specifies an optional long description of a data source.
Host Name
The name or the IP address of the server to which you want to connect.
Port Number
Specifies the port number of the server listener.
Database
Specifies the name of the database to which you want to connect.
Schema Definition
Specifies the name and location of the configuration file where the relational map of native data is written. For example, C:\Users\Default\AppData\Local\Progress\DataDirect\MongoDB Schema\MainServer.config. The default is:
application_data_folder\Local\Progress\DataDirect\MongoDB Schema\host_name.config
Refer to the "Schema Definition" connection option topic in your driver documentation for details.
Note: Your authentication settings determine for which databases the Schema Tool retrieves metadata. If authentication is not used, the Schema Tool retrieves metadata for all databases on the server. If authentication is used, the Schema Tool returns only the metadata for the database specified in the Database option field. However, if you are assigned the clusterAdmin role, the Schema Tool returns metadata for all the databases on the server for which you have read privileges when authentication is enabled.
5. Optionally, click the Advanced tab to specify the config options values to determine how native data is mapped to the relational schema.
Advanced tab of the MongoDB driver
In the Config Options field, enter a semicolon separated list of config options and their values. For example, columnDiscoverySampleSize=1000;UppercaseIdentifiers=true;. Click Apply; then, click on the General tab. The following table provides a short description of each config option.
Table 10. Schema Tool Config Options
Config Option
Characteristic
columnDiscoverySampleSize
Specifies the number of rows the driver fetches per collection when sampling data to detect columns and gather column statistics. The information collected in these samples is used when defining a schema definition with the Schema Tool. Larger fetch sizes return samples that are more representative of your data, but at the expense of slower performance when generating a configuration file. See "About Column Information and Statistics" for additional information on how sampling is used for statistics.
The default is 1000.
DefaultVarcharSize
Determines the default length of fields that are mapped as VARCHAR.
Valid values:
length | multiplier
where:
length
is the default length in characters given to columns that are discovered and mapped as VARCHAR.
multiplier
is a positive number immediately followed by the character x. For example, 3x. The positive integer is multiplied by the size of the largest object detected in a column to determine the default VARCHAR length for that column.
Note: When specifying a multiplier, you can define the maximum and minimum limits of the default length generated with the MaxVarcharSize and MinVarcharSize config options.
The default is 1.5x
KeywordConflictSuffix
Specifies a string of up to five alphanumeric characters that the driver appends to any object or field name that conflicts with a SQL engine keyword.
string
where:
string
is a string of up to five alphanumeric characters.
For example, a field called CASE exists in the native MongoDB data. To avoid a naming conflict with the SQL engine keyword CASE, you could set KeywordConflictSuffix=TAB. In this scenario, the driver maps the Case object to the CASETAB column.
There is no default value.
LeadingUnderscoreReplacement
LeadingUnderscoreReplacement specifies the string of characters that replace leading underscores used in identifiers for collections, documents, and arrays.
Valid values:
string
where:
string
is comprised of any Unicode character or group of characters, including spaces.
For example, MongoDB collections automatically include the _id field. By specifying LeadingUnderscoreReplacement=XX, the _id field becomes the XXID column in the relational view of the data.
Note: The Table Wizard builds table and column identifiers by concatenating the names of nested collections, documents, and arrays. When specifying a value for LeadingUnderscoreReplacement, consider that the total length of identifiers must not exceed 128 characters in length.
MaxVarcharSize
Specifies the maximum default length of fields that are mapped as VARCHAR when a multiplier is specified for the DefaultVarcharSize config option (DefaultVarcharSize=multiplier).
The default is 4000.
MinVarcharSize
Specifies the minimum default length, in characters, of fields that are mapped as VARCHAR when a multiplier value is specified for the DefaultVarcharSize config option (DefaultVarcharSize=multiplier).
The default is 255.
SchemaFilter
Specifies a comma-separated list of database and collection pairs for which you want the driver to fetch metadata. SchemaFilter can significantly improve connection times by limiting the collections for which metadata is fetched to only those that are required by your application. This value takes the following form:
SchemaFilter=database_name:collection_name[[,database_name:collection_name]...]
See "SchemaFilter (Config Option)" for detailed list of supported values.
UppercaseIdentifiers
Defines how the driver maps identifiers.
If set to true, the driver maps identifiers to uppercase.
If set to false, The driver maps identifiers to the mixed case name of the object being mapped. If mixed case identifiers are used, SQL statements must enclose those identifiers in double quotes, and the case of the identifier, must exactly match the case of the identifier name.
See "Naming Conflicts" for additional information about using identifiers.
The default is true.
Note: If you receive an error message indicating that naming conflicts have occurred, you must specify the UppercaseIdentifiers config options to false before the driver will connect to a database.
6. Optionally, click the Security tab to specify values for security-related connection options to determine the security settings used when accessing data with the Schema Tool and driver.
Security tab of the MongoDB driver
Enter the appropriate information for each connection option that is applicable to your security environment; then, click Apply. The following table provides a short description of each. For more information on these and other connection options, refer to the "Connection Option Descriptions" chapter in your driver documentation.
Connection Options
Description
User Name
Specifies the default user ID that is used to connect to your database. Your ODBC application may override this value or you may override it in the logon dialog box or connection string.
Encryption Method
The method used to encrypt data sent between the Schema Tool and the database server.
If set to 0 - No Encryption, data is not encrypted.
If set to 1 - SSL, data is encrypted using SSL. If the server is not configured for SSL, the connection fails.
Validate Server Certificate
Determines whether the driver and/or Schema Tool validates the certificate that is sent by the database server when SSL encryption is enabled.
If enabled, the driver validates the certificate that is sent by the database server. Any certificate from the server must be issued by a trusted CA in the truststore file. If the Host Name In Certificate option is specified, the driver also validates the certificate using a host name. The Host Name In Certificate option provides additional security against man-in-the-middle (MITM) attacks by ensuring that the server the driver is connecting to is the server that was requested.
If disabled, the driver does not validate the certificate that is sent by the database server. The driver ignores any truststore information specified by the Trust Store and Trust Store Password options.
Trust Store
Specifies the directory that contains the truststore file and the truststore file name to be used when SSL is enabled (Encryption Method=1) and server authentication is used.
Trust Store Password
Specifies the password that is used to access the truststore file when SSL is enabled (Encryption Method=1) and server authentication is used.
Key Store
Specifies the fully qualified path and file name of the keystore file to be used when SSL is enabled (Encryption Method=1) and SSL client authentication is enabled on the database server.
Key Store Password
Specifies the password used to access the keystore file when SSL is enabled (Encryption Method=1) and SSL client authentication is enabled on the database server.
Key Password
Specifies the password used to access the individual keys in the keystore file when SSL is enabled (Encryption Method=1) and SSL client authentication is enabled on the database server.
Host Name In Certificate
Specifies the host name for certificate validation when SSL encryption is enabled (Encryption Method=1) and validation is enabled (Validate Server Certificate=1).
7. Click on the Schema Tool button to open the Schema Tool.
*If you are creating a new schema definition, proceed to Creating a Schema with the Table Wizard.
*If you are modifying an existing schema definition, proceed to the next step.
8. If you are using an existing schema map, choose one of the following sampling behaviors to execute at connection:
*All Collections: The driver samples all new and existing collections to detect changes. This provides the most accurate view of your data, but, depending on the number and size of your collections, can take a long time to process.
*Only New Collections: The driver samples only newly discovered collections. This provides the quickest processing time, allowing you to begin using the tool faster. If you only want to map new collections, or if your existing collections are unchanged, this method is recommended.