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
Last Modified: 26-OCT-00