Amazon Redshift Connectors > Part III: Data Integration with Amazon Redshift Connector > Amazon Redshift SQL ELT optimization > Configuring Amazon Redshift ODBC connection

Configuring Amazon Redshift ODBC connection

Amazon Redshift supports Amazon ODBC Redshift drivers on Windows and Linux systems. You must install the Amazon ODBC Redshift 64-bit driver based on your system requirement.

Note: Informatica certifies Amazon Redshift ODBC driver version, AmazonRedshiftODBC-64-bit-1.4.8.1000-1.x86_64, to use for SQL ELT optimization.

Configuring Amazon Redshift ODBC connection on Windows

Before you establish an ODBC connection to connect to Amazon Redshift on Windows, you must configure the ODBC connection.

Perform the following steps to configure an ODBC connection on Windows:

1Download the Amazon Redshift ODBC drivers from the AWS website.

You must download the Amazon Redshift ODBC 64-bit driver.

2Install the Amazon Redshift ODBC drivers on the machine where the Secure Agent is installed.

3Open the folder in which ODBC data source file is installed.

4Run the odbcad32.exe file.

The ODBC Data Source Administrator dialog box appears.

5Click System DSN.

The System DSN tab appears. The following image shows the System DSN tab on the ODBC Data Source Administrator dialog box:

The following image shows the System DSN box.

6Click Configure.

The Amazon Redshift ODBC Driver DSN Setup dialog box displays. The following image shows the Amazon Redshift ODBC Driver DSN Setup dialog box where you can configure the Connection Settings and Credentials section:

The following image shows the Amazon Redshift ODBC Driver DSN Setup box where you can configure the connection settings and credentials fields.

7Specify the following connection properties in the Connection Settings section:

Property	Description
Data Source Name	Name of the data source.
Server	Location of the Amazon Redshift server.
Port	Port number of the Amazon Redshift server.
Database	Name of the Amazon Redshift database.

Note: You must specify the Server, Port, and Database values from the JDBC URL.

8Specify the following credentials in the Credentials section:

Property	Description
User	User name to access the Amazon Redshift database.
Password	Password for the Amazon Redshift database.
Encrypt Password For	Encrypts the password for the following users: - Current User Only - All Users of This Machine Default is Current User Only.

9Click Test to test the connection in the Amazon Redshift ODBC Driver DSN Setup box.

10Click OK.

The Amazon Redshift ODBC connection is configured successfully on Windows.

After you configure the Amazon Redshift ODBC connection, you must create an ODBC connection to connect to Amazon Redshift.

For more information about how to create an ODBC connection to connect to Amazon Redshift, see Creating an ODBC connection

Configuring Amazon Redshift ODBC connection on Linux

Before you establish an ODBC connection to connect to Amazon Redshift on Linux, you must configure the ODBC connection.

Perform the following steps to configure an ODBC connection on Linux:

1Download the Amazon Redshift ODBC drivers from the AWS website.

You must download the Amazon Redshift ODBC 64-bit driver.

2 Install the Amazon Redshift ODBC drivers on the machine where the Secure Agent is installed.

3Configure the odbc.ini file properties in the following format:

[ODBC Data Sources]
driver_name=dsn_name

[dsn_name]
Driver=path/driver_file

Host=cluster_endpoint
Port=port_number
Database=database_name

4Specify the following properties in the odbc.ini file:

Property	Description
ODBC Data Sources	Name of the data source.
Driver	Location of the Amazon Redshift ODBC driver file.
Host	Location of the Amazon Redshift host.
Port	Port number of the Amazon Redshift server.
Database	Name of the Amazon Redshift database.

Note: You must specify the Host, Port, and Database values from the JDBC URL.

5Set the following environment variables for the operating system:

Variable	Description
LD_LIBRARY_PATH	Directory where the Amazon Redshift ODBC driver is installed.
ODBCINI	Directory that contains the odbc.ini file.
ODBCHROME	ODBC installation directory.

6Run the following command to export the odbc.ini file.

Export ODBCINI=/<odbc.ini file path>/odbc.ini

7 Restart the Secure Agent.

The Amazon Redshift ODBC connection on Linux is configured successfully.

After you configure the Amazon Redshift ODBC connection, you must create an ODBC connection to connect to Amazon Redshift.

For more information about how to create an ODBC connection to connect to Amazon Redshift, see Creating an ODBC connection

Creating an ODBC connection

You must create an ODBC connection to connect to Amazon Redshift after you configure the ODBC connection.

Perform the following steps to create an Amazon Redshift ODBC connection on the Connections page:

1In Administrator, click Connections.

2In the upper right corner, click New Connections.

The New Connection page appears. The following image shows the New Connection page:

3Configure the following connection details in the Connection Details section:

Property	Description
Connection Name	Name of the ODBC connection.
Description	Description of the connection.
Type	Type of the connection. Select the type of the connection as ODBC.

4Configure the following connection details in the Connection Properties section:

Property	Description
Runtime Environment	The name of the runtime environment where you want to run the tasks.
User Name	User name of the Amazon Redshift account.
Password	Password for the Amazon Redshift account.
Data Source Name	Enter the name of the ODBC data source name that you created for the Amazon Redshift database.
Schema	Amazon Redshift schema name.
Code Page	Select the code page that the Secure Agent must use to read or write data.
ODBC Subtype	Enter the value of the ODBC Subtype field as Redshift.

The Amazon Redshift ODBC connection is created successfully.