JDBC Connection Properties
You use connection properties to configure the connection between your JDBC client application and your Vertica database. The properties provide the basic information about the connections, such as the server name and port number to use to connect to your database. They also let you tune the performance of your connection and enable logging.
You can set a connection property in any of three ways:
- Include the property name and value as part of the connection string you pass to the
DriverManager.getConnection()
method. - Set the properties in a
Properties
object, and then pass it to theDriverManager.getConnection()
method. - Use the
VerticaConnection.setProperty()
method. With this approach, you can change only those connection properties that remain changeable after the connection has been established.
In addition, some of the standard JDBC connection properties have getters and setters on the Connection
interface (such as Connection.setAutocommit()
).
Connection Properties
The properties in the following table can only be set before you open the connection to the database. Two of them are required for every connection.
Property | Description |
---|---|
ConnSettings |
A string containing SQL statements that the JDBC driver automatically runs after it connects to the database. You can use this property to set the locale or schema search path, or perform other configuration that the connection requires. Required?: No Default Value: none |
DisableCopyLocal |
When set to true, disables file-based COPY LOCAL operations, including copying data from local files and using local files to store data and exceptions. You can use this property to prevent users from writing to and copying from files on a Vertica host, including an MC host. Required?: No Default Value: false |
Label |
Sets a label for the connection on the server. This value appears in the client_label column of the V_MONITOR.SESSIONS system table. Required?: No Default Value: jdbc-driver_version-random_number |
LoginTimeout |
The number of seconds Vertica waits for a connection to be established to the database before throwing a Required?: No Default Value: 0 (no TCP timeout) |
SSL |
When set to true, use SSL to encrypt the connection to the server. Vertica must be configured to handle SSL connections before you can establish an SSL-encrypted connection to it. See TLS Protocol in the Administrator's Guide. Required?: No Default Value: false |
Password |
The password to use to log into the database. Required?: Yes Default Value: none |
User |
The database user name to use to connect to the database. Required?: Yes Default Value: none |
ConnectionLoadBalance |
A Boolean value indicating whether the client is willing to have its connection redirected to another host in the Vertica database. This setting has an effect only if the server has also enabled connection load balancing. See About Native Connection Load Balancing in the Administrator's Guide for more information about native connection load balancing. Required?: No Default Value: false |
BackupServerNode |
A string containing the host name or IP address of one or more hosts in the database. If the connection to the host specified in the connection string times out, the client attempts to connect to any host named in this string.The host name or IP address can also include a colon followed by the port number for the database. If no port number is specified, the client uses the standard port number (5433) . Separate multiple host name or IP address entries with commas. Required?: No Default Value: none |
PreferredAddressFamily |
The IP version to use if the client and server have both IPv4 and IPv6 addresses and you have provided a host name. Valid values are:
Required?: No Default Value: none |
KeyStorePath |
The server path to a .JKS file containing your private keys and their corresponding certificate chains. For information on creating a keystore, refer to documentation for your development environment. For information on creating a keystore, refer to the Java documentation. Required?: No Default Value: none |
KeyStorePassword |
The password protecting the keystore file. If individual keys are also encrypted, the keystore file password must match the password for a key within the keystore. Required?: No Default Value: none |
TrustStorePath |
The local path to a .JKS truststore file containing certificates from authorities you trust. Required?: No Default Value: none |
TrustStorePassword |
The password protecting the truststore file. Required?: No Default Value: none |
General Properties
The following properties can be set after the connection is established. None of these properties are required.
Property | Description |
---|---|
AutoCommit |
Controls whether the connection automatically commits transactions. Set this parameter to false to prevent the connection from automatically committing its transactions. You often want to do this when you are bulk loading multiple batches of data and you want the ability to roll back all of the loads if an error occurs. Previous Property NameNote: This property was called defaultAutoCommit in previous versions of the Vertica JDBC driver. Set After Connection: Default Value: true |
DirectBatchInsert |
Determines whether a batch insert stored data directly into ROS (true) or using AUTO mode (false). Set After Connection: Default Value: false |
MultipleActiveResultSets |
Allows more than one active result set on a single connection via MultipleActiveResultSets (MARS). If both MultipleActiveResultSets and ResultBufferSize are turned on, MultipleActiveResultSets takes precedence. The connection does not provide an error, however ResultBufferSize is ignored. Set After Connection: Default Value: false |
ReadOnly |
When set to true, makes the data connection read-only. Any queries attempting to update the database using a read-only connection cause a Set After Connection: Default Value: false |
ResultBufferSize |
Sets the size of the buffer the Vertica JDBC driver uses to temporarily store result sets. A value of 0 means ResultBufferSize is turned off. Note: This property was named maxLRSMemory in previous versions of the Vertica JDBC driver. Set After Connection: Default Value: 8912 (8KB) |
SearchPath |
Sets the schema search path for the connection. This value is a string containing a comma-separated list of schema names. See Setting Search Paths in the Administrator's Guide for more information on the schema search path. Set After Connection: Default Value: "$user", public, v_catalog, v_monitor, v_internal |
ThreePartNaming |
A Boolean value that controls how DatabaseMetaData reports the catalog name. When set to true, the database name is returned as the catalog name in the database metadata. When set to false, NULL is returned as the catalog name. Enable this option if your client software is set up to get the catalog name from the database metadata for use in a three-part name reference. Set After Connection: Default Value: true |
TransactionIsolation |
Sets the isolation level of the transactions that use the connection. See Changing the Transaction Isolation Level for details. Note: In previous versions of the Vertica JDBC driver, this property was only available using a getter and setter on the Set After Connection: Default Value: TRANSACTION_READ_COMMITTED |
Logging Properties
The properties that control client logging must be set before the connection is opened. None of these properties are required, and none can be changed after the Connection
object has been instantiated.
Property | Description |
---|---|
LogLevel |
Sets the type of information logged by the JDBC driver. The value is set to one of the following values:
Default Value: |
LogNameSpace |
Restricts logging to just messages generated by a specific packages. Valid values are:
Default Value: none |
LogPath |
Sets the path where the log file is written. Default Value: The current working directory |
Kerberos Connection Parameters
Use the following parameters to set the service and host name principals for client authentication using Kerberos.
Parameters | Description |
---|---|
JAASConfigName |
Provides the name of the JAAS configuration that contains the JAAS Krb5LoginModule and its settings Default Value: verticajdbc |
KerberosServiceName |
Provides the service name portion of the Vertica Kerberos principal, for example: Default Value: vertica |
KerberosHostname |
Provides the instance or host name portion of theVertica Kerberos principal, for example: Default Value: Value specified in the servername connection string property |
Routable Connection API Connection Parameters
Use the following parameters to set properties to enable and configure the connection for Routable Connection lookups.
Parameters | Description |
---|---|
EnableRoutableQueries |
Enables Routable Connection lookup. See Routing JDBC Queries Directly to a Single Node Default Value: false |
FailOnMultiNodePlans |
If the query plan requires more than one node, then the query fails. Only applicable when EnableRoutableQueries = true. Default Value: true |
MetadataCacheLifetime |
The time in seconds to keep projection metadata. Only applicable when EnableRoutableQueries = true. Default Value: |
MaxPooledConnections |
Cluster-wide maximum number of connections to keep in the VerticaRoutableConnection’s internal pool. Only applicable when EnableRoutableQueries = true. Default Value: 20 |
MaxPooledConnections PerNode |
Per-node maximum number of connections to keep in the VerticaRoutableConnection’s internal pool. Only applicable when EnableRoutableQueries = true. Default Value: 5 |
You can also use VerticaConnection.setProperty()
method to set properties that have standard JDBC Connection setters, such as AutoCommit.
For information about manipulating these attributes, see Setting and Getting Connection Property Values.