Defining a Table

Administrators define tables as needed to structure data. To define a table within the data grid, complete this task.

The examples in these steps illustrate adding commands to a configuration script. When the script is complete, the administration tool executes the script to define the table.

Alternatively, you can execute each step immediately as a separate administration tool command, instead of accumulating them in a script.

Note: Statistics for a table or an index must be enabled at creation time and cannot be enabled or disabled afterward. For more information, see Enabling Statistics.

Prerequisites

A realm service must be running and reachable.

Either the realm must contain a valid data grid definition, or your configuration script file must contain commands to create a valid data grid definition.

Procedure

  1. In a text editor, either begin editing a script file, or continue adding commands to an existing script.
    Follow the convention of naming your script with the .tibdg file name extension.
  2. Add a script command to create the table. For example: 
    table create table_name key_column_name key_column_type
    table create table_name key_col_1 col_1_type key_col_2 col_2_type
    Every table requires a primary key, which can consist of one or more columns. The first example creates a key with one column. The second example creates a key with two columns.
    The data type of key columns must be either long or string.
  3. Define additional columns.
    For each column in the table, add a script command to create the column. For example: 
    column create table_name column_name column_type
    Only the following FTL datatypes are valid as column types:
    • Long
    • Double
    • String
    • DateTime
    • Opaque
  4. Optional. Define secondary indexes.

    For each index in the table, add a script command to create the index. For example: 

    index create table_name index_name column_name
    index create table_name index_name column_1 column_2 column_3
    The data type of index columns must be either long or string.
  5. Run the script to create the tables in the data grid. 
    tibdg -s script_file_path

What to do next

Note: If an index is added or deleted from a table, re-indexing occurs in the background. To verify that re-indexing has been completed look for log entries such as these in the tibdgnode log file: 
2017-02-23 09:54:35.309 info ridx: reindexing of table t3 started
2017-02-23 09:54:35.640 info ridx: table t3 has been reindexed

You can repeat this task to define additional tables. After creating the table, grant users or roles permissions to read from or write to the table.

  • Column Names
    Choose column names that follow these rules for SQL identifiers.
  • Secondary Indexes
    A secondary index can increase query efficiency by reducing the number of rows to examine.
  • Enabling Statistics
    Set the row_counts attribute during table or index create to exact.
  • Row Expiration
    Rows are considered expired when they have exceeded their configured time-to-live (TTL) value. Ordinarily, rows in a table are not deleted until a client explicitly deletes them. In some situations however, the data in the rows might only remain valid or relevant for a short span of time. Leaving these rows in the table indefinitely consumes disk space and in some situations can slow down your queries. The row expiration feature is designed to remove outdated rows.