Ruby 1.8.6 Rails 1.2.3
Gem 0.9.2 ibm_db 0.7.0
DB2 LUW v8.2 FP14 (server)
DB2 Win v9 CC (FP2) (client)

I have a DB2 table w/ a non-PK GENERATED ALWAYS AS column, essentially a read-only column. The column is called FULL_NAME
(which is GENERATED ALWAYS as FIRST_NAME CONCAT ' ' CONCAT LAST_NAME).

If I drop into script/console and Find() a
row, change a column, then save(), the UPDATE SQL that is generated references FULL_NAME, which causes DB2 error:
[IBM][CLI Driver][DB2/LINUX] SQL0798N A value cannot be specified for column "FULL_NAME" which is defined
as GENERATED ALWAYS. SQLSTATE=428C9
SQLCODE=-798

Date: 2007-06-14 15:58
Sender: Alex Pitigoi

As mentioned in the initial forum answer the problem is caused
by having the ActiveRecord and IBM_DB adapter generating INSERT
statements that include generated non-identity columns with empty
values. While this will obviously fail with DB2 LUW, there are
a few important challenges in devising a fix:
 1. AR does not seem to have a concept of generated columns outside
the IDENTITY concept, therefore overriding the INSERT statement
generation becomes no longer trivial and will involve some intricate
customization
 2. GENERATED non-identity column attribute is a DB2 LUW concept
that is not found in DB2 for i5 or zOS, and it's probably better
to implement this in the application layer (i.e. after_create
is one AR feature that can help), which paradoxically will increase
portability.

That being said, the fix to this will be postponed for a while,
but here's a work-around in the meanwhile:  "remove_column
full_name" followed by "add_column full_name"
in a Rails migration, subsequently backed by an after_create
implementation in the associated model.

Here's in brief the migrations used to test this Rails-like
work-around:

# creating the legacy type table with non-identity generated
column
class CreatePeople < ActiveRecord::Migration
  def self.up
    create_table :people, :force => true do |t|
      t.column :first_name, :string
      t.column :last_name, :string
      t.column :full_name, :string
    end
    execute "SET INTEGRITY FOR PEOPLE OFF"
    execute "ALTER TABLE PEOPLE ALTER COLUMN FULL_NAME DROP
DEFAULT SET GENERATED AS ( FIRST_NAME CONCAT ' ' CONCAT LAST_NAME
)"
    execute "SET INTEGRITY FOR PEOPLE IMMEDIATE CHECKED
FORCE GENERATED"
  end

  def self.down
    drop_table :people
  end
end

# removing non-identity column constrain
class RemoveGeneratedCol < ActiveRecord::Migration
  def self.up
    remove_column :people, :full_name
    add_column    :people, :full_name, :string, :default =>
''
  end

  def self.down
    execute "SET INTEGRITY FOR PEOPLE OFF"
    execute "ALTER TABLE PEOPLE ALTER COLUMN FULL_NAME DROP
DEFAULT SET GENERATED AS ( FIRST_NAME CONCAT ' ' CONCAT LAST_NAME
)"
    execute "SET INTEGRITY FOR PEOPLE IMMEDIATE CHECKED
FORCE GENERATED"
  end
end

# implementing generated column constrain in the model
class Person < ActiveRecord::Base
  def after_create
    p = Person.find("#{id}")
    p.full_name = p.first_name + ' ' + p.last_name
    p.save
  end
end

# results in the Rails console:
> ruby script\console
Loading development environment.
>> newone = Person.new
=> #<Person:0x358ebb8 @new_record=true,
@attributes={"first_name"=>nil,
"full_name"=>"",
"last_name"=>nil}>
>> newone.first_name = 'Gill'
=> "Gill"
>> newone.last_name = 'Bates'
=> "Bates"
>> newone.save
=> true
>> Person.find 1
=> #<Person:0x34f254c @attributes={"id"=>1,
"first_name"=>"Gill",
"full_name"=>"Gill Bates",
"last_name"=>"Bates"}>