1. SQL Beginner's Guide
  2. Introduction to the SQL for Oracle NoSQL Database Shell
  3. Shell Utility Commands

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 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 mrtable-agent-statistics

show mrtable-agent-statistics [-agent <agentID>][-table <tableName>][-json]

Shows the latest statistics as of the last one minute for multi-region table agents. With no arguments, this command shows combined statistics over all regions the MR Table spans.

Input Parameters

Optionally, you can enable the following flags with appropriate parameters with this command:

Table A-1 Input Parameters

Flag Parameter Description
- agent agentID Limits the statistics to the agent ID specified. You can find the agent ID from the JSON config file created while configuring your agent. See Configure XRegion Service.
- table tableName Limits the statistics to the MR Table specified.
- json - Returns the complete statistics in JSON format. Even though the statistics are returned in JSON format by default, specifying this flag adds additional information in the output such as operation, return code, and the return code's description.

Output Statistics

The statistics reported by the show mrtable-agent-statistics can be categorized as those used to:
  • Monitor streams from other regions

    Table A-2 Output Statistics 1

    Statistic Description
    completeWriteOps Number of complete write operations per region.
    lastMessageMs

    Timestamp when the agent sees the last message from a remote region, in milliseconds.

    If this statistic information is not available, -1 is printed as its output value.
    lastModificationMs

    Timestamp of the last operation performed in each remote region, in milliseconds.

    If this statistic information is not available, -1 is printed as its output value.
    laggingMs (avg, max, min)

    In a multi-region KVStore, each shard in a region pushes all the operations performed on all its tables to the agent's queue. The agent replicates the contents of its queue, in event order, to all other regions. The lagging statistic represents the time difference between an event being pushed into the queue and replicated to the other regions by the agent. If this value is high, it indicates that the queue is getting backed up. A smaller value indicates that the agent is able to keep up with the number of events coming from remote regions. The lagging statistics are reported as average, minimum, and maximum in milliseconds for each remote region.

    If this statistic information is not available, -1 is printed as its output value.
    latencyMs (avg, max, min) In MR tables, the latency statistic indicates the time taken in milliseconds for each operation to travel from its origin (remote) region to the target (local) region.
    The latency is computed as T2 - T1, where:
    • T1 is the timestamp when an operation is performed in the remote region, and
    • T2 is the timestamp when the agent persisted the replicated operation to the local region.
    For each remote region, the latency statistics are reported as the average, minimum, and maximum latency for all the operations from that region.

    If this statistic information is not available, -1 is printed as its output value.
  • Check the persistence of remote data

    Table A-3 Output Statistics 2

    Statistic Description
    puts Number of write operations received.
    dels Number of delete operations received.
    streamBytes Total bytes replicated from a remote region.
    persistStreamBytes Reports the total number of bytes that are successfully committed in the local region. This is different from the total bytes replicated from a remote region because in case of any conflicts with operations from other regions, some of the operations may not persist if they fail the built-in conflict resolution rule.
    winPuts Number of write operations performed successfully. More specifically, this statistic excludes the writes that failed to win the conflict resolution rule, in case of a conflict with writes in other regions.
    winDels Number of delete operations performed successfully. More specifically, this statistic excludes the deletes that failed to win the conflict resolution rule, in case of a conflict with deletes in other regions.
    incompatibleRows Number of operations that did not persist because of incompatible table schemas. This can happen when there is a schema mismatch between the origin region and the region that is trying to replicate the row to its local data store.
  • Monitor the interaction between admin and the agent

    Table A-4 Output Statistics 3

    Statistic Description
    requests All the DDL commands executed by the user on an MR table are converted into requests to the agent by the admin. This statistic reports the number of requests posted by the admin.
    responses Number of requests processed and responded by the agent.
  • Monitor multi-region tables
    When you execute the show mrtable-agent-statistics command with the -table flag, it returns the table level statistics indicating:
    1. Persistence of remote data in the local region: This includes the statistics such as puts, dels, winPuts, winDels, streamBytes, persistStreamBytes, and incompatibleRows discussed above.
    2. Progress of table initialization in each remote region: This is indicated by the state attribute under the Initialization statistics in the output. The table below lists the different possible values for state and their meaning.

      Table A-5 Table Initialization States

      State Description
      NOT_START MR table initialization has not started, or there is no need to do initialization. For example, if the agent resumes the stream from an existing checkpoint successfully, there is no need to re-initialize the MR table.
      IN_PROGRESS MR table initialization is ongoing, that is, the MR table initialization has started and the data is being replicated from the remote regions.
      COMPLETE MR table initialization is complete and table transfer is done. The agent is streaming from the remote region.
      ERROR MR table initialization cannot complete because of an irrecoverable error. You can view the error severity in the agent log as WARNING or SEVERE. The agent log can be found in the directory specified in the JSON config file. See Configure XRegion Service.
      SHUTDOWN MR table initialization cannot complete because the service is shut down.
    3. Persistence of the table data per remote region:

      Table A-6 Output Statistics 4

      Statistic Description
      transferStartMs

      Timestamp of the initiation of table initialization, in milliseconds.

      If this statistic information is not available, -1 is printed as its output value.
      transferCompleteMs

      Timestamp of the completion of table initialization, in milliseconds.

      If this statistic information is not available, -1 is printed as its output value.
      elapsedMs The time elapsed from the start of the table initialization until its completion.

      elapsedMs = transferCompleteMs - transferStartMs

      This statistic is reported in milliseconds. Before the transfer completion, it reports -1 indicating the unavailability of this statistic.

      transferBytes Number of bytes transferred from the remote (origin or source) region to the local (target) region.
      transferRows Number of rows transferred from the remote region to the local region successfully.
      expireRows Number of rows expired before transferring from the remote region. Because of their TTL value, some rows might expire during the replication. Such rows expire by the time they reach the agent. This statistic counts such expired rows.
      persistBytes Reports the total number of bytes that are successfully committed in the local region. This excludes the rows that are not committed in the local region because they failed the built-in conflict resolution rule. In case of row updates, the entire row is counted for this statistic.
      persistRows Reports the total number of rows that are successfully committed in the local region. Similar to the above statistic, the rows that are not committed in the local region because of the built-in conflict resolution rule are excluded for this count.

Example

Below are a few examples of the statistics returned by the show mrtable-agent-statistics command with different input parameters.

Note:

If any of the statistics information is not available, -1 is reported as the value for that statistic parameter in the output. 
# MR table agent statistics for a specific agent 
kv-> show mrtable-agent-statistics -agent 0 -json
{
  "operation": "show mrtable-agent-statistics",
  "returnCode": 5000,
  "description": "Operation ends successfully",
  "returnValue": {
    "XRegionService-1_0": {
      "timestamp": 1592901180001,
      "statistics": {
        "agentId": "XRegionService-1_0",
        "beginMs": 1592901120001,
        "dels": 1024,
        "endMs": 1592901180001,
        "incompatibleRows": 100,
        "intervalMs": 60000,
        "localRegion": "slc1",
        "persistStreamBytes": 524288,
        "puts": 2048,
        "regionStat": {
          "lnd": {
            "completeWriteOps": 10,
            "laggingMs": {
              "avg": 512,
              "max": 998,
              "min": 31
            },
            "lastMessageMs": 1591594977587,
            "lastModificationMs": 1591594941686,
            "latencyMs": {
              "avg": 20,
              "max": 40,
              "min": 10
            }
          },
          "dub": {
            "completeWriteOps": 20,
            "laggingMs": {
              "avg": 535,
              "max": 1024,
              "min": 45
            },
            "lastMessageMs": 1591594978254,
            "lastModificationMs": 1591594956786,
            "latencyMs": {
              "avg": 30,
              "max": 45,
              "min": 15
            }
          }
        },
        "requests": 12,
        "responses": 12,
        "streamBytes": 1048576,
        "winDels": 1024,
        "winPuts": 2048
      }
    }
  }
}
# MR table agent statistics for a specific MR table 
kv-> show mrtable-agent-statistics -table users -json
{
  "operation": "show mrtable-agent-statistics",
  "returnCode": 5000,
  "description": "Operation ends successfully",
  "returnValue": {
    "XRegionService-1_0": {
      "tableID": 12,
      "tableName": "users",
      "timestamp": 1592901300001,
      "statistics": {
        "agentId": "XRegionService-1_0",
        "beginMs": 1592901240001,
        "dels": 1000,
        "endMs": 1592901300001,
        "expiredPuts": 200,
        "incompatibleRows": 100,
        "initialization": {
          "lnd": {
            "elapsedMs": 476,
            "expireRows": 100,
            "persistBytes": 6492160,
            "persistRows": 6340,
            "state": "COMPLETE",
            "transferBytes": 8115200,
            "transferCompleteMs": 1592822625333,
            "transferRows": 7925,
            "transferStartMs": 1592822624857
          },
          "dub": {
            "transferStartMs": 0,
            "transferCompleteMs": 0,
            "elapsedMs": -1,
            "transferRows": 0,
            "persistRows": 0,
            "expireRows": 0,
            "transferBytes": 0,
            "persistBytes": 0,
            "state": "NOT_START"
          }
        },
        "intervalMs": 60000,
        "localRegion": "fra",
        "persistStreamBytes": 104960000,
        "puts": 100000,
        "streamBytes": 115200000,
        "tableId": 12,
        "tableName": "users",
        "winDels": 745,
        "winPuts": 90000
      }
    }
  }
}

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.