Shell Utility Commands

connect

connect -host <hostname> -port <port> -name <storeName>
         [-timeout <timeout ms>]
         [-consistency <ABSOLUTE(default) | NONE_REQUIRED>]
         [-durability <COMMIT_SYNC(default) | COMMIT_NO_SYNC | COMMIT_WRITE_NO_SYNC>]
         [-username <user>] [-security <security-file-path>]

Connects to a data store to perform data access functions. If the instance is secured, you may need to provide the log in credentials.

The following are the mandatory parameters:

-host: Identifies the host name of a node in your store.
-port <port>: The TCP/IP port on which Oracle NoSQL Database should be contacted. This port must be free (unused) on each node.
-name <storename>: Specifies the name of the store.
-security: Specifies the path to the security file in a secure deployment of the store.

For example: $KVROOT/security/user.security

The store supports the following optional parameters:

-consistency: Configures the read consistency used for this session. The read operations are serviced either on a master or a replica node depending on the configured value. For more details on consistency, see Consistency Guarantees. The following policies are supported. They are defined in the Consistency class of Java APIs.
If you do not specify this value, the default value ABSOLUTE is applied for this session.
- ABSOLUTE - The read operation is serviced on a master node. With ABSOLUTE consistency, you are guaranteed to obtain the latest updated data.
- NONE-REQUIRED - The read operation can be serviced on a replica node. This implies, that if the data is read from the replica node, it may not match what is on the master. However, eventually, it will be consistent with the master.
For more details on the policies, see Consistency in the Java Direct Driver API Reference Guide.
-durability: Configures the write durability setting used in this session. This value defines the durability policies to be applied for achieving master commit synchronization, that is, the actions performed by the master node to return with a normal status from the write operations. For more details on durability, see Durability Guarantees.
If you do not specify this value, the default value COMMIT_SYNC is applied for this session.
- COMMIT_NO_SYNC - The data is written to the host's in-memory cache, but the master node does not wait for the data to be written to the file system's data buffers or subsequent physical storage.
- COMMIT_SYNC - The data is written to the in-memory cache, transferred to the file system's data buffers, and then synchronized to a stable storage before the write operation completes normally.
- COMMIT_WRITE_NO_SYNC - The data is written to the in-memory cache, and transferred to the file system's data buffers, but not necessarily into physical storage.
For more details on the policies, see Durability in the Java Direct Driver API Reference Guide.
-timeout: Specifies the store request timeout in milliseconds.
-username: Specifies a username to log on as in a secure deployment.

consistency

consistency [[ABSOLUTE(default) | NONE_REQUIRED] [-time -permissible-lag <time_ms> -timeout <time_ms>]]

Configures the read consistency used for this session.

The read operations are serviced either on a master or a replica node depending on the configured value. For more details on consistency, see Consistency Guarantees. The following policies are supported. They are defined in the Consistency class of Java APIs.

If you do not specify this value, the default value ABSOLUTE is applied for this session.

ABSOLUTE - The read operation is serviced on a master node. With ABSOLUTE consistency, you are guaranteed to obtain the latest updated data.
NONE-REQUIRED - The read operation can be serviced on a replica node. This implies, that if the data is read from the replica node, it may not match what is on the master. However, eventually, it will be consistent with the master.

For more details on the policies, see Consistency in the Java Direct Driver API Reference Guide.

Other non-mandatory parameter includes:

-time: Indicates the use of time-based options for tuning the consistency. The following parameters are supported:

-permissible-lag: Sets the maximum allowable delay between the replica node and master. Enter the value in milliseconds.
-timeout: Configures the request timeout used for this session. The default value is 5000ms.

For example, you can define the consistency as follows:

sql-> consistency ABSOLUTE -time -permissible-lag 1000 -timeout 5000;

The following sample output confirms the applied read policy:

Read consistency policy: Consistency.Time[permissibleLag_ms=1000, timeout_ms=5000]

describe


describe | desc [as json] 
 {table table_name [field_name[,...] ] |
 index index_name on table_name
 }

Describes information about a table or index, optionally in JSON format.

Specify a fully-qualified table_name as follows:

Entry specification	Description
`table_name`	Required. Specifies the full table name. Without further qualification, this entry indicates a table created in the default namespace (sysdefault), which you do not have to specify.
`parent-table.child-table`	Specifies a child table of a parent. Specify the parent table followed by a period (.) before the child name. For example, if the parent table is `Users`, specify the child table named `MailingAddress` as `Users.MailingAddress`.
`namespace-name:table-name`	Specifies a table created in the non-default namespace. Use the namespace followed by a colon (:). For example, to reference table `Users`, created in the `Sales` namespace, enter table_name as `Sales:Users`.

Following is the output of describe for table ns1:t1:

sql-> describe table ns1:t1;
 === Information ===
 +-----------+------+-----+-------+----------+----------+--------+----------+---------+-------------+
 | namespace | name | ttl | owner | sysTable | r2compat | parent | children | indexes | description |
 +-----------+------+-----+-------+----------+----------+--------+----------+---------+-------------+
 | ns1       | t1   |     |       | N        | N        |        |          |         |             |
 +-----------+------+-----+-------+----------+----------+--------+----------+---------+-------------+

 === Fields ===
 +----+------+---------+----------+-----------+----------+------------+----------+
 | id | name |  type   | nullable |  default  | shardKey | primaryKey | identity |
 +----+------+---------+----------+-----------+----------+------------+----------+
 |  1 | id   | Integer | N        | NullValue | Y        | Y          |          |
 +----+------+---------+----------+-----------+----------+------------+----------+
 |  2 | name | String  | Y        | NullValue |          |            |          |
 +----+------+---------+----------+-----------+----------+------------+----------+

sql->

This example shows using describe as json for the same table:

sql-> describe as json table ns1:t1;
{
  "json_version" : 1,
  "type" : "table",
  "name" : "t1",
  "namespace" : "ns1",
  "shardKey" : [ "id" ],
  "primaryKey" : [ "id" ],
  "fields" : [ {
    "name" : "id",
    "type" : "INTEGER",
    "nullable" : false,
    "default" : null
  }, {
    "name" : "name",
    "type" : "STRING",
    "nullable" : true,
    "default" : null
  } ]
}

durability

durability [[COMMIT_WRITE_NO_SYNC | COMMIT_SYNC |
COMMIT_NO_SYNC] | [-master-sync <sync-policy> -replica-sync <sync-policy>
-replica-ask <ack-policy>]] <sync-policy>: SYNC, NO_SYNC, WRITE_NO_SYNC
<ack-policy>: ALL, NONE, SIMPLE_MAJORITY

Configures the write durability used for this session.

exit

exit | quit

Exits the interactive command shell.

help

help [command]

Displays help message for all shell commands and sql command.

history

history [-last <n>] [-from <n>] [-to <n>]

Displays command history. By default all history is displayed. Optional flags are used to choose ranges for display.

import

import -table table_name -file file_name [JSON | CSV]

Imports records from the specified file into table table_name.

Specify a fully-qualified table_name as follows:

Entry specification	Description
`table_name`	Required. Specifies the full table name. Without further qualification, this entry indicates a table created in the default namespace (sysdefault), which you do not have to specify.
`parent-table.child-table`	Specifies a child table of a parent. Specify the parent table followed by a period (.) before the child name. For example, if the parent table is `Users`, specify the child table named `MailingAddress` as `Users.MailingAddress`.
`namespace-name:table-name`	Specifies a table created in the non-default namespace. Use the namespace followed by a colon (:). For example, to reference table `Users`, created in the `Sales` namespace, enter table_name as `Sales:Users`.

Use -table to specify the name of a table into which the records are loaded. The alternative way to specify the table is to add the table specification "Table: table_name" before its records in the file.

For example, this file contains the records to insert into two tables, users and email:

Table: users
<records of users>
...
Table: emails
<record of emails>
...

The imported records can be either in JSON or CSV format. If you do not specify the format, JSON is assumed.

load

load -file <path to file>

Load the named file and interpret its contents as a script of commands to be executed. If any command in the script fails execution will end.

For example, suppose the following commands are collected in the script file test.sql:

### Begin Script ###
load -file test.ddl
import -table users -file users.json
### End Script ###

Where the file test.ddl would contain content like this:

DROP TABLE IF EXISTS users;
CREATE TABLE users(id INTEGER, firstname STRING, lastname STRING,
age INTEGER, primary key (id));

And the file users.json would contain content like this:

{"id":1,"firstname":"Dean","lastname":"Morrison","age":51}
{"id":2,"firstname":"Idona","lastname":"Roman","age":36}
{"id":3,"firstname":"Bruno","lastname":"Nunez","age":49}

Then, the script can be run by using the load command in the shell:

> java -jar KVHOME/lib/sql.jar -helper-hosts node01:5000 \
-store kvstore
sql-> load -file ./test.sql
Statement completed successfully.
Statement completed successfully.
Loaded 3 rows to users.

mode

mode [COLUMN | LINE | JSON [-pretty] | CSV]

Sets the output mode of query results. The default value is JSON.

For example, a table shown in COLUMN mode:

sql-> mode column;
sql-> SELECT * from users;
 +-----+-----------+-----------+-----+
 | id  | firstname | lastname  | age |
 +-----+-----------+-----------+-----+
 |   8 | Len       | Aguirre   |  42 |
 |  10 | Montana   | Maldonado |  40 |
 |  24 | Chandler  | Oneal     |  25 |
 |  30 | Pascale   | Mcdonald  |  35 |
 |  34 | Xanthus   | Jensen    |  55 |
 |  35 | Ursula    | Dudley    |  32 |
 |  39 | Alan      | Chang     |  40 |
 |   6 | Lionel    | Church    |  30 |
 |  25 | Alyssa    | Guerrero  |  43 |
 |  33 | Gannon    | Bray      |  24 |
 |  48 | Ramona    | Bass      |  43 |
 |  76 | Maxwell   | Mcleod    |  26 |
 |  82 | Regina    | Tillman   |  58 |
 |  96 | Iola      | Herring   |  31 |
 | 100 | Keane     | Sherman   |  23 |
 +-----+-----------+-----------+-----+
 ...

100 rows returned

Empty strings are displayed as an empty cell.

sql-> mode column;
sql-> SELECT * from tab1 where id = 1;
 +----+------+----+------+
 | id |  s1  | s2 |  s3  |
 +----+------+----+------+
 |  1 | NULL |    | NULL |
 +----+------+----+------+

1 row returned

For nested tables, identation is used to indicate the nesting under column mode:

sql-> SELECT * from nested;
+----+-------+------------------------------------------------------------+
| id | name  |                           details                          |
+----+-------+------------------------------------------------------------+
|  1 | one   | address                                                    |
|    |       |     city    | Waitakere                                    |
|    |       |     country | French Guiana                                |
|    |       |     zipcode | 7229                                         |
|    |       | attributes                                                 |
|    |       |     color   | blue                                         |
|    |       |     price   | expensive                                    |
|    |       |     size    | large                                        |
|    |       | phone       | [(08)2435-0742, (09)8083-8862, (08)0742-2526]|
+----+-------+------------------------------------------------------------+
|  3 | three | address                                                    |
|    |       |     city    | Viddalba                                     |
|    |       |     country | Bhutan                                       |
|    |       |     zipcode | 280071                                       |
|    |       | attributes                                                 |
|    |       |     color   | blue                                         |
|    |       |     price   | cheap                                        |
|    |       |     size    | small                                        |
|    |       | phone       | [(08)5361-2051, (03)5502-9721, (09)7962-8693]|
+----+-------+------------------------------------------------------------+
...

For example, a table shown in LINE mode, where the result is displayed vertically and one value is shown per line:

sql-> mode line;
sql-> SELECT * from users;

 > Row 1
 +-----------+-----------+
 | id        | 8         |
 | firstname | Len       |
 | lastname  | Aguirre   |
 | age       | 42        |
 +-----------+-----------+

  > Row 2
 +-----------+-----------+
 | id        | 10        |
 | firstname | Montana   |
 | lastname  | Maldonado |
 | age       | 40        |
 +-----------+-----------+

  > Row 3
 +-----------+-----------+
 | id        | 24        |
 | firstname | Chandler  |
 | lastname  | Oneal     |
 | age       | 25        |
 +-----------+-----------+
 ...
100 rows returned

Just as in COLUMN mode, empty strings are displayed as an empty cell:

sql-> mode line;
sql-> SELECT * from tab1 where id = 1;

 > Row 1
 +---------+------+
 | id      | 1    |
 | s1      | NULL |
 | s2      |      |
 | s3      | NULL |
 +---------+------+

1 row returned

For example, a table shown in JSON mode:

sql-> mode json;
sql-> SELECT * from users;
{"id":8,"firstname":"Len","lastname":"Aguirre","age":42}
{"id":10,"firstname":"Montana","lastname":"Maldonado","age":40}
{"id":24,"firstname":"Chandler","lastname":"Oneal","age":25}
{"id":30,"firstname":"Pascale","lastname":"Mcdonald","age":35}
{"id":34,"firstname":"Xanthus","lastname":"Jensen","age":55}
{"id":35,"firstname":"Ursula","lastname":"Dudley","age":32}
{"id":39,"firstname":"Alan","lastname":"Chang","age":40}
{"id":6,"firstname":"Lionel","lastname":"Church","age":30}
{"id":25,"firstname":"Alyssa","lastname":"Guerrero","age":43}
{"id":33,"firstname":"Gannon","lastname":"Bray","age":24}
{"id":48,"firstname":"Ramona","lastname":"Bass","age":43}
{"id":76,"firstname":"Maxwell","lastname":"Mcleod","age":26}
{"id":82,"firstname":"Regina","lastname":"Tillman","age":58}
{"id":96,"firstname":"Iola","lastname":"Herring","age":31}
{"id":100,"firstname":"Keane","lastname":"Sherman","age":23}
{"id":3,"firstname":"Bruno","lastname":"Nunez","age":49}
{"id":14,"firstname":"Thomas","lastname":"Wallace","age":48}
{"id":41,"firstname":"Vivien","lastname":"Hahn","age":47}
...
100 rows returned

Empty strings are displayed as "".

sql-> mode json;
sql-> SELECT * from tab1 where id = 1;
{"id":1,"s1":null,"s2":"","s3":"NULL"}

1 row returned

Finally, a table shown in CSV mode:

sql-> mode csv;
sql-> SELECT * from users;
8,Len,Aguirre,42
10,Montana,Maldonado,40
24,Chandler,Oneal,25
30,Pascale,Mcdonald,35
34,Xanthus,Jensen,55
35,Ursula,Dudley,32
39,Alan,Chang,40
6,Lionel,Church,30
25,Alyssa,Guerrero,43
33,Gannon,Bray,24
48,Ramona,Bass,43
76,Maxwell,Mcleod,26
82,Regina,Tillman,58
96,Iola,Herring,31
100,Keane,Sherman,23
3,Bruno,Nunez,49
14,Thomas,Wallace,48
41,Vivien,Hahn,47
...
100 rows returned

Like in JSON mode, empty strings are displayed as "".

sql-> mode csv;
sql-> SELECT * from tab1 where id = 1;
1,NULL,"","NULL"

1 row returned

Note:

Only rows that contain simple type values can be displayed in CSV format. Nested values are not supported.

output

output [stdout | file]

Enables or disables output of query results to a file. If no argument is specified, it shows the current output.

page

page [on | <n> | off]

Turns query output paging on or off. If specified, n is used as the page height.

If n is 0, or "on" is specified, the default page height is used. Setting n to "off" turns paging off.

show faults

show faults [-last] [-command <index>]

Encapsulates commands that display the state of the store and its components.

show ddl

show ddl <table>

The show ddl query retrieves the DDL statement for a specified table. If the table has indexes, the statement returns the DDLs for the table and the indexes.

Example : Fetch the DDL for a specified table.

The following statement fetches the DDL for the BaggageInfo table.

show ddl BaggageInfo;

Output:

CREATE TABLE IF NOT EXISTS BaggageInfo (ticketNo LONG, fullName STRING, gender STRING,
      contactPhone STRING, confNo STRING, bagInfo JSON, PRIMARY
    KEY(SHARD(ticketNo)))

In the following example, the fixedschema_contact index exists in the BaggageInfo table. The statement retrieves the DDLs for the BaggageInfo table and fixedschema_contact index on the table.

show ddl BaggageInfo;

Output:

CREATE TABLE IF NOT EXISTS BaggageInfo (ticketNo LONG, fullName STRING, gender STRING,
        contactPhone STRING, confNo STRING, bagInfo JSON, PRIMARY
      KEY(SHARD(ticketNo)))CREATE INDEX IF NOT EXISTS fixedschema_contact ON
    BaggageInfo(contactPhone)

show indexes

show_indexes_statement ::= SHOW [AS JSON] INDEXES ON table_name

The show indexes statement provides the list of indexes present on a specified table. The parameter AS JSON is optional and can be specified if you want the output to be in JSON format.

Example 1: List indexes on the specified table

The following statement lists the indexes present on the users2 table.

SHOW INDEXES ON users2;
indexes
   idx1

Example 2: List indexes on the specified table in JSON format

The following statement lists the indexes present on the users2 table in JSON format.

SHOW AS JSON INDEXES ON users2;
{"indexes" : 
     ["idx1"]
}

show namespaces

show [AS JSON] namespaces

Shows a list of all namespaces in the system.

For example:


sql-> show namespaces
namespaces
  ns1
  sysdefault
sql-> show as json namespaces
{"namespaces" : ["ns1","sysdefault"]}

show query

show query <statement>

Displays the query plan for a query.

For example:

sql-> show query SELECT * from Users;
RECV([6], 0, 1, 2, 3, 4)
[
  DistributionKind : ALL_PARTITIONS,
  Number of Registers :7,
  Number of Iterators :12,
  SFW([6], 0, 1, 2, 3, 4)
  [
    FROM:
    BASE_TABLE([5], 0, 1, 2, 3, 4)
    [Users via primary index] as $$Users

    SELECT:
    *
  ]
]

show regions

show_regions_statement ::= SHOW [AS JSON] REGIONS

The show regions statement provides the list of regions present in a multi-region Oracle NoSQL Database setup. The parameter AS JSON is optional and can be specified if you want the output to be in JSON format.

Example 1: Fetching all regions in a multi-region database setup

SHOW REGIONS;
 regions
    my_region1 (remote, active)
    my_region2 (remote, active)

Example 2: Fetching all regions in a multi-region database setup in JSON format

SHOW AS JSON REGIONS;
{"regions" : [
    {"name" : "my_region1", "type" : "remote", "state" : "active"},
    {"name" : "my_region2", "type" : "remote", "state" : "active"}
]}

show roles

show [as json] roles | role <role_name>

Shows either all the roles currently defined for the store, or the named role.

show tables

show [as json] {tables | table table_name}

Shows either all tables in the data store, or one specific table, table_name.

Specify a fully-qualified table_name as follows:

Entry specification	Description
`table_name`	Required. Specifies the full table name. Without further qualification, this entry indicates a table created in the default namespace (sysdefault), which you do not have to specify.
`parent-table.child-table`	Specifies a child table of a parent. Specify the parent table followed by a period (.) before the child name. For example, if the parent table is `Users`, specify the child table named `MailingAddress` as `Users.MailingAddress`.
`namespace-name:table-name`	Specifies a table created in the non-default namespace. Use the namespace followed by a colon (:). For example, to reference table `Users`, created in the `Sales` namespace, enter table_name as `Sales:Users`.

The following example indicates how to list all tables, or just one table. The empty tableHierarchy field indicates that table t1 was created in the default namespace:

sql-> show tables
tables
  SYS$IndexStatsLease
  SYS$PartitionStatsLease
  SYS$SGAttributesTable
  SYS$TableStatsIndex
  SYS$TableStatsPartition
  ns10:t10
  parent
  parent.child
  sg1
  t1

sql-> show table t1
tableHierarchy
  t1

To show a table created in a namespace, as shown in the list of all tables, fully-qualify table_name as follows. In this case, tableHierarchy field lists namespace ns1 in which table t1 was created. The example also shows how the table is presented as json:


sql-> show tables;
tables
  SYS$IndexStatsLease
  SYS$PartitionStatsLease
  SYS$SGAttributesTable
  SYS$TableStatsIndex
  SYS$TableStatsPartition
  ns1:foo
  ns1:t1

sql-> show table ns1:t1;
tableHierarchy(namespace ns1)
  t1
sql-> show as json table ns1:t1;
{"namespace": "ns1"
"tableHierarchy" : ["t1"]}

show users

show [as json] users | user <user_name>

Shows either all the users currently existing in the store, or the named user.

timeout

timeout [<timeout_ms>]

The timeout command configures or displays the request timeout for this session in milliseconds(ms).

The request timeout is the amount of time that the client will wait to get a response to a request that it has sent.

If the optional timeout_ms attribute is specified, then the request timeout is set to the specified value.

If the optional timeout_ms attribute is not specified, then the current value of request timeout is displayed.

Example A-1 timeout

The following example gets the current value of the request timeout.

sql-> timeout
Request timeout used: 5,000ms

Example A-2 timeout

The following example set the request timeout value to 20000 milliseconds (20 seconds).

sql-> timeout 20000
Request timeout used: 20,000ms

Note:

A shell command may require multiple requests to a server or servers. The timeout applies to each such individual request. A shell command sends out multiple requests and has to wait for each of them to return before the command is finished. As a result, a shell command may have to wait for longer time than the specified timeout and this total wait could be greater than the wait time of the individual request.

timer

timer [on | off]

Turns the measurement and display of execution time for commands on or off. If not specified, it shows the current state of timer. For example:

sql-> timer on
sql-> SELECT * from users where id <= 10 ;
 +----+-----------+-----------+-----+
 | id | firstname | lastname  | age |
 +----+-----------+-----------+-----+
 |  8 | Len       | Aguirre   |  42 |
 | 10 | Montana   | Maldonado |  40 |
 |  6 | Lionel    | Church    |  30 |
 |  3 | Bruno     | Nunez     |  49 |
 |  2 | Idona     | Roman     |  36 |
 |  4 | Cooper    | Morgan    |  39 |
 |  7 | Hanae     | Chapman   |  50 |
 |  9 | Julie     | Taylor    |  38 |
 |  1 | Dean      | Morrison  |  51 |
 |  5 | Troy      | Stuart    |  30 |
 +----+-----------+-----------+-----+

10 rows returned

Time: 0sec 98ms

verbose

verbose [on | off]

Toggles or sets the global verbosity setting. This property can also be set on a per-command basis using the -verbose flag.

version

version

Display client version information.