Connections > ODBC connection properties > Connect to ODBC
  

Connect to ODBC

Let's configure the ODBC connection properties to connect to ODBC-compliant databases or data warehouses.
You can use the ODBC connection to connect to any ODBC-compliant endpoint. However, the connection also provides you with ODBC subtypes to connect to specific endpoints. The subtype defines the additional capabilities that you can configure in the connection or the mapping when you connect to the endpoint to read from or write data.
See the following table for the ODBC subtypes and the defined functionalities:
ODBC Subtype
Endpoint
Functionality
Azure DW
Microsoft Azure SQL Data Warehouse
Enable SQL ELT optimization in mappings for read and write operations.
DB2
DB2 databases
  • - Enable SQL ELT optimization in mappings for read and write operations.
  • - Call a stored procedure using an SQL transformation.
  • - Use Kerberos authentication to connect to DB2.
Google BigQuery
Google BigQuery
Enable SQL ELT optimization in mappings for read and write operations.
PostgreSQL
PostgreSQL
Enable SQL ELT optimization in mappings for read and write operations.
Redshift
Amazon Redshift
Enable SQL ELT optimization in mappings for read and write operations.
SAP IQ
SAP IQ database
Read data from the SAP IQ database.
SAP Sybase ASE
Sybase ASE databases
  • - Read from or write to Sybase ASE databases.
  • - Use Kerberos authentication to connect to SAP Sybase ASE.
Snowflake
Snowflake
Enable SQL ELT optimization in mappings for read and write operations.
Teradata
Teradata
  • - Read from and write to a Teradata database.
  • - Enable SQL ELT optimization in mappings for read and write operations.
  • - Call a stored procedure using an SQL transformation.
  • - Run SQL queries in Teradata using saved queries from an SQL transformation.
Note: If you want to use an SSL-enabled ODBC Teradata connection, ensure that you set an appropriate value for the SSL Mode option under WebSocket while configuring the Teradata ODBC driver.
Other
Microsoft Access, Microsoft Excel, and Netezza
Enable SQL ELT optimization in mappings for read and write operations on the Microsoft Access, Microsoft Excel, or Netezza endpoint.
You can also use the Other subtype to connect to any ODBC-compliant endpoint to read or write data.

Before you begin

Before you get started, you'll need to install the ODBC driver and ODBC client on the Secure Agent machine to establish an ODBC connection.
Check out Prerequisites to learn more about the configuration prerequisites.

Connection details

The following table describes the basic connection properties:
Property
Description
Connection Name
Name of the connection.
Each connection name must be unique within the organization. Connection names can contain alphanumeric characters, spaces, and the following special characters: _ . + -,
Maximum length is 255 characters.
Description
Description of the connection. Maximum length is 4000 characters.
Type
ODBC
Use Secret Vault
Stores sensitive credentials for this connection in the secrets manager that is configured for your organization.
This property appears only if secrets manager is set up for your organization.
This property is not supported by Data Ingestion and Replication.
When you enable the secret vault in the connection, you can select which credentials that the Secure Agent retrieves from the secrets manager. If you don't enable this option, the credentials are stored in the repository or on a local Secure Agent, depending on how your organization is configured.
For information about how to configure and use a secrets manager, see Secrets manager configuration.
Runtime Environment
The name of the runtime environment where you want to run tasks.
Select a Secure Agent, Hosted Agent, or serverless runtime environment.
For more information about how to configure a serverless environment, see Use the serverless runtime environment.
ODBC Subtype
The ODBC connection subtype that you need to select to connect to a specific ODBC-compliant endpoint.
For information on the ODBC subtypes and their capabilities, see Connect to ODBC.
Authentication Mode
The authentication method to connect to DB2 or SAP Sybase ASE.
This property appears only if you select the ODBC subtype as DB2 or SAP Sybase ASE.
Select one of the following authentication modes from the list:
  • - Database. Uses the user name and password to connect to the selected ODBC subtype.
  • - Kerberos. Uses Kerberos authentication to connect to the selected ODBC subtype.
    • When you choose this option on Windows, ensure that the user account that starts the Secure Agent service exists in the endpoint for the selected ODBC subtype and has the required permissions to interact with it.
    Note: You can't configure Kerberos authentication when you use a Hosted Agent or serverless runtime environment.
    Default is Database.
Kerberos Connection Properties
Additional connection properties to use Kerberos authentication to connect to DB2 or SAP Sybase ASE.
This property appears only if you select the ODBC subtype as DB2 or SAP Sybase ASE and authentication mode as Kerberos.
If you specify more than one property, separate each key-value pair with a semicolon.
For example, if you don't set the required environment variables on the Secure Agent machine before you use Kerberos authentication, add the KRB5_CONFIG and KRB5CCNAME properties in the following format:
KRB5_CONFIG=<Absolute path of the Kerberos configuration file>\krb5.conf;KRB5CCNAME=<Absolute path of the credential cache file>/<File name>
User Name
The user name to connect to the ODBC-compliant endpoint.
Password
The password to connect to the ODBC-compliant endpoint.
The password cannot contain a semicolon.
Data Source Name
The data source name of the ODBC object.
Schema
The schema name of the ODBC object.
Code Page
The code page of the ODBC-compliant endpoint server or flat file defined in the connection.
Select one of the following code pages:
  • - MS Windows Latin 1. Select for ISO 8859-1 Western European data
  • - UTF-8. Select for Unicode data
  • - Shift-JIS. Select for double-byte character data
  • - ISO 8859-15 Latin 9 (Western European)
  • - ISO 8859-2 Eastern European
  • - ISO 8859-3 Southeast European
  • - ISO 8859-5 Cyrillic
  • - ISO 8859-9 Latin 5 (Turkish)
  • - IBM EBCDIC International Latin-1
  • - Japanese Extended UNIX Code (incl. JIS X 0212)
  • - Japanese EUC (with \<-> Yen mapping)
  • - Japanese EUC (Packed Format)
  • - IBM EBCDIC Japanese
  • - IBM EBCDIC Japanese CP939
  • - Japanese EBCDIC Fujitsu
  • - HITACHI KEIS Japanese
  • - NEC ACOS JIPSE Japanese
  • - UNISYS Japanese
  • - MITSUBISHI MELCOM Japanese
  • - Japanese EBCDIC-Kana Fujitsu
  • - HITACHI KEIS-Kana Japanese
  • - NEC ACOS JIPSE-Kana Japanese
  • - UNISYS-Kana Japanese
  • - MITSUBISHI MELCOM-Kana Japanese
  • - EBCDIC Japanese
  • - EBCDIK Japanese
  • - PC Japanese SJIS-78 syntax (IBM-942)
  • - PC Japanese SJIS-90 (IBM-943)
  • - EBCDIC Japanese Katakana SBCS
  • - EBCDIC Japanese Katakana (w/ euro)
  • - EBCDIC Japanese Latin-Kanji (w/ euro)
  • - EBCDIC Japanese Extended (DBCS IBM-1390 combined with DBCS IBM-1399)
  • - EBCDIC Japanese Latin (w/ euro update)
  • - EBCDIC Japanese Katakana SBCS (w/ euro update)
  • - MS Taiwan Big-5 w/ HKSCS extensions
  • - MS Windows Traditional Chinese, superset of Big 5
  • - Taiwan Big-5 (w/ euro update)
  • - Taiwan Big-5 (w/o euro update)
  • - PC Chinese GBK (IBM-1386)
  • - Chinese EUC
  • - Simplified Chinese (GB2312-80)
  • - Hong Kong Supplementary Character Set
  • - ISO 8859-8 Hebrew
  • - PC Hebrew (old)
  • - PC Hebrew (w/o euro update)
  • - PC Hebrew (w/ euro update)
  • - MS Windows Hebrew (older version)
  • - MS Windows Hebrew (w/o euro update)
  • - Lotus MBCS encoding for Windows Hebrew
  • - EBCDIC Hebrew (updated with sheqel, control characters)
  • - EBCDIC Hebrew (w/ euro)
  • - EBCDIC Hebrew (updated w/ euro and new sheqel, control characters)
  • - Israeli Standard 960 (7-bit Hebrew encoding)

Advanced settings

The following table describes the advanced connection properties:
Property
Description
Driver Manager for Linux
The driver manager for the Secure Agent machine hosted on Linux.
When you create a new ODBC connection on Linux, select one of the following driver managers from the list:
  • - Data Direct
  • - unixODBC2.3.0
  • - unixODBC2.3.4
Default is UnixODBC2.3.0.
To connect to Teradata, you can use only Data Direct as the driver manager on Linux.
Connection Environment SQL
The SQL statement to set up the ODBC-compliant endpoint environment when you connect to a PostgreSQL or Teradata database. The database environment applies for the entire session that uses this connection.
You can add single or multiple SQL statements. Separate each SQL statement with a semicolon.
For example, you can enter this statement to set the time zone:
SET timezone to 'America/New_York';
You can set SQL statements in a Teradata connection used in mappings enabled with or without SQL ELT optimization. However, when you connect to a PostgreSQL database, this property applies only when you enable SQL ELT optimization in a mapping.

Related links