Commands Supported by the Ocient JDBC CLI Program

commands are not case sensitive for ddl statements, see docid 2wcd8nmbi5m9fctufiqtm 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 cancel uuid; parameter data type description uuid string a query universally unique identifier (uuid) that is currently running use docid 7uosju7ajx4yd61vqbhqu or search the sys queries table to find the uuid connect connects to a sql node for details about connection steps, see docid\ fpyaptctcu3mul0c66p8d syntax connect to \<url> \[ user username using password ] 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 details about ports, see docid\ pusva0 a2unmu385gcra 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 identityprovider an sso integration established in the database for details, see docid\ xga0pas8wadtq33 a x7v 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 the 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 https //docs oracle com/javase/7/docs/api/java/sql/resultset html 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 details, see /#set adjustfactor priorityadjusttime the default frequency to adjust the query priority for details, see /#set adjusttime serviceclassname sp ecifies the name of the service class to use for the database session ssonumericaddress specifies the sso callback url as 127 0 0 1 when this value is false , the url is localhost the default value is false 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 sso authorization is enabled 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 sso ssooktanativetokenpath the path to the {{okta}} native token (aes 256gcm jwe) the path is relative to your home directory (i e ' /') ssosslcallback specifies whether to use https instead of http for the sso callback url the default value is false ssotimeoutseconds the number of seconds before the sso 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 connection 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 describe table \[schema ]table \[verbose] parameter description table a table identifier schema a qualifying schema name for the specified table if a qualifying schema is not specified 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 describe view \[schema ]view \[verbose] parameter description view a view identifier schema a qualifying schema name for the specified table if a qualifying schema is not specified 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 details about using this command, see docid\ gh07 bsvqn9kqarjdm3la syntax extract to location { local | s3 } \[ options ( \[ param=value \[ , ] ] ) ] as query get jdbc version returns the jdbc version of the driver syntax get jdbc version get schema retrieves the default name schema for tables syntax get schema kill issues a hard kill command to the virtual machine to terminate a query running on the system use only if cancel fails syntax kill uuid parameter description uuid a query 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 client servers this is the equivalent of running select from sys queries on all client servers syntax list all queries list indexes lists the indexes on the specified table and its columns syntax list indexes \[schema ]table \[verbose] parameter description table a table identifier schema a qualifying schema name for the specified table if a qualifying schema is not specified 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 list system tables \[verbose] 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 list tables \[verbose] 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 list views \[verbose] 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 output gis kml \<filename> 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 subsequent queries print out their results add append to add output to the specified file instead of overwriting it syntax output next query filename \[append] 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 performance \[ network | database | off ] 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 quit report log on enables {{log4j2}} 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 details about configuring log4j, see https //logging apache org/log4j/2 x/manual/configuration html 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 unzip p \<path to ocient jdbc4> log4j2 xml syntax report log on \[ interval ] 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 report log off select retrieves the appropriate result set of a sql select statement syntax sql select statement set maxrows sets the maximum amount of rows allowed in a result set applies only to the statement object that executed the command syntax set maxrows num of rows 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 set adjustfactor priorityadjustfactor parameter description priorityadjustfactor a percentage amount to raise or lower the query priority 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 set adjusttime priorityadjtime 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 set maxtempdisk percentage 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 set maxtime seconds 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 set parallelism parallelism 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 set priority priority 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 set schema schema 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 set serviceclass service class name 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 source filename parameter description filename a valid filename or path timing enables or disables reporting the execution time of each query syntax timing { on | off } related links docid 5vn0ryh6u3fbhbr ooaxk https //docs ocient com/system catalog