Defining a Table Using SQL DDL Commands

Instead of using the tibdg command-line tool to define tables and index columns, you can use SQL Data Definition Language (DDL) command strings from within an application. When you pass these commands to the ExecuteUpdate API of the tibdgSession object, you can dynamically create and drop tables, and secondary table indexes, in a running data grid.

Prerequisites

  1. A realm server must be running and reachable.
  2. The realm server must contain a valid data grid definition.
  3. The tibdgadmind process must be running.
  4. The data grid processes must be running (such as state keeper, nodes, and proxies).
  • Creating a New Table
    Use the SQL DDL command CREATE TABLE to create a new table in the data grid.
  • Dropping a Table
    Use the SQL command DROP TABLE to remove a table and the data from the data grid.
  • Creating an Index
    Use the SQL command CREATE INDEX to create a secondary index for a table in the data grid.
  • Dropping an Index
    Use the SQL command DROP INDEX to remove a secondary index from a table in the data grid.
  • SQL Data Type Mapping
    ActiveSpaces uses a small set of data types for storing data in the data grid. Several SQL data types have been mapped to each of the ActiveSpaces data types. The following table lists each data type and the SQL data types that have been mapped to it:
  • Date and Time Functions
    ActiveSpaces provides support for several date and time functions which can be used in queries.
  • SQL LIKE Operator
    The LIKE operator is used in a WHERE clause to search for a specified pattern in a column's value.
  • SQL Aggregate Functions
    TIBCO ActiveSpaces supports the use of aggregate functions as output columns in queries.