I think I will try this on my own pc before destroying the server...

Can you suggest a reliable ddl-extraction tool?

What are domains in a database? Don't worry I know what columns are. :)

Thanks for your time on this.

Peter.

________________________________________
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Helen Borrie
Sent: Thursday, 19 April 2007 9:56 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] SQL Question

At 08:55 PM 19/04/2007, you wrote:

>Hi Helen,
>
>Thanks for both answers. I did the dialect command and got the following:
>Client SQL dialect is set to: 1 and database SQL dialect is: 1
>
>Nice to know now what I can and can't do with this particular dialect. Is
>it difficult changing the dialect that the database understands? Or could
>this mean a possible restructuring of the database? I don't think my boss
>will be too interested in me suggesting something too invasive.
>I am definitely no expert so don't think I could upgrade it unless it is
>fairly simple to do.

It's "simple" in one way - just a gfix -sq command. BUT DO NOT DO IT
TO A DATABASE THAT HAS DATA IN IT!

Several Dialect 3 data formats are different to Dialect 1, as are
some data types. You are probably going to need to explore doing the
conversion sooner or later: it is not something you are going to do
while waiting for the day's first coffee to brew. A Migration Guide
is available amongst the IB 6.0 Beta doc set....but I've never heard
of anyone who successfully followed the procedures there and ended up
with a good database. I've retrieved a couple that got euchred by it.

Most of us oldtimers agree that the safest way to go is to

1. use a reliable ddl-extraction tool to extract a clean script of the
database
2. edit the script to change all references to the DATE type to
TIMESTAMP in both domain definitions and column definitions
3. do ditto to any domains and column defs that are populated by
generators, changing INTEGER to BIGINT
4. do similar changes in any trigger and SP definitions

Do whatever it takes at this stage to make sure you have a safe copy
of your dialect 1 database in a safe place (never jump out of
aeroplanes without a parachute).

5. go into isql (or another reliable utility that has a
script-runner) as the user whom you want to be the owner of the
database but don't connect to a database
6. do a
SQL> set sql dialect 3; -- ensures the client into dialect 3
7. create the database
8. input the corrected DDL script, hopefully creating the database
(keep doing this and any needed corrections until you finally have
the empty database you need. You can happily drop any database you
are unsure about.)

Then, use a good datapump program, e.g. IBDatapump, a.k.a. IBPump, to
pump the data in a safe order from the D1 database

By this means, you ensure that the data will be stored as the right
types and in the correct format on-disk.

./heLen