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.