Center

Tools

AMI Center has several tools to help developers keep track of the various center objects such as tables, indexes triggers, etc.

These tools are actionable commands that can be run from the AMIDB Shell Tool. They are:

• SHOW: Returns information on a group of objects e.g. SHOW TABLES
• DESCRIBE: Returns the definition of a given object
• DIAGNOSE: Returns the memory usage of a given table
• DISABLE and ENABLE: Tools for turning timers & triggers on/off
• SETLOCAL: Sets local variables for a given session, e.g: returned table limits

For a full breakdown of each center tool, see the list below.

SHOW

Overview

Provides a table listing all records and relevant Metadata of a particular type with optional filtering and sorting.

The general format for a SHOW query is as follows:

SHOW [FULL] object_type [WHERE where_expr ] [ORDER BY expr [ASC|DESC] [, expr [ASC|DESC] ...]]

Expressions contained in square brackets ([]) are optional. Script tool commands are case insensitive, however object names need to exactly match.

The full list of valid object_type entries is listed in the table below.

Object Type	Additional Argument(s)	Description	Examples
TABLES	FROM center_name	Show all tables Optionally from a specified center	SHOW TABLES, SHOW TABLES FROM my_center
TABLE table_name	FROM center_name	Show a specific table	SHOW TABLE accounts, SHOW TABLE accounts FROM my_center
COLUMNS	FROM center_name	Show all columns Optionally from a specified center	SHOW COLUMNS, SHOW COLUMNS FROM my_center
COMMANDS		Show all user-registered commands that have been defined in AMI	SHOW COMMANDS
CONNECTIONS		Show all active connections to the center	SHOW CONNECTIONS
DATASOURCES		Show the full list of datasources available to the center	SHOW DATASOURCES
DATASOURCE_TYPES		Show the full list of accepted datasource types for the center	SHOW DATASOURCES_TYPES
DBO dbo_name	FULL	Show the information of the specified DBO Optionally supply the keyword FULL to retrieve the full name (may cause text wrapping in console)	SHOW FULL DBO my_dbo, SHOW DBO my_dbo
DBO_PLUGIN dbo_type		Show the list of DBOs of the specified DBO plugin type	SHOW DBO_PLUGIN simple_timer
DBOS		Show the full list of DBOs	SHOW FULL DBOs
INDEXES	FROM center_name	Show all indexes Optionally from a specified center	SHOW INDEXES, SHOW INDEXES FROM my_center
METHODS		Show all methods	SHOW METHODS
METHOD method_name(method input)		Show details about the supplied method	SHOW METHOD abs(Number), SHOW METHOD binaryToStr(Binary)
PLUGINS		Show all plugins	SHOW PLUGINS
PLUGIN_OPTIONS		Show all the options for all plugins	SHOW PLUGIN_OPTIONS
PROCEDURES		Show all procedures AMI native procedures are prefixed with underbars (__)	SHOW PROCEDURES
PROCESSES		Show all processes	SHOW PROCESSES
PROPERTIES		Show all properties	SHOW PROPERTIES
RELAYS		Show all relays connected to the center	SHOW RELAYS
REPLICATIONS		Show all replications/replicated centers	SHOW REPLICATIONS
REPLICATION_SOURCES		Show all replication sources I.e, which center(s) have been replicated	SHOW REPLICATION_SOURCES
RESOURCES		Show all user-uploaded resources Corresponds to files in amione/resources	SHOW RESOURCES
SESSIONS		Show all active sessions connected to the center	SHOW SESSIONS
TIMERS	FULL	Show all timers Optionally supply the keyword FULL to retrieve the full name (may cause text wrapping in console)	SHOW FULL TIMERS, SHOW TIMERS
TRIGGERS	FULL	Show all triggers Optionally supply the keyword FULL to retrieve the full name (may cause text wrapping in console)	SHOW FULL TRIGGERS, SHOW TRIGGERS
VARS		Show all session variables This will also include DBOs	SHOW VARS

Note

For tables, only PUBLIC tables are listed.

Examples

Example 1

This returns all tables whose name contains "data" and will order the results by the number of columns in the table:

SHOW TABLES WHERE TableName =~ "data" ORDER BY ColumnsCount

Example 2

This returns all columns for the myorders table, alphabetized by name:

SHOW TABLE myorders order by ColumnName

SHOW FULL

Using the FULL keyword will return the fully rendered text output of a given object's name instead of the first few characters.

This can be useful if you need the full text output, however it can result in text-wrapping in the console output which may be difficult to read.

While FULL can apply to any center object, it is most relevant for the following:

• Timers
• Triggers
• DBO

Center Replication

You can also show tables from other connected centers (via center replication). To see what centers you have connected, in AMIDB run: SHOW CENTERS. Then:

SHOW TABLE some_table FROM connected_center

DESCRIBE

Overview

Returns a table with a column titled "SQL" containing the AMI SQL statements necessary to reconstruct the supplied center object.

The full list of describable objects is listed here:

DESCRIBE DBO dbo_name
DESCRIBE INDEX index_name ON table_name
DESCRIBE PROCEDURE procedure_name
DESCRIBE TABLE table_name
DESCRIBE TIMER timer_name
DESCRIBE TRIGGER trigger_name

Example

This example will return the full AMI SQL CREATE statement that can be used to create the __CENTER table and its primary key index.

DESCRIBE TABLE __CENTER;

This returns the following AMI SQL:

CREATE PUBLIC TABLE __CENTER(CenterName String,Url String,CertFile String,Password String) USE PersistEngine="TEXT" RefreshPeriodMs="250" OnUndefColumn="REJECT" InitialCapacity="100";
CREATE INDEX pk ON __CENTER(CenterName SORT) USE Constraint="UNIQUE";

Note

AMI system tables like __CENTER and __COLUMN have unique indexes associated to them. For more information on how indexes are implemented and used in AMI, see here.

Center Replication

You can also describe tables and other objects from other connected centers (via center replication).

On a given center, run the DESCRIBE command:

DESCRIBE TABLE some_table FROM connected_center

This will return the AMI SQL query to create the replicated table in the current center.

DIAGNOSE

Overview

Returns a table providing details on the approximate memory footprint of tables, columns and indexes.

DIAGNOSE TABLE table_name [,table_name ...]
DIAGNOSE COLUMN column_name on table_name [,column_name on table_name ...]
DIAGNOSE INDEX index_name ON table_name [,index_name on table_name ...]

Example

This example will return the memory used by the __CENTER table:

DIAGNOSE TABLE __CENTER;

The output will be a table similar to the following:

Table String	TYPE String	NAME String	EST_MEMORY Long	COUNT Integer	CARDINALITY Integer	COMMENT String
__CENTER	COLUMN	CenterName	0	0	0	8-BYTE ADDRESSED JAVA OBJECT (String)
__CENTER	COLUMN	Url	0	0	0	8-BYTE ADDRESSED JAVA OBJECT (String)
__CENTER	COLUMN	CertFile	0	0	0	8-BYTE ADDRESSED JAVA OBJECT (String)
__CENTER	COLUMN	Password	0	0	0	8-BYTE ADDRESSED JAVA OBJECT (String)
__CENTER	TABLE_OVERHEAD	null	1048704	0	null	COLUMNAR STORAGE ENGINE
__CENTER	INDEX	pk	292	0	0	1 TREEMAP

DISABLE and ENABLE

Overview

Used to disable/enable triggers and timers by name. Disabled triggers and timers will not get executed when they otherwise would.

By default, when triggers and timers are created, they are enabled. Check the enabled/disabled status using SHOW TRIGGERS and SHOW TIMERS.

DISABLE TRIGGER trigger_name
ENABLE TRIGGER trigger_name

DISABLE TIMER trigger_name
ENABLE TIMER trigger_name

Example

This example will disable the trigger named my_trigger and enable the timer my_timer:

DISABLE TRIGGER my_trigger;
ENABLE TIMER my_timer;

SETLOCAL

Overview

Controls local variables for the command line session. These local variables dictate behavior of the command line interface.

Run SETLOCAL to see the list of variables that can be changed.

Note

This command cannot be run from the AMIDB Shell Tool.

SETLOCAL varname = value

Variables

Variables	Description
limit	The default limit to apply to queries (similar to the limit field in the datamodel), -1 means no default limit
max_print_chars	Total number of characters to print to the console for a query result. This prevents from very large query results overwhelming terminal
multiline	If set to "off" then pressing enter will automatically execute the user entered. If set to "on" then pressing enter will progress to a new line for additional input. Pressing enter twice in succession will execute the users entered text.
show_plan	If set to "off" the engine will not show the query plan, this is more efficient as some overhead is necessary to gather and display this importation. If set to "on" then the engine will produce verbose output on the steps and time taking for each step in the query. This can be useful for optimizing your queries.
timeout	Amount of time in milliseconds that the console will hang waiting for a response. It must be a positive number
unprintable_chars	Determines how to print unprintable ASCII characters, for example 0x01 (SOH). If set to "marker" then an upside down question mark (¿) is printed in place of any unprintable chars (default). If set to "show" the raw, unprintable, is sent to stdout. If set to "hide" then nothing is printed. If set to "show_code" then an upside down question mark (¿) is printed followed by the chars 4 digit hexcode is printed. For example, the SOH would be printed as: ¿0001
datetime_format	The format to use when printing UTC and UTCN columns. If set to empty string, the Raw long value (unix epoch) is printed. The default is "YYYY-MM-DD HH:mm:ss.SSS.z"
timezone	Works in conjunction with datetime_format, to determine local time for display. For example EST5EDT would be used for New York. Default is UTC
string_template	Should commands interpret string templates (default is off). If set to "on" then ${...} will be evaluated and replaced with the contents. If set to "off" then ${...} will be treated as a literal.
password_encrypt	If set to "on" then login command should be supplied with an encrypted password. The password will be decrypted using the key stored on the AMI server. Note, use strEncrypt(...) method to get the encrypted version of a plain text password. Ex: select strEncrypt("demopass");. If set to "off" then login command should be supplied with plain text password.

Example

setlocal timeout=30000