Create a connection to an ODBC database

Connections to an ODBC database are made by selecting ODBC (via Direct Access gateway) from the list of connectors in the Add data connection or Data load editor dialogs.

Industry-accepted best practices must be followed when using or allowing access through the ODBC Connector. Administrators must follow The Principle of Least Privilege when setting up source database privileges and permissions.

Data gateway

Source properties
Database properties	Description	Required
Direct Access gateway	Select the Direct Access gateway through which you need to connect to your data source. Information noteUsers that need to leverage gateway-enabled data connections must have the Can Consume Data permission for the space with which the gateway is associated.	Yes

Database properties

Database properties
Database properties	Description	Required
System DSN ODBC driver	Choose one of the following: System DSN: This option requires you to define a System DSN for your data source. For more information, see Creating a System DSN. ODBC driver: If you choose this option, after you select your driver from the ODBC source drop-down list, you will need to configure the connection properties in the Connection string field.	Yes
ODBC source	Click the Load button. The drop-down list will be populated with either of the following: All System DSNs (if you selected System DSN) defined on the Direct Access gateway machine. All drivers (if you selected ODBC driver) installed on the Direct Access gateway machine. Choose the driver or System DSN for your data source.	Yes
Connection string	Do one of the following: If you selected ODBC driver, enter a connection string. If you selected System DSN, optionally append parameters to the existing DSN parameters. Information noteAs the existing DSN parameters are not exposed in the UI, be careful not to enter parameters that might override existing DSN parameters. As each driver type handles duplicate parameters differently (some use the first instance while others use the last), best practice is to check the DSN parameters on the Direct Access gateway machine before entering any additional parameters. Information noteAlthough the connection string usually starts with Driver= (for drivers) or DSN= (for System DSNs), these parameters should be omitted as they are automatically added when establishing a connection to the data source. Example of an ODBC driver connection string - MariaDB syntax: SERVER=mydatabase.mydomain.com;USER=odbc_user;PASSWORD=odbc_pw;DATABASE=odbc_test;PORT=3306 Warning noteDo not include sensitive parameters such as user, password, tokens, and so on, in the connection string, as such parameters might be exposed as plain text when establishing the connection. Instead, use the Encrypted Properties option (see below) to store sensitive connection string parameters.	Yes
Encrypted properties	You can securely store encrypted connection string properties. For each encrypted property, you have to provide a Name and a Value. Use the and buttons to the right of the Value field to add and remove properties. The connection string will be completed with the specified property names and their unencrypted values when connecting to the data source.	No
Attached file properties	For each attached file property, specify a property Name and a File name. Use the and buttons to the right of the File button to add and remove properties. A connection string will be completed with specified property names and temporary path to uploaded the file when connecting to the data source.	No

Database specific properties

Database specific properties
Database properties	Description	Required
SQL syntax	Select your data source syntax or select Custom SQL syntax to specify a custom syntax. Out-of-the-box syntax is provided for the following data sources: DB2 MariaDB Sybase ASE SAP HANA Greenplum	Yes
Identifier start delimiter	The character string that is used as the starting delimiter of a quoted (delimited) identifier in SQL statements. This field is read-only unless you selected Custom syntax.	A value needs to be provided if Custom SQL syntax is selected.
Identifier end delimiter	The character string that is used as the ending delimiter of a quoted (delimited) identifier in SQL statements. This field is read-only unless you selected Custom syntax.	A value needs to be provided if Custom SQL syntax is selected.
SELECT statement template for Data Preview	This field is read-only unless you selected Custom syntax. The template supports the use of the following variables: ${QO}: Corresponds to the Identifier start delimiter value. The character string that is used as the starting delimiter of a quoted (delimited) identifier in SQL statements. ${QC}: Corresponds to the Identifier end delimiter value. The character string that is used as the ending delimiter of a quoted (delimited) identifier in SQL statements. ${COLUMN_LIST}: List of columns used in result set. ${TABLE_NAME}: The table name. ${TABLE_OWNER}: The table owner. ${SCHEMA_NAME}: The schema name. ${FILTER} LIMIT 50: When an SQL statement is generated for Data Preview, ${FILTER} will be replaced by the values defined in the “Filter data” text box. For example, if a selected table has Category and Product columns, and the following values are defined in the “Filter data” text box: Category=“beverages” and Product=“Coke” Then the configured filter will be applied to the SELECT statement when the data is previewed. Warning noteDo not omit "LIMIT" keywords from the SELECT statement template for Data Preview. "LIMIT" keywords differ according to the SQL syntax supported by the data source. For example, MariaDB uses "LIMIT" while SAP Sybase ASE uses TOP. Even if "LIMIT" keywords are omitted, the connector will always truncate the data preview result set to a maximum of 50 records. However, in some cases, performing just SELECT xxx FROM yyy without limiting the result-set could take a long time to complete on the database server, impacting Select dialog performance. See also: Filter data from an ODBC database Select and load data from an ODBC data source	Values need to be provided if Custom SQL syntax is selected.

Miscellaneous properties

Miscellaneous properties
Database properties	Description	Required
Allow non-select queries	You can execute load scripts that do not start with an SQL SELECT statement, for example UPDATE, INSERT, EXEC, and WITH ... AS. For more information, see Executing non-SELECT statements	No
Load primary key metadata	Turn on this option to load primary key metadata. Information noteThe Load primary key metadata option is not supported with all drivers. If this option is turned on for an unsupported data source, a message will be shown in the Select dialog (when retrieving a table) that an error occurred while trying to retrieve the primary key metadata. In such a case, you need to turn off the Load primary key metadata option before retrying the operation.
Query timeout	Amount of time before the query times out. Can be set from 30 seconds to 65535 seconds. Default is 150.	No

Load optimization properties

If you have large datasets, the Bulk Reader will automatically load larger portions of data in the iterations within a load, instead of loading data row by row.

Load optimization properties
Property	Description	Required
Maximum string length	Maximum length of string fields. This can be set from 256 to 16384 characters. The default value is 4096. Setting this value close to the maximum length may improve load times, as it limits the need to allocate unnecessary resources. If a string is longer than the set value, it will be truncated, and the exceeding characters will not be loaded.	No

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!

Leave your feedback here