Previous Next JavaScript must be enabled to correctly display this content

## Shell Utility Commands

The following sections describe the utility commands accessed through `"java -jar" <kvhome>/lib/sql.jar <command>"`.

The interactive prompt for the shell is:

    sql->

The shell comprises a number of commands. All commands accept the following flags:

- -help

  Displays online help for the command.

- ?

  Synonymous with -help. Displays online help for the command.

The shell commands have the following general format:

1. All commands are structured like this:

       sql-> command [arguments]

2. All arguments are specified using flags that start with "-"

3. Commands and subcommands are case-insensitive and match on partial strings(prefixes) if possible. The arguments, however, are case-sensitive.

### 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.