Using the OpenLink Rails ODBC Adapter with IBM DB/2

Table of Contents

Configuration

To obtain the correct type mappings (detailed below), ensure LongDataCompat is set to 1 in the file db2cli.ini:
e.g.

[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.

Type Mappings

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
:string	String	VARCHAR(255)
:boolean	Object	SMALLINT (DECIMAL(1) if :emulate_boolean connection option is true)
:datetime	Time	TIMESTAMP
:time	Time	TIME
:timestamp	Time	TIMESTAMP
:date	Date	DATE
:text	String	CLOB
:integer	Fixnum	INTEGER
:float	Float	DOUBLE
:binary	String	BLOB
:decimal	BigDecimal	NUMERIC

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

Auto-Generated Primary Key Offset

The adapter defines primary keys as

• column_name INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 10000) PRIMARY KEY so 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.

CLOB/BLOB Size Limits

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.

e.g.
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.

Migration Restrictions

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:

• change_column
• remove_column
• 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.