JDBC connection properties

Boolean value that determines which mode Vertica uses when connecting to a JDBC client:

• true: binary transfer (default)

• false: text transfer

Binary transfer is generally more efficient at reading data from a server to a JDBC client and typically requires less bandwidth than text transfer. However, when transferring a large number of small values, binary transfer may use more bandwidth.

The data output by both modes is identical with the following exceptions for certain data types:

• FLOAT: Binary transfer has slightly better precision.

• TIMESTAMPTZ: Binary transfer can fail to get the session time zone and default to the local time zone, while text transfer reliably uses the session time zone.

• NUMERIC: Binary transfer is forcibly disabled for NUMERIC data by the server for Vertica 11.0.2+.

Sets a label for the connection on the server. This value appears in the client_label column of the SESSIONS system table.

Default: jdbc-driver-version-random_number

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. This property has been deprecated in favor of the TLSmode property.

Default: false

TLSmode identifies the security level that Vertica applies to the JDBC connection. Vertica must be configured to handle TLS connections before you can establish an encrypted connection to it. See TLS protocol for details. Valid values are:

• disable: JDBC connects using plain text and implements no security measures.

• require: JDBC connects using TLS without verifying the CA certificate.

• verify-ca: JDBC connects using TLS and confirms that the server certificate has been signed by the certificate authority. This setting is equivalent to the deprecated ssl=true property.

• verify-full: JDBC connects using TLS, confirms that the server certificate has been signed by the certificate authority, and verifies that the host name matches the name provided in the server certificate.

If this property and the SSL property are set, this property takes precedence.

Default: disable

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 for more information about native connection load balancing.

Default: false

The IP version to use if the client and server have both IPv4 and IPv6 addresses and you have provided a host name, one of the following:

• ipv4: Connect to the server using IPv4.

• ipv6: Connect to the server using IPv6.

• none: Use the IP address provided by the DNS server.

Default: none

Required if oauthaccesstoken is unspecified, allows a user to refresh and obtain a new oauthaccesstoken when their old one expires.

If you set this parameter, you must also set the following refresh properties in oauthjsonconfig:

• oauthdiscoveryurl or oauthtokenurl

• oauthclientid

• oauthclientsecret

In cases where introspection fails (e.g. when the access token expires), Vertica responds to the request with an error. If introspection fails and OAuthRefreshToken is specified, the driver attempts to refresh and silently retrieve a new access token. Otherwise, the driver passes error to the client application.

A JSON string or file that lets you set the following:

• oauthclientid: The client ID of the client application registered in the identity provider.

• oauthclientsecret: The client secret of the client application registered in the identity provider.

• oauthtokenurl: The endpoint to which token refresh requests are sent. The format for this depends on your provider. For examples, see the Keycloak and Okta documentation.

  If oauthdiscoveryurl is not set: this parameter is only required for IDP valdiation if oauthdiscoveryurl, but should also be set for JWT validation to support token refresh.

• oauthauthurl: The authorization endpoint used for single sign-on. For examples, see the Keycloak and Okta documentation.

• oauthdiscoveryurl: Also known as the OpenID Provider Configuration Document, this endpoint contains a list of all other endpoints supported by the IDP. If set, the other endpoints (such as oauthtokenurl and oauthauthurl) do not need to be specified. This parameter is only supported for Keycloak. For other identity providers like Okta, the endpoints must be set manually.

  If you set both oauthdiscoveryurl and another endpoint (like oauthtokenurl), oauthdiscoveryurl takes precedence.

  This parameter is only required for IDP valdiation, but should also be set for JWT validation to support token refresh.

• oauthscope: The requested OAuth scopes, delimited with spaces. These scopes define the extent of access to the resource server (in this case, Vertica) granted to the client by the access token. For details, see the OAuth documentation.

• oauthvalidatehostname: Boolean, whether to verify the subjectAltName of the identity provider host. If enabled, the IP address or hostname must be set as the subjectAltName in its certificate. Hostname verification is enabled by default.

Unlike oauthaccesstoken or oauthrefreshtoken, which must be set programmatically by the client when they attempt to connect, the same oauthjsonconfig can be reused between connections to the database.

For example, to set it as a connection property:

Properties myProp new Properties();
String oauthrefreshparams = "{
    \"oauthdiscoveryurl\": \"http://203.0.113.1:8443/realms/myrealm/.well-known/openid-configuration",
    \"oauthclientid\": \"vertica\",
    \"oauthclientsecret\": \"eba23135-834f-1341-aa34-bf9345713dfc\",
    \"oauthscope\": \"offline_access openid\",
    \"oauthvalidatehostname\": \"false\"
}"
myProp.put.setProperty("oauthjsonconfig", oauthrefreshparams);

The number of seconds Vertica waits for the client to log in to the database before throwing a SQLException.

Default: 0 (no timeout)

The number of seconds the JDBC client waits before attempting to connect to the next node if the Vertica process is running, but does not respond. The "next" node is determined by the either the BackupServerNode connection property or DNS resolution. If you only provide a single IP address, the JDBC client returns an error.

A timeout value of 0 instructs JDBC to wait indefinitely for an error/a successful connection rather than attempt to connect to another node.

Default: 0 (no timeout)

The number of seconds the JDBC client has to establish a TCP connection to a Vertica node. A typical use case for this property is to let JDBC connect to another node if the system is down for maintenance and modifying the JDBC application's connection string is infeasible.

Default: 0 (no timeout)

The number of milliseconds for the server to reply to a request after the client has established a connection with the database.

Default: 0

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.

Set After Connection: Connection.setAutoCommit()

Default: true

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.

Default: false

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: VerticaConnection.setProperty()

Default: false

When set to true, makes the data connection read-only. Any queries attempting to update the database using a read-only connection cause a SQLException.

Set After Connection: Connection.setReadOnly()

Default: false

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: VerticaConnection.setProperty()

Default: 8912 (8KB)

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 for more information on the schema search path.

Set After Connection: VerticaConnection.setProperty()

Default: "$user", public, v_catalog, v_monitor, v_internal

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: VerticaConnection.setProperty()

Default: true

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 PGConnection object. You can now set it in the same way as other connection properties.

Set After Connection: Connection.setTransactionIsolation()

Default: TRANSACTION_READ_COMMITTED

Sets the type of information logged by the JDBC driver. The value is set to one of the following values:

• "DEBUG"

• "ERROR"

• "TRACE"

• "WARNING"

• "INFO"

• "OFF"

Default: "OFF"

Restricts logging to just messages generated by a specific packages. Valid values are:

• com.vertica — All messages generated by the JDBC driver

• com.vertica.jdbc — All messages generated by the top-level JDBC API

• com.vertica.jdbc.kv — A ll messages generated by the JDBC KV API)

• com.vertica.jdbc.core — Connection and statement settings

• com.vertica.jdbc.io — Client/server protocol messages

• com.vertica.jdbc.util — Miscellaneous utilities

• com.vertica.jdbc.dataengine — Query execution and result set iteration

• com.vertica.dataengine — Query execution and result set iteration

The path for the log file.

Default: The current working directory

Provides the name of the JAAS configuration that contains the JAAS Krb5LoginModule and its settings

Default: verticajdbc

Provides the service name portion of the Vertica Kerberos principal, for example: vertichost@EXAMPLE.COM

Default: vertica

Provides the instance or host name portion of the Vertica Kerberos principal, for example: verticaosEXAMPLE.COM

Default: Value specified in the servername connection string property

Enables Routable Connection lookup. See Routing JDBC queries directly to a single node

Default: false

If the query plan requires more than one node, then the query fails. Only applicable when EnableRoutableQueries = true.

Default: true

The time in seconds to keep projection metadata. Only applicable when EnableRoutableQueries = true.

Default:

Cluster-wide maximum number of connections to keep in the VerticaRoutableConnection’s internal pool. Only applicable when EnableRoutableQueries = true.

Default: 20

Per-node maximum number of connections to keep in the VerticaRoutableConnection’s internal pool. Only applicable when EnableRoutableQueries = true.

Default: 5