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 could 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 server 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.

  • 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
    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.