Additional Data Type Information

AutoNumber

The settings for this data type are SMALLINT, INTEGER, DECIMAL and BIGINT.
When connected to DB2 databases prior to version 7.1, AutoNumber field functionality is implemented in DB2 using insert and update triggers.
 
When connected to a DB2 version 7.1 or higher database, Scriptoria uses the IDENTITY property to replicate the functionality of Access AutoNumber fields. The "GENERATED ALWAYS AS IDENTITY (START WITH x, INCREMENT BY y, CACHE z)" usage is specified when new DB2 tables are created and either the Access table contains no data or the "Data" option is not checked. In all other cases, "GENERATED BY DEFAULT AS IDENTITY (START WITH x, INCREMENT BY y, CACHE z)" is used.
The START WITH value can be any positive or negative value that could be assigned to this column. The default is 1.
 
The INCREMENT BY parameter cannot be zero or exceed the value of a large integer. The default is 1.
The CACHE value must be in the range of 2 to 32,767 and defaults to 20. Set the CACHE to 0 for NO CACHE.

AutoNumber Note (Version 6.1 and lower):

When adding AutoNumber fields to existing DB2 tables in Prototyping Mode, the UDF extensions must be activated. Adding AutoNumber fields is not allowed in “Production Mode”, but can be accomplished on any platform (without UDF extensions) when in Migration Mode.

Binary

This is not an intrinsic Access type, but is supported for use with other database system’s binary types. This includes the DB2 CHAR and VARCHAR type when FOR BIT DATA is specified. If the VARCHAR length exceeds 255, the type is supported by Access as long binary (OLE Object). DB2 REFERENCE types are also represented as Access Binary field.

Currency

ODBC interprets DECIMAL types with a precision less than or equal to 15 as “Double”. A precision of 16 or more as interpreted by ODBC as Text and may cause problems if used in numeric calculations.

Date / Time

The DB2 DATE type is represented as the Access Date / Time type when linked using ODBC, even though it contains no “Time” component.  The DB2 TIMESTAMP is not represented as a date, but as a 26 character Text field.

It may be important to note that Access and DB2 Date data types support different century ranges. The range of Access centuries are between the year 100 and 9999 AD. With DB2, the centuries range from 0001 to 9999. As with floating point numbers, a “Write Conflict” error is issued when DB2 dates doesn’t match valid Access date ranges. For example, a DB2 date of “17 August, 0099” will be flagged as an error by Access.

Hyperlink

DB2 Data Link types are represented as Access Hyperlink types. Although these types are not identical, they share many similarities. Unlike Access Hyperlink fields, DB2 Data Link columns may be managed by DB2, and a number features may be specified.  Specifying Data Link features is accomplished by using the Data Link entry fields on the Default Data Type Settings page as well as for individual columns.
Currently, transferring data from Access Hyperlink fields to DB2 Data Link columns (using Data Links Server) is not supported. Any data in Hyperlink fields is ignored when transferring Access data to DB2 when the DATALINK type is specified. If you wish to retain existing Hyperlink data, specify the LONG VARCHAR type.
When importing DB2 databases, Access Hyperlink fields are created for any DB2 DATALINK columns and the data is transferred to Access. Access interprets most DATALINK data as it would native Hyperlink data.

Memo

LONG VARCHAR data types hold up to 32,700 bytes and require 24 bytes of row storage compared with a minimum of 72 bytes for LOB types. Tables that specify numerous Access Text fields may exceed DB2 Row Size limits (usually 8K), especially when the length of the Text fields is large. If you encounter table “Row Size” limitations, define some Access Text or Memo fields as LONG VARCHAR.

Text

Access Text fields with a designated length of 255 that specify CHAR as the DB2 default data type are automatically converted to VARCHAR(255).  There are tradeoffs with using VARCHAR versus CHAR. CHAR types can be more efficient from a performance standpoint, but VARCHAR types can be more space conserving and may be increased in length using ALTER table.  The columns comprising a DB2 index must have a combined length of 255 characters or less.
You can also choose LONG VARCHAR as the default DB2 equivalent for Access Text fields, however, LONG VARCHAR columns cannot be indexed.  The length of all columns that comprise an index cannot exceed 255 bytes.
You may also choose to set the For Bit Data property.