SQL System Catalog

SQL System Catalog mk Thu, 03/28/2013 - 17:08

A relational database system uses a system catalog to store information on database objects, such as tables, columns, functions, procedures, users. The MonetDB SQL system catalog is a collection of regular mostly readonly system tables and system views located in the schemas "sys" and "tmp". The relations are modified indirectly through SQL commands such as CREATE TABLE. Some system views are implemented as wrappers around table producing system functions to present internal bookkeeping information.

Below you find categories of system tables and views currently provided.

Tip: to find out which system tables and views are available in your database run query:
  SELECT schema_id, name, type, substring(query, 0, 50) as query FROM sys.tables WHERE system ORDER BY schema_id, name;

Schema, table and columns

Schema, table and columns mk Thu, 03/28/2013 - 17:19

All objects belong to a SQL schema, which ensures a conflict-free namespace for accessing them and a focus for user authorization. The schema contains tables and views administered. Statistics on columns can be gathered via the ANALYZE command.

sys.schemas
name type references description
"id" INTEGER   The unique internal identifier for the schema.
"name" VARCHAR   The unique SQL schema name. Names are case sensitive.
"authorization" INTEGER sys.auths.id The identifier associated with the authorization.
"owner" INTEGER sys.auths.id The reference to the owner of this schema.
"system" BOOLEAN   Is it a MonetDB system schema.
sys.table_types
name type references description
"table_type_id" SMALLINT   The unique internal identifier for the table type.
"table_type_name" VARCHAR   The unique SQL name of this table type.
sys.tables
name type references description
"id" INTEGER   The unique internal identifier for the table or view.
"name" VARCHAR   The SQL table or view name. Names are case sensitive.
"schema_id" INTEGER sys.schemas.id The schema identifier.
"query" VARCHAR   The query to populate a view.
"type" SMALLINT sys.table_types. table_type_id The table type identifier.
See sys.table_types for meaning of the identifiers.
"system" BOOLEAN   Is it a MonetDB system table or view.
"commit_action" SMALLINT   Action on commit for temp tables: 0=Not a temp table, 1=Delete rows, 2=Preserve rows, 3=Drop temp table.
"access" SMALLINT   Table access code: 0 = READWRITE, 1 = READ ONLY, 2 = INSERT ONLY.
"temporary" TINYINT   Retention period indicator; 0=persistent, 1=session.
sys.columns
name type references description
"id" INTEGER   The unique internal identifier for the column.
"name" VARCHAR   The SQL column name. Names are case sensitive.
"type" VARCHAR sys.types.sqlname The SQL type name.
"type_digits" INTEGER   The number of digits (radix 2) for numeric types or max length for character/binary strings.
"type_scale" INTEGER   The precision after decimal point. Only applicable for decimal/numeric types.
"table_id" INTEGER sys.tables.id The table this column belongs to.
"default" VARCHAR   The default lexical value.
"null" BOOLEAN   Nulls are allowed constraint.
"number" INTEGER   Column position in the underlying table, starting from 0.
"storage" VARCHAR   Internal storage class identifier.
sys.statistics
name type references description
"column_id" INTEGER sys.columns.id The unique internal identifier for the column.
"type" VARCHAR sys.types.sqlname The SQL type name.
"width" INTEGER   The number of bytes for fixed size data types or number of bytes per character for character/binary strings.
"stamp" TIMESTAMP   Timestamp when the statistics for the column was created or last updated.
"sample" BIGINT   The number of sample items/rows on which the statistic is based.
"count" BIGINT   The number of items/rows on which the statistic is based.
"unique" BIGINT   The number of unique value found for this column.
"nils" BIGINT   The number of NULLs found for this column.
"minval" CLOB   The minimum value found for this column.
"maxval" CLOB   The maximum value found for this column.
"sorted" BOOLEAN   Whether the data in this column is sorted in ascending order.
"revsorted" BOOLEAN   Whether the data in this column is sorted in descending order.

Table Data Partitioning

Table Data Partitioning dinther Thu, 06/27/2019 - 15:03

Tables can be merged into one virtual merge table to provide Data Partitioning. Optionally the merge table can have a table partitioning scheme allowing data to be partitioned automatically by column or expression and on distinct values or value ranges upon insert. The partitioning schemes are stored in below system tables:

sys.table_partitions
name type references description
"id" INTEGER   The unique internal identifier for the table partition.
"table_id" INTEGER sys.tables.id The merge table id this partition belongs to.
"column_id" INTEGER sys.columns.id The merge table's column id by which the partitioning data is selected OR null when an expression is used.
"expression" VARCHAR   The merge table's expression by which the partitioning data is selected OR null when a column is used.
"type" INTEGER   The partitioning type: 5=By Column Range, 6=By Expression Range, 9=By Column Value, 10=By Expression Value.
sys.range_partitions
name type references description
"table_id" INTEGER sys.tables.id The partition table id this partition belongs to.
"partition_id" INTEGER sys.table_partitions.id The merge table's partition id this range partition belongs to.
"minimum" VARCHAR   The minimum value of the range. Can be null when WITH NULL VALUES is specified.
"maximum" VARCHAR   The maximum value of the range. Can be null when WITH NULL VALUES is specified.
"with_nulls" BOOLEAN   Whether this range also include the NULLs of the Column/Expression.
sys.value_partitions
name type references description
"table_id" INTEGER sys.tables.id The partition table id this partition belongs to.
"partition_id" INTEGER sys.table_partitions.id The merge table's partition id this value partition belongs to.
"value" VARCHAR   The partitioning value. Can be null when WITH NULL VALUES is specified.

Functions, arguments, types

Functions, arguments, types mk Thu, 03/28/2013 - 20:35

The functions (including procedures, aggregates, filter functions and loader functions) are all collected in the tables below. It provides an roadmap to check availability of operations supported. Functions can be overloaded, so the same function name can exist multiple times in sys.functions but each will have different arguments, so different function signatures.

sys.functions
name type references description
"id" INTEGER   The unique internal identifier for the function/procedure/aggregate.
"name" VARCHAR   The SQL name.
"func" VARCHAR   MAL function identifier or SQL function definition.
"mod" VARCHAR   MAL function module identifier.
"language" INTEGER sys.function_languages.language_id The programming language id code in which the function is defined, see table sys.function_languages.
"type" INTEGER sys.function_types.function_type_id Type of function, see table sys.function_types.
"side_effect" BOOLEAN   It does (not) lead to updates.
"varres" BOOLEAN   Does the function have varres?
"vararg" BOOLEAN   Can the function be called with a variable number of arguments?
"schema_id" INTEGER sys.schemas.id The schema the function belongs to.
"system" BOOLEAN   Is it a preloaded MonetDB system function/procedure/aggregate.
This column is available from release Apr2019 (11.33.3) onwards.
sys.systemfunctions
name type references description
"function_id" INTEGER sys.functions.id

The unique internal identifier of a system function.
Note: this is not a system controlled foreign key reference, and is not automatically removed when the referred function is dropped from sys.functions.

Note: As of release Apr2019 (11.33.3) table sys.systemfunctions has become deprecated and will be removed in a future release. Use new column sys.functions."system" instead to determine whether a function is a system function or not.

sys.function_languages
name type references description
"language_id" SMALLINT   The unique internal identifier of a function programming language.
"language_name" VARCHAR   A description of the programming language.
sys.function_types
name type references description
"function_type_id" SMALLINT   The unique internal identifier of a function type.
"function_type_name" VARCHAR   A description of the function type.
sys.args
name type references description
"id" INTEGER   The unique internal identifier for function argument.
"func_id" INTEGER sys.functions.id The corresponding function.
"name" VARCHAR   The SQL name.
"type" VARCHAR sys.types.sqlname The SQL type name.
"type_digits" INTEGER   The number of digits for number types.
"type_scale" INTEGER   The precision after decimal point.
"inout" TINYINT   Whether the argument is usable for input (=0) or output or both (=1).
"number" INTEGER   The function argument position ranging from 1 to n for functions and from 0 to n-1 for procedures (sys.functions.type = 2). For functions a number 0 represents the return arg type.
sys.types
name type references description
"id" INTEGER   The unique internal identifier for the data type.
"systemname" VARCHAR   MAL data type name.
"sqlname" VARCHAR   SQL data type name.
"digits" INTEGER   The number of digits (in the specified radix) for numerical, floating point, time, timestamp and interval types.
"scale" INTEGER   Type of scale. 0=not applicable, 1=decimal, 2=exponential (floating point).
"radix" INTEGER   Radix for the number of digits value: 2 (= binary) or 10 (= decimal).
"eclass" INTEGER   The internal storage class equivalence identifier.
"schema_id" INTEGER sys.schemas.id The schema identifier.

Objects, keys, indices, sequences

Objects, keys, indices, sequences mk Thu, 03/28/2013 - 20:32
sys.objects
name type references description
"id" INTEGER   The internal object identifier. Primary key of this table is the combination of "id" and "nr".
"name" VARCHAR   The SQL name.
"nr" INTEGER   Position within the object list.
sys.keys
name type references description
"id" INTEGER sys.objects.id The unique internal key identifier.
"table_id" INTEGER sys.tables.id The corresponding table identifier.
"type" INTEGER sys.key_types.key_type_id Key type id: 0=Primary Key, 1=Unique Key, 2=Foreign Key, see table sys.key_types.
"name" VARCHAR   The SQL name given to the key.
"rkey" INTEGER sys.keys.id when rkey > 0 Related key designator. -1 if not applicable.
"action" INTEGER   FKey on UPDATE/DELETE rule action type: 0=NO ACTION, 1=CASCADE, 2=RESTRICT, 3=SET NULL, 4=SET DEFAULT.
-1 if not applicable.
sys.key_types
name type references description
"key_type_id" SMALLINT   The unique internal identifier of a key type.
"key_type_name" VARCHAR   A description of the key type.
sys.idxs
name type references description
"id" INTEGER sys.objects.id The unique internal index identifier.
"table_id" INTEGER sys.tables.id The corresponding table identifier.
"type" INTEGER sys.index_types.index_type_id The corresponding index type identifier, see table sys.index_types.
"name" VARCHAR   The SQL name.
sys.index_types
name type references description
"index_type_id" SMALLINT   The unique internal identifier of an index type.
"index_type_name" VARCHAR   A description of the index type.
sys.sequences
name type references description
"id" INTEGER   The unique internal sequence identifier.
"schema_id" INTEGER sys.schemas.id The corresponding schema identifier.
"name" VARCHAR   The SQL name.
"start" BIGINT   The start value of the sequence.
"minvalue" BIGINT   The minimal value of the sequence.
"maxvalue" BIGINT   The maximal value of the sequence.
"increment" BIGINT   The sequence increment value.
"cacheinc" BIGINT   The increment to ease transaction management.
"cycle" BOOLEAN   The repeat the sequence upon hitting its bounds.

Triggers, ids, dependencies, comments

Triggers, ids, dependencies, comments mk Thu, 03/28/2013 - 20:38
sys.triggers
name type references description
"id" INTEGER   The unique internal trigger identifier.
"name" VARCHAR   Its SQL name.
"table_id" INTEGER sys.tables.id The corresponding triggering table identifier
"time" SMALLINT   BEFORE/AFTER
"orientation" SMALLINT   FOR EACH ROW/FOR EACH STATEMENT
"event" SMALLINT   Update/insert/delete/truncate event.
"old_name" VARCHAR   The correlation SQL name for the previous state.
"new_name" VARCHAR   The correlation SQL name for the new state.
"condition" VARCHAR   The SQL condition.
"statement" VARCHAR   The SQL statement(s) to be executed.

 

sys.ids
name type references description
"id" INTEGER sys.*.id The identifier of the object. The referenced table where this object id is stored is available in column "sys_table".
"name" VARCHAR   The name of the object.
"schema_id" INTEGER sys.schemas.id If applicable: The schema identifier the object belongs to, else NULL.
"table_id" INTEGER sys.tables.id If applicable: The table identifier the object belongs to, else NULL.
"table_name" VARCHAR sys.tables.name If applicable: The table name the object belongs to, else NULL.
"obj_type" VARCHAR   Object type name.
"sys_table" VARCHAR   Corresponding system table name where details on the object can be queried.
sys.dependency_types
name type references description
"dependency_type_id" SMALLINT   The unique identifier for the dependency type.
"dependency_type_name" VARCHAR   The unique name of this dependency type.
sys.dependencies
name type references description
"id" INTEGER sys.ids.id The identifier which has a dependency.
"depend_id" INTEGER sys.ids.id The identifier reference on which it depends. The combination of "id" and "depend_id" is unique key.
"depend_type" SMALLINT sys.dependency_types
.dependency_type_id
To which sys.<obj>.id it depends: a table or view, a column, a function, an auth, an object.
See sys.dependency_types for meaning of the identifiers
sys.dependencies_vw
name type references description
"id" INTEGER sys.ids.id The identifier of the object which has a dependency.
"obj_type" VARCHAR   Object type name.
"name" VARCHAR   The name of the object.
"used_by_id" INTEGER sys.ids.id The identifier reference on which it depends. The combination of "id" and "used_by_id" forms a unique key.
"used_by_obj_type" VARCHAR   Object type name of the used_by_id.
"used_by_name" VARCHAR   The name of the object referenced by the used_by_id.
"depend_type" SMALLINT sys.dependency_types
.dependency_type_id
To which sys.<obj>.id it depends: a table or view, a column, a function, an auth, an object.
"dependency_type_name" VARCHAR sys.dependency_types
.dependency_type_name
The name of the dependency type.

 

sys.comments
name type references description
"id" INTEGER sys.ids.id The identifier of the object which has a comment.
"remark" VARCHAR   The remark comment associated to the object.

 

Following convenience views exist to find specific dependencies more easily:

SELECT * FROM sys.dependency_owners_on_schemas;
SELECT * FROM sys.dependency_schemas_on_users;
SELECT * FROM sys.dependency_tables_on_foreignkeys;
SELECT * FROM sys.dependency_tables_on_functions;
SELECT * FROM sys.dependency_tables_on_indexes;
SELECT * FROM sys.dependency_tables_on_procedures;
SELECT * FROM sys.dependency_tables_on_triggers;
SELECT * FROM sys.dependency_tables_on_views;
SELECT * FROM sys.dependency_views_on_functions;
SELECT * FROM sys.dependency_views_on_procedures;
SELECT * FROM sys.dependency_views_on_views;
SELECT * FROM sys.dependency_columns_on_functions;
SELECT * FROM sys.dependency_columns_on_indexes;
SELECT * FROM sys.dependency_columns_on_keys;
SELECT * FROM sys.dependency_columns_on_procedures;
SELECT * FROM sys.dependency_columns_on_triggers;
SELECT * FROM sys.dependency_columns_on_types;
SELECT * FROM sys.dependency_columns_on_views;
SELECT * FROM sys.dependency_keys_on_foreignkeys;
SELECT * FROM sys.dependency_functions_on_functions;
SELECT * FROM sys.dependency_functions_on_procedures;
SELECT * FROM sys.dependency_functions_on_triggers;
SELECT * FROM sys.dependency_functions_on_types;
SELECT * FROM sys.dependency_functions_on_views;
SELECT * FROM sys.dependency_args_on_types;

Storage of tables and columns

Storage of tables and columns dinther Thu, 08/30/2018 - 18:17

The information on calculated storage of data tables and columns are accessible from the system views below. These views give the database administrator insight in the actual footprint of the persistent tables and the maximum playground used when indices are introduced upon them. The actual storage footprint of an existing database can be obtained by querying the views sys.schemastorage or sys.tablestorage or sys.storage (for most detailed information). It represents the actual state of affairs, i.e. storage of files on disk of columns and foreign key indices, and possible temporary hash indices. For strings we take a sample to determine their average length.

sys.schemastorage
name type references description
"schema" VARCHAR sys.schemas.name The schema name.
"storages" BIGINT   The total number of storage files used by all tables in the schema.
"columnsize" BIGINT   The total size in bytes of all the column data of all tables in the schema.
"heapsize" BIGINT   The total size in bytes of all the column heap data of all tables in the schema.
"hashsize" BIGINT   The total size in bytes of all the column hash data of all tables in the schema.
"imprintsize" BIGINT   The total size in bytes of all the column imprints data of all tables in the schema.
"orderidxsize" BIGINT   The total size in bytes of all the ordered column indices data of all tables in the schema.
sys.tablestorage
name type references description
"schema" VARCHAR sys.schemas.name The schema name.
"table" VARCHAR sys.tables.name The table name.
"rowcount" BIGINT   The number of rows in the table.
"storages" BIGINT   The number of storage files used by the table. Besides column storage files also indices and dictionary heaps and pkey hashes require a storage file.
"columnsize" BIGINT   The total size in bytes of all the column data of the table.
"heapsize" BIGINT   The total size in bytes of all the column heap data of the table.
"hashsize" BIGINT   The total size in bytes of all the column hash data of the table.
"imprintsize" BIGINT   The total size in bytes of all the column imprints data of the table.
"orderidxsize" BIGINT   The total size in bytes of all the ordered column indices data of the table.
sys.storage
name type references description
"schema" VARCHAR sys.schemas.name The schema name.
"table" VARCHAR sys.tables.name The table name.
"column" VARCHAR sys.columns.name The column name or name of the index or heap (for dictionary encoded strings).
"type" VARCHAR sys.types.sqlname The data type name.
"mode" VARCHAR   State of the column, such as writable.
"location" VARCHAR   The location (relative path and file name) of the persistent storage data file.
"count" BIGINT   The number of data values in the column storage.
"typewidth" INTEGER   Maximum width of the data in bytes for the column. Large objects without a maximum specified will have 0.
"columnsize" BIGINT   The total size in bytes of the column data.
"heapsize" BIGINT   The total size in bytes of the heap data of the column if existing such as when the column is CHAR(n) or VARCHAR(n) or CLOB(n) or BLOB(n).
"hashes" BIGINT   The total size in bytes of the hash data of a column data if existing.
"phash" BOOLEAN   Whether it has a primary key hash?
"imprints" BIGINT   The total size in bytes of the imprints data of a column data if existing.
"sorted" BOOLEAN   Whether the column data is sorted?
"revsorted" BOOLEAN   Whether the column data is reverse sorted?
"unique" BOOLEAN   Whether all values in the column data are distinct from each other?
"orderidx" BIGINT   The total size in bytes of the ordered index data of a column data if existing.

 

By changing the storagemodelinput table directly, the footprint for yet to be loaded databases can be assessed. See also Storage Model and Disk Space

sys.storagemodelinput
name type references description
"schema" VARCHAR sys.schemas.name The schema name.
"table" VARCHAR sys.tables.name The table name.
"column" VARCHAR sys.columns.name The column name or name of the index or heap (for dictionary encoded strings).
"type" VARCHAR sys.types.sqlname The data type name.
"typewidth" INTEGER   Maximum width of the data in bytes for the column. Large objects without a maximum specified will have 0.
"count" BIGINT   The estimated number of tuples.
"distinct" BIGINT   Indication of distinct number of strings.
"atomwidth" INTEGER   Average width of strings or clob.
"reference" BOOLEAN   Whether the column is used as foreign key reference?
"sorted" BOOLEAN   Whether the column data is sorted? If set there is no need for an index.
"unique" BOOLEAN   Whether all values in the column data are distinct from each other?
"isacolumn" BOOLEAN   Whether the storage is a column
sys.storagemodel
name type references description
"schema" VARCHAR sys.schemas.name The schema name.
"table" VARCHAR sys.tables.name The table name.
"column" VARCHAR sys.columns.name The column name or name of the index or heap (for dictionary encoded strings).
"type" VARCHAR sys.types.sqlname The data type name.
"count" BIGINT   The number of data values in the column.
"columnsize" BIGINT   The total size in bytes of the column.
"heapsize" BIGINT   The total size in bytes of the heap of the column if existing such as when the column is CHAR(n) or VARCHAR(n) or CLOB(n) or BLOB(n).
"hashsize" BIGINT   The total size in bytes of the hash of a column data if existing.
"imprintsize" BIGINT   The total size in bytes of the imprint of a column data if existing.
"orderidxsize" BIGINT   The total size in bytes of the ordered indexx of a column data if existing.
"sorted" BOOLEAN   Whether the column data is sorted?
"unique" BOOLEAN   Whether all values in the column data are distinct from each other?
"isacolumn" BOOLEAN   Whether the storage is a column
sys.tablestoragemodel
name type references description
"schema" VARCHAR sys.schemas.name The schema name.
"table" VARCHAR sys.tables.name The table name.
"rowcount" BIGINT   The number of rows in the table.
"storages" BIGINT   The number of storage files used by the table. Besides column storage files also indices and dictionary heaps and pkey hashes require a storage file.
"columnsize" BIGINT   The total size in bytes of all the column data of the table.
"heapsize" BIGINT   The total size in bytes of all the column heap data of the table.
"hashsize" BIGINT   The total size in bytes of all the column hash data of the table.
"imprintsize" BIGINT   The total size in bytes of all the column imprints data of the table.
"orderidxsize" BIGINT   The total size in bytes of all the ordered column indices data of the table.

Users, roles, privileges, sessions

Users, roles, privileges, sessions mk Thu, 03/28/2013 - 20:37

The user access grants are organized by authorization groups.

sys.auths
name type references description
"id" INTEGER   The unique authorization identifier.
"name" VARCHAR   The SQL authorization name of the user or role.
"grantor" INTEGER sys.auths.id when grantor > 0 The corresponding authorization grantor.

 

sys.users   and   sys.db_user_info
name type references description
"name" VARCHAR sys.auths.name The unique user login name.
"fullname" VARCHAR   Full name of user.
"default_schema" INTEGER sys.schemas.id The default schema for this user.

 

sys.roles
name type references description
"id" INTEGER   The unique role identifier.
"name" VARCHAR   The role name.
"grantor" INTEGER sys.auths.id when grantor > 0 The corresponding authorization grantor.

 

sys.user_role
name type references description
"login_id" INTEGER sys.auths.id The corresponding user identifier.
"role_id" INTEGER sys.roles.id The corresponding role identifier.

 

sys.privileges
name type references description
"obj_id" INTEGER sys.ids.id The identifier of the object for which privilege(s) are given to a certain user or role.
"auth_id" INTEGER sys.auths.id The id of the user or role.
"privileges" INTEGER sys.privilege_codes.privilege_code_id Privileges classification code, see table sys.privilege_codes.
"grantor" INTEGER sys.auths.id when grantor > 0 The corresponding grantor authorization identifier.
"grantable" INTEGER   Permission to pass privilege rights to other users or roles.
sys.privilege_codes
name type references description
"privilege_code_id" INTEGER   The unique internal code of a privilege or combination of privileges.
"privilege_code_name" VARCHAR   A description of the privilege(s) associated with this code.

 

sys.sessions
name type references description
"sessionid" INTEGER   The internal session identifier.
"username" VARCHAR sys.users.name The name of the user who created the session.
"login" TIMESTAMP   The login start timestamp of the session.
"idle" TIMESTAMP   The timestamp of the session.
"optimizer" VARCHAR sys.optimizers.name The optimizer pipeline name.
"sessiontimeout" INTEGER   The session timeout time in milliseconds, 0 means timeout is disabled.
"querytimeout" INTEGER   The query execution timeout time in milliseconds, 0 means timeout is disabled.
"workerlimit" INTEGER   The maximum number of worker threads allowed, 0 means limit is disabled.
"memorylimit" INTEGER   The memory limit in MB for this session, 0 means limit is disabled.

Note: the sys.sessions structure has been enhanced from release Jun2020 (11.37.7) onwards. Older versions will show less and some different columns.

 

QueryLog catalog, calls, history, queue

QueryLog catalog, calls, history, queue mk Fri, 03/29/2013 - 14:12

The tables below provide insight into queries compiled and executed. See also: /Documentation/ServerAdministration/QueryTiming/QueryHistory

sys.querylog_catalog
name type references description
"id" OID   The internal query identifier.
"owner" VARCHAR sys.users.name The user defining it.
"defined" TIMESTAMP   Time when the query was added to the catalog.
"query" CLOB   The complete SQL query statement.
"pipe" CLOB sys.optimizers.name The MAL optimizer pipeline.
"plan" CLOB   The MAL execution plan.
"mal" INTEGER   Size of MAL plan
"optimize" BIGINT   Optimization time in micro-seconds.
sys.querylog_calls
name type references description
"id" OID sys.querylog_catalog.id Reference to the querylog definition.
"start" TIMESTAMP   Time the statement was started.
"stop" TIMESTAMP   Time the statement was completely finished.
"arguments" CLOB   The actual call structure.
"tuples" BIGINT   The number of tuples in the result set
"run" BIGINT   The time spent (in usec) until the result export.
"ship" BIGINT   The time spent (in usec) to ship the result set.
"cpu" INTEGER   The average cpu load percentage during execution.
"io" INTEGER   The percentage time waiting for IO to finish.
sys.querylog_history
name type references description
"id" OID sys.querylog_catalog.id Reference to the querylog definition.
"owner" VARCHAR sys.users.name The user defining it.
"defined" TIMESTAMP   Time when the query was added to the catalog.
"query" CLOB   The complete SQL query statement.
"pipe" CLOB sys.optimizers.name The MAL optimizer pipeline.
"plan" CLOB   The MAL execution plan.
"mal" INTEGER   Size of MAL plan
"optimize" BIGINT   Optimization time in micro-seconds.
"start" TIMESTAMP   Time the statement was started.
"stop" TIMESTAMP   Time the statement was completely finished.
"arguments" CLOB   The actual call structure.
"tuples" BIGINT   The number of tuples in the result set
"run" BIGINT   The time spent (in usec) until the result export.
"ship" BIGINT   The time spent (in usec) to ship the result set.
"cpu" INTEGER   The average cpu load percentage during execution.
"io" INTEGER   The percentage time waiting for IO to finish.

 

sys.queue
name type references description
"tag" BIGINT   Unique internal query call identifier.
"sessionid" INTEGER sys.sessions.sessionid The internal session identifier.
"username" VARCHAR sys.users.name The name of the user responsible for the call.
"started" TIMESTAMP   The date and time the query was started.
"status" VARCHAR   running or finished or paused.
"query" CLOB   The SQL query itself.
"finished" TIMESTAMP   The date and time the query finished, else null.
"workers" INTEGER   The number of worker threads based on history.
"memory" INTEGER   The amount of memory used in MB based on history.

Note: the sys.queue structure has been enhanced from release Jun2020 (11.37.7) onwards. Older versions will show less and some different columns.

Prepared statements

Prepared statements dinther Thu, 06/11/2020 - 20:03

System views for querying active prepared statements information in the user session.

These views are available from release Jun2020 (11.37.7) onwards.

sys.prepared_statements
name type references description
"sessionid" INTEGER sys.sessions.sessionid The internal session identifier.
"username" VARCHAR sys.users.name The user who created the prepared statement.
"statementid" INTEGER   The internal prepared statement identifier. This value must be used to execute the prepared statement.
"statement" VARCHAR   The original SQL prepare statement.
"created" TIMESTAMP   Creation date and time of the prepared statement.

 

sys.prepared_statements_args
name type references description
"statementid" INTEGER sys.prepared_statements.statementid The internal prepared statement identifier.
"type" VARCHAR sys.types.sqlname The SQL type name.
"type_digits" INTEGER   The number of digits (radix 2) for numeric types or max length for character/binary strings.
"type_scale" INTEGER   The precision after decimal point. Only applicable for decimal/numeric types.
"inout" TINYINT   Whether the argument is an input (=1) argument or an output(=0) result column.
"number" INTEGER   The argument position starting from 0. First the output result columns are listed next the input parameters.
"schema" VARCHAR   The schema name of an output argument.
"table" VARCHAR   The table name of an output argument.
"column" VARCHAR   The column name of an output argument.

 

Optimizer pipelines

Optimizer pipelines mk Fri, 03/29/2013 - 15:30

See also: /Documentation/SQLReference/PerformanceOptimization/OptimizerPipelines

sys.optimizers
name type references description
"name" VARCHAR   The unique optimizer pipeline name.
"def" VARCHAR   The optimizer pipeline definition.
"status" VARCHAR   Status: stable or experimental.

Environment variables

Environment variables mk Fri, 03/29/2013 - 15:30

The system keeps a small table of global variables, initialized upon system restart, session restart, or declared explicitly by the user.

sys.environment
name type references description
"name" VARCHAR   The unique identifying name of the global environment variable, such as monet_version and monet_release.
"value" VARCHAR   The value (as string) for the environment variable of the current instance of MonetDB.
sys.var()
name type references description
"schema" VARCHAR   The schema in which the session variable is created
"name" VARCHAR   The declared name for the variable. The combination of schema and name uniquely identifies a specific variable.
"type" VARCHAR   The data type of the variable.
"value" VARCHAR   The value (as string) for the variable in the current session.
sys.var_values
name type references description
"var_name" VARCHAR   The unique identifying name of the system session variable, such as current_schema, optimizer or last_id.
"value" VARCHAR   The value (as string) for the system variable of the current session.
sys.keywords
name type references description
"keyword" VARCHAR   The reserved SQL keyword.