Connection Properties

When connecting to NuoDB, there are many properties available to configure specific driver behavior. They may be defined in a number of ways including:

  • In JDBC, as part of the connection URL.

  • In the C++ API, via a call to Connection::openDatabase().

  • In the C API via NuoDB_Connection->openDatabase()

  • On the nuosql command line with the --connection-property argument (one argument for each property), for example:

    • nuosql test --user dba --password goalie --connection-property isolation=read_committed --connection-property clientInfo=user123

  • Other drivers have their own methods.

Name Description

allowSRPFallback=
[ true | false ]

Allows the driver to fall back to SRP if the TLS connection fails (when used in combination with trustStore ).

  • Required (set to true) during rolling upgrade of the security protocol.

always-recompile-queries=
[ true | false ]

When set to true, all queries will be fully recompiled on every execution, the sql statement cache will be not be used.

  • The default value is false

ciphers=cipherlist

Enables the client to provide one or more appropriate ciphers for encrypting the network connection to the database.

  • If this property is not specified the default ciphers AES-256-CTR and RC4 are enabled. The server chooses a preferred cipher from this list.

  • If no matching cipher is found, the connection will be refused.

  • The cipherlist is comma-separated and cipher names are case-sensitive and must be provided exactly as shown.

  • The full list of available ciphers are AES-256-CTR, RC4, and None.

clientInfo=info

Arbitrary information about the connecting client.

  • This is available in the CLIENTINFO column of the SYSTEM.CONNECTIONS and SYSTEM.LOCALCONNECTIONS system tables.

clientProcessID=id

The process ID (pid) of the connecting client.

  • The client may set this property only if not already set by the driver. The .NET and Python drivers set this for the client. The client setting of this property does not overrides the driver setting of this property.

  • The clientProcessId is then available in the CLIENTPROCESSID column of the SYSTEM.CONNECTIONS and SYSTEM.LOCALCONNECTIONS system tables.

direct=
[ true | false ]

Connect directly to TE(s) rather than via the AP(s).

  • The default value is false and connections are made via the AP(s) specified in the connection URL.

  • When direct=true, the client connection is made directly with a TE.

    • The hosts and port numbers in the connection URL are assumed to specify the hosts and port numbers of TE processes.

  • The use of the direct connection property is supported for nuosql and the JDBC, C, C++, and Go drivers.

    • When using nuosql, it supports an equivalent --direct option.

  • Where possible, direct connection should be made using the load-balancing capability of the APs.

  • This connection property is useful when connecting external SQL applications directly to TEs that are running on a different network - such as in the Cloud, on Kubernetes clusters, and in other containerized deployments (such as Docker) or via SSH tunneling.

  • For more details, see Direct TE Connections.

    • Although it is for JDBC, the basic principles apply to all drivers supporting direct.

[ on | off ]

idle-timeout=number

The maximum time (in seconds) which indicates how long idle connections are left open.

  • Although the IDLE_CONNECTION_TIMEOUT system property is a global setting for timing out idle connections, you can use this connection property to override the system property on a per connection basis.

  • The default value is -1, which means that the global setting is used. If you set a value greater than 0, this property overrides any value set for IDLE_CONNECTION_TIMEOUT. You may also set a value of 0 to specifically disable this property for the connection.

  • Any time a client connection is terminated for being idle for too long, a message will be logged under the net category.

  • For more information on IDLE_CONNECTION_TIMEOUT, see SQL System Properties.

isolation=level

The default transaction isolation level for the connection to use.

  • Isolation levels supported are consistent_read and read_committed. Note that these values are case-sensitive.

  • For more information, see CONSISTENT READ and READ COMMITTED.

keepAlive=
[ true | false ]

Allows enabling of TCP KeepAlive on the connection from client to Transaction Engine.

  • The default value is false.

  • When keepAlive=true, the driver enables TCP KeepAlive on the socket that it opens to the Transaction Engine.

LBPolicy=valueA

From the client application, this property allows selective load balancing across Transaction Engines.

LBQuery=<selector>(<filter>)

From the client application, this property allows selective load balancing across Transaction Engines.

lobChunkingEnabled=
[ true | false ]

(JDBC only) Enables BLOB and CLOB streaming to the JDBC client.

  • The default value is true (enabled).

lobChunkSize=size

(JDBC only) Overrides the default chunk size for BLOB and CLOB streaming.

  • The default value is 65536 bytes.

lock-wait-timeout=sec

Configures lock wait time (in seconds) when establishing a new connection. For more information, see About the NuoDB SQL Configuration File.

  • The default value is equal to the system property DEFAULT_LOCK_WAIT_TIMEOUT. For more information, see SQL System Properties.

max-result-set-size-bytes=
size

Sets the maximum size of a result set that can be sent to the client in a single network packet.

  • The default value is 100000

memory-limit-bytes=
size

The memory limit (per connection) in bytes for all blocking SQL engine operations:

  • Hash-grouping, sorting, sorting via priority queue for limit, distinct (via hash-grouping), union, listagg, table functions, and stored procedures returning result set(s) that accumulate data - in main memory.

  • To run statements with a memory limit different from the system-wide limit, start a new connection with this connection property set to the per-connection limit.

  • If you do not define a value for memory-limit-bytes, the value set for the DEFAULT_CONNECTION_MEMORY_LIMIT system property is the default setting used.

password=pwd

The password for the user account used to connect to the database.

PreferInternalAddress=
[ true | false ]

Use internal address and port for TE, as specified by the external-address and external-port process labels.

queryoptimizer= optimizer

Configures the version of the optimizer to be used for the duration of the current connection.

  • Default value: v3

  • Possible values: v2, v3

  • The SQLENGINE field in the SYSTEM.CONNECTIONS system table displays the version of the optimizer for the current connection.

Optimizer v2 was deprecated in NuoDB 6.0.

readOnly

Not implemented.

rollbackMode=
[ procedure | transaction | off ]

Controls the behavior of transactions executed inside a stored procedure.

schema=name

The default schema that the connection should use when objects (such as tables and views) are not fully qualified.

TimeZone=timezone

The default time zone for the connection to use (this is also known as the session time zone).

  • If not specified, the application’s default time zone is used.

trustedCertificates=
[ true | false ]

The trusted certificates as a PEM-encoded string. Only supported by the C/C++ driver.

trustStore=filepath

Path to file which stores certificates from trusted entities.

  • Trust store format depends on the driver.

    • C/C++ driver expects a PEM file, typically ca.cert. nuosql uses this driver, so it also requires this file.

    • JDBC driver requires a PKCS12 or JKS format file, typically nuoadmin-truststore.p12.

  • Turns on SSL/TLS, turns off SRP.

trustStorePassword=
password

Verifies the integrity of the trustsStore.
NOTE: JDBC only.

user=name

The user name for connecting to the database.

verbose-errors

Not implemented.

verifyHostname=
[ true | false ]

Verifies the DN name of the SSL server against the name presented on the certificate.

  • The default value is true.

wait-for-pending-commits=
[ true | false ]

For internal use only.

Example

SET LOCK_WAIT_TIMEOUT 10;
SELECT * FROM SYSTEM.CONNECTIONPROPERTIES ORDER BY PROPERTY;
        PROPERTY               VALUE
 ------------------------- ---------------

 TimeZone                  Etc/UTC
 always-recompile-queries  false
 idle-timeout              -1
 isolation                 8
 lobChunkSize              65536
 lobChunkingEnabled        false
 lock-wait-timeout         10
 max-result-set-size-bytes 100000
 memory-limit-bytes        0
 query-optimizer           v3
 readOnly                  false
 rollbackMode              procedure
 schema                    USER
 user                      DBA
 verbose-errors            Not implemented
 wait-for-pending-commits  true