Firebird/InterBase® and IBExpert generate system database objects, and store their own specific system information about the database objects in system tables. System objects are displayed in the DB Explorer in red, if the system options have been flagged in the Register Database dialog (called using the right mouse button Additional/DB Explorer).
A newly created database is almost 0,5 MB large. This is due to the system tables that are automatically generated by Firebird/InterBase® when a database is created.
These system tables contain a wealth of information, which IBExpert uses in many of its functionalities. Although you may consult and study the information in these system objects, please do not manipulate any data contained in them, as it will almost certainly result in a corrupt database!
RDB$DATABASE: as with all system tables there is column included for a description, to aid database documentation. Since IBExpert version 2012.11.15, a database description can be specified and is subsequently displayed in the SQL Assistant, on the Descriptions page. The database description is also now included in the HTML documentation if the Include descriptions... option is enabled. This table also includes the
RDB$CHARACTER_SET_NAMEcolumn, displaying the database default character set.
RDB$FILE: administrates all secondary files and shadows, if any exist. The
RDB$FILE_SEQUENCEcolumn contains a
SMALLINTnumber specifying the file sequence. A miximum of 65535 secondary database files and shadow files are permitted.
RDB$PAGES: administrates the database pages.
RDB$RELATION_IDpoints to the respective table and
RDB$PAGE_TYPEspecifies whether it is a data or index page.
RDB$DEPENDENCIES: this displays dependencies between tables, views and constraints:
RDB$DEPENDENT_NAMEand the element upon which the other element is dependent is displayed in the
RDB$DEPENDED_ON_NAME. If the dependency is on a column, then this is named in
RDB$RELATIONS: all tables and views are stored in this table. View definitions are stored in
RDB$VIEW_SOURCE; the binary definition in
RDB$VIEW_BLR. System tables are flagged with a 1 in the
RDB$SYSTEM_FLAG column, user tables with 0. If the table is an external file, its name can be found in the
RDB$EXTERNAL_FILE column. The table owner is named in the
RDB$RELATION_FIELDS: stores the column definitions of the individual tables. The sequence in which Firebird/InterBase® displays individual fields following a
SELECT *query, is determined by the value in
RDB$FIELD_POSITIONcolumn, the value 0 appearing first. Each table column is based on a domain, which is displayed in
RDB$FIELD_SOURCE. If a field has been specified as
NOT NULL, the value 1 is displayed in the
RDB$NULL_FLAGcolumn. An anomalous collation order can be viewed in the
RDB$RELATION_CONSTRAINTS: constraints, in the broadest sense, can be found in this table. The name is stored in
RDB$CONSTRAINT_NAME, the respective table in
RDB$CONSTRAINT_TYPEcontains one of the following values:
RDB$INDICES: here you can view the index names (
RDB$INDEX_NAME) and the table names (
RDB$RELATION_NAME). Those indices belonging to a specific table are numbered sequentially, beginning with the number 1 (displayed in
RDB$INDEX_ID). Unique indices are displayed in
RDB$UNIQUE_FLAG with the flag 1,
RDB$INDEX_TYPE shows whether the index is ascending (0) or descending (1) and
RDB$INDEX_INACTIVE displays inactive indices with the value 1. The index selectivity is stored in the
RDB$INDEX_SEGMENTS: this stores the columns that the index is composed of. Their order can be viewed in the
RDB$REF_CONSTRAINTS: this stores how key violations are to be handled. The foreign key name can be found in
RDB$CONSTRAINT_NAME, the corresponding primary key or secondary keys can be viewed in
RDB$CONST_NAME_UQ. Specification of the treatment of key violations can be separately defined for
DELETEactions and can be viewed in
RDB$DELETE_RULErespectively. Actions include the default value,
RDB$CHECK_CONSTRAINTS: validity checks are stored here. Validity checks are made using triggers, the trigger name can be seen in the column,
RDB$TRIGGER_NAME, and the name of the validity check in
RDB$CONSTRAINT_NAME. If the name of a column is displayed in the
RDB$TRIGGER_NAMEcolumn, then this indicates a
NOT NULLconstraint, which are also stored in this table.
RDB$VIEW_RELATIONS: this system table stores all tables belonging to a view. The individual tables are numbered sequentially in the
RDB$VIEW_CONTEXTcolumn. If a table alias is used, this is displayed in the
RDB$FIELDS: this stores the definition of all domains, including data type, size, character set and collation. If a field definition includes a computed by statement, this can be viewed in
RDB$TYPES: this stores data types and object types (
PROCEDURE), character sets and some other information.
RDB$FIELD_DIMENSIONS: this stores array definitions.
RDB$CHARACTER_SETS: here you can find a full list of character sets available in your Firebird/InterBase® version.
RDB$COLLATIONS: this table stores a list of all collations available for the character set IDs, found in
RDB$PROCEDURES: all stored procedures in a database are stored in this system table. The procedure name is stored in
RDB$PROCEDURE_NAMEand it is allocated a sequential numbered identifier in
RDB$PROCEDURE_ID. The number of parameters can be viewed in the
RDB$PROCEDURE_INPUTScolumn the values output by the procedure in
RDB$PROCEDURE_OUTPUTS. The source code is stored in
RDB$PROCEDURE_SOURCEand its binary translation in
RDB$PROCEDURE_BLR. Only the procedure owner (displayed in
RDB$OWNER_NAME) and the SYSDBA may assign rights to a procedure.
RDB$PROCEDURE_PARAMETERS: this stores information about the individual parameters, and in which procedure they are used. The individual parameters are each assigned a sequential number beginning with 0. If the field,
RDB$PARAMETER_TYPEdisplays the value 0, then it is an input parameter, an output parameter displays the value 1. This also references the
RDB$TRIGGERS: this table stores a list of all triggers in the database. Along with the trigger name you can find the name of the corresponding table (
RDB$RELATION_NAME) to which the trigger applies, the trigger type (
RDB$TRIGGER_TYPE) and, if several triggers for a single table have the same
RDB$TRIGGER_TYPEvalue, the field,
RDB$TRIGGER_SEQUENCEdecides in which order the triggers are executed, starting with the lowest value. If duplicate values are found in this column the triggers with the same value are executed in alphabetical order. The trigger source code can be found in
RDB$TRIGGER_SOURCEwith its binary translation in
RDB$TRIGGER_BLR. Deactivated triggers are flagged with a 1 in the
RDB$ROLES: this includes the role names defined for the database and the role owners, the users who defined them.
RDB$PRIVILEGES: this table stores details of which rights which users have received and who granted them. The
RDB$PRIVILEGEfield displays the type of right granted:
X(execute). If the
RDB$GRANT_OPTIONfield is flagged with 1, then the grantee may grant this right to to others.
RDB$RELATION_NAMEshows for which table or procedure the permissions have been granted, and if the permission is restricted to a specific column, this is displayed in the
RDB$EXCEPTIONS: contains a full list of all exceptions.
RDB$FILTERS: this table contains all blob filters. The routine is specified in
RDB$ENTRYPOINT, the DLL file name in
RDB$FUNCTIONS: this includes all UDFs incorporated in the database. The routine is specified in
RDB$ENTRYPOINTand the DLL file name in
RDB$RETURN_ARGUMENTdisplays which of the parameters is the return value.
RDB$FUNCTION_ARGUMENTS: this table lists the individual UDF parameters. The parameters are numbered sequentially in
RDB$ARGUMENT_POSITION, parameter types are displayed in
RDB$FIELD_TYPE, this column referencing the table,
RDB$MECHANISMdisplay either the value 0 when the parameter is passed by value, and 1 when the parameter is passed by reference. Particularly of interest with strings is the length recorded in
RDB$CHARACTER_SET_IDindicates the character set.
RDB$GENERATORS: this table stores the generator name and a unique number. The generator value is not stored in this system table.
RDB$TRANSACTIONS: this system table displays transactions running across multiple databases. 0 indicates that the transaction is in limbo, 1 that it has been committed, 2 that it has been rolled back.
By querying these system tables you get a snapshot of the current activities in the database. For example,
MON$DATABASE provides a lot of the database header information that could not be obtained previously via SQL: such details as the on-disk structure (ODS) version, SQL dialect, sweep interval, OIT and OAT and so on.
You can view other activites, such as who is connected to your database, which transactions and statements are running and so on. You even can cancel a running query by executing a
DELETE statement on
When querying the monitor tables it's important to remember that it's just a snapshot.
MON$ system tables include the following in Firebird version 2.1:
MON$DATABASE: connected database.
MON$ATTACHMENTS: connected attachments.
MON$TRANSACTIONS: started transactions.
MON$STATEMENTS: prepared statements.
MON$CALL_STACK: call stack of active PSQL requests.
MON$IO_STATS: I/O statistics.
MON$RECORD_STATS: record-level statistics.
The following improvements have been incorporated in Firebird 2.5:
MON$CONTEXT_VARIABLES: delivers data about context variables, (includes an overview of all user-defined context variables set by
MON$MEMORY_USAGE: includes current memory usage at database, session, transaction or statement level) in ODS 11.2 and higher databases. Also, in these databases, it becomes possible to terminate a client connection from another connection through the
- The original design in Firebird 2.1 allowed non-privileged database users to see monitoring information pertaining only to their
CURRENT_CONNECTION. In Firebird 2.5 they can request information for any attachment that was authenticated using the same user name.
MON$metadata for ODS 11.2 databases.
- Terminating a client: the
MON$structures are, by design, read-only. Thus, user DML operations on them are prohibited. However, a mechanism is built in to allow deleting (only) of records in the
MON$ATTACHMENTStables. The effect of this mechanism is to make it possible, respectively, to cancel running statements and, for ODS 11.2 databases, to terminate client sessions.
DELETE FROM MON$STATEMENTS
WHERE MON$ATTACHMENT_ID = 32
DELETE FROM MON$ATTACHMENTS
WHERE MON$ATTACHMENT_ID <> CURRENT_CONNECTION
These objects should not be manipulated in any way, otherwise certain IBExpert functionalities will be detrimentally affected.
A special browser is implemented for the
IBE$VERSION_HISTORY table. When
IBE$VERSION_HISTORY is opened in the Table Editor, a new Version Browser page is automatically opened:
Select the database object and the versions you wish to compare. Text and code is highlighted according to whether it has been added, modified or deleted.
IBExpert version 2013.10.08 introduced support of logging of the client address (IP address).
IBE$VERSION_HISTORY table already exists in your database you should add the following changes manually if you need to log the client address and the
RDB$GET_CONTEXT function is available:
New column in the
ALTER TABLE IBE$VERSION_HISTORY ADD IBE$VH_CLIENT_ADDRESS VARCHAR(32) CHARACTER SET NONE;
Additional line of code in
NEW.IBE$VH_CLIENT_ADDRESS = RDB$GET_CONTEXT('SYSTEM', 'CLIENT_ADDRESS');