The creator - a Firebird user stored in the security database - of a database and database objects becomes automatically the "owner". The owner of the database (beside SYSDBA) can run a backup, execute "gfix" and more. The owner of database objects like tables, views, stored procedures ... can add/drop fields, grant SQL privileges to other users, alter stored procedures and more. The database owner is not necessarily the owner of objects inside the database and changing the database owner via a backup/restore cycle doesn't change the owner of database objects automatically. Hmm, quite confusing, right? But that's the way how it currently works.

Each Firebird installation comes with a pre-installed user called SYSDBA. SYSDBA is an interesting user for those, who don't want to fiddle around with user management. Usually, this is the case for Firebird newbies. But, using SYSDBA as owner is not the best idea, because you don't have control over the password if there is already a Firebird installation at your customers site or even worse, web-hosters probably don't allow using SYSDBA. So, SYSDBA is fine for in-house usage, but a NO GO for deployment. At least this is my opinion.

What's next? Right! How do I change the owner from SYSDBA to something else? There is no regular DDL statement for that. One way is to recreate the database via a SQL script and pump data over. A second approach is to use a carefully researched mix of backup/restore cycles and direct system table manipulations via our tool FBOwnerMigrator. This is a wizard driven Windows application which guides you through the necessary steps. The good news for you is, we don't want money for it. ;-) And the bad news, there is no public download available. I was often asked: "Why?". I answered: "Because it hacks system tables which may corrupt your database". Although corruption did not happen yet and there are already quite some people out there using this tool for some years. But one never knows.

So, if you want a copy, please contact me.


Regards,
Thomas

One of the most important thing when using Firebird is having a proper transaction management concept in your client application in place, so that the transaction numbers oldest interesting transaction (OIT) and oldest active transaction (OAT) can move forward, thus making the gap between the OIT/OAT and the next transaction (NT) not too big, otherwise you may suffer from bad performance.

Reasons for stuck transaction numbers can be rolled back transactions with a lot of undone modifications, long-running transactions (does not apply to read-only read committed transactions), calling commit retaining without doing a "hard" commit from time to time, failed distributed transaction (2PC). It's beyond the scope here to go into this in more detail.

Well, when you are suffering from bad performance, while the performance was ok after a backup/restore cycle, usually your transaction numbers got stuck due to reasons mentioned above. I find a self-written little nifty application called "Transaction Statistic Logger" pretty useful, to gather transaction statistics over time, store the transaction numbers in a log file or another Firebird database and analyze the data and spot timestamps where the gap got bigger and bigger. Here is a screen of the logger application:



I did not add any diagramming facility, because this can be done via Excel and an ODBC connection to the log database pretty easy. For example:




Pretty interesting when doing consulting jobs at sites with poor transaction management or when trying to unterstand how other applications handle transactions. And in combination with the versioning monitoring tables thingy I described here, the results are even more interesting. ;-)


Regards,
Thomas

Firebird 2.1 added the long-awaited monitoring tables. By querying special system tables starting with MON$, you get a snapshot of the current activities in your database like 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 MON$STATEMENTS. Firebird 2.5 will also allow you to cancel an entire attachment via a DELETE on MON$ATTACHMENTS.

But, everytime you query the monitoring tables, it's just a snapshot. There might be a special tracing facility in a later Firebird release which allows you to analyze what happened in your database over a period of time (something comparable (?) to tkprof for the Oracle guys), but you even can now build a monitoring tables versioning facility by adding a bunch of new tables and one stored procedure to your ODS 11.1 (or higher) Firebird database.

The ERD for the new tables looks like (click to enlarge):



Each execution of the versioning stored procedure will insert one record into MTS_VERSION with the current timestamp and the entire result sets of the MON$ tables into the other MTS_ tables, which are in respect to the table structure basically 1:1 copies to the MON$ tables + a foreign key field to the MTS_VERSION table.

This way, with a time scheduling facility executing the stored procedure periodically, you will get a history of what happened in your database. Pretty simple, but powerful.

Regards,
Thomas

Firebird 2.0 and the latest production release 2.1 have been great releases with a lot of new features. For example, I could not live without monitoring tables, database triggers, common table expressions (CTE) for querying recursive structures, re-worked indexing including function-based indices, and and and ... anymore.

Now, beside the already available snapshot builds, the Firebird project made Firebird 2.5 Alpha 1 available for testing. V2.5 will be a release that introduces the new architecture that will progress into Firebird 3.0 providing full support for fine-grained multi-threading and SMP.

The downloads are available here.


Thomas