Migration issues with InterBase v4.x to InterBase v5.x databases

By: Borland Staff

Abstract: 3 issues to consider

Problem:
We purchased Delphi 3 C/S and received Interbase v4.2. We are now
ready to distribute our application.  I have a customer who purchased
Interbase v5 and need to know if the databases created using v4.2 are
compatible or if there is an upgrade procedure.

Solution:
Yes and No.  They are compatible but, there are some things that need
to be considered when migrating to the new server version with older
version databases.

Some points of concern:

(Issue #1)
InterBase On Disk Structure (ODS).  This is a characteristic kept on the
databases header page that allows for support of various database 
structures and features.

InterBase v4.x has an ODS of 8.0.  InterBase v5.x has an ODS of 9.0.
A v5.x server can access a v4.x database.  Upon doing so the server
converts the ODS to 8.2.  This allows for some of the v5.x features
(index garbage collection) to work.  The database will not be converted
to ODS 9.0 until a full backup and restore on the v5.x server is 
performed.

  On the v5.x server perform
  --------------------------
  gbak -v -b my80_or_82.gdb my90.gbk
  gbak -v -c my90.gbk my90.gdb

To any potential problems it is a good idea to deploy the backup (.gbk)
file and have the customer restore the database with the server engine
that they have.

WARNING: The v4.x server CANNOT access a v5.x ODS 9.0 database.  When
the server was built it had know knowledge of nor did ODS 9.0 exist.

(Issue #2)
There are new key words with InterBase v5.x.  These new key words are:

        ACTION
        ADMIN
        CASCADE
        RESTRICT
        ROLE
        FREE_IT

You need to ensure that your Database Definitions DO NOT contain any
of these key words.  For compatibility, migration and data loss reasons
GBAK will NOT catch these key words during the backup or restore
process.

A good way to test this is to perform, on a v5.x server, a metadata
extract.  Write the output to a file and try to rebuild the database
'metadata only' with the v5.x engine.

  To extract the DDL:
  -------------------
  isql -extract my90.gdb -o myDDLscript.sql
   
  To rebuild the database:
  ------------------------
  isql -i myDDLscript.sql

You may want to modify the script to create the database under a
different name when doing this.

(Issue #3)
Query performance.  This is crutial as it will be the first thing a 
customer notices if the new engine does not process your queries as fast
as it did before.  There were many changes to the InterBase optimizer
between v4.x and v5.x.  The optimizer is the utility that determines
and builds the best access plan to retrieve the data requested.

InterBase offers the SET PLAN option for use with ISQL and WISQL.  When
using this option the engine will return the query plan that the
optimizer has chosen to take.  If the optimizer does not for some reason
take what may be the fastest method, it can be over-ridden by naming the
plan in the queries syntax.  Please note that this example does NOT
really demonstrate a sub_optimal plan selection but, rather what might
happen and how to use the SET PLAN and how to name the PLAN in a query.

  Example Script:
  ---------------

  create table index_test1 (
    a integer not null,
    b integer);
  commit;

  create unique index single_key_on_a_idx on index_test1 (a);
  create unique index multi_key_on_ab_idx on index_test1 (a,b);
  commit;

  insert into index_test1 (a,b) values (1,1);
  insert into index_test1 (a,b) values (2,1);
  insert into index_test1 (a,b) values (3,2);
  insert into index_test1 (a,b) values (4,1);
  insert into index_test1 (a,b) values (5,1);
  commit;

  set plan;

  select * from index_test1 where a = 3;
  select * from index_test1 where a = 3 and b = 2;
  commit;

  select * from index_test1 where a = 3
  PLAN (INDEX_TEST1 INDEX (SINGLE_KEY_ON_A_IDX)); 
  commit;  

  Example Output:
  ---------------

  D:BORCON>isql -i for_paper.sql
  Use CONNECT or CREATE DATABASE to specify a database
  Database:  for_emery.gdb, User: sysdba
  Database:  for_emery.gdb, User: sysdba

  PLAN (INDEX_TEST1 INDEX (MULTI_KEY_ON_AB_IDX))

            A           B
  =========== ===========

            3           2


  PLAN (INDEX_TEST1 INDEX (MULTI_KEY_ON_AB_IDX))

            A           B
  =========== ===========

            3           2


  PLAN (INDEX_TEST1 INDEX (SINGLE_KEY_ON_A_IDX))

            A           B
  =========== ===========

            3           2

Server Response from: ETNASC04