Using the OpenLink Rails ODBC Adapter with IBM DB/2
Table of Contents
To obtain the correct type mappings (detailed below),
ensure LongDataCompat is set to 1 in the file
[db2_alice_rails1] DBALIAS=RAILS1 LongDataCompat=1
Because DB2 does not allow numeric literals to be bound to numeric columns, you must set the connection option :convert_numeric_literals to true to avoid errors similar to:
'The data types of the operands for the operation "=" are not compatible.'
e.g. In your Rails application's database.yml file:
development: adapter: odbc dsn: a610_db2_alice_rails1 username: db2admin password: db2admin trace: true convert_numeric_literals: true
If convert_numeric_literals is off, the following query generates the above error
SELECT * FROM accounts WHERE (accounts.id = '1')Turning on convert_numeric_literals changes the query to
SELECT * FROM accounts WHERE (accounts.id = 1)which DB2 accepts.
ActiveRecord defines a number of abstract data types. These and the corresponding Ruby data types are listed in the table below. Which native database types the abstract types map to will depend to some extent on the underlying ODBC driver used by the OpenLink Rails ODBC Adapter. The native type mappings for the OpenLink DB2 agents/Lite drivers are shown below.
|Active Record abstract type||Ruby type||DB2 type|
|:primary_key||-||INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 10000) PRIMARY KEY|
|:boolean||Object||SMALLINT (DECIMAL(1) if :emulate_boolean connection option is true)|
It is suggested you use these native database types when creating tables to support your Rails models. Example table definitions are attached below:
- db2.sql These are the table definitions used for creating fixtures during testing - similar fixture definitions can be found for other databases in the ActiveRecord sources under active-record-root/test/fixtures/db_definitions
The adapter defines primary keys as
column_name INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 10000) PRIMARY KEYso that auto-generated primary key values start at 10000. This is done to ensure that rows inserted with an explicit primary key value do not collide with rows for which the primary key was generated during the insert. Although inserts using an explicit primary key value are supported, they are not recommended. You should instead allow the database to generate the primary key value for you. The identity offset is also required to support some of the tests in the ActiveRecord test suite which do perform explicit inserts.
The ActiveRecord types :text and :binary are mapped to
the DB2 types CLOB and BLOB respectively, with a maximum
length of 1MB. Neither type mapping includes a ':limit
=> xxx' option, consequently CLOB and BLOB columns are
created without a length specifier.
A BLOB column is defined as
- colname BLOB instead of
- colname BLOB (integer [K | M | G])
When the length specifier is omitted, the maximum length of DB2 LOB columns defaults to 1MB.
Because of limitations imposed by DB2 SQL, there are several ActiveRecord methods which cannot be implemented. The affected methods all relate to migrations. They are:
- rename_column The native DB2 rails adapter also does not implement these methods (see http://www.db2onrails.com/pages/migrations)
Although the Rails ODBC Adapter does support migrations against DB2, obviously the above methods cannot be used. Consequently, in the ActiveRecord test suite, many of the tests in migration_test.rb fail. Several tests add columns to a table. The tests then attempt to remove these columns during the 'teardown' phase after each test completes. Because remove_column is not supported against DB2, the added columns are not removed and subsequent tests may return a 'duplicate column name' error when attempting to add columns of the same name.