Shell Utility Commands

connect

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

Connects to a KVStore to perform data access functions. If the instance is secured, you may need to provide login credentials.

consistency

consistency [[NONE_REQUIRED | NONE_REQUIRED_NO_MASTER |
ABSOLUTE] [-time -permissible-lag <time_ms> -timeout <time_ms>]]

Configures the read consistency used for this session.

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.