16. How to monitor Firebird server activity?
Firebird 2.1 introduces server-side monitoring via special system tables. This way you can monitor your server directly from SQL Those system tables all have prefix MON$ in their name. To use them, you need to make sure your database file is created with Firebird 2.1 or higher (ODS version 11.1). If you have a database that is created with earlier versions, you need to do backup and subsequent restore with Firebird 2.1 to have those tables.
For detailed information about each monitoring table, read the file
README.monitoring_tables in ‘doc’ directory of your Firebird installation.
Please note that monitoring tables just offer the data, but you’ll probably need some way to aggregate those and spot the problems. Most of these tools can also be used to monitor earlier Firebird versions as well.
17. How to migrate Paradox, dBase or FoxPro database to Firebird?
The easiest way is to download the freeware IBDataPump by CleverComponents. It will extract the metadata from Paradox/dBase/FoxPro database, create all the tables in a Firebird database and then copy all the data. You’ll probably have a ready-to-go Firebird database in less than one hour.
18. How to lock records in a table?
While there shouldn’t be many reasons to do this in MGA database system like Firebird, there are ways to do it.
One is to use a dummy update for all the records you wish to lock. Many developers do this by accident and get the deadlocks. Example that locks employee 8:
-- start transaction
update employee set emp_no = emp no where emp_no = 8;
update employee set .. where emp no 8;
-- end transaction
A more elegant way is to use the SELECT.. WITH LOCK syntax.
-- start transaction
select * from employee where emp_no = 8 WITH LOCK
update employee set ... where emp_no 8:
-- end transaction
Please note that locking easily leads to deadlocks with NO WAIT and application hanging with WAIT transactions. Use it only if you’re really sure you know what you are doing and why.
19. How to load a file into database column?
While some other database systems might have an SQL function for this, with Firebird you need an application. Datatype that holds binary files is called BLOB, and you should use sub_type zero, as sub_type one is for text-only data. Let’s create a table to hold the file. We’ll have a filename column and a blob column containing the file itself:
CREATE TABLE t1
(
file_name VARCHAR(200),
file_data BLOB SUB_TYPE 0
);
The blobs are loaded via parametrized query:
INSERT INTO t1 (file_name, file_data) VALUES (?,? );
20. How to get a list of tables, views and columns in Firebird database?
Tables and views are stored in RDBSRELATIONS system table. System tables and views have RDB$SYSTEM_FLAG set, while user defined ones hake zero or NULL. You can distinguish views from tables as they have field RDB$ VIE W_BLR set. Please note that there is also a field RDB$VIEW_SOURCE which stored human-readable view source and can be set to NULL - database would still be completely functional as it uses precompiled BLR. Here’s query to list all user tables:
select rdb$relation_narne
from rdb$relations
where rdb$view_blr is null
and (rdb$system_flag is null or rdb$system_flag = 0):
A query to list all views:
select rdb$relation_name
from rdb$relations
where rdb$view bir is not null
and (rdb$system_flag is null or rdb$system_flag 0);
Table and view columns are stored in RDBSRELATION_FIELDS. It stores the name, null flag, default value, and domain, in order to get the datatype you need to read domain info from rdbSfields. Here’s a query that lists all tables with their columns:
select frdb$relation_name, PrdbSfield_name
from rdb$relation_fields f
join rdb$relations ron f. rdb$relation name r. rdb$relation_narne
and r. rdb$view_ blr is null
and (r.rdb$system_flag is null or r.rdb$system_fiag = 0)
order by 1, frdb$field_position;
Firebird 2.1 introduces server-side monitoring via special system tables. This way you can monitor your server directly from SQL Those system tables all have prefix MON$ in their name. To use them, you need to make sure your database file is created with Firebird 2.1 or higher (ODS version 11.1). If you have a database that is created with earlier versions, you need to do backup and subsequent restore with Firebird 2.1 to have those tables.
For detailed information about each monitoring table, read the file
README.monitoring_tables in ‘doc’ directory of your Firebird installation.
Please note that monitoring tables just offer the data, but you’ll probably need some way to aggregate those and spot the problems. Most of these tools can also be used to monitor earlier Firebird versions as well.
17. How to migrate Paradox, dBase or FoxPro database to Firebird?
The easiest way is to download the freeware IBDataPump by CleverComponents. It will extract the metadata from Paradox/dBase/FoxPro database, create all the tables in a Firebird database and then copy all the data. You’ll probably have a ready-to-go Firebird database in less than one hour.
18. How to lock records in a table?
While there shouldn’t be many reasons to do this in MGA database system like Firebird, there are ways to do it.
One is to use a dummy update for all the records you wish to lock. Many developers do this by accident and get the deadlocks. Example that locks employee 8:
-- start transaction
update employee set emp_no = emp no where emp_no = 8;
update employee set .. where emp no 8;
-- end transaction
A more elegant way is to use the SELECT.. WITH LOCK syntax.
-- start transaction
select * from employee where emp_no = 8 WITH LOCK
update employee set ... where emp_no 8:
-- end transaction
Please note that locking easily leads to deadlocks with NO WAIT and application hanging with WAIT transactions. Use it only if you’re really sure you know what you are doing and why.
19. How to load a file into database column?
While some other database systems might have an SQL function for this, with Firebird you need an application. Datatype that holds binary files is called BLOB, and you should use sub_type zero, as sub_type one is for text-only data. Let’s create a table to hold the file. We’ll have a filename column and a blob column containing the file itself:
CREATE TABLE t1
(
file_name VARCHAR(200),
file_data BLOB SUB_TYPE 0
);
The blobs are loaded via parametrized query:
INSERT INTO t1 (file_name, file_data) VALUES (?,? );
20. How to get a list of tables, views and columns in Firebird database?
Tables and views are stored in RDBSRELATIONS system table. System tables and views have RDB$SYSTEM_FLAG set, while user defined ones hake zero or NULL. You can distinguish views from tables as they have field RDB$ VIE W_BLR set. Please note that there is also a field RDB$VIEW_SOURCE which stored human-readable view source and can be set to NULL - database would still be completely functional as it uses precompiled BLR. Here’s query to list all user tables:
select rdb$relation_narne
from rdb$relations
where rdb$view_blr is null
and (rdb$system_flag is null or rdb$system_flag = 0):
A query to list all views:
select rdb$relation_name
from rdb$relations
where rdb$view bir is not null
and (rdb$system_flag is null or rdb$system_flag 0);
Table and view columns are stored in RDBSRELATION_FIELDS. It stores the name, null flag, default value, and domain, in order to get the datatype you need to read domain info from rdbSfields. Here’s a query that lists all tables with their columns:
select frdb$relation_name, PrdbSfield_name
from rdb$relation_fields f
join rdb$relations ron f. rdb$relation name r. rdb$relation_narne
and r. rdb$view_ blr is null
and (r.rdb$system_flag is null or r.rdb$system_fiag = 0)
order by 1, frdb$field_position;
No comments:
Post a Comment