hardygps wrote:
I'm trying to load some fairly large tables. The process will be run daily for quite a few tables. Run time is important. In order to minimize run time, the external tables are defined with smallint, integer, bigint and decimal columns. This reduces the time it takes to create the external tables and presumably the time it will take Firebird to insert the data from these tables into corresponding internal tables.
I'd appreciate if you could point me to any documentation on defining external tables with these column types as well as columns defined as date.
Here are some specific questions:
- What are the alignment requirements for date, decimal, smallint, bigint, integer and char data items?
- How must a decimal(x, y) field appear in the external table?
- How must a date field appear in the external table?
- How must a time field appear in the external table?
- Is there a limit on the number of columns in an external table?
- Is there documentation on external tables that answer any of these questions?
Ann W. Harrison answers:
The documentation is sparse because in the bad old days, different systems stored some types in different ways. Now, the only real problems are endian.
What are the alignment requirements for date, decimal, smallint, bigint, integer and char data items? Char has no alignment requirement
Varchar must be on a two-byte boundary. The first two bytes are the actual length of the field, but the field will be stored at its full declared length.
Smallint is stored on 2 byte boundaries.
Int is stored on four byte boundaries, as is float.
bigint requires an eight byte boundary, as does double.
DateTime is stored as two 32-bit quantities on four byte boundaries.
Date and Time are 32 bit quantities on two byte boundaries.
How must a decimal(x, y) field appear in the external table?
- the value stored equal to the actual value times 10 ** y.
- if x < 5, small int with two byte boundaries else if x < 10 integer on four byte boundaries else if x < 19 bigint on eight byte boundaries
How must a date field appear in the external table?
A 32 bit integer representing the number of days since November 17, 1858
The number of tens of microseconds (I think) since midnight.
Is there a limit on the number of columns in an external table?
Not different from internal tables.
Is there documentation on external tables that answer any of these questions?
The datatype discussion in Helen's book will certainly help.