database.yml

Configuring database.yml for IBM DB2

In order to connect a Rails application to any database, you need to specify the adapter that you intend to use and the credentials in the config\database.yml file. Let’s see how to do this for IBM DB2.

Prerequisites

You will need to have the IBM DB2 driver and adapter installed on your system (if you don’t, run gem install ibm_db or install them through the Starter Toolkit for DB2 on Rails).

Two types of connection

There are two type of connections available to suite your needs: through an Alias or through TCP/IP address. Typically the Alias based connection is used to connect to a local database, while the net address one is used for remote databases.

Alias based connection


development:
  adapter: ibm_db
  database: demo
  username: db2admin
  password: db2password
  schema: cangiano

Database represents the database alias DEMO. All the fields above are mandatory except for schema. If you don’t specify any schema, the adapter will use the username by default (you’re highly encouraged to specify the schema, in order to prevent issues due to the schema_info table being shared among applications).

TCP/IP connection


development:
  adapter: ibm_db
  database: demo
  username: db2admin
  password: db2password
  schema: cangiano
  host: myhostname
  port: 50000

‘host’ can be an hostname or an IP address.

Notes

Linux/Unix users will need to set the environment with the command: 

. /home/db2inst1/sqllib/db2profile

Please note that you need to specify ‘ibm_db’ and not ‘db2′ in order to use the IBM adapter.

If you have any questions, please join us at the Rubyforge forum.

[Slashdot] [Digg] [Reddit] [del.icio.us] [Facebook] [Technorati] [Google] [StumbleUpon]

Posted by Admin | October 15 2007 11:00 pm

2 Responses to “database.yml”

  1. Marco on 17 Mar 2008 at 12:16 pm #

    I’ve some troubles using schema parameter, i’ve got:
    [IBM][CLI Driver][DB2] SQL0567N “MYSCHEMA” non รจ un ID di autorizzazione valido. SQLSTATE=42503
    SQLCODE=-567

    I’ve installed DB2 connect 8.2 with fixpack 16, if i use find_by_sql and i specify the schema ONLY inside the query it works but doing so i lose all rails advantages

  2. Frank Kuepper on 29 May 2008 at 5:18 am #

    @Marco:

    I had a similar problem.

    In my case, I tried to connect to a legacy database on a z/OS hos, where the tables are organized in schemas, which do not correspond to an actual user-id.

    The ConnectionAdapter IBM_DB2_ZOS_8 issues a “set current sqlid”-statement in it’s set_schema-method, which ends up in sqlcode -567.

    I came up with a small hack to emulate the schema for the tables by bypassing “set current sqlid” and prefixing the table-names with the schema-name inside ActiveRecord. Here’s what I’ve done:

    require ‘ibm_db’
    module ActiveRecord
    module ConnectionAdapters
    class IBM_DB2_ZOS_8
    # ignored set schema due to racf problems; rather redirect to ActiveRecord::Base
    def set_schema(schema)
    ActiveRecord::Base.schema = schema
    end
    end
    # modify schema operations to cope with prefixed table-names
    class IBM_DBAdapter
    def unprefix_table_name(table_name)
    table_name.gsub(/[^.]*\./, “”)
    end
    alias columns_base columns
    def columns(table_name, name = nil)
    columns_base(unprefix_table_name(table_name), name)
    end
    end
    end
    class Base
    # prefix tables with schema names as all tables are schema qualified, but racf prevents
    # from using set current sqlid :-(
    class << self
    attr_accessor :schema
    alias set_table_name_base set_table_name
    def set_table_name (table_name)
    if ActiveRecord::Base.schema
    set_table_name_base “#{ActiveRecord::Base.schema}.#{table_name}”
    else
    set_table_name_base table_name
    end
    end
    end
    end
    end

    I put that in my environment.rb and now everything works fine - including setting different schemas for development, test and production via database.yml the pure Rails way :-)

    Two things to notice:

    1. Of course db migrations are not working this way. But for me that’s not an issue, as I’m not the owner of the database and not authorized to make changes.

    2. Depending on what connection you use, you might have to tweak a different ConnectionAdapter.

Trackback URI | Comments RSS

Leave a Reply