Configuring the ODBC Driver on Windows
You must configure the Informatica MongoDB ODBC driver before you can run the MongoDB tasks or mappings. Configure the ODBC driver on a 32-bit or 64-bit Windows machine.
1. Place the MongoDB<XX>bit.zip file in the following location on the Windows machine: C:\temp.
2. Unzip the MongoDB<XX>bit.zip file.
3. Perform the following steps based on the Secure Agent installation location:
- - If you installed the Secure Agent at the default location on the Windows machine at C:\Program Files\Informatica Cloud Secure Agent, copy mongodb from C:\temp\Win<XX> to the following directory: C:\Program Files\Informatica Cloud Secure Agent\main\drivers\odbc
- - If you installed the Secure Agent at a custom location on the Windows machine, copy mongodb from C:\temp\Win<XX> to the following directory: <Secure_Agent_Installation_Directory>\main\drivers\odbc
4. To create the driver registry, double-click the following file: C:\temp\Win<XX>\MongoDBDriver<XX>bit.reg.
5. To create a <XX>-bit sample MongoDB data source name, double-click the following file: C:\temp\Win<XX>\SampleMongoDBDSN<XX>bit.reg.
6. If the Secure Agent is installed at the default location on Windows, perform the following steps:
- a. Open the following file in a text editor: C:\Program Files\Informatica Cloud Secure Agent\main\drivers\odbc\mongodb\Tools\Schema Editor\Editor.config
- b. Set JAVA_HOME to the following directory: C:\Program Files\Informatica Cloud Secure Agent\jre
7. If the Secure Agent is installed at the custom location on a Windows 32-bit machine, perform the following steps:
- a. Type regedit in the Windows start search box, and click Enter to open the Registry Editor.
- b. Navigate to Computer > HKEY_LOCAL_MACHINE > SOFTWARE > Wow6432Node > ODBC > ODBCINST.INI > Informatica MongoDB ODBC Driver.
- c. Update the driver and setup path to the following directory: <Secure_Agent_Installation_Directory>\main\drivers\odbc\mongodb\lib\SimbaMongoDBODBC32.dll
- d. Navigate to Computer > HKEY_LOCAL_MACHINE > SOFTWARE > Wow6432Node > ODBC > ODBC.INI > Sample Informatica MongoDB DSN.
- e. Update the driver path to the following directory: <Secure_Agent_Installation_Directory>\main\drivers\odbc\mongodb\lib\SimbaMongoDBODBC32.dll
- f. Update the ExternalSchemaFile path to <Secure_Agent_Installation_Directory>\main\drivers\odbc\mongodb\MongoSchema32.sdd
- g. Open the following file in a text editor: <Secure_Agent_Installation_Directory>\main\drivers\odbc\mongodb\Tools\Schema Editor\Editor.config
- h. Set JAVA_HOME to the following directory: <Secure_Agent_Installation_Directory>\jre
8. If the Secure Agent is installed at the custom location on a Windows 64-bit machine, perform the following steps:
- a. Type regedit in the Windows start search box, and click Enter to open the Registry Editor.
- b. Navigate to Computer > HKEY_LOCAL_MACHINE > SOFTWARE > ODBC > ODBCINST.INI > Informatica MongoDB ODBC Driver.
- c. Update the driver and setup path to the following directory: <Secure_Agent_Installation_Directory>\main\drivers\odbc\mongodb\lib\SimbaMongoDBODBC64.dll
- d. Navigate to Computer > HKEY_LOCAL_MACHINE > SOFTWARE > ODBC > ODBC.INI > Sample Informatica MongoDB DSN.
- e. Update the driver path to the following directory: <Secure_Agent_Installation_Directory>\main\drivers\odbc\mongodb\lib\SimbaMongoDBODBC64.dll
- f. Update the ExternalSchemaFile path to <Secure_Agent_Installation_Directory>\main\drivers\odbc\mongodb\MongoSchema64.sdd
- g. Open the following file in a text editor: <Secure_Agent_Installation_Directory>\main\drivers\odbc\mongodb\Tools\Schema Editor\Editor.config
- h. Set JAVA_HOME to the following directory: <Secure_Agent_Installation_Directory>\jre
Configuring the Data Source Name on Windows
Configure the data source name before you create a MongoDB connection. You must configure the MongoDB ODBC driver properties, advanced properties, and schema when you configure a data source name.
Create a data source name in the ODBC datasource administrator to extract data from and load data to a MongoDB database. The data source properties provide information for the MongoDB server and the database. The advanced properties are read and write operations. You can also define a schema after you create a database.
You can find the ODBC datasource administrator in the Control Panel on Windows. Access the correct ODBC datasource administrator in the machine where you install the Secure Agent.
Configure the ODBC data source name in the 64-bit or the 32-bit ODBC datasource administrator. You can access the 64-bit ODBC datasource administrator, odbcad32.exe, in 64-bit Windows from the following location: C:\Windows\System32. You can access the 32-bit ODBC datasource administrator, odbcad32.exe, in 64-bit Windows from the following location: C:\Windows\SysWOW64.
MongoDB ODBC Data Source Advanced Properties
When you create a data source name, configure the advanced properties from Advanced Options.
The following table describes the advanced properties in the Informatica MongoDB ODBC driver:
Property | Description |
|---|
Documents fetched per block | The maximum number of documents that the Secure Agent reads for every call to the MongoDB database. Default is 4096. |
Nested column separator | Separator character for arrays and nested documents. The nested column separator must be consistent across connections used in a mapping. For example, if one connection uses the period (.) as the nested column separator and another connection in the same mapping uses the underscore (_) as the separator, then the mapping fails. You can use the underscore (_) as the nested column separator. |
Maximum number of columns to flatten | The maximum number of array elements that the ODBC driver flattens into multiple nested columns. Default is 5. |
Read preference | Server that you prefer to read data from if you configure replica sets. You can select one of the following server options: - - Primary. The Secure Agent reads data from the primary server. If the primary server is offline, the session fails.
- - Primary Preferred. The Secure Agent reads data from the primary server if the primary server is available. If the primary server is offline, the Secure Agent reads data from the secondary server.
- - Secondary. The Secure Agent reads data from the secondary server. If the secondary server is offline, the session fails.
- - Secondary Preferred. The Secure Agent reads data from the secondary server if the secondary server is available. If the secondary server is offline, the Secure Agent reads data from the primary server.
- - Nearest. The Secure Agent reads data from the nearest available server.
Default is primary. |
Sampling strategy | Number of rows to scan in the schema definition. You can select one of the following sampling strategies: - - Start. Scans the specified number of rows from the start.
- - End. Scans the specified number of rows from the end.
- - Random. Scans the specified number of rows in random order.
Default is End. |
Documents to sample (0 to sample all documents) | Number of documents to scan. Default is 100. |
String Columns Lengths | The string column length to use for the fields. You can select one of the following string column lengths: - - Standard. The string column length to use for the standard fields. Default is 255.
- - Container. The string column length to use for the container fields. Default is 511.
- - DocumentAsJSON. The string column length to use for the documentAsJSON fields. Default is 1023.
|
Use SQL_WVVARCHAR for String datatype | The Secure Agent maps the String datatype to SQL_WVARCHAR ODBC instead of SQL_VARCHAR. Default is disabled. |
Enable reading/writing as JSON document. | Read or write data as a JSON document. If enabled, the driver reports a special column named documentAsJSON that retrieves or stores whole documents as JSON formatted strings. Default is disabled. Note: For a MongoDB connection, if you toggle between enabling and disabling this option, the metadata cache might lose its integrity. Instead of changing the Enable reading/writing as JSON document property for a MongoDB connection, create separate connections with this property. |
Show container columns when generating metadata | Show the container columns when the Secure Agent generates the metadata. Default is disabled. |
Enable SSL | Establish secure communication to the MongoDB server over SSL. If enabled, you must specify the path where the SSL certificates for the MongoDB server are stored. Default is disabled. |
Check GetLastError on writes | Calls the MongoDB CheckGetLastError() function to check for failures after a write operation. Default is enabled. |
Enable Updating Multiple Rows | The Informatica MongoDB ODBC driver updates multiple rows for each Secure Agent write call. If enabled, the driver updates all rows that match the filter condition. If disabled, the driver updates only the first row that matches the filter condition. Default is disabled. |
Omit default NULL column on insert | The Secure Agent does not write columns with NULL value to a MongoDB target. Default is enabled. |
Truncate documents larger than 16 MB | Truncate the document size to 16 MB when you load data to MongoDB. Default is disabled. |
Active Metadata Location | Read metadata changes from the MongoDB database or from a local file. Required if you choose to store the metadata in a local file. Default is database. |