ODBC Driver Configuration Options
You can use the configuration options described in the following table to control the behavior of the Amazon Redshift ODBC driver.
In Microsoft Windows, you typically set driver options when you configure a data
source name (DSN). You can also set driver options in the connection string when you
connect programmatically, or by adding or changing registry keys in
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\your_DSN.
For more information about configuring a DSN, see Install and Configure the Amazon Redshift
ODBC Driver on Microsoft Windows Operating Systems. For an example of setting driver
options in a connection string, see Connect to Your Cluster
Programmatically.
In Linux and Mac OS X, you set driver configuration options in your odbc.ini and amazon.redshiftodbc.ini files, as described in Configure the ODBC Driver on Linux and Mac OS X Operating Systems. Configuration options set in an amazon.redshiftodbc.ini file apply to all connections. In contrast, configuration options set in an odbc.ini file are specific to a connection. Configuration options set in odbc.ini take precedence over configuration options set in amazon.redshiftodbc.ini.
| ODBC Option | Matching PostgreSQL option exists? | Default Value | Description |
|---|---|---|---|
| AccessKeyID | No | null | The access key ID for the IAM role or IAM user configured for IAM database authentication. The AccessKeyID option is part of a set of options used to configure IAM database authentication. For more information, see JDBC and ODBC Options for Providing IAM Credentials. AccessKeyID and SecretAccessKey must be specified together. |
| AutoCreate | No | false | Specify true to create a database user with the name specified for DbUser if one does not exist. The AutoCreate option is part of a set of options used to configure IAM database authentication. For more information, see JDBC and ODBC Options for Creating Database User Credentials. |
| BoolsAsChar | Yes | 0 |
When this option is enabled (1), the driver exposes Boolean values as data type SQL_VARCHAR with a length of 5. When this option is disabled (0), the driver exposes Boolean values as data type SQL_BIT. |
| Database | Yes | The name of the database to use when the connection is established. | |
| DbGroups | No | null | A comma-delimited list of the names of existing database groups the database user joins for the current session. The DbGroups option is part of a set of options used to configure IAM database authentication. For more information, see JDBC and ODBC Options for Creating Database User Credentials. |
| DbUser | No | null | The name of a database user. The DbUser option is part of a set of options used to configure IAM database authentication. For more information, see JDBC and ODBC Options for Creating Database User Credentials. |
| Driver | Yes | The location of the Amazon Redshift ODBC driver shared object file. | |
| Fetch | Yes | 100 | The number of rows that the driver returns when UseDeclareFetch is enabled. |
| Iam | Yes | Specify 1 to use IAM database authentication. For more information, see JDBC and ODBC Options for Creating Database User Credentials. | |
| InstanceProfile | No | The Amazon EC2 instance profile to use for the connection. For more information, see JDBC and ODBC Options for Creating Database User Credentials. | |
| KeepAlive | No. If keepalives are disabled at the TCP/IP level, KeepAliveTime and KeepAliveInterval are set to 0. | 1 |
When this option is enabled (1), the driver uses TCP keepalives to prevent connections from timing out. When this option is disabled (0), the driver does not use TCP keepalives. |
| KeepAliveCount | No | 0 |
The number of TCP keepalive packets that can be lost before the connection is considered broken. When this option is set to 0, the driver uses the TCP/IP system default for this setting. |
| KeepAliveTime | Yes | 0 |
The number of seconds of inactivity before the driver sends a TCP keepalives packet. When this option is set to 0, the driver uses the TCP/IP system default for this setting. |
| KeepAliveInterval | Yes | 0 |
The number of seconds between each TCP keepalive retransmission. |
| Locale | No | en-US | The locale to use for error messages. |
| MaxBytea | Yes | 255 | The maximum data length for BYTEA columns, in bytes. |
| MaxLongVarChar | Yes | 8190 | The maximum data length for LONG VARCHAR columns, in UTF-8 code units. |
| MaxVarchar | Yes | 255 | The maximum data length for VARCHAR columns, in UTF-8 code units. |
| Port | Yes |
The port to connect to on the Amazon Redshift server. Note By default, Amazon Redshift uses port 5439. |
|
| Profile | No | null | The name of a profile in an AWS credentials or config file that contains values for the JDBC connection options. The Plugin_Name option is part of a set of options used to configure IAM database authentication. For more information, see Using a Configuration Profile. |
| PWD or Password | Yes | The password to use to connect to the Amazon Redshift server. | |
| SecretAccessKey | No | null | The secret access key for the IAM role or IAM user configured for IAM database authentication. The SecretAccessKey option is part of a set of options used to configure IAM database authentication. For more information, see JDBC and ODBC Options for Providing IAM Credentials. AccessKeyID and SecretAccessKey must be specified together. |
| Server or Servername | Yes | The IP address or hostname of the Amazon Redshift server. | |
| SessionToken | No | null | The temporary session token for the IAM role configured for IAM database authentication. SessionToken is not required when IAM database authentication is configured for an IAM user. The SessionToken option is part of a set of options used to configure IAM database authentication. For more information, see JDBC and ODBC Options for Providing IAM Credentials. If SessionToken is used, AccessKeyID and SecretAccessKey must also be specified. |
| SingleRowMode | No | 0 |
When this option is enabled (1), the driver returns query results one row at a time. Enable this option if you plan to query large results and don't want to retrieve the entire result into memory. When this option and UseDeclareFetch are both disabled (0), the driver retrieves the entire query result into memory. Note If UseDeclareFetch is enabled (1), then it takes precedence over SingleRowMode. If SingleRowMode is enabled (1) and UseDeclareFetch is disabled (0), then SingleRowMode takes precedence over UseMultipleStatements. |
| SSLCertPath | Yes | The default file name is root.crt and
the default path is the location of the driver DLL file.
|
The full path of the file containing the certificate authority bundle for verifying the server certificate. If this option is not set, then the driver looks in the folder that contains the driver DLL file. |
| SSLMode | Yes | require | The SSL certificate verification mode to use when
connecting. For more information about possible SSL modes to use,
see Using SSL and Trust CA Certificates in
ODBC.
Important Amazon Redshift has changed the way that we manage SSL certificates. If you must use an ODBC driver version earlier than 1.3.7.1000, you might need to update your current trust root CA certificates to continue to connect to your clusters using SSL. For more information, see Transitioning to ACM Certificates for SSL Connections. |
| TextAsLongVarchar | Yes | 0 |
When this option is enabled (1), the driver returns TEXT columns as LONG VARCHAR data. When this option is disabled (0), the driver returns TEXT columns as TEXT data. |
| UID | Yes | The user name to use to connect to the Amazon Redshift server. | |
| UseDeclareFetch | Yes | 0 |
When this option is enabled (1), the driver returns a specific number of rows at a time. To set the number of rows, use the Fetch option. When this option is disabled (0) and SingleRowMode is enabled (1), the driver returns query results one row at a time. If SingleRowMode is also disabled (0), then the driver retrieves the entire query result into memory. Note If UseDeclareFetch is enabled, then UseDeclareFetch takes precedence over SingleRowMode and UseMultipleStatements. |
| UseMultipleStatements | No | 0 |
When this option is enabled (1), the driver can run queries that are split into separate statements. When this option is disabled (0), the driver runs queries as single statements. Note If UseDeclareFetch is enabled (1), then UseDeclareFetch takes precedence over UseMultipleStatements. If UseDeclareFetch is disabled (0) but SingleRowMode is enabled (1), then SingleRowMode takes precedence over UseMultipleStatements. |
| Username | Yes | The same information as UID (the user name to use to connect to the Amazon Redshift server). If UID is defined, then UID takes precedence over Username. | |
| UseUnicode | No | 0 |
When this option is enabled (1), the driver returns data as Unicode character types:
When this option is disabled (0), the driver returns data as regular SQL types:
|
