MACHSQL

MACHSQL is an interactive tool that performs SQL queries through the terminal screen.

Run Option Description

[mach@localhost]$ machsql -h


Short OptionLong OptionDescription
-s--serverConnecting server IP address (default: 127.0.0.1)
-u--userUser name (default: SYS)
-p--passwordUser password (default: MANAGER)
-P--portServer port number (default: 5656)
-n--nlsNLS configuration
-f--scriptSQL script file to run
-z--timezone=+-HHMMSet Timezone ex) +0900   -1230
-o--output

Filename to save query results

-i--silentRuns without the copyright notice
-v--verboseDetailed output
-r--format

Specifies output file format (default: csv)

-h--helpDisplays options
-cN/AAdd Connection parameter(Supported from version 6.1 or later)

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
# Supported from version 6.1 or later
machsql -s 127.0.0.1 -u sys -p manager -P 8888 -c ALTERNATIVE_SERVERS=192.168.0.147:9209;CONNECTION_TIMEOUT=10

Environment Variable MACHBASE_CONNECTION_STRING

Specifies basic connection parameters. For example, to add CONNECTION_TIMEOUT, ALTERNATIVE_SERVERS, you may use environment variable setting below.

export MACHBASE_CONNECTION_STRING=ALTERNATIVE_SERVERS=192.168.0.148:8888;CONNECTION_TIMEOUT=3

Setting connection parameter with -c option, it takes precedence over environment variables. This option is supported from version 6.1 or later

SHOW Command

Displays information such as tables, tablespaces, and indexes.
SHOW command list:

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

SHOW INDEX

Displays index information.

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.


SHOW INDEXES

Displays entire list of 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.


SHOW INDEXGAP

Displays index building GAP information.

Example:

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


SHOW LSM

Displays LSM index building information.

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.


SHOW LICENSE

Displays 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

Displays all query statements (Prepare, Execute, Fetch) registered in the server.

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 STORAGE

Displays the disk usage for each table created by the user.

Syntax:

SHOW STORAGE


Example:

Mach> CREATE TAGDATA TABLE TAG (name varchar(20) primary key, time datetime basetime, value double summarized);
Created successfully.
 
Mach> SHOW STORAGE
TABLE_NAME                                          DATA_SIZE            INDEX_SIZE           TOTAL_SIZE          
------------------------------------------------------------------------------------------------------------------------  
_TAG_DATA_0                                         50335744             0                    50335744            
_TAG_DATA_1                                         50335744             0                    50335744            
_TAG_DATA_2                                         50335744             0                    50335744            
_TAG_DATA_3                                         50335744             0                    50335744            
_TAG_META                                           0                    0                    0


SHOW TABLE

Displays information about the table created by the user.

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

Displays a list of all tables created by the user.

Example:

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


SHOW TABLESPACE

Displays tablespace information.

Example:

Mach> CREATE TABLE t1 (id integer);
Created successfully.
Mach> CREATE INDEX t1_idx_id ON t1(id);
Created successfully.

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

[INDEX]
TABLE_NAME                                COLUMN_NAME                               INDEX_NAME                       
----------------------------------------------------------------------------------------------------------------------------------
T1                                        ID                                        T1_IDX_ID                    
[1] row(s) selected.



SHOW TABLESPACES

Displays a complete list of tablespaces.

Example:

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

-- Insert data here
...
...


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


SHOW USERS

Displays a list of users.

Example:

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

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