System Tables

LeanXcale’s query engine provides a series of virtual tables giving access to the metadata of stored databases, also known as data dictionaries in other databases.

All the system tables are part of a virtual schema called LXSYS. Each of the following subsections is devoted to each of the tables in the system.

1. LXSYS.TABLES

It shows all the tables. Initially, only metadata tables are shown. The columns of the table are as follows:

tableCat: Database on which the table resides.
tableSchem: Schema on which the table resides.
tableName: Name of the table.
owner: User owner of the table.
tableType: It is the kind of table. It can have the following values:
- SYSTEM TABLE.
- TABLE.
- SEQUENCE.
- VIEW.
comment: a comment made on the table.

Note that indexes do not appear in this list because they are not tables by themselves.

lx% select * from lxsys.tables;

tableCat    tableSchem    tableName                   owner      tableType     comment

  db          LXSYS         AGGREGATIONS                lxadmin    SYSTEM TABLE
  db          LXSYS         ALL_CONS_COLUMNS            lxadmin    SYSTEM TABLE
  db          LXSYS         AUTO_SPLITS                 lxadmin    SYSTEM TABLE
  db          LXSYS         COLUMNS                     lxadmin    SYSTEM TABLE
  db          LXSYS         COMPONENTS                  lxadmin    SYSTEM TABLE
  db          LXSYS         CONNECTIONS                 lxadmin    SYSTEM TABLE
  db          LXSYS         DATASTORE_STATS             lxadmin    SYSTEM TABLE
  db          LXSYS         FOREIGN_KEYS                lxadmin    SYSTEM TABLE
  db          LXSYS         GROUP_PERMISSION            lxadmin    SYSTEM TABLE
  db          LXSYS         INDEX_COLUMNS               lxadmin    SYSTEM TABLE
  db          LXSYS         INDEXES                     lxadmin    SYSTEM TABLE
  db          LXSYS         ONLINE_AGGREGATE_FILTERS    lxadmin    SYSTEM TABLE
  db          LXSYS         ONLINE_AGGREGATES           lxadmin    SYSTEM TABLE
  db          LXSYS         ONLINE_AGGREGATIONS         lxadmin    SYSTEM TABLE
  db          LXSYS         PLANS_CACHE                 lxadmin    SYSTEM TABLE
  db          LXSYS         PRIMARY_KEYS                lxadmin    SYSTEM TABLE
  db          LXSYS         QUERY_CACHE                 lxadmin    SYSTEM TABLE
  db          LXSYS         TABLE_CHECKS                lxadmin    SYSTEM TABLE
  db          LXSYS         TABLE_FUNCTIONS             lxadmin    SYSTEM TABLE
  db          LXSYS         TABLE_PARTITIONS            lxadmin    SYSTEM TABLE
  db          LXSYS         TABLE_STATS                 lxadmin    SYSTEM TABLE
  db          LXSYS         TABLES                      lxadmin    SYSTEM TABLE
  db          LXSYS         TRANSACTIONS                lxadmin    SYSTEM TABLE
  db          LXSYS         TRIGGER_FUNCTIONS           lxadmin    SYSTEM TABLE
  db          LXSYS         TRIGGERS                    lxadmin    SYSTEM TABLE
  db          LXSYS         TYPE_INFO                   lxadmin    SYSTEM TABLE
  db          LXSYS         USER_GROUP                  lxadmin    SYSTEM TABLE
  db          LXSYS         VIEW_TABLES                 lxadmin    SYSTEM TABLE

2. LXSYS.COLUMNS

This system table shows all the columns of all tables in the system. It has the following columns:

tableCat: Database on which the table resides.
tableSchem: Schema on which the table resides.
tableName: Name of the table.
columnName: Name of the column.
dataType: Type of the column as an INTEGER. The possible values are:
- 4: INTEGER
- 12 VARCHAR
- …
typeName: It is the SQL type name. If NOT NULL constraint is set on the column is shown after the type name. E.g.: INTEGER NOT NULL.
columnSize: For VARCHAR columns it provides the max length. For DECIMAL columns it provides the length of the non decimal part. For TIMESTAMP columns it is 6. For other column types is -1.
bufferLength. Deprecated. Do not use it.
decimalDigits. For DECIMAL columns it provides the number of fractional digits.
numPrecRadix. Indicates the base of the numeric columns. It is always 10.
nullable. Deprecated. 1 if it can be NULL, 0 if it has NON NULL constraint.
remarks. Comment on the column.
columnDef. Deprecated. Do not use it.
sqlDataType. Deprecated. Do not use it.
sqlDatetimeSub. Deprecated. Do not use it.
charOctetLength. For VARCHAR columns it shows the max length in bytes.
ordinalPosition. The position of the column in the create statement. The first column has position 1.
isNullable. YES if it can be NULL, NO if it has NON NULL constraint.
scopeCatalog. Deprecated. Do not use it.
scopeSchema. Deprecated. Do not use it.
scopeTable. Deprecated. Do not use it.
sourceDataType Deprecated. Do not use it.
isAutoincrement. For non-system tables it is YES if the column is auto-incremental, NO, otherwise. For system tables is blank.
isGeneratedcolumn. For non-system tables it is YES if the column is auto-generated, NO, otherwise. For system tables is blank.

Here there is a fragment of the table contents after creating a user table T with two columns PK and C:

lx% select * from lxsys.columns;

  tableCat    tableSchem    tableName                   columnName             dataType    typeName                               columnSize    bufferLength    decimalDigits    numPrecRadix    nullable    remarks    columnDef    sqlDataType    sqlDatetimeSub    charOctetLength    ordinalPosition    isNullable    scopeCatalog    scopeSchema    scopeTable    sourceDataType    isAutoincrement    isGeneratedcolumn

  db          APP           T                           PK                     4           INTEGER                                -1                                             10              1                                                                    -1                 1                  YES                                                                          NO                 NO
  db          APP           T                           C                      12          VARCHAR(4096) CHARACTER SET "UTF-8"    4096                                           10              1                                                                    4096               2                  YES                                                                          NO                 NO
  db          LXSYS         AGGREGATIONS                name                   12          VARCHAR                                -1                                             10              1                                                                    -1                 1                  YES
...

3. LXSYS.PRIMARY_KEYS

It shows the primary keys for user created tables.

The column names of this system table are:

columnName: Name of the column.
keySeq. 1 if it is a sequence, 0 otherwise.
pkName. Same as columnName.
tableCat: Database on which the table resides.
tableSchem: Schema on which the table resides.
tableName: Name of the table.

This is the contents after creating a table t with a column pk as primary key:

lx% select * from lxsys.primary_keys;

  columnName    keySeq    pkName    tableCat    tableSchem    tableName

  PK            0         PK        db          APP           T

4. LXSYS.FOREIGN_KEYS

It contains all the declared foreign keys in the system for user created tables. The column names of this system table are:

pktableCat: Database of the table with the referenced primary key.
pktableSchem: Schema of the table with the referenced primary key.
pktableName: Table with the referenced primary key.
pkcolumnName: Column name of the referenced primary key.
fktableCat: Database of the table with the foreign key.
fktableSchem: Schema of the table with the foreign key.
fktableName: Table with the foreign key.
fkcolumnName: Column with the foreign key.
keySeq: 1 if is a sequence, 0 otherwise.
updateRule: Rule for updates on the primary key.
deleteRule: Rule for deletes on the primary key.
fkName: Name of the foreign key.
pkName: Name of the primary key.
deferrability: 1 is the foreign key check is delayed to the commit of the transaction, 0 if it is checked immediately after changing the primary key. Currently, not supported. An example of this table after creating a table with a foreign key to another table:

lx% select * from lxsys.foreign_keys;

  pktableCat    pktableSchem    pktableName    pkcolumnName    fktableCat    fktableSchem    fktableName    fkcolumnName    keySeq    updateRule    deleteRule    fkName                   pkName    deferrability

  db            APP             T1             PK              db            APP             T2             FK_TO_T1        0         3             3             FK_db-APP-T2_FK_TO_T1    [PK]      7

5. LXSYS.INDEXES

It shows all the indexes created for user created tables in the system.

The columns are:

tableCat: Database on which the table resides.
tableSchem: Schema on which the table resides.
tableName: Name of the table.
non_unique: Non unique constraint.
index_qualifier: ??
index_name: Name of the index.
type: ??
ordinal_position: ??
column_name: Name of the indexed column.
asc_or_desc: 1 if ascending ordered, and 0 if descending ordered.
cardinality: Cardinality of the index.
pages: ??
filter_condition: ??

An example of the table contents with an index created for a table is:

> select * from LXSYS.indexes;

6. LXSYS.TABLE_CHECKS

It shows all the table integrity constraints defined in user created tables (NOT NULL, …). The columns are:

name: Constraint name.
expression: Constraint description.
tableCat: Database on which the table resides.
tableSchem: Schema on which the table resides.
tableName: Name of the table.

7. LXSYS.TRANSACTIONS

It shows all active transactions in the Query Engine relating the information to the connection. The following fields are showed:

txn. The transaction id.
state: State of the transaction. // ?? possible values??
conn: Connection id.
session: Session id.
mode: Isolation level of the session.
uid: user that created the session.
startTs: Start timestamp of the transaction.
time: Epoch with the timestamp at which the transaction was started.
commitTs: If the transaction already committed its commit timestamp, -1 otherwise.

lx% select * from LXSYS.TRANSACTIONS;

  txn           state     conn                                    session    mode                  uid        startTs       time               commitTs

  1031227002    ACTIVE    681efbc4-6f56-41ac-b11f-3dc32bbac7f9    6          SNAPSHOT_ISOLATION    lxadmin    1031226001    435391393809375    -1

8. LXSYS.CONNECTIONS

It contains all the currently open connections with the database.

The columns are:

connectionId: The identifier of the connection.
isCurrent: true if it is the one that you are using, false otherwise.
uid: The user who created the connection.
isolationLevel: Isolation level of the connection.
created: When the connection was created.
used: Last time the connection was used.
hostIP: the IP from which the client is connected.
status: A value indicating the status that can be:
- ACTIVE when the connection is running an action.
- IDLE when the connection is not doing anything.
- FETCHING when the server is sending the resultset to the client side and the client side is iterating the resultset.
lastAction: Last action executed.
lastSql: Last SQL statement executed.
database: Database to which the connection was established.
schema: Schema to which the connection was established.

This is an example of the table contents with just the connection being used by the SQL console:

lx% select * from LXSYS.CONNECTIONS;

connectionId                            isCurrent    uid        isolationLevel        created                    used                       hostIP    status    lastAction        lastSql                            database    schema

f262cbab-f6db-4362-aac4-7727a32220ee    true         lxadmin    SNAPSHOT_ISOLATION    2024-02-08 13:45:04.662    2024-02-08 19:18:24.019              ACTIVE    EXECUTE_DIRECT    select * from LXSYS.CONNECTIONS    db          APP

9. LXSYS.ONLINE_AGGREGATIONS

It contains the user defined online aggregations.

Its columns are:

onlineRelationTable: Name of the online aggregation table.
ordinalPosition: ??
name: Name of the online aggregate column.
aggType: Kind of aggregation that can be:
- COUNT.
- SUM.
- MAX.
- MIN.
aggExpression: Expression over which the online aggregate is applied.
tableCat: Database where is defined.
tableSchem: Schema where is defined.
tableName: Name of the parent table over which the online aggregation has been defined.

An example follows:

lx% select * from LXSYS.ONLINE_AGGREGATIONS where onlineRelationTable = 'AGG_TRIPS' order by ordinalPosition;

 onlineRelationTable  ordinalPosition  name        aggType  aggExpression          tableCat  tableSchem  tableName
 AGG_TRIPS            1                DURATION    SUM      CAST2INT[$DURATION]    demo      APP         TRIPS
 AGG_TRIPS            2                MAX_START   MAX      $START_TS              demo      APP         TRIPS
 AGG_TRIPS            3                MIN_START   MIN      $START_TS              demo      APP         TRIPS
 AGG_TRIPS            4                PASSENGERS  SUM      CAST2INT[$PASSENGERS]  demo      APP         TRIPS
 AGG_TRIPS            5                COUNT_ALL   COUNT                           demo      APP         TRIPS

10. LXSYS.ONLINE_AGGREGATES

It shows information about the group by expressions for existing online aggregation relations

lx% select * from LXSYS.ONLINE_AGGREGATES where onlineRelationTable = 'AGG_TRIPS' order by ordinalPosition;

 onlineRelationTable  ordinalPosition  name       groupBy                                                                    tableCat  tableSchem  tableName
 AGG_TRIPS            1                END_YEAR   TS_EXTRACT['YEAR', AUTOCAST[($START_TS + AUTOCAST[(1000 * $DURATION)])]]   demo      APP         TRIPS
 AGG_TRIPS            2                END_MONTH  TS_EXTRACT['MONTH', AUTOCAST[($START_TS + AUTOCAST[(1000 * $DURATION)])]]  demo      APP         TRIPS
 AGG_TRIPS            3                END_DAY    TS_EXTRACT['DAY', AUTOCAST[($START_TS + AUTOCAST[(1000 * $DURATION)])]]    demo      APP         TRIPS
 AGG_TRIPS            4                END_HOUR   TS_EXTRACT['HOUR', AUTOCAST[($START_TS + AUTOCAST[(1000 * $DURATION)])]]   demo      APP         TRIPS

11. LXSYS.ONLINE_AGGREGATE_FILTERS

It shows information about the filter expressions for existing online aggregation relations

lx% select * from LXSYS.ONLINE_AGGREGATE_FILTERS where onlineRelationTable = 'AGG_TRIPS';

 onlineRelationTable  filter                                  tableCat  tableSchem  tableName
 AGG_TRIPS            (TS_EXTRACT['YEAR', $START_TS] = 2021)  demo      APP         TRIPS

12. LXSYS.AGGREGATIONS

It shows information about the aggregation columns for user defined tables.

lx% select * from LXSYS.AGGREGATIONS;

name         aggType  tableCat  tableSchem  tableName
 PASSENGERS  SUM      demo      APP         MANUAL_AGG_TRIPS
 DURATION    SUM      demo      APP         MANUAL_AGG_TRIPS
 MIN_START   MIN      demo      APP         MANUAL_AGG_TRIPS
 COUNT_ALL   SUM      demo      APP         MANUAL_AGG_TRIPS
 MAX_START   MAX      demo      APP         MANUAL_AGG_TRIPS

13. LXSYS.TABLE_PARTITIONS

It shows information about the table partitions and the split points for each partition.

The columns are:

catalog: Database name.
schema: Schema name.
tableName: Table name.
regionId: Id of the partition/region/fragment.
address: Id of the storage server holding the partition.
minValue: Min value contained by the partition. MIN means the lowest possible value.
maxValue: Max value contained by the partition. MAX means the highest possible value.

This is an example with a table with a single partition containing all data.

lx% select * from LXSYS.TABLE_PARTITIONS order by tableName;

  catalog    schema    tableName       regionId      address    minValue    maxValue

  db         APP       T            1649267441666    ds100      MIN         MAX

14. LXSYS.TABLE_STATS

It contains the collected statistics and their values.

Its columns are:

catalog: Database name.
schema: Schema name.
tableName: Table name.
regionId: Id of the partition/region/fragment.
address: Id of the storage server holding the partition.
stat: Name of the stat.
value: Value of the stat.

This is an example of the stats of an empty table:

lx% select * from LXSYS.TABLE_STATS;

  catalog    schema    tableName    regionId         address    stat        value

  db         APP       T            1649267441666    ds100      nndwr       1
  db         APP       T            1649267441666    ds100      nmbs        3
  db         APP       T            1649267441666    ds100      rtime       1707415699
  db         APP       T            1649267441666    ds100      depth       1
  db         APP       T            1649267441666    ds100      nblkwr      4
  db         APP       T            1649267441666    ds100      noldused    1
  db         APP       T            1649267441666    ds100      nsplit      1
  db         APP       T            1649267441666    ds100      noldblks    1
  db         APP       T            1649267441666    ds100      mtime       1707415699
  db         APP       T            1649267441666    ds100      stime       1707415699
  db         APP       T            1649267441666    ds100      wtime       1707415699
  db         APP       T            1649267441666    ds100      nnd         1
  db         APP       T            1649267441666    ds100      nused       1
  db         APP       T            1649267441666    ds100      ctime       1707415699
  db         APP       T            1649267441666    ds100      ncache      2
  db         APP       T            1649267441666    ds100      nblks       3

15. LXSYS.DATASTORE_STATS

It shows information about table statistics kept by the storage servers.

Its columns are:

address: Id of the storage server holding the partition.
stat: Name of the stat.
value: Value of the stat.

This shows the number of blocks held in each storage server:

lx% select * from LXSYS.DATASTORE_STATS where stat like '%nblks%';

  address    stat          value

  ds103      mblk/nblks    8192
  ds102      mblk/nblks    8192
  ds101      mblk/nblks    8192
  ds100      mblk/nblks    8192

16. LXSYS.USER_GROUP

Deprecated. Not to be used.

17. LXSYS.GROUP_PERMISSION

It contains the permissions that have been granted. Its columns are:

groupId
path
catalog
schema
entityName
perms

lx% select * from LXSYS.GROUP_PERMISSION where groupId = 'APP';
 groupId  path  catalog  schema  entityName  isRead  isWrite  isAlter
 APP      /seq  demo     APP                 true    true     true
 APP      /tbl  demo     APP                 true    true     true

18. LXSYS.PLANS_CACHE

It shows execution plans that are currently cached.

Its columns are:

initialPlan: Initial query plan after compilation.
executionPlan: Final query plan produced by the optimizer.
tableCat: Database name.
tableSchem: Schema name.
tableName: Table name.

An example with a fragment of the contents is:

lx% select * from LXSYS.PLANS_CACHE;

initialPlan                                            executionPlan                                                        tableCat    tableSchem    tableName

LogicalProject(inputs=[0..4])
  LogicalTableScan(table=[[db, LXSYS, PLANS_CACHE]])   EnumerableTableScan.ENUMERABLE.[](table=[db, LXSYS, PLANS_CACHE])    db          LXSYS         PLANS_CACHE
...

19. LXSYS.TRIGGER_FUNCTIONS

It contains all predefined and user defined trigger functions.

Its columns are:

triggerName: Name of the trigger function.
description: Short description of the purposed of the trigger.

An example is:

%lx select * from LXSYS.TRIGGER_FUNCTIONS;

  triggerName                  description

  HashFieldTrigger             Hash Field Trigger
  UUIDTrigger                  UUID Trigger
  AutoGeohashTrigger           Auto Geohash Trigger
  AutoIncrementTrigger         Auto Increment Trigger
  DefaultValueTrigger          Default Value Trigger
  Trigram_Trigger              Trigger to maintain trigram index
  ForceAutoIncrementTrigger    Force Auto Increment Trigger
  DeleteForeignKeyTrigger      Check for Referential Integrity when deleting row from referenced table
  ForeignKeyTrigger            Check for Referential Integrity

20. LXSYS.TRIGGERS

It contains all predefined and user defined trigger functions.

Its columns are:

triggerName: Name of the trigger.
triggerFunction: Name of the trigger function.
args: parameters of the trigger.
isBefore: true if executed before the triggering action, false otherwise.
isInsert: true if the triggering action is an insert, false otherwise.
isUpdate: true if the triggering action is an update, false otherwise.
isDelete: true if the triggering action is a delete, false otherwise.
priority: Specifies the precedence of a trigger compared to other triggers that are defined on the same event. Triggers with lower priority values are executed before triggers with higher priority values.
isEnable: true if it is currently enabled, false if disabled.
tableCat: Database name.
tableSchem: Schema name.
tableName: Table name.

%lx select * from LXSYS.TRIGGERS;
triggerName              triggerFunction       args                      isBefore  isInsert  isUpdate  isDelete  priority  tableCat            tableSchem  tableName

 I_autoIncrementTrigger  autoIncrementTrigger  I,NOUSR.TAUTO_IDENTITY_I  true      true      false     false     100       contrib_regression  NOUSR       TAUTO
 _UUID__UUIDTrigger      UUIDTrigger           _UUID_                    true      true      false     false     100       contrib_regression  NOUSR       TESTTAB_FK
 _UUID__UUIDTrigger      UUIDTrigger           _UUID_                    true      true      false     false     100       contrib_regression  NOUSR       BYTEATAB
 _UUID__UUIDTrigger      UUIDTrigger           _UUID_                    true      true      false     false     100       contrib_regression  NOUSR       BOOLTAB
 _UUID__UUIDTrigger      UUIDTrigger           _UUID_                    true      true      false     false     100       contrib_regression  NOUSR       BYTEATAB2

21. LXSYS.TABLE_FUNCTIONS

It shows available table functions.

Its columns are:

functionName: Name of the table function.
parameters: List of pararmeters.
description: Description of how to use it.

An example with a short fragment of the contents is:

lx% Select * from LXSYS.TABLE_FUNCTIONS;

functionName            parameters           description
oaValidator             (String, String)     oaValidator receives Online Aggregator Name ...
...

22. LXSYS.VIEW_TABLES

It shows user defined views.

Its columns are:

query: The query associated to the view.
holder: //?? what is this
tableCat: Database name.
tableSchem: Schema name.
tableName: Table name.

And example is:

lx% Select * from LXSYS.VIEW_TABLES;

query                             holder        tableCat  tableSchem  tableName
SELECT * FROM "T1" WHERE "I" = 8  adhoc-APP-T1  adhoc     APP         MYVIEW