LucidDbSystemViews

From Eigenpedia

Jump to: navigation, search

Contents

Overview

The DBA views defined by LucidDB provide a global picture of the contents of the system catalog (tables, columns, etc) and the volatile state of the system (sessions, cache utilization, etc). Not all object types and attributes are exposed by the DBA views yet. DBA views are published in the SYS_ROOT schema, so they can be queried like this:

select * from sys_root.dba_stored_tables;

There are two system-defined columns which appear in many of the system views:

  • MOF_ID: This is the internal object identifier for descriptor objects stored in the catalog repository. This identifier is NOT guaranteed to be preserved across an object's lifetime; for example, during catalog upgrade, MOF_ID's are reassigned for all objects. Therefore, applications should never store MOF_ID's on their own. However, MOF_ID's are guaranteed to be preserved within the scope of a single transaction, so it is safe to use MOF_ID for joining DBA views to underlying repository views.
  • LINEAGE_ID: This is a universally unique identifier (UUID) assigned to an object when it is created. (Not all repository objects receive a LINEAGE_ID; for example, SQL/MED storage options have their own system views, but are really just details of first-class obejcts.) Because it is a UUID, a LINEAGE_ID is guaranteed not to conflict with any other object, even one created on a completely separate instance of LucidDB. Also, a LINEAGE_ID is guaranteed to be preserved throughout an object's lifetime (including object renames and catalog upgrades). Therefore, it is safe for applications to store and use LINEAGE_ID's for any identification purpose.

DBA_COLUMNS

The DBA_COLUMNS view contains one row for each column of every table-like object defined in the catalog:

Column name Data type Notes
CATALOG_NAME VARCHAR(128) Catalog in which the table resides.
SCHEMA_NAME VARCHAR(128) Schema in which the table resides.
TABLE_NAME VARCHAR(128) Table-like object in which the column resides.
COLUMN_NAME VARCHAR(128) Name of the column.
ORDINAL_POSITION INTEGER Position of the column when the object was created. First position starts at 1.
DATATYPE VARCHAR(128) Datatype of the column.
PRECISION INTEGER Numeric precision for numbers, length for strings.
DEC_DIGITS INTEGER Digits to the right of the decimal for numbers.
IS_NULLABLE BOOLEAN Nullability of the column.
REMARKS VARCHAR(65535) Additional information about the column.
MOF_ID VARCHAR(128)
LINEAGE_ID VARCHAR(128)

DBA_COLUMN_STATS

The DBA_COLUMN_STATS view contains one row for each analyzed column of every table-like object defined in the catalog:

Column name Data type Notes
CATALOG_NAME VARCHAR(128) Catalog in which the table resides.
SCHEMA_NAME VARCHAR(128) Schema in which the table resides.
TABLE_NAME VARCHAR(128) Table-like object in which the column resides.
COLUMN_NAME VARCHAR(128) Name of the column.
DISTINCT_VALUE_COUNT BIGINT Number of distinct values in the column.
IS_DISTINCT_VALUE_COUNT_ESTIMATED BOOLEAN Indicates whether DISTINCT_VALUE_COUNT is estimated or exact.
PERCENT_SAMPLED FLOAT Percentage of column sampled for statistics (100.0 indicates computed statistics).
SAMPLE_SIZE BIGINT Number of rows in the sample used to generate statistics (number of rows in table for computed statistics).
BAR_COUNT INTEGER Number of histogram bars to be found in DBA_COLUMN_HISTOGRAMS.
ROWS_PER_BAR INTEGER Number of rows represented by each histogram bar, excluding the last bar.
ROWS_LAST_BAR INTEGER Number of rows represented by the last histogram bar.
LAST_ANALYZE_TIME TIMESTAMP Date and time of the last analysis of this column.

DBA_COLUMN_HISTOGRAMS

The DBA_COLUMN_HISTOGRAMS view contains one row for each histogram bar for each analyzed column of every table-like object defined in the catalog:

Column name Data type Notes
CATALOG_NAME VARCHAR(128) Catalog in which the table resides.
SCHEMA_NAME VARCHAR(128) Schema in which the table resides.
TABLE_NAME VARCHAR(128) Table-like object in which the column resides.
COLUMN_NAME VARCHAR(128) Name of the column.
ORDINAL INTEGER Position of this histogram bar in relation to the others for this column.
START_VALUE VARCHAR(128) The lower bound of this histogram bar, converted to a string.
VALUE_COUNT BIGINT The number of distinct values represented by this histogram bar. May be zero if a single value spans multiple bars.

DBA_FOREIGN_SERVERS

The DBA_FOREIGN_SERVERS view contains one row for each SQL/MED foreign server defined in the catalog:

Column name Data type Notes
FOREIGN_WRAPPER_NAME VARCHAR(128) Foreign data wrapper used to access the server.
FOREIGN_SERVER_NAME VARCHAR(128) Name of the foreign server.
CREATOR VARCHAR(128) Authorization ID which created the foreign server.
CREATION_TIMESTAMP TIMESTAMP Time/date when foreign server was created.
LAST_MODIFIED_TIMESTAMP TIMESTAMP Time/date when foreign server was last modified.
REMARKS VARCHAR(65535) Additional information about foreign server.
MOF_ID VARCHAR(128)
LINEAGE_ID VARCHAR(128)

DBA_FOREIGN_SERVER_OPTIONS

The DBA_FOREIGN_SERVER_OPTIONS view contains one row for each foreign server SQL/MED option setting defined in the catalog:

Column name Data type Notes
FOREIGN_WRAPPER_NAME VARCHAR(128) Foreign data wrapper used to access the foreign server.
FOREIGN_SERVER_NAME VARCHAR(128) Name of the foreign server.
OPTION_NAME VARCHAR(65535) Name of option specified during creation of foreign server.
OPTION_VALUE VARCHAR(65535) Value of the above option.
MOF_ID VARCHAR(128)

DBA_FOREIGN_TABLES

The DBA_FOREIGN_TABLES view contains one row for each SQL/MED foreign table defined in the catalog:

Column name Data type Notes
FOREIGN_WRAPPER_NAME VARCHAR(128) Foreign data wrapper used to access the foreign server.
FOREIGN_SERVER_NAME VARCHAR(128) Foreign server where foreign table resides.
FOREIGN_TABLE_NAME VARCHAR(128) Name of foreign table.
CREATOR VARCHAR(128) Authorization ID which created the foreign table.
CREATION_TIMESTAMP TIMESTAMP Time/date when foreign table was created.
LAST_MODIFIED_TIMESTAMP TIMESTAMP Time/date when foreign table definition was last modified.
LAST_ANALYZE_ROW_COUNT BIGINT Number of rows stored when foreign table was last analyzed.
LAST_ANALYZE_TIMESTAMP TIMESTAMP Time/date when foreign table was last analyzed.
REMARKS VARCHAR(65535) Additional information about foreign table.
MOF_ID VARCHAR(128)
LINEAGE_ID VARCHAR(128)

DBA_FOREIGN_TABLE_OPTIONS

The DBA_FOREIGN_TABLE_OPTIONS view contains one row for each foreign table SQL/MED option setting defined in the catalog:

Column name Data type Notes
FOREIGN_WRAPPER_NAME VARCHAR(128) Foreign data wrapper used to access the foreign server.
FOREIGN_SERVER_NAME VARCHAR(128) Foreign server where foreign table resides.
FOREIGN_TABLE_NAME VARCHAR(128) Name of foreign table.
OPTION_NAME VARCHAR(65535) Name of option specified during creation of foreign table.
OPTION_VALUE VARCHAR(65535) Value of the above option.
MOF_ID VARCHAR(128)

DBA_FOREIGN_WRAPPERS

The DBA_FOREIGN_WRAPPERS view contains one row for each SQL/MED foreign data wrapper defined in the catalog:

Column name Data type Notes
FOREIGN_WRAPPER_NAME VARCHAR(128) Name of foreign data wrapper.
LIBRARY VARCHAR(65535) Path to a JAR containing the wrapper implementation or the fully-qualified name of a class on the server's classpath.
LANGUAGE VARCHAR(128) Language of the wrapper implementation.
CREATOR VARCHAR(128) Authorization ID which created the foreign data wrapper.
CREATION_TIMESTAMP TIMESTAMP Time/date when foreign data wrapper was created.
LAST_MODIFIED_TIMESTAMP TIMESTAMP Time/date when foreign data wrapper was last modified.
REMARKS VARCHAR(65535) Additional information about the foreign data wrapper.
MOF_ID VARCHAR(128)
LINEAGE_ID VARCHAR(128)

DBA_FOREIGN_WRAPPER_OPTIONS

DBA_FOREIGN_WRAPPER_OPTIONS contains one row for each SQL/MED foreign wrapper option setting defined in the catalog:

Column name Data type Notes
FOREIGN_WRAPPER_NAME VARCHAR(128) Name of the foreign data wrapper.
OPTION_NAME VARCHAR(65535) Name of option specified during creation of foreign data wrapper.
OPTION_VALUE VARCHAR(65535) Value of the above option.
MOF_ID VARCHAR(128)

DBA_FUNCTION_PARAMETERS

The DBA_FUNCTION_PARAMETERS view contains one row for each input/output parameter to a function.

Column name Data type Notes
CATALOG_NAME VARCHAR(128) Catalog in which the routine resides.
SCHEMA_NAME VARCHAR(128) Schema in which the routine resides.
ROUTINE_SPECIFIC_NAME VARCHAR(128) Specific name of the routine.
PARAMETER_NAME VARCHAR(128) Name of the routine parameter.
ORDINAL_POSITION INTEGER Position of the parameter when the routine was created. First position starts at 1. Input parameters and return parameters have separate ordinals.
DATATYPE VARCHAR(128) Data type of the parameter.
PRECISION INTEGER Numeric precision for numbers, length for strings.
DEC_DIGITS INTEGER Digits to the right of the decimal for numbers.
REMARKS VARCHAR(65535) Additional information about the parameter.
MOF_ID VARCHAR(128)
LINEAGE_ID VARCHAR(128)

DBA_LABELS

The DBA_LABELS view contains one row for each label created.

Column name Data type Notes
LABEL_NAME VARCHAR(128) Name of the label.
PARENT_LABEL_NAME VARCHAR(128) Name of the parent label for which this label serves as an alias. Null if this label is not an alias.
CSN BIGINT Commit sequence number corresponding to the id of the last committed transaction at the time the label was created. This is used to determine what snapshot of data should be read by sessions using this label setting.
CREATION_TIMESTAMP TIMESTAMP Time/date when the label was created.
MODIFICATION_TIMESTAMP TIMESTAMP Time/date when the label definition was last modified.
REMARKS VARCHAR(65535) Additional information on the label.
MOF_ID VARCHAR(128)
LINEAGE_ID VARCHAR(128)

DBA_OBJECTS_IN_USE

The DBA_OBJECTS_IN_USE view contains one row for each usage of an object by a statement (e.g. a table being accessed by a query). Usage of an object prevents it from being dropped until the statement completes.

Column name Data type Notes
SESSION_ID BIGINT Session using object
STMT_ID BIGINT Statement using object
MOF_ID VARCHAR(128) MOFID of object in use

DBA_PERFORMANCE_COUNTERS

The DBA_PERFORMANCE_COUNTERS view contains one row for each performance counter available for LucidDB.

Column name Data type Notes
SOURCE_NAME VARCHAR(128) Source of the performance information (e.g. JVM)
COUNTER_NAME VARCHAR(1024) Name of the performance counter
COUNTER_UNITS VARCHAR(128) Units in which the performance counter is measured, or null if not known
COUNTER_VALUE VARCHAR(65535) Current value of the performance counter

LucidDbMonitor provides a graphical console for monitoring these counters. See LucidDbPerformanceCounters for a description of the available counters.


DBA_REPOSITORY_PROPERTIES

The DBA_REPOSITORY_PROPERTIES view contains one row for each property used in defining the connection to the runtime metadata repository where LucidDB's catalog is stored.

Column name Data type Notes
PROPERTY_NAME VARCHAR(128) Name of property
PROPERTY_VALUE VARCHAR(1024) Value of property

DBA_ROUTINES

The DBA_ROUTINES view contains one row for each SQL-invocable routine defined in the catalog:

Column name Data type Notes
CATALOG_NAME VARCHAR(128) Catalog in which the table resides.
SCHEMA_NAME VARCHAR(128) Schema in which the table resides.
INVOCATION_NAME VARCHAR(128) Name used to invoke routine.
SPECIFIC_NAME VARCHAR(128) Specific name used to distinguish overloaded routines.
EXTERNAL_NAME VARCHAR(65535) Fully-qualified external method name.
ROUTINE_TYPE VARCHAR(128) One of the following types: procedure or function.
CREATOR VARCHAR(128) Authorization ID which created the routine.
CREATION_TIMESTAMP TIMESTAMP Time/date when routine was created.
LAST_MODIFIED_TIMESTAMP TIMESTAMP Time/date when routine was last modified.
IS_TABLE_FUNCTION BOOLEAN True if routine returns a table (ie. is a UDX)
PARAMETER_STYLE VARCHAR(128) Style of the routine parameters (RPS_JAVA, RPS_JAVA_FARRAGO, ...)
IS_DETERMINISTIC BOOLEAN True if the routine is deterministic.
DATA_ACCESS VARCHAR(128) Data access level of the routine (RDA_NO_SQL, RDA_CONTAINS_SQL, RDA_READS_SQL_DATA, RDA_MODIFIES_SQL_DATA)
REMARKS VARCHAR(65535) Additional information about the routine.
MOF_ID VARCHAR(128)
LINEAGE_ID VARCHAR(128)

DBA_ROUTINE_PARAMETERS

The DBA_ROUTINE_PARAMETERS view contains one row for each parameter of every SQL-invocable routine defined in the catalog. This view does not contains parameters of functions (which can be viewed in DBA_FUNCTION_PARAMETERS.)

Column name Data type Notes
CATALOG_NAME VARCHAR(128) Catalog in which the routine resides.
SCHEMA_NAME VARCHAR(128) Schema in which the routine resides.
ROUTINE_SPECIFIC_NAME VARCHAR(128) Specific name of the routine.
PARAMETER_NAME VARCHAR(128) Name of the routine parameter.
ORDINAL_POSITION INTEGER Position of the parameter when the routine was created. First position starts at 1. Input parameters and return parameters have separate ordinals.
DATATYPE VARCHAR(128) Data type of the parameter.
PRECISION INTEGER Numeric precision for numbers, length for strings.
DEC_DIGITS INTEGER Digits to the right of the decimal for numbers.
REMARKS VARCHAR(65535) Additional information about the parameter.
MOF_ID VARCHAR(128)
LINEAGE_ID VARCHAR(128)

DBA_SCHEMAS

The DBA_SCHEMAS view contains one row for each schema defined in the catalog:

Column name Data type Notes
CATALOG_NAME VARCHAR(128) Catalog in which the schema resides.
SCHEMA_NAME VARCHAR(128) Name of the schema.
CREATOR VARCHAR(128) Authorization ID which created the schema.
CREATION_TIMESTAMP TIMESTAMP Time/date when schema was created.
LAST_MODIFIED_TIMESTAMP TIMESTAMP Time/date when schema was last modified.
REMARKS VARCHAR(65535) Additional information on the schema.
MOF_ID VARCHAR(128)
LINEAGE_ID VARCHAR(128)

DBA_SESSIONS

The DBA_SESSIONS view contains one row for each session connected to the system.

Column name Data type Notes
SESSION_ID BIGINT Session ID (unique across lifetime of server)
CONNECT_URL VARCHAR(128) URL used to connect to LucidDB
CURRENT_USER_NAME VARCHAR(128) User name currently set for session
CURRENT_ROLE_NAME VARCHAR(128) Role name currently set for session
SESSION_USER_NAME VARCHAR(128) User name with which session was authenticated
SYSTEM_USER_NAME VARCHAR(128) Account name of user on client
SYSTEM_USER_FULLNAME VARCHAR(128) Full name of user on client, if known
SESSION_NAME VARCHAR(128) Name assigned to session, if any
CLIENT_PROGRAM_NAME VARCHAR(128) Name of client program, if known
CLIENT_PROCESS_ID INTEGER Client process ID, if known
CURRENT_CATALOG_NAME VARCHAR(128) Catalog name currently set as qualifier for session
CURRENT_SCHEMA_NAME VARCHAR(128) Schema name currently set as qualifier for session
IS_CLOSED BOOLEAN Whether this session has already been closed
IS_AUTO_COMMIT BOOLEAN Whether this session is currently in autocommit mode
IS_TXN_IN_PROGRESS BOOLEAN Whether this session currently has a transaction in progress
CURRENT_LABEL_NAME VARCHAR(128) If non-null, the label name currently set for session

DBA_SQL_STATEMENTS

The DBA_SQL_STATEMENTS view contains one row for each SQL statement currently open in any session

Column name Data type Notes
STMT_ID BIGINT Statement ID (unique across all sessions and across lifetime of server)
SESSION_ID BIGINT ID of session which created this statement
SQL_TEXT VARCHAR(65535) Text of SQL statement
CREATION_TIMESTAMP TIMESTAMP Timestamp of statement creation
PARAMETER_VALUES VARCHAR(65535) String representation for collection of parameter values used for execution of a prepared statement

DBA_STORED_TABLES

The DBA_STORED_TABLES view contains one row for each local table (stored and managed by LucidDB) defined in the catalog:

Column name Data type Notes
CATALOG_NAME VARCHAR(128) Catalog in which the stored table resides.
SCHEMA_NAME VARCHAR(128) Schema in which the stored table resides.
TABLE_NAME VARCHAR(128) Name of stored table.
CREATOR VARCHAR(128) Authorization ID which created the stored table.
CREATION_TIMESTAMP TIMESTAMP Time/date when stored table was created.
LAST_MODIFICATION_TIMESTAMP TIMESTAMP Time/date when stored table definition was last modified.
LAST_ANALYZE_ROW_COUNT BIGINT Number of rows stored when table was last analyzed.
LAST_ANALYZE_TIMESTAMP TIMESTAMP Time/date when stored table was last analyzed.
CURRENT_ROW_COUNT BIGINT Number of rows currently stored in table.
DELETED_ROW_COUNT BIGINT Number of deleted rows remaining to be purged from table via ALTER TABLE REBUILD.
REMARKS VARCHAR(65535) Additional information on stored table.
MOF_ID VARCHAR(128)
LINEAGE_ID VARCHAR(128)

DBA_SYSTEM_INFO

The DBA_SYSTEM_INFO view contains one row for each item of information available about the environment in which LucidDB is running. This information is useful for diagnostics. This view is a superset of the information in DBA_PERFORMANCE_COUNTERS.

Column name Data type Notes
SOURCE_NAME VARCHAR(128) Source of the information (e.g. getenv)
ITEM_NAME VARCHAR(1024) Name of the information item within the source
ITEM_UNITS VARCHAR(128) Name of the units in which the value is measured, or null if not known
ITEM_VALUE VARCHAR(65535) Value of the information item

DBA_SYSTEM_PARAMETERS

The DBA_TABLES view contains one row for each system parameter.

Column name Data type Notes
PARAM_NAME VARCHAR(128) Name of parameter
PARAM_VALUE VARCHAR(1024) Current setting for parameter

DBA_TABLES

The DBA_TABLES view contains one row for each table-like object (view, local table, foreign table) defined in the catalog (in effect, it is a union view of the shared attributes of DBA_LOCAL_TABLES, DBA_FOREIGN_TABLES, and DBA_VIEWS):

Column name Data type Notes
CATALOG_NAME VARCHAR(128) Catalog in which table-like object resides.
SCHEMA_NAME VARCHAR(128) Schema in which table-like object resides.
TABLE_NAME VARCHAR(128) Name of the table-like object.
TABLE_TYPE VARCHAR(128) One of the following types: LocalView, LocalTable, or ForeignTable
CREATOR VARCHAR(128) Authorization ID which created the table-like object.
CREATION_TIMESTAMP TIMESTAMP Time/date when table-like object was created.
LAST_MODIFIED_TIMESTAMP TIMESTAMP Time/date when definition of table-like object was last modified.
REMARKS VARCHAR(65535) Additional information about table-like object.
MOF_ID VARCHAR(128)
LINEAGE_ID VARCHAR(128)

DBA_THREADS

The DBA_THREADS view contains one row for each thread known to the server JVM (native-code threads may not appear here).

Column name Data type Notes
THREAD_ID BIGINT ID of thread within JVM
THREAD_GROUP_NAME VARCHAR(128) Name of containing thread group
THREAD_NAME VARCHAR(128) Name of thread
THREAD_PRIORITY INTEGER Priority of thread as defined by Java
THREAD_STATE VARCHAR(128) State of thread as defined by Java
IS_ALIVE BOOLEAN Whether the thread is still alive
IS_DAEMON BOOLEAN Whether the thread is running as a daemon
IS_INTERRUPTED BOOLEAN Whether the thread has been interrupted

DBA_THREAD_STACK_ENTRIES

The DBA_THREAD_STACK_ENTRIES view contains one row for each stack frame of each thread known to the JVM.

Column name Data type Notes
THREAD_ID BIGINT ID of thread within JVM
STACK_LEVEL INTEGER 0-based position of frame on stack (0 is deepest in stack, 1 is caller of 0, ...)
ENTRY_STRING VARCHAR(1024) Standard Java string representation for a stack entry
CLASS_NAME VARCHAR(128) Name of Java class in which stack frame method is defined
METHOD_NAME VARCHAR(128) Name of Java method executed by stack frame
FILE_NAME VARCHAR(1024) Name of Java source file, or null if debug info is not available
LINE_NUM INTEGER Line number in Java source file, or null if debug info is not available
IS_NATIVE BOOLEAN Whether the stack frame represents execution of a native method

DBA_VIEWS

The DBA_VIEWS view contains one row for each view defined in the catalog:

Column name Data type Notes
CATALOG_NAME VARCHAR(128) Catalog in which the view resides.
SCHEMA_NAME VARCHAR(128) Schema in which the view resides.
VIEW_NAME VARCHAR(128) Name of the view.
CREATOR VARCHAR(128) Authorization ID which created the view.
CREATION_TIMESTAMP TIMESTAMP Time/date when view was created.
LAST_MODIFICATION_TIMESTAMP TIMESTAMP Time/date when view definition was last modified.
ORIGINAL_TEXT VARCHAR(65535) Original text used to create view.
REMARKS VARCHAR(65535) Additional information about the view.
MOF_ID VARCHAR(128)
LINEAGE_ID VARCHAR(128)