Rails migration and CREATE UNIQUE INDEX not working on DashDB / DB2

Question

I'm working on Rails 4.2.7 application on IBM Bluemix using Ruby 2.3.0. When I've installed Devise User model migration has been generated, just a standard file, no fancy options. During the deploy the migration is firing up three queries. First one for users table creation:

CREATE TABLE users (
 id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) PRIMARY KEY, 
 email varchar(255) DEFAULT '' NOT NULL, 
 encrypted_password varchar(255) DEFAULT '' NOT NULL, 
 reset_password_token varchar(255),  
 reset_password_sent_at timestamp,  
 remember_created_at timestamp,  
 sign_in_count integer DEFAULT 0 NOT NULL,  
 current_sign_in_at timestamp,  
 last_sign_in_at timestamp,  
 current_sign_in_ip varchar(255),  
 last_sign_in_ip varchar(255),  
 created_at timestamp NOT NULL,  
 updated_at timestamp NOT NULL)

And two for unique index creation:

CREATE UNIQUE INDEX index_users_on_email ON USERS(email)
CREATE UNIQUE INDEX index_users_on_reset_password_token ON USERS(reset_password_token)

First statement runs fine, the table is being created. However, the problem is that the table type is created with organize by column by default. CREATE UNIQUE INDEX statements were failing and it has appeared that to run them, the table has to be organized by row.

I can remove the migration and create the table directly in the DB via SQL statement and adding organize by row clause at the end and then run two remaining queries.

The problem is that I've encountered the same issue when migration wanted to run two below queries it did fail again:

CREATE TABLE schema_migrations (version varchar(255) NOT NULL)
CREATE UNIQUE INDEX unique_schema_migrations ON schema_migrations (version)

I think I can keep bumping on the same problem again and so I would like to find a way to make all tables organized by row by default, preferably with the migration. Has anyone encountered this issue before?

My manifest.yml looks following:

applications:
- path: .
  buildpack: https://github.com/cloudfoundry/ruby-buildpack.git
  memory: 1024M
  instances: 1
  domain: eu-gb.mybluemix.net
  name: windykacja
  host: windykacja
  disk_quota: 1024M
  services:
  - dashDB-win

and here is my gemfile:

source 'https://rubygems.org'
ruby '2.3.0'

gem 'rails', '4.2.7'
gem 'sass-rails', '~> 5.0'
gem 'uglifier', '>= 1.3.0'
gem 'coffee-rails', '~> 4.1.0'
gem 'devise'
gem 'bootstrap-sass', '~> 3.3.6'
gem 'jquery-rails'
gem 'turbolinks'
gem 'jbuilder', '~> 2.0'
gem 'sdoc', '~> 0.4.0', group: :doc


group :production do
    gem 'rails_12factor'
    gem 'ibm_db'
end

group :development, :test do
  gem 'byebug'
end

group :development do
  gem 'web-console', '~> 2.0'
  gem 'spring'
  gem 'sqlite3'
end

Answer 1

There is a database configuration parameter dft_table_org that can help. You will have to use the DB2 command-line processor or CLPPlus to issue the command:

update db cfg for <your-db-name> using dft_table_org row

Note that depending on the type of the dashDB service you have, you may not have sufficient authority to execute this command.

On the other hand, since you are using dashDB, presumably you plan to benefit from the column store performance, and it does not use indexes (except for enforcing uniqueness), so an alternative would be, instead of creating unique indexes, add unique constraints to the table, which are supported for column-organized tables as well.

ALTER TABLE users ADD CONSTRAINT u_users_on_email UNIQUE (email)

etc. Interestingly, DB2 will still create unique indexes to back those constraints, but they will not be used for data access in column-organied tables.