Connection Driver Reference
JDBC Manual

Commands Supported by the Ocient JDBC CLI Program

Commands are not case-sensitive.

All commands, except QUIT, must end with a semicolon at the command line.

You can cancel any query that does not update data by pressing CTRL+C.

CANCEL

Cancels the current query that is still running on the system.

Syntax

Shell


Parameter

Data Type

Description

uuid

String

A query unique identifier (uuid) that is currently running. Use Commands Supported by the Ocient JDBC CLI Program  or search the sys.queries table to find the uuid.

CONNECT

Connects to a SQL Node. For more information on connection steps, see Connect Using JDBC.

Syntax

Shell


The <url> parameter must follow this format: jdbc:ocient://node_address1:port1[,node_address2:port2[,...]]/database_name[;property=value[;...]]

If you do not specify the username and password, then the database uses the default credentials specified at startup.

JDBC URL parameters

Parameter

Description

node_address

A hostname or IP address.

The database attempts to connect using each DNSName:Port pair in the comma-delimited list from left to right until a successful connection happens.

port

Standard JDBC port number is 4050.

For more information on ports, see Ports.

database_name

The identifier used for your database.

Additional JDBC URL Properties

The JDBC URL can include these optional properties in the key-value format property=value. Separate each property pair by semicolons.

URL properties are case-insensitive.

To authenticate using Single Sign-On, set the handshake property to SSO and leave the user and password properties empty. For example: jdbc:ocient://DNSName:Port[,IP2:Port2,...]/databaseName;handshake=SSO;user=;password=;

Parameter

Description

defaultSchema

Default schema.

force

If set to true (case-sensitive), this parameter disables load-balancing for the connection.

handshake

Specifies the handshake protocol used for the connection.



Supported options include: "CBC", "GCM", "SSO"



"GCM" - (Galois/Counter Mode). This is the default encryption and is the recommended password encryption algorithm.

"CBC" - (Cipher Block Chaining) for password encryption.

"SSO" - Single Sign-On.

logfile

The filename to use for JDBC tracing.

loglevel

If set to ERROR (warnings and errors only) or DEBUG (verbose tracing) and logfile is also set, JDBC tracing is enabled. This parameter is case-sensitive.

longQueryThreshold

Estimated query runtime in milliseconds before deeper query optimization runs.



0 = Use database server default.

-1 = Never run deeper optimization.

maxRows

Maximum allowed result set size in number of rows.

maxTempDisk

Maximum allowed temp disk usage as a percentage (0 - 100).

maxThreadsPerResultSet

The maximum number of threads the client uses to fetch rows from the server per Result Set, as defined in the official JDBC documentation. In this context, a thread represents a logical connection to the database.



Applications that create and operate on Statement objects concurrently might find value in setting this property. Defaults to 0, which effectively creates an unbounded Result Set thread pool.

maxTime

Maximum allowed runtime of a query in seconds before it is canceled on the server.

networkTimeout

Network connection timeout in milliseconds.



If unspecified, this defaults to 10000 milliseconds.

parallelism

Limits a query to running on a specified number of cores on each CPU.

password

The password for the user.

priority

Query priority. This sets the priority for queries to run on the server side.



If unspecified, this defaults to 1.0.

priorityAdjustFactor

The default query priority adjustment value. For more information, see SET ADJUSTFACTOR.

priorityAdjustTime

The default frequency to adjust the query priority. For more information, see SET ADJUSTTIME.

serviceClassName

Specifies the name of the service class to use for the database session.

ssoOAuthCodeCallbackPort

If ssoOAuthFlow=authorizationCode, this property specifies the port that the SSO authorization uses.

If unspecified, the default value is 7050.

ssoOAuthFlow

This property is only applicable if the property handshake is set to SSO.



Forces the driver to use either the "authorizationCode" or "deviceGrant" flow to establish a Single Sign-On session.



If this property is not provided, the Ocient® system uses the "authorizationCode" flow when a web browser is available to the client and the "deviceGrant" flow when a web browser is not available to the client.

ssoDebugMode

When set to true, the system records additional log messages related to the Single Sign-On.

ssoOktaNativeTokenPath

The path to the OKTA® Native Token (AES-256GCM JWE). The path is relative to your home directory (i.e. '~/').

ssoTimeoutSeconds

The number of seconds before the Single Sign-On connection request times out. The default value is 60.

statementPooling

This property has been removed as of Ocient JDBC version 2.104 and later.

When set to ON, recently used statements are cached.

Set to OFF when using a third party application that implements statement pooling

tls

Can be set to unverified or on. Enables SSL/TLS encryption for the connect.

timeoutMillis

Number of milliseconds before cancellable operations are timed out and killed by the driver. 0 means no timeout. Default: 0

user

The identifier of the user.

DESCRIBE TABLE

Lists the columns of the specified table and its associated data types.

Syntax

Shell


Parameter

Description

table

A table identifier.

schema

A qualifying schema name for the specified table.



If a qualifying schema is not given for the table, the default name schema is assumed.

verbose

If specified, the Ocient system prints the full column metadata for the specified table as returned by GetColumns.

DESCRIBE VIEW

Returns the query text used to create the specified view.

Syntax

Shell


Parameter

Description

view

A view identifier.

schema

A qualifying schema name for the specified table.



If a qualifying schema is not given for the table, the default name schema is assumed.

verbose

If specified, the Ocient system prints the full column metadata for the specified table as returned by GetColumns.

EXTRACT

Extracts a result set in delimited files to a specified location. For more information on using this command, see Data Extract Manual.

Syntax

SQL


GET JDBC VERSION

Returns the JDBC version of the driver.

Syntax

Shell


GET SCHEMA

Retrieves the default name schema for tables.

Syntax

Shell


KILL

Issues a hard kill command to the virtual machine to terminate a query that is running on the system. Use only if CANCEL fails.

Syntax

Shell


Parameter

Description

uuid

A query unique identifier (uuid) that is currently running. Use LIST ALL QUERIES or search the sys.queries table to find the uuid.

LIST ALL QUERIES

List all the current running queries across all the cmdComp servers. This is the equivalent of running SELECT * FROM sys.queries on all cmdComp servers.

Syntax

Shell


LIST INDEXES

Lists the indexes on the specified table and its columns.

Syntax

Shell


Parameter

Description

table

A table identifier.

schema

A qualifying schema name for the specified table.



If a qualifying schema is not given for the table, the default name schema is assumed.

verbose

If specified, the Ocient system prints the full column metadata for the specified table as returned by getIndexInfo.

LIST SYSTEM TABLES

Lists all system catalog tables in the database.

Syntax

Shell


Parameter

Description

verbose

If verbose is specified, the full table metadata as returned by getTables is printed.

LIST TABLES

Lists all tables (in all schemata) in the database.

Syntax

Shell


Parameter

Description

verbose

If verbose is specified, the full table metadata as returned by getTables is printed.

LIST VIEWS

Lists all views (in all schemata) in the database.

Syntax

Shell


Parameter

Description

verbose

If verbose is specified, the full table metadata as returned by getViews is printed.

OUTPUT GIS KML

Outputs the next query in KML format to the specified filename. This flag affects only the next query.

This syntax prints out all GIS types to the KML file, and non-GIS types are added to the description of elements in the same row. You can upload this KML file to certain visualization tools.

Syntax

Shell


Parameter

Description

filename

A filename for the KML output.

OUTPUT NEXT QUERY

Output the next query in CSV format to the specified filename. This flag only affects the next query. All queries after print out their results.

Add APPEND to have output added to the specified file instead of overwriting it.

Syntax

Shell


Parameter

Description

filename

A filename for the CSV output.

PERFORMANCE

Enables different levels of query result output for measuring benchmarks by using different performance options.

Syntax

Shell


Parameter

Description

OFF

This is the default mode.

The system executes a query through all normal stages, including the database, client network, and client processing.

NETWORK

The system executes a query through the database and client network, but discards rows before they are processed by the client (nothing is printed to stdout).

Use this mode when you think the client network is hindering performance.

DATABASE

The system executes a query through the database only. The database does not send any matching rows to the client.

Use this mode when you think the database query execution is affecting performance.

PERFORMANCE ON mode has been removed as of JDBC version 3.0.

QUIT

Closes the command-line interface.

Syntax

Shell


REPORT LOG ON

Enables Apache® Log4J 2™ -based metrics collection and redirects reporting events to the configured appenders.

The REPORT LOG ON command requires JDBC driver version 2.88 or later.

The Log4j configuration determines the destination for report logs. The Ocient logger name is com.ocient.util.ReportLogger. For more information about configuring Log4j, see the Log4j documentation.

The default Log4j configuration routes report logs to the RollingFileAppender. The default configuration includes these settings:

Properties

  • fileName="logs/${sys:ocient.jdbc.sessionId:-unknown}/report.log"
  • filePattern="logs/${sys:ocient.jdbc.sessionId:-unknown}/%d{yyyy-MM-dd}-%i-report.log.gz

Rollover Policies

  • TimeBasedTriggeringPolicy
  • SizeBasedTriggeringPolicy (100MB)

You can retrieve the default configuration from the ocient-jdbc4 jar file by using an archive utility like unzip. For example:

Shell


Syntax

Shell


Parameter

Description

interval

Optional. Sets the Log4j reporting interval in seconds.



If unspecified, defaults to 30.

REPORT LOG OFF

Disables file-based metrics collection.

The REPORT LOG OFF command requires JDBC driver version 2.88 or later.

Syntax

Shell


SELECT

Retrieves the appropriate result set of a SQL SELECT statement.

Syntax

Shell


SET MAXROWS

Sets the maximum amount of rows allowed in a result set. Applies only to the statement object that executed the command.

Syntax

Shell


Parameter

Description

num_of_rows

The maximum amount of rows allowed in a result set. If a query returns more rows than the specified value, the result set is truncated by num_of_rows.



To disable this setting, you can either set the maximum rows to 0 or use the RESET keyword; e.g., SET MAXROWS RESET;

SET ADJUSTFACTOR

Adjusts the query priority by a specified percentage amount at every interval set by the SET ADJUSTTIME command.

The SET ADJUSTFACTOR command requires JDBC driver version 2.52 or later.

Syntax

Shell


Parameter

Description

priorityAdjustFactor

A percentage amount to raise or lower the query priority by. Accepted values are in the range of [-1,1].



At every interval set by the priorityAdjTime, the query priority is multiplied by 1 + priorityAdjustFactor to get a new priority value.

SET ADJUSTTIME

Modifies how frequently the query priority is adjusted during the course of execution.

The SET ADJUSTTIME command requires JDBC driver version 2.52 or later.

Syntax

Shell


Parameter

Description

priorityAdjTime

The frequency for adjusting the query priority in seconds.

SET MAXTEMPDISK

Sets the maximum percentage of temporary disk used by queries. Applies only to the statement object that executed the command.

Syntax

Shell


Parameter

Description

percentage

The maximum percentage of temporary disk used by queries. Any queries exceeding this threshold are killed.



To disable this setting, use the RESET keyword; e.g., SET MAXTEMPDISK RESET;

SET MAXTIME

Sets the maximum query time. Applies only to the statement object that executed the command.

Syntax

Shell


Parameter

Description

seconds

The maximum query time. Queries longer than this are killed.



To disable this setting, you can either set the maximum time to 0 or use the RESET keyword; e.g., SET MAXTIME RESET;

SET PARALLELISM

Sets a limit on the number of cores on each CPU that can be allocated to running the query. Applies only to the statement object that executed the command.

The parallelism value limits a query to running on the specified number of cores on each CPU.

Syntax

Shell


Parameter

Description

parallelism

Limits a query to running on a specified number of cores on each CPU.



To disable this setting, you can either set the parallelism value to -1 or use the RESET keyword; e.g., SET PARALLELISM RESET;



SET PRIORITY

Sets the scheduling priority to use for queries. Applies only to the statement object that executed the command.

Syntax

Shell


Parameter

Description

priority

The scheduling priority to use for queries.



Set from 0 to 100. To disable this setting, you can either set priority to -1 or use the RESET keyword; e.g., SET PRIORITY RESET;

SET SCHEMA

Changes the default schema for tables. Applies only to the statement object that executed the command.

Syntax

Shell


Parameter

Description

schema

The default schema for new tables.

SET SERVICECLASS

Limits a query to using the specified service class. Applies only to the statement object that executed the command.

Syntax

Shell


Parameter

Description

service_class_name

The service class that a query is limited to using.



To disable this setting, you can use the RESET keyword; e.g., SET SERVICECLASS RESET;

SOURCE

Reads and executes commands found in the specified file.

Syntax

Shell


Parameter

Description

filename

A valid file name or path.

TIMING

Enables or disables reporting the execution time of each query.

Syntax

Shell


Related Links

JDBC Manual