Mission Data Blog
Louisville-based Web Development & Software Engineering

Our project has entered the stage where we start tuning the data access to improve performance. This project uses Hibernate to access a AS400/iSeries system via the JT400 JDBC driver. My prior experience using the SQL Server
database introduced me to Microsoft’s Query Analyzer, which is a nice tool for identifying where you need to index the database. A bit of searching turned up an IBM tool called Visual Explain that fits the same need. The sysadmin started the profiling tool against my connection, and I ran through a standard usage scenario in our application. Visual Explain showed each query, how long it took, the access strategy, and recommended indexes. This kind of tool is absolutely essential for database performance tuning.

The second important item we learned from this process is the performance difference between a true SQL index on the iSeries and a keyed logical index. From iSeries V4R2 on, SQL indexes have page sizes of 64k. By comparison, a DDS logical will max out at 32k, and will typically be much smaller. The performance is so much better that our customer is converting all all their DDS logical indexes to SQL, even if only their legacy RPG applications use it. This IBM document has a good discussion of this issue.

iSeries systems and Ruby are separate universes right now; I know of no native Ruby way to connect to an AS400 system. However, with the help of Java we can bridge the gap.

Probably the simplest example is connecting to an iSeries system using JDBC to select from some tables, and with the right libraries it is straightforward. It’s also fun to be doing some nifty quick Ruby but with some preexisting and proven Java libraries.

Also, if you use Java and Ruby, definitely put arton’s RubyJavaBridge in your pocket; you’ll end up using it more than once.

What you’ll need:

• Ruby (well yea)
• Java (ok)
• access to an AS400 system (that’s the whole point here…)
  For testing, I’ve been using a free account available from Holger Scherer Software und Beratung.
• jt400.jar jdbc drivers from JTOpen, a great open-source Java library for AS400 access.
• RubyJavaBridge is the keystone for this.

Installing the software is the hardest part of this exercise, but it’s more tedium than anything. Once you’ve got it all downloaded and running the fun can begin.

(more…)