db2 table with composite pk and no sequence or identity column

[holgum]

I am trying to use the composite_primary_keys gem with arjdbc and db2. We had been working w/ oracle using oracle-enhanced-adapter and cpk gem, but now have to switch to DB2 (don't ask).

The problem we run into is when trying to insert into a DB2 table with a composite primary key and no identity column or sequence. The arjdbc adapter for db2 always seems to want to find a sequence even though there is none (as would be expected when working with a cpk table).

Exception:

ActiveRecord::StatementInvalid: ActiveRecord::JDBCError: com.ibm.db2.jcc.am.vo: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=.;LUE FOR
FROM sysibm;FROM, DRIVER=4.8.87: 
SELECT NEXT VALUE FOR FROM sysibm.sysdummy1
from arjdbc/jdbc/RubyJdbcConnection.java:838
:in execute_query' from c:/bin/jruby-1.7.12/lib/ruby/gems/shared/gems/activerecord-jdbc-adapter-1.3.7/lib/arjdbc/jdbc/adapter.rb:431
    ...
select_value' from c:/bin/jruby-1.7.12/lib/ruby/gems/shared/gems/activerecord-jdbc-adapter-1.3.7/lib/arjdbc/db2/adapter.rb:187
:in next_sequence_value'
from c:/bin/jruby-1.7.12/lib/ruby/gems/shared/gems/activerecord-4.0.5/lib/active_record/relation.rb:51
:in insert' from c:/bin/jruby-1.7.12/lib/ruby/gems/shared/gems/composite_primary_keys-6.0.6/lib/composite_primary_keys/active_record_overides.rb:17 
:in create_record'

This is with:

• rails 4.0.5
• activerecord-jdbc-adapter 1.3.7
• composite_primary_keys 6.0.0

But also tried with Rails 3.2.18, Rails 4.1 combinations. Haven't looked under the covers enough to know if this is something that the CPK gem should be overriding or if the db2 support in arjdbc is making assumptions it maybe shouldn't.

I posted an issue in the cpk project (https://github.com/composite-primary-keys/composite_primary_keys/issues/212) but I thought someone here might weigh in on solution or work-around.

--- Want to back this issue? **[Post a bounty on it!](https://www.bountysource.com/issues/2642011-db2-table-with-composite-pk-and-no-sequence-or-identity-column?utm_campaign=plugin&utm_content=tracker%2F136963&utm_medium=issues&utm_source=github)** We accept bounties via [Bountysource](https://www.bountysource.com/?utm_campaign=plugin&utm_content=tracker%2F136963&utm_medium=issues&utm_source=github).

[holgum]

Poking through the code some more, ArJdbc assumes that a DB2 table will always have an Identity column or a sequence. Since a table with composite primary key will have neither, it blows up trying to find a nil sequence.

I am able to get inserts on CPK tables with CPK gem working on DB2 with the following monkey patch to ArJdbc:

ArJdbc.load_java_part :DB2
require 'arjdbc/db2/column'
require 'arjdbc/db2/adapter'

ArJdbc::DB2.module_eval do  
    alias_method :orig_prefetch_primary_key?, :prefetch_primary_key?

    def prefetch_primary_key?(table_name = nil)

      # check for composite primary key - if found, return false to avoid the sequence lookup
      if primary_keys(table_name).size > 1
        return false
      end
      return orig_prefetch_primary_key?(table_name)  # TRUE if table has NO identity column.

    end
end