MACHSQL

MACHSQL is a tool to dynamically execute SQL queries against Machbase server in the console, and it also allows you to check the results on the screen.

Options and Features

[mach@localhost]$ machsql -h

Table 1. MACHSQL options and description

Short option Long option Description
-s --server It specifies the IP address of the server where machbased is running. (e.g. localhost)
-u --user It specifies the user ID to login machbased. (e.g. SYS)
-p --password It specifies the user's password to login machbased. (e.g. MANAGER)
-P --port It specifies the port number to access machbased. The default value is 5656, and it is set in machbase.conf file.
-n --nls It specifies the NLS.
-f --script It designates the external file name to execute.
-o --output It specifies the external file name to store the execution results. It is stored as a tab delimiter.
-i --silent It doesn't display copyright statements.
-v --verbose Run in Verbose mode.
-t --testing Run in Testing mode.
-r --format It specifies teh format of the file to be output. (e.g. csv))
-h --help Display help message.

Example:

machsql -s localhost -u sys -p manager
machsql --server=localhost --user=sys --password=manager
machsql -s localhost -u sys -p manager -f script.sql

SHOW Commands

The commands display information about index, tablespace, and other servers.

List of show options

  • SHOW INDEX
  • SHOW INDEXES
  • SHOW INDEXGAP
  • SHOW LSM
  • SHOW LICENSE
  • SHOW STATEMENTS
  • SHOW TABLE
  • SHOW TABLES
  • SHOW TABLESPACE
  • SHOW TABLESPACES​
  • SHOW USERS

SHOW INDEX

It displays the detailed information about a specified index.

Syntax:

SHOW INDEX index_name

Example:

Mach> CREATE TABLE t1 (c1 INTEGER, c2 VARCHAR(10));
Created successfully.
Mach> CREATE VOLATILE TABLE t2 (c1 INTEGER, c2 VARCHAR(10));
Created successfully.
Mach> CREATE INDEX t1_idx1 ON t1(c1) INDEX_TYPE LSM;
Created successfully.
Mach> CREATE INDEX t1_idx2 ON t1(c1) INDEX_TYPE BITMAP;
Created successfully.
Mach> CREATE INDEX t2_idx1 ON t2(c1) INDEX_TYPE REDBLACK;
Created successfully.
Mach> CREATE INDEX t2_idx2 ON t2(c2) INDEX_TYPE REDBLACK;
Created successfully.

Mach> SHOW INDEX t1_idx2;
TABLE_NAME                                COLUMN_NAME                               INDEX_NAME                       
----------------------------------------------------------------------------------------------------------------------------------
INDEX_TYPE   BLOOM_FILTER  KEY_COMPRESS  MAX_LEVEL   PART_VALUE_COUNT BITMAP_ENCODE
--------------------------------------------------------------------------------------------
T1                                        C1                                        T1_IDX2                          
LSM          ENABLE   COMPRESSED    2           100000      EQUAL
[1] row(s) selected.
Elapsed time: 0.001

SHOW INDEXES

It displays the list of all indexes.

Syntax:

SHOW INDEXES

Example:

Mach> CREATE TABLE t1 (c1 INTEGER, c2 VARCHAR(10));
Created successfully.
Mach> CREATE VOLATILE TABLE t2 (c1 INTEGER, c2 VARCHAR(10));
Created successfully.
Mach> CREATE INDEX t1_idx1 ON t1(c1) INDEX_TYPE LSM;
Created successfully.
Mach> CREATE INDEX t1_idx2 ON t1(c1) INDEX_TYPE BITMAP;
Created successfully.
Mach> CREATE INDEX t2_idx1 ON t2(c1) INDEX_TYPE REDBLACK;
Created successfully.
Mach> CREATE INDEX t2_idx2 ON t2(c2) INDEX_TYPE REDBLACK;
Created successfully.

Mach> SHOW INDEXES;
TABLE_NAME                                COLUMN_NAME                               INDEX_NAME                       
----------------------------------------------------------------------------------------------------------------------------------
INDEX_TYPE
---------------
T1                                        C1                                        T1_IDX1                          
LSM
T1                                        C1                                        T1_IDX2                          
LSM
T2                                        C2                                        T2_IDX2                          
REDBLACK
T2                                        C1                                        T2_IDX1                          
REDBLACK
[4] row(s) selected.
Elapsed time: 0.001
Mach>
​

SHOW INDEXGAP

It displays information about index gap.

Example:

Mach> SHOW INDEXGAP
TABLE_NAME                                INDEX_NAME                                GAP
-------------------------------------------------------------------------------------------------------------
INDEX_TABLE                               T1_IDX1                                   0
INDEX_TABLE                               T1_IDX2                                   0

SHOW LSM

It displays information about LSM index.

Example:

Mach> SHOW LSM;
TABLE_NAME                                INDEX_NAME                                LEVEL       COUNT
--------------------------------------------------------------------------------------------------------------------------
T1                                        IDX1                                      0           0
T1                                        IDX1                                      1           100000
T1                                        IDX1                                      2           0
T1                                        IDX1                                      3           0
T1                                        IDX2                                      0           100000
T1                                        IDX2                                      1           0
[6] row(s) selected.
Mach>

SHOW LICENSE

It displays the license information.

Example:

Mach> SHOW LICENSE
INSTALL_DATE          ISSUE_DATE            EXPIRY_DATE  TYPE        POLICY     
---------------------------------------------------------------------------------------
2016-07-01 10:24:37   20160325              20170325    2           0          
[1] row(s) selected.

SHOW STATEMENTS

It displays SQL statements used in all sessions.

Example:

Mach> SHOW STATEMENTS
USER_ID     SESSION_ID  QUERY                                                                            
--------------------------------------------------------------------------------------------------------------
0           2           SELECT ID USER_ID, SESS_ID SESSION_ID, QUERY FROM V$STMT                         
[1] row(s) selected.

SHOW TABLE

It displays information about table info.

Syntax:

SHOW TABLE table_name

Example:

Mach> CREATE TABLE t1 (c1 INTEGER, c2 VARCHAR(10));
Created successfully.
Mach> CREATE INDEX t1_idx1 ON t1(c1) INDEX_TYPE LSM;
Created successfully.
Mach> CREATE INDEX t1_idx2 ON t1(c1) INDEX_TYPE BITMAP;
Created successfully.

Mach> SHOW TABLE T1
[ COLUMN ]
----------------------------------------------------------------
NAME                          TYPE                LENGTH
----------------------------------------------------------------
C1                            integer             11
C2                            varchar             10

[ INDEX ]
----------------------------------------------------------------
NAME                          TYPE                COLUMN
----------------------------------------------------------------
T1_IDX1                       LSM                 C1
T1_IDX2                       LSM                 C1

SHOW TABLES

It displays lists of a table.

Example:

Mach> SHOW TABLES
NAME                                     
--------------------------------------------
BONUS                                    
DEPT                                     
EMP                                      
SALGRADE                                 
[4] row(s) selected.

SHOW TABLESPACE

It displays information about tablespace.

Example:

Mach> CREATE TABLE t1 (id integer);
Created successfully.
Elapsed time: 0.071
Mach> CREATE INDEX t1_idx_id ON t1(id);
Created successfully.
Elapsed time: 0.081

Mach> SHOW TABLESPACE SYSTEM_TABLESPACE;
[TABLE]
NAME                                      TYPE
-------------------------------------------------------
T1                                        LOG
[1] row(s) selected.
Elapsed time: 0.001

[INDEX]
TABLE_NAME                                COLUMN_NAME                               INDEX_NAME                       
----------------------------------------------------------------------------------------------------------------------------------
T1                                        ID                                        T1_IDX_ID                    
[1] row(s) selected.
Elapsed time: 0.000
Mach>

SHOW TABLESPACES

It displays information about tablespaces.

Example:

Mach> CREATE TABLESPACE tbs1 DATADISK disk1 (DISK_PATH="tbs1_disk1"), disk2 (DISK_PATH="tbs1_disk2"), disk3 (DISK_PATH="tbs1_disk3");
Created successfully.
Mach>

Insert some records...
...
...


Mach> SHOW TABLESPACES;
NAME                                                                              DISK_COUNT  USAGE
-----------------------------------------------------------------------------------------------------------------------
SYSTEM_TABLESPACE                                                                 1           0
TBS1                                                                              3           25824256
[2] row(s) selected.

SHOW USERS

It displays names of registered users.

Example:

Mach> CREATE USER testuser IDENTIFIED BY 'test1234';
Created successfully.

Mach> SHOW USERS;
USER_NAME                                
--------------------------------------------
SYS                                      
TESTUSER
[2] row(s) selected.

results matching ""

    No results matching ""