The system database contains information about all database catalogs in the server cluster, configuration of the current server and various memory, network and transaction statistics.
Contents |
System catalog table syscatalogs. Disk-based table containing records for all databases on the server instance. This table will be replicated to all nodes in a cluster.
Table columns:
| Name | Type | Description |
|---|---|---|
| id | BIGINT | Unique numeric identifier of database |
| name | VARCHAR | Database name |
| path | VARCHAR | Directory for files |
| locale | INTEGER | Language Codepage number |
Systables is a list of all tables in the database cluster.
| Name | Type | Description |
|---|---|---|
| catalog_id | BIGINT | Database this table belongs to. References syscatalogs (id) |
| id | BIGINT | Table id. Unique to the server cluster. Each time a table is altered, a new id is assigned. The original id when the table was created is found in creation_stamp_id. |
| name | VARCHAR | Table name, unique to the catalog. |
| path | VARCHAR | Physical path to table data |
| count | BIGINT | Approximate number of rows. This is generated by GENERATE STATISTICS SQL command |
| part_fld | SMALLINT | The column which this table is partitioned on. Abbreviation of "partition field". References syscolumns(id). Unless the value is 255, meaning the table is replicated. |
| data_provider_id | INTEGER | The source provider of the data. E.g. Memory, disk or registry. |
| creation_stamp_id | BIGINT | The first id of the table, assigned when it was created |
| stats_timestamp | DATETIME | The last time the statistics were generated |
| stats_utimestamp | BIGINT | The number of table modifications that had happened at the last GENERATE STATISTICS |
Description of the column types. Contains some statistic information for the optimizer, as generated by GENERATE STATISTICS
| Name | Type | Description |
|---|---|---|
| table_id | BIGINT | Table this column belongs to. References systables (id) |
| position | SMALLINT | Logical position of the column, as defined by the order of the columns in the CREATE TABLE statement. Unique to the table. |
| id | SMALLINT | Physical position of the column on disk. Unique to the table. Primary key columns will always come first in this ordering. |
| name | VARCHAR | Name of the column, unique to the table. |
| type | SMALLINT | Data type id of the column |
| type_sz | SMALLINT | Maximum length of the data in bytes for this column |
| default | VARCHAR | Constant default value. NULL if default_func is used. |
| default_func | SMALLINT | Internal function id used to generate default values. -1 if 'default' value column is used. |
| nullable | SMALLINT | 0: NOT NULL option (default); 1: NULL option |
| comment | VARCHAR | Column comment as set by the COMMENT option |
| unique | FLOAT | Approximate proportion of uniqueness in the column. Where 1 means all fields are unique and 0 would (theoretically) mean that all fields were equal. Generated by GENERATE STATISTICS. |
| avg | INTEGER | Approximate average size of the column's fields' data. Blob and text columns are not calculated. Generated by GENERATE STATISTICS. |
Catalogue of all the database indices and foreign keys.
| Name | Type | Description |
|---|---|---|
| table_id | BIGINT | Table this index belongs to. References systables (id) |
| id | INTEGER | Unique id per-table |
| name | VARCHAR | Constraint name |
| keys | VARCHAR | ' ("vertical bar" or "pipe") character, e.g. "0|1|3|" |
| ref_table_id | BIGINT | Id of the table if this index is a foreign key, or -1 if none. May reference systables (id) |
| ref_id | SMALLINT | Id of the foreign index, or -1 if none. May reference sysindexes (id) |
| on_update | SMALLINT | The ON UPDATE action of a foreign key. 0: NOACTION (default); 1: CASCADE (currently not supported) |
| on_delete | SMALLINT | The ON DELETE action of a foreign key. 0: NOACTION (default); 1: CASCADE |
| unique | SMALLINT | The UNIQUE option. 1: index is UNIQUE, 0: index is not UNIQUE |
| height | INTEGER | Height of the b+tree |
| dpages | INTEGER | Number of data leaf pages of the b+tree |
One row per stored procedure in the server cluster.
| Name | Type | Description |
|---|---|---|
| catalog_id | BIGINT | Catalog/database this procedure belongs to. References syscatalogs (id) |
| proc_id | BIGINT | Globally unique procedure id |
| name | VARCHAR | Name of the stored procedure, unique to the database catalog. |
| sql | VARCHAR | SQL code of the procedure |
There is one row per server instance in the cluster in the sysinstances table.
| Name | Type | Description |
|---|---|---|
| instance_id | INTEGER | Node identifier taking account the partition group it belongs too. The node_id calculated: node_id = (node id {1,2,..32})*(partition_group_id*32). For example, for node (1) in partition group 1, the node_id = 32. Node_id/32 will tell the partition group id the node belongs too. |
| partition_group_id | INT | partition group identifier the node belongs to |
| unique_id | BIGINT | Similar to node_id, except the unique node id never changes. node_id will change when partition group is split. |
| endpoint | VARCHAR | Connection endpoint. This is the connection string for the network protocol. This will be '<ip-number>:<port-number>' for a TCP/IP connection, or <server-name>/<pipe-name>/scimore for a named pipe. |
| current | SMALLINT | This will be 1 for the one instance that the query is run through. Other instances in the cluster will be 0. |
| pg_mask | INT | 32 bit availability partition groups' mask. The nodes in the partition enumerated [1,32], each node's id represents the bit in the mask. For example, initially partition group consist 3 nodes:1,2,3, the mask =
1 + 2 + 4 = 7. If node (2) is voted off, the mask becomes 5. |
| v_lower | INT | Nodes' hash range starts value. |
| v_upper | INT | Nodes' hash range end value. |
A cluster change history/log table. When either failover or admin executes cluster changes, the new configuration set is inserted to the table.
| Name | Type | Description |
|---|---|---|
| view_id | BIGINT | Auto incremental value. Increments per new cluster configuration inserted. |
| cluster_version | INT | Cluster version. If admin changes cluster, the version will increment. If a node is voted off the cluster, the cluster version will remain the same and [group_mask] will change. |
| group_id | INT | Unique group id the node belongs to. |
| group_version | INT | Like with cluster version, group version increments, when particular clusters' group is changed, e.g. add/remove nodes in group, split group. |
| group_mask | INT | The same as [SysInstances] "pg_mask" column |
| node_id | INT | The same as [SysInstances] "instance_id" column. |
| unique_id | BIGINT | The same as [SysInstances] "unique_id" column |
| endpoint | VARCHAR | The same as [SysInstances] "endpoint" column |
The table list users and roles.
| Name | Type | Description |
|---|---|---|
| catalog_id | BIGINT | Database identifier the role belongs too. Users always belong to 0 that is SYSTEM catalog. |
| id | BIGINT | Unique user or role id |
| grants | VARCHAR | ' the list of roles IDs the user/role belongs to. For example, user belongs to roles: 1,2,4, the field value will be: '1|2|4' |
| name | VARCHAR | Either globally unique username or unique per database the role name. |
| password | VARCHAR | Encrypted users' value. |
| owner_id | BIGINT | Unique identifier of the user what created the particular user. If value '-1' user don't have the parent/creator. |
| role | INT | Type of the security object: 0 - user, 1 - role, 2 - built-in role, 3- performance role |
The map between the user/role and the explicit permissions.
| Name | Type | Description |
|---|---|---|
| user_id | BIGINT | User or role id (Sysusers(id)). |
| id | BIGINT | permission unique identifier. |
| object_id | BIGINT | Depending on objet_type field value, either unique identifier of database, table or procedure |
| object_type | INT | The value identify the object type: 1 - system/server, 2 - database, 4 - table, 8 - procedure |
| permission_type | INT | The permission id, values |
| allow | BIT | 1 - grant, 0 - deny the permission. |
| grantee_id | BIGINT | The unique identifier of the user who granted/denied permission. |
| grantable | BIT | Identify is user can grant the permission to others. |
A table containing the current server instance configuration. This table is derived from information in the registry and startup settings of the server instances.
These are virtual tables generated dynamically to provide monitoring capabilities.
Information about the current connection sessions
| Name | Type | Description |
|---|---|---|
| instance_id | INTEGER | Node id. References sysinstances (id) |
| id | BIGINT | Unique session id, unique within cluster |
| exec_state | INTEGER | Execution state of the internal thread handling this session:
|
| ph_pages_io | BIGINT | Number of pages that have been physically accessed on disk in the current transaction. |
| lg_pages_io | BIGINT | Total number of logical pages accessed in the current transaction. |
| tran_id | BIGINT | Unique id of most recent transaction of this session |
| tran_state | INTEGER | Transaction's current commit state:
|
| tran_iso | INTEGER | Transaction's isolation level:
|
| tran_auto_commit | INTEGER | Transaction's automatic commit mode. 0: Auto-commit off; 1: Auto-commit on. |
| locks | INTEGER | Number of row-level locks that the transaction is currently holding. |
| blocked_by_tran_id | BIGINT | Transaction id that this session is waiting for locks from |
| error | VARCHAR | Text of the most recent error message |
| sql | VARCHAR | Last SQL statement executed |
| quants_0 | INT | The number of executed LOW priority quants. Per transaction. |
| quants_1 | INT | The number of executed NORMAL priority quants. Per transaction. |
| quants_2 | INT | The number of executed HIGH priority quants. Per transaction. |
| quants_3 | INT | The number of executed HIGHEST priority quants. Per transaction. |
| max_priority | INT | Maximum allowed priority |
| query_priority | INT | Starting query priority. If dynamic priority throttling is disabled, the starting priority will never change. |
| throttle_priority | INT | When dynamic priority throttling is enabled, the priority will decrease for heavy queries, the "throttle_priority" identify the current transactions’' priority. |
| cpu_time | BIGINT | Session used CPU time in microseconds. Per transaction. |
| net_requests | INT | Number of network IOs. Per transaction. |
If database has been secured, musers store users' aggregated performance statistics.
| Name | Type | Description |
|---|---|---|
| instance_id | INT | Node id. References sysinstances(id) |
| user_id | BIGINT | User id. |
| transactions | BIGINT | Number of transactions executed by the user. |
| cpu_time | BIGINT | Number of CPU time in microseconds. |
| mem_pages | BIGINT | Number of accessed database pages. |
| disk_pages | BIGINT | Number of pages read from disk |
| page_faults | BIGINT | Number of flushed dirty pages to disk in order to read another page. |
| quants_highest | BIGINT | Number of executed quants with [Highest] priority |
| quants_higher | BIGINT | Number of executed quants with [Higher] priority |
| quants_normal | BIGINT | Number of executed quants with [Normal] priority |
| quants_low | BIGINT | Number of executed quants with [Low] priority |
| max_priority | INT | Maximum query priority |
| avg_priority | INT | Average query priority. |
| min_priority | INT | minimum query priority. |
One system process per node per CPU. Within each node there are several worker threads - CPU schedulers, where each uses the distinct CPU.
| Name | Type | Description |
|---|---|---|
| instance_id | INTEGER | Node id that this process is part of. References sysinstances (id) |
| id | INTEGER | Unique process id internal to the node |
| busy | INTEGER | Number of executing transactions |
| total | INTEGER | Total number of fibers allocated in this process |
| queue | INTEGER | Number of SQL requests awaiting to service them. |
| switch_per_sec | INTEGER | Number of internal context switches between fibers per second per process |
| connections | INTEGER | Number of active connections per process. Each connection is serviced by one CPU process, assigned on connection creation. |
Statistics on current network connections
| Name | Type | Description |
|---|---|---|
| instance_id | INTEGER | Node id. References sysinstances (id) |
| id | INTEGER | Id of the communication protocol |
| protocol | VARCHAR | Name of the communication protocol |
| read_bytes_sec | INTEGER | Bytes per second currently being received on the network layer |
| write_bytes_sec | INTEGER | Bytes per second currently being sent on the network layer |
| requests_sec | INTEGER | Number of network layer requests per second |
Performance monitor on tables
| Name | Type | Description |
|---|---|---|
| instance_id | INTEGER | Node id. References sysinstances (id) |
| id | BIGINT | Table id. References systables (id) |
| deletes_sec | INTEGER | Current rate of deletes per second |
| inserts_sec | INTEGER | Current rate of inserts per second |
| updates_sec | INTEGER | Current rate of updates per second |
| selects_sec | INTEGER | Current rate of selects per second |
| cache_hit_ratio | INTEGER | Current cache hit ratio percentage for the database pages of the table |
| cache_page_io_sec | INTEGER | Current rate of pages being read from the cache per second |
| disk_page_io_sec | INTEGER | Current rate of pages being read from disk per second |
| file_size | INTEGER | Size of the table's file on disk in MB |
| page_split_sec | INTEGER | Current rate of B+tree page splits per second |
| page_alloc_sec | INTEGER | Current rate of allocated pages per second |
| page_free_sec | INTEGER | Current rate of freed pages per second |
| ncount | INTEGER | Approximate number of rows in the table. copied from systables, used by optimizer. The values are populated when generating statistics, SQL GENERATE STATISTICS FOR DATABASE mydb |
| dpages | INTEGER | Number of B+Tree data leaf pages in the table. copied from systables, used by optimizer and populated when generating statistics. |
mprocedures is a virtual table containing information on how many times a procedure has been executed.
| Name | Type | Description |
|---|---|---|
| instance_id | INTEGER | Node id. References sysinstances (id) |
| id | BIGINT | procedure_id. References sysprocedures (id) |
| avg_cpu | INTEGER | Average CPU cost (unimplemented) |
| executed | INTEGER | Number of times the procedure has been executed |
The dynamic mtransactionlog table provides information on the transaction log file of each server instance.
| Name | Type | Description |
|---|---|---|
| instance_id | INTEGER | Node id. References sysinstances (id) |
| log_bytes_sec | INTEGER | Current rate of bytes being sent to the log manager per second |
| disk_sec | INTEGER | Current rate of disk write operations to transaction log per second |
| commit_sec | INTEGER | Current rate of commit/rollback requests received per second. disk_sec may be lower than commit_sec, if there are group-commits happening. A group-commit is when the process waits for other commits before writing to disk, for efficiency. |
| file_size | INTEGER | Current size of transaction log file in MB |
The dynamic mtransactions table provides statistical information on current transactions.
| Name | Type | Description |
|---|---|---|
| instance_id | INTEGER | Node id. References sysinstances (id) |
| exec_tran | INTEGER | Current number of active transactions in progress (not committed or aborted) |
| tran_sec | INTEGER | Current rate of transactions per second |
| commit_sec | INTEGER | Current rate of committed transactions per second |
| abort_sec | INTEGER | Current rate of aborted transactions per second |
The dynamic mlocks table provides statistical information on locks for each node.
| Name | Type | Description |
|---|---|---|
| instance_id | INTEGER | Node id. References sysinstances (id) |
| deadlock_sec | INTEGER | Current rate of deadlocks per second |
| grant_locks | INTEGER | Current number of allocated and granted locks |
| lock_sec | INTEGER | Current rate of locks granted per second |
| block_tran | INTEGER | Current number of blocked transactions |
The dynamic mpagecache table provides information on the transaction log file of each node.
| Name | Type | Description |
|---|---|---|
| instance_id | INTEGER | Node id. References sysinstances (id) |
| free | INTEGER | Current number of free pages in the memory cache |
| pinned | INTEGER | Current number of pages locked in memory, that cannot be page faulted, moved, etc. |
| dirty | INTEGER | Current number of modified pages in the memory cache, unwritten to disk |
| page_faults | INTEGER | Current rate of page faults (pages that have to be swapped out to disk so another can be swapped in) per second. |
| checkpoint_pages_sec | INTEGER | Current rate of pages flushed to disk by transaction checkpoints per second |
| checkpoint_group_pages | INTEGER | Current number of pages flushed to disk in a group, while a checkpoint is in progress. |
| lwriter_pages_sec | INTEGER | Current rate of pages flushed by transaction checkpoints per second for the lazy-writer process |
| lwriter_group_pages | INTEGER | Current number of pages flushed to disk in a group for the lazy-writer process. |