Problem: At some point in time my external files got messed up. When I select data from them it comes back all jarbled and mis-aligned. Solution: Note: This issue is fixed in v5.5. The problem is not because of but, manifest through the GBAK and RESTORE process. If you are not interested in the how's and why's your work around options are as follows: Work Around Options: -------------------------------- 1) Don't GBAK and RESTORE you databases if you have external tables. An Unacceptable Solution 2) Drop your external table (not the actual file on disk) definition from the database before you GBAK. Then RESTORE database and re-define the external table definition Why it happens: Example: ---------8< cut script 8<---------------------------- connect 18284.gdb user "sysdba" password "masterkey"; commit; drop database; commit; create database "18284.gdb" user "sysdba" password "masterkey"; commit; create table intern_tbl ( a_num integer, a_char char(10), a_vchar varchar(20)); commit; create table extern_tbl external file "d:temp18284_ext.txt" ( ext_num char(10), ext_char char(10), ext_vchar char(20)); commit; /* * insert 5 records into the internal table */ insert into intern_tbl (a_num, a_char, a_vchar) values (1, "first", "the first record"); insert into intern_tbl (a_num, a_char, a_vchar) values (2, "second", "the second record"); insert into intern_tbl (a_num, a_char, a_vchar) values (3, "third", "the third record"); insert into intern_tbl (a_num, a_char, a_vchar) values (4, "fourth", "the fourth record"); insert into intern_tbl (a_num, a_char, a_vchar) values (5, "fifth", "the fifth record"); commit; select * from intern_tbl; commit; select * from extern_tbl; commit; insert into extern_tbl ( ext_num, ext_char, ext_vchar) select cast(a_num as char(10)), cast(a_char as char(10)), cast(a_vchar as char(20)) from intern_tbl; commit; select * from extern_tbl; commit; ---------8< cut script 8<---------------------------- When you run the script you get the following results. Database: 18284.gdb, User: sysdba Records affected: 1 Records affected: 1 Records affected: 1 Records affected: 1 Records affected: 1 1 first the first record 2 second the second record 3 third the third record 4 fourth the fourth record 5 fifth the fifth record Records affected: 5 Records affected: 0 Records affected: 5 1 first the first record 2 second the second record 3 third the third record 4 fourth the fourth record 5 fifth the fifth record Records affected: 5 GBAK and RESTORE and SELECT you get: select * from intern_tbl; 1 first the first record 2 second the second record 3 third the third record 4 fourth the fourth record 5 fifth the fifth record Records affected: 5 select * from extern_tbl; record the first 1 first record the second 2 second record the third 3 third record the fourth 4 fourth record the fifth 5 fifth Records affected: 5 OOOOOOOOOOOOOOOPs!!! While it appears as though GBAK is what breaks the external file usage, it is actually the server itself. There is a system table named: [RDB$RELATION_FIELDS] which contains three fields that pertain to this issue. RDB$FIELD_NAME RDB$RELATION_NAME RDB$FIELD_POSITION Here is a query you can run against your database. If you run it against the db before gbak and after restore you will find the following results: select cast(rdb$relation_name as char(20)), cast(rdb$field_name as char(20)), cast(rdb$field_position as char(20)) from rdb$relation_fields where rdb$relation_name not starting with "RDB$"; Output: ======= original database system table stuff INTERN_TBL A_NUM 0 INTERN_TBL A_CHAR 1 INTERN_TBL A_VCHAR 2 EXTERN_TBL EXT_NUM 0 EXTERN_TBL EXT_CHAR 1 EXTERN_TBL EXT_VCHAR 2 database system table stuff after gbak and restore INTERN_TBL A_NUM 0 INTERN_TBL A_VCHAR 2 INTERN_TBL A_CHAR 1 EXTERN_TBL EXT_VCHAR 2 EXTERN_TBL EXT_CHAR 1 EXTERN_TBL EXT_NUM 0 RDBMS are built on the princple of relational algebra which does not apply ordering to sets. When the gbak -restore process writes the records to the table, the rows are not guaranteed to be ordered as they were prior to the database being backed up. When we obtain table 'field' data from the system tables for external tables we _do_not_ sort by field position, like we do with internal tables. The data stored in the external file is very much reliant on the ordering of the columns. Because the columns are NOT written back to the system table in the same order, your data becomes misaligned. Ref Bug#8883
Last Modified: 27-OCT-00