FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
 Procedural SQL (PSQL)Compatibility Issues 

Improvements and additions to the Firebird utilities continue.

Monitoring

New virtual tables:

RDB$KEYWORDS:

RDB$KEYWORD_NAME

Keyword name
RDB$KEYWORD_RESERVED

Whether keyword is a reserved word

MON$COMPILED_STATEMENTS:

MON$COMPILED_STATEMENT_ID

Compiled statement ID
MON$SQL_TEXT

Text of the SQL query
MON$EXPLAINED_PLAN

Plan (in the explained form) of the SQL query
MON$OBJECT_NAME

PSQL object name
MON$OBJECT_TYPE

PSQL object type
MON$PACKAGE_NAME

Package name of the PSQL object
MON$STAT_ID

Runtime statistics ID (references MON$*_STATS tables)

New columns in the tables:

In MON$ATTACHMENTS:

MON$SESSION_TIMEZONE

Actual timezone of the session

In MON$STATEMENTS:

MON$COMPILED_STATEMENT_ID

Compiled statement ID (references MON$COMPILED_STATEMENTS)

In MON$CALL_STACK:

MON$COMPILED_STATEMENT_ID

Compiled statement ID (references MON$COMPILED_STATEMENTS)

In SEC$GLOBAL_AUTH_MAPPING:

SEC$DESCRIPTION

Textual description

docnext count = 10

isql

Unify display of system procedures and functions & packages with other system objects

Alex Peshkov

Tracker tickets: #7411, #7475

The SHOW SYSTEM command of isql now lists system packages and their procedures and functions.

The SHOW SYSTEM <object-type> now supports the additional object types PROCEDURES, PACKAGES, and PUBLICATIONS (see also below).

The equivalent “normal” SHOW <object-type> commands no longer list system functions, procedures or packages.

Display statement BLR

Adriano dos Santos Fernandes

Tracker ticket: #6910

The SET EXEC_PATH_DISPLAY command is a debug command to show the BLR (compiled form) of the statement.When a statement is executed, it retrieves the compiled execution path of a DML statement formatted as BLR text.

SET EXEC_PATH_DISPLAY {BLR | OFF};
Warning

This feature is intimately tied to engine internals.Its usage is discouraged if you do not understand very well how these internals are subject to change between versions.

Replication information added to SHOW output

Dmitry Yemanov

Tracker tickets: #7001, #7425

  • The SHOW DATABASE command now reports the “Replica mode” and “Publication” state

  • The SHOW TABLE name command now reports the publications that include the table

  • The SHOW PUBLICATIONS [name] command will show the named publication, or — without name — all user-defined publications in the current database

    Note

    Currently, Firebird doesn’t support user-defined publication, so SHOW PUBLICATIONS currently will never list any publications.

  • The command SHOW SYSTEM now also lists system publications, and SHOW SYSTEM PUBLICATIONS was added to only show system publications

Display per-table statistics

Vlad Khorsun

Tracker tickets (pull requests): #7218

isql can now show per-table statistics for an executed statement.

SET PER_TABle_stats [ON | OFF]

This command turns the display of per-table statistics on or off as desired.If no parameter is supplied to the command, it toggles the current state of the per-table statistics display.

When set to ON, isql shows per-table run-time statistics after query execution.It is set to OFF by default.This command is independent of the SET STATS command.The name PER_TABLE_STATS can be abbreviated up to PER_TAB.Tables in the output are sorted by their relation id’s.

Example (width reduced from original output):

-- check current value
SQL> SET;
...
Print per-table stats:   OFF
...

-- turn per-table stats on
SQL> SET PER_TABLE_STATS ON;
SQL> SELECT COUNT(*) FROM RDB$RELATIONS JOIN RDB$RELATION_FIELDS USING (RDB$RELATION_NAME);

                COUNT
=====================
534

Per table statistics:
--------------------+-------+-----+------+------+------+-------+-----+-------+
Table name          |Natural|Index|Insert|Update|Delete|Backout|Purge|Expunge|
--------------------+-------+-----+------+------+------+-------+-----+-------+
RDB$INDICES         |       |    3|      |      |      |       |     |       |
RDB$RELATION_FIELDS |       |  534|      |      |      |       |     |       |
RDB$RELATIONS       |     59|     |      |      |      |       |     |       |
RDB$SECURITY_CLASSES|       |    3|      |      |      |       |     |       |
--------------------+-------+-----+------+------+------+-------+-----+-------+

Note, some system tables are shown that were not listed in the query;the engine reads some additional metadata when preparing the query.

-- turn per-table stats off, using shortened name
SQL> SET PER_TAB OFF;

gbak

Parallel backup/restore

Vlad Khorsun

Tracker tickets: #1783,#3374

A new command-line switch has been added to gbak: -PAR[ALLEL] <N>.

It defines how many parallel workers will be used for the requested task.

Usage examples:

gbak -b -par 4 -user <username> -pass <password> <dbname> <backupname>
gbak -r -par 4 -user <username> -pass <password> <backupname> <dbname>

gfix

Parallel sweep and ICU dependencies rebuild

Vlad Khorsun

Tracker tickets: #7447, #7550

A new command-line switch has been added to gfix: -PAR[ALLEL] <N>.

It defines how many parallel workers will be used for the requested task.

Usage example:

gfix -sweep -par 4 -user <username> -pass <password> <dbname>
gfix -icu -par 4 -user <username> -pass <password> <dbname>

The -parallel option is only valid in combination with the -sweep and -icu tasks.

ODS upgrade

Dmitry Yemanov

Tracker tickets: #7397

A new command-line switch has been added to gfix: -UP[GRADE].

It allows to upgrade ODS of the database to the latest supported minor version (within the supported major version).

Usage example(s):

gfix -upgrade <dbname> -user <username> -pass <password>