I’d like to take a moment to provide a small status update on what’s been going on in the DB2 on Rails world. The Alphaworks starter toolkit hasn’t been update for a long time, so someone may wrongly assume that the project is not maintained, but nothing could be further from the truth. The toolkit will be updated in order to provide a Windows one click install alternative sometime in the near future, but the real action these days has moved to Rubyforge.
The development and improvement of the IBM_DB Ruby driver and ActiveRecord adapter is considered a priority and there is continuos work carried out by the API development team. In fact, another important update for the ibm_db gem was released just yesterday. The gem makes the installation and upgrade process very straightforward on both Windows and Linux. If you’ve already installed a previous version through RubyGems, all you have to do in order to upgrade to the current 0.9.4 version, is run: gem update (with sudo if on Linux/Unix).
The new gem fixes a few bugs and, more interestingly, adds support for XML columns in Rails 2 style migrations. This means that you can define tables that take advantage of pureXML, with the new “sexy migration” syntax. Here is an example:
class CreateCustomers < ActiveRecord::Migration
def self.up
create_table :customers do |t|
t.string :name
t.xml :info
t.timestamps
end
end
def self.down
drop_table :customers
end
end
Concisely, the main message is: DB2 works with Rails 2, it’s supported by IBM and there are many customers who are successfully using it for their projects.
You may not be able to use minor conveniences (like rake db:create), given that these aren’t implemented for DB2 by the Rails team, but that won’t affect your ability to use DB2 on Rails. That said, on a side note, I started working on a Rails plugin called DB2 Extended in order to simplify the development process for you, and add DB2 specific functionalities to the framework. It’s a personal project, not an IBM one, and it’s still in its infancy, but feel free to take advantage of it and contribute with your own patches.
It’s all about you
The API team efforts would be wasted if they weren’t focused on what really interests you. It is therefore important that you let us know about your experiences with the DB2 on Rails stack, send us your feedback, be it encouragingly kind words or frustrated ones.
If you discover a bug, it’s crucial that you log a ticket on Rubyforge. And if you are unsure about it being an actual bug please don’t be shy, and feel free to start a discussion thread on the fora. We thank you in advance for your partecipation.
![[Slashdot]](http://slashdot.org/favicon.ico)
![[Digg]](http://digg.com/favicon.ico)
![[Reddit]](http://reddit.com/favicon.ico)
![[del.icio.us]](http://del.icio.us/favicon.ico)
![[Facebook]](http://www.facebook.com/favicon.ico)
![[Technorati]](http://technorati.com/favicon.ico)
![[Google]](http://www.google.com/favicon.ico)
![[StumbleUpon]](http://www.stumbleupon.com/favicon.ico)
Posted by Antonio Cangiano | May 02 2008 | Announcements | No Comments »
Just in time for halloween, we released a new version of DB2. If you are in to instant gratification, you can get DB2 Express-C v9.5 immediately from http://www.ibm.com/db2/express . I am not going to provide a detailed description of what is new in this version but I will say that if you were impressed with DB2 pureXML before you will be absolutely amazed what you can do with XML in DB2 v9.5 and DB2 Express-C includes pureXML at no charge, naturally. Oh, and it is all available to a Ruby on Rails programmer.
If you like running your database servers on Linux then you will really like DB2 Express-C v9.5. DB2 engine on Linux now uses threaded (as opposed to process) model of execution. This means that your database server will now use a lot less memory and you will be doing a lot less figuring out how to set it up for optimum memory utilization. In v9.5 you can replace a dozen or so memory tuning parameters with a single one. While threaded engine is new to Linux, DB2 for Windows has always been threaded. Less time to tune database server means more time to spend writing really cool Ruby on Rails applications with DB2. Oh, and I almost forgot … you no longer have to be root to install DB2 on Linux.
If you like counting money (and who doesn’t), there is a new datatype which is really good. It is called DECFLOAT and it allows you to do money arithmetic without loosing any of it in rounding. The really cool thing is that if you run DB2 Express-C on Linux on POWER 6servers (System p or System i) you have hardware assist right in silicone that makes these operations blazingly fast. We will have to wait for Intel and AMD to put decimal arithmetic in to their silicone. Intel, AMD, are you listening?
If you are building applications that deal with credit card data (here we go with the money again), or need to comply with privacy legislation or need to do anything to identify the actual originator of a transaction and record all actions performed in an audit log then you are also in luck with DB2 v9.5. Let’s say you are building a online store application and will want to accept credit cards. You may not know it yet, but your application will have to comply with something called PCI DSS. An whether you know it or not, if you can’t prove that you comply, your merchant account will be revoked so unless you figured out a way of how to take cash on the web, you will be out of business. I am not going to describe the exact details of how DB2 helps you comply with PCI but I say that being able to identify the end user completing a transaction rather then some proxy id you are using to connect from your code in to the database is the key component of it. Another important aspect is beign able to record everything that is going on in an audit log that can be reviewed and shown to the auditor when asked. Oh, and DB2 can encrypt data both in database and on the wire but believe it or not, the first two features I talked about are actually more important for achieving compliance and staying in business. And these features apply to more then just PCI DSS. The same requirements exist for complying with privacy and corporate governance legislation.
There are also a lot of new functions that make DB2 run faster and make it more scalable like optimistic locking. Then there is a raft of functions that make it easier to manage, back up and recover if need be. There is also a brand new and, you guessed it, free IBM Data Studio for building database objects, working with XML and creating web services (both SOAP and RESTfull) but I will save these topics for another post.
Posted by Leon Katsnelson | November 01 2007 | Announcements and Uncategorized | 2 Comments »
DB2 on Rails provides the developer with many ways to retrieve information about errors which have occurred. Rails developers can take a look at the logs within the log folder in order to read the SQL errors returned by the datasever. But what about simple Ruby scripts? Let’s try to connect to DB2 with the wrong user credentials:
conn = IBM_DB::connect("mydb","myuser","my_wrong_pass")
#=> false
false. That doesn’t really help us too much. We know that something went wrong, but we don’t exactly know what. Luckily for us, the IBM_DB driver gives us all the tools that we need to properly troubleshoot problems. The example above can be rewritten in this way:
if conn = IBM_DB::connect("mydb","myuser","my_wrong_pass")
# do something interesting
else
# conn is false
raise IBM_DB::conn_errormsg
end
#=> RuntimeError: [IBM][CLI Driver] SQL30082N Security processing failed with reason
“24″ (”USERNAME AND/OR PASSWORD INVALID”). SQLSTATE=08001 SQLCODE=-30082
Okay, that should have us covered when it comes to failed attempts to connect to the database, but what about failed queries? Analogously, the IBM_DB driver provides us with the IBM_DB::stmt_errormsg method:
if stmt = IBM_DB::exec(conn, "SELECT * FROM WRONG_TABLE)
# process the results
else
# stmt is false
raise IBM_DB::stmt_errormsg
end
#=> RuntimeError: => [IBM][CLI Driver][DB2/NT] SQL0204N " DB2ADMIN.WRONG_TABLE" is
an undefined name. SQLSTATE=42704 SQLCODE=-204
DB2 error messages are usually easy to understand and with a bit of help from the DB2 Information Center you should be able to get out of trouble most of the time.
DB2 CLI Tracing
Despite the helpful error messages, there are situations in which troubleshooting can be hard because everything seems to be in the right place but your application is still acting up for some reason. The ultimate tool when it comes to troubleshooting for DB2 and Ruby/Rails application problems is to enable DB2 CLI (Call Level Interface) tracing. The CLI trace captures information about applications that access the DB2 CLI driver. Tracing gives you the ability to analyze low level calls to the C driver API with details on the input and output, to and from the database. The resulting trace is not particularly easy to understand for a DB2 novice, but it can offer a microscopic view which is invaluable for understanding problems that are hidden by the abstractions of higher level APIs, and allow you to see to a certain degree what’s happening under the hood.
The two free PDFs, Call Level Interface Guide and Reference, Volume 1 and Volume 2 are the best references if you need to look up calls in your CLI trace files. Instructions on how to enable CLI level tracing for DB2 Express-C LUW (Linux/Unix/Windows) can be found here.
Posted by Antonio Cangiano | October 19 2007 | How-to and Tips&Tricks | 5 Comments »
With an increasing number of Rails developers adopting DB2 as their database of choice, and the welcoming approach towards suggestions and requests on our end, it comes as no surprise that the feedback is rolling in faster than ever. We are very pleased about this, and in this short article I’d love to address two of the most requested features.
rake db:test:purge
Running rake db:test:purge generates the following error:
rake aborted!
Task not supported by 'ibm_db'
db:test:purge is a rake task defined in the Rails gem within the file rails-1.2.3/lib/tasks/databases.rake. This file takes care of, amongst other things, defining the specific behavior for each database adapter known to rails, when the user requests a database purge. It is essentially a big case statement with specific ruby code which drops all the user objects from the supported database.
ibm_db is currently shipped independently from Rails, therefore it is missing from that case statement, and as a result the task will appear to not be supported by ibm_db.
If you are using the ibm_db adapter for DB2, you don’t want to miss the opportunity to use rake db:test:purge and take advantage of other tasks that rely on this in order to work. What you can do is, manually insert a snippet of code for the ibm_db case in databases.rake.
An easy and straightforward implementation of this would be dropping the database and recreating it from scratch. I’m not allowed into the other adapters implementations, but I assume this could be what some of the other adapters may be doing. I believe this is not the right thing to do with DB2 though. In fact, this would have two main drawbacks. Firstly, creating a database every time, implies that the task becomes quite slow, because the creation of a database in DB2 is a “magical process” that can take up to a minute (for good reason, and that minute can save you lots of money in the years to come as you use the database). Secondly, the database that you have created in the first place may have many options and parameters configured, and collecting them all and reapplying them may not be the easiest or the smartest thing to do.
A different approach would be to handle this the right way, by dropping all the user schemas and the objects contained within the database. The code needs to be placed within the case statement we mentioned above:
desc “Empty the test database“
task :purge => :environment do
abcs = ActiveRecord::Base.configurations
case abcs[”test“][”adapter“]
end
end
With the current (Rails 1.2.3) databases.rake you can practically just copy and paste the following code at line 145 in the file:
when “ibm_db“
ActiveRecord::Base.establish_connection(:test)
conn = ActiveRecord::Base.connection.connection
begin
ActiveRecord::Base.connection.execute(”CREATE TABLESPACE SYSTOOLSPACE“)
systool_existing = false
rescue
systool_existing = true
end
user_schemas_sql = “SELECT SCHEMANAME FROM SYSCAT.SCHEMATA WHERE DEFINER <> ‘SYSIBM’ AND
SCHEMANAME NOT IN (’NULLID’, ‘ERRORSCHEMA’, ‘SYSTOOLS’)“
schemas = ActiveRecord::Base.connection.select_all(user_schemas_sql)
unless schemas.empty?
errortabschema = ‘ERRORSCHEMA‘
errortab = ‘ERRORTABLE‘
schemas.each do |schema|
schema_name = schema[”schemaname“].strip.upcase
sql = “CALL SYSPROC.ADMIN_DROP_SCHEMA(’#{schema_name}‘, NULL, ?, ?)“
stmt = IBM_DB::prepare(conn, sql)
IBM_DB::bind_param(stmt, 1, “errortabschema“, IBM_DB::SQL_PARAM_INPUT)
IBM_DB::bind_param(stmt, 2, “errortab“, IBM_DB::SQL_PARAM_INPUT)
IBM_DB::execute(stmt)
end
ActiveRecord::Base.connection.execute(”DROP TABLESPACE SYSTOOLSPACE“) unless systool_existing
ActiveRecord::Base.connection.execute(”DROP SCHEMA ERRORSCHEMA RESTRICT“)
end
This can also be used do define a method on its own (e.g. purge_database(:mydb)), should you require that functionality somewhere in your code. In that case, just make sure to modify the first lines in order to use the already established connection or define your own.
rake db:test:clone
Now that rake db:test:purge is working, you will be able to successfully run rake db:test:clone. Out of the box, there are two limitations though. Rails doesn’t acknowledge tablespaces and foreign keys (the lack of the first is understandable as it is strongly related to DB2, but the lack of the second is much less justifiable).
This doesn’t affect many developers but if it does affect you, it is both annoying and problematic. Suppose in fact, that you have created the database objects in your development db through migrations. You may have specified a certain tablespace name by passing :options => “IN mytablespace” to the create_table method. Running rake db:test:clone will generate the table in the test database within the default tablespace (USERSPACE1) rather than in the one that you’ve specified for the development database. Not only this, but if you manually defined foreign keys by executing sql statements in the migrations or directly, these will not appear in the cloned test database.
This is not an issue specific to DB2, it is just the way it works in Rails at the moment. In fact there are third party plugins that attempt to introduce these and other features that are shortcoming of Rails’ core (e.g. Foreign Key Schema Dumper Plugin for MySQL and PostgreSQL).
In order to address those two concerns when using DB2, you don’t have to operate on the definition of the task directly. In fact, the db:test:clone loads the dumped schema in the test environment. The culprit is therefore ActiveRecord SchemaDumper which doesn’t know anything about DB2 Tablespaces and about foreign keys. Changes to this will also affect the db:schema:dump task, which will in turn produce more correct and “database aware” db\schema.rb files.
The file schema_dumper.rb within the ActiveRecord gem (activerecord-1.15.3\lib\active_record\schema_dumper.rb) can be directly modified for your specific needs. At line 21 the dump method becomes:
def dump(stream)
header(stream)
tables(stream)
if @connection.adapter_name == “IBM_DB“
foreign_keys(stream)
end
trailer(stream)
stream
end
At line 89 within the table method, we need to specify code to handle the possibility of a non-default tablespace:
if @connection.adapter_name == “IBM_DB“
tbspace_sql = “select TBSPACE from syscat.tables where tabname=’#{table.upcase}‘“
table_space = @connection.select_one(tbspace_sql)[”tbspace“]
if table_space != “USERSPACE1“
tbl.print %Q(, :options => “IN #{table_space}“)
end
end
At this point, just after the index method, we need to define the method foreign_keys:
def foreign_keys(stream)
references = @connection.select_all(”SELECT * FROM SYSCAT.REFERENCES“)
for reference in references
constraint = reference[”constname“]
schema = reference[”tabschema“]
table = reference[”tabname“]
cols = reference[”fk_colnames“]
ref_schema = reference[”reftabschema“]
ref_table = reference[”reftabname“]
ref_cols = reference[”pk_colnames“]
if reference[”updaterule“] == “R“
update_action = “RESTRICT“
else
update_action = “NO ACTION“
end
delete_action = case reference[”deleterule“]
when “A“
“NO ACTION“
when “R“
“RESTRICT“
when “C“
“CASCADE“
when “N“
“SET NULL“
end
foreign_key_sql = “ execute(“ALTER TABLE #{schema}.#{table} add
CONSTRAINT #{constraint} FOREIGN KEY (#{cols.strip})n
REFERENCES #{ref_schema}.#{ref_table} (#{ref_cols.strip})
ON UPDATE #{update_action} ON DELETE #{delete_action}n
ENFORCED ENABLE QUERY OPTIMIZATION“)n “
stream.print foreign_key_sql
stream.puts
end
end
As you can imagine, it is possible to prevent the need to modify the file directly, by simply extending the SchemaDumper class in, for example, a plugin. You would have to overwrite the original methods within the SchemaDumper class:
module ActiveRecord
class SchemaDumper
def dump(stream)
end
private
def table(table, stream)
end
def foreign_keys(stream)
end
end
end
It would be beneficial to aggregate several improvements in a “DB2 PowerPack” plugin of some sort, and it’s very likely that we will eventually work on publishing something like this.
Posted by Antonio Cangiano | June 20 2007 | How-to | 1 Comment »
Next »
on Rails