Subject RE: [firebird-support] Re: Upgrading from FB 1.5 to 2.1 with SQL-Dialect 1
Author Helen Borrie
At 08:59 PM 25/02/2010, Svein Erling Tysvær wrote:
>If I remember correctly, TIMESTAMP in dialect 3 is the same as DATE in dialect 1. Dialect 1 does not have anything equivalent to TIME and DATE in dialect 3.
>Any particular reason you do not want to upgrade to dialect 3 (can it be any harder than upgrading from Firebird 1.5 to Firebird 2.1, the only thing I can think of is that you have to do backup/restore to upgrade dialect

No, backup/restore upgrades ODS, not dialect - a restore doesn't change data.

Also, beware of the gfix utility to up the dialect: it does NOT change anything except the header page - and will then cause you to have a database full of dialect 1 data that the engine thinks is dialect 3. Dragon country.

If you want to upgrade to v.2.anything, the easiest and most efficient method is to create a new, empty, dialect 3 database and pump the data into it.

A trick you can do is to make a metadata-only backup in v.1.5, restore it as an empty database with your preferred page size in 2.1.3 and then use gfix on *that* database to change the dialect. After that, run the metadata script to fix up the system blr and you are (more or less) ready to pump.

While on the Fb 1.5 server, get a full extract script with isql or another tool. Use Fb 1.5.6 isql if possible - it has fewer bugs. You'll need this for several things, such as eyeballing the source code of procedures and triggers. Put the Migration and Installation document beside you to help identify what will need to be altered.

Then grab one of the pumping tools around and pump your data. Make sure you do it in the right order so as not to violate key constraints and dependencies.

When I said "more or less" - there could well be some broken blr in stored procedures and other the Migration doc carefully so you know what to watch out for.

It will be a big day's work; but it doesn't make sense to upgrade to a v.2+ server when you have a database that only works with an SQL subset that's more than a decade old.

Re monitoring: AFAIU, the 2.1 server simply doesn't create the mon$ structures in a D1 database. I think that, once you have the empty database in D3, you'll need to do a fresh backup and restore to have the mon$ structures installed.