Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

What is an integrated database?

The integrated database feature on the AS/400 is often taken for granted. Comparisons of the IBM DB2 against SQL for performance, cost, and support are provided. The results may surprise you.

And why should I care? For most AS/400 programmers -- and users -- the integrated database feature is taken for granted. For the world outside of i, it's likely the most misunderstood.

The fact that DB2 is integrated into the core of the i platform is a historical artifact. I can go back to the days of the System/3 when data was stored on disk packs and even then the database was integrated into the operating system. RPG programmers have always known where their data was stored, and that's a luxury compared to most other machines in which the location of data is more mysterious.

Let me take you on a ride on the database decision train -- a ride we i developers never really have to take.

Other platforms aren't integrated?
That's right. No other platform has a full-featured relational database shipped as part of the base operating system. The choice of database is wildly divergent on other platforms. In fact, unless you're a Microsoft development shop and using Microsoft SQL Server, there isn't even a consensus standard database for most platforms.

While the number of large databases has dwindled over the years to the "big three" (DB2, SQL Server and Oracle), the number of small and special purpose databases is getting larger every day. These databases often cost additional money, sometimes a lot of money. Database software has long been a profitable niche; one of the most popular programs in the early PC market was dBase.

Money, Money, Money: When choosing a database, one of your first decisions is going to be about money. How much are you willing to spend for your database? Free databases exist, but remember the maxim: software isn't free, you are. In this context, if you get a free database package, it will be up to you to install, configure and and administer it. I'll expand on that a little bit later when I talk about MySQL for the i.

If you don't go free, expect to spend hundreds or even thousands for a small PC database, or tens of thousands for something larger. The larger databases are opting for "per-user" pricing. Oracle, for example, charges about a thousand dollars a user for its enterprise edition.

Features: You can't choose a database until you compare the features. And here things start to get interesting. While most larger databases support at least some level of SQL access, determining support for other features is a little more complex.

For example, do you want security with that database? How about auditing? And then we get into the more esoteric issues, like number of CPUs or maximum amount of memory -- never an issue with our beloved DB2 for the i. Often these sorts of capabilities, if available at all, come with additional fees. The features I just mentioned are all available in Oracle, but as add-on options. So now you have to decide which of these features you might need before doing your pricing.

With an integrated database, you don't have any of those headaches. The built-in journaling capabilities of DB2 provide auditing details that go beyond the capabilities of all but the largest commercial database. Because DB2 is integrated with the operating system, you've got role-based security from the get-go. The list goes on.

Development support: Next, you have to decide how you're going to access your database. Various databases have hooks for various programming languages. The likelihood of a given hook existing depends on the database and the language. For example, most databases have Java hooks -- typically these are Java database connectivity (JDBC) drivers, and most of them allow direct access to the database from other machines.

Other languages are not so fortunate. For example, while PHP has better than average database support, you use different functions to access different databases, i.e., mssql_connect vs. mysql_connect to access Microsoft SQL Server and MySQL respectively. As you can guess, coding database-independent applications requires some serious creativity.

We don't worry about those things in RPG, because a CHAIN in RPG goes to a DB2 file, and the code is optimized for access.

Application selection: Another important issue when it comes to standalone databases is that fact that your database selection affects your application selection and vice versa. For example, most applications support MySQL, and of course anything that has to do with Windows will know about Microsoft SQL Server. But what if you find something slightly off the beaten path that does what you need? Let's say, for instance, that you want to use PostgreSQL. Are you sure your application will support it? What about future applications? Should you just pick one of the most popular databases and say to heck with features and/or price? With an integrated database, that's not an issue. If a product runs on the platform, it uses the database -- some applications may choose to use a database other than the one integrated into the platform, but that's a very rare situation.

Ongoing support
Even after you've selected your database, you still have to then live with that selection. The differences between integrated and standalone databases will be felt on a daily basis.

Installation and configuration: I've used MySQL on PCs over the years, and it's been a stable and professional quality database. However, in order to further my research on databases I installed MySQL on my V5R4 machine here. I wanted to know exactly what the issues were. And while the installation was perhaps as smooth as any installation could be, it wasn't without some work.

While there is some level of configuration for DB2, it's nothing compared with the setup for a standalone database. While most of them will run out of the box, the fact is that minimally you will need to lock down "root" access and then enable access for other users. Typically there is some time spent in the command line of the database -- in MySQL, that's the mysqladmin application that runs in the PASE shell. While typically not an extremely difficult operation and often done with the help of a "getting started" guide, this type of setup typically requires you to understand a completely new command syntax.

A special note on security: With an integrated database your security is also integrated. That is, the user profiles that are used to sign on for application access are the same as those used to secure your database. That is not the case with a standalone database. For example, with MySQL you will have to set up individual users and passwords and use those users and passwords in your applications. Mapping an existing user profile on the i to the corresponding one on MySQL is not a trivial exercise (the password handling is particularly difficult).

Backup and recovery: Most databases have some level of backup and recovery, but it's typically going to be a completely different process than, for example, saving your program objects. With the i, everything is an object and backups and restores don't have to differentiate between the various pieces of an application. The increasing sophistication of the base save capabilities -- features such SAVCHGOBJ and save-while-active -- make it very easy to integrate your data backups along with your normal save activities.

Performance: I've been doing some initial testing on performance. In order to try and compare apples to apples, I created a little program that accesses both DB2 and MySQL databases on my i via JDBC calls from a PC. I'm using the latest drivers from MySQL and from IBM and the Java 6 JVM, and I expected DB2 to handily defeat the MySQL calls.

Much to my surprise, in a very unscientific initial testing it turns out that MySQL beats DB2. Now, this is for a JDBC interface over a network connection and the difference isn't huge, but it's not insignificant, either. In every test I ran for simple SELECT statements MySQL generally took about 20-40% less time than the corresponding DB2 statement. There are some other variables in play here, such as using DDL vs. DDS to create the database, and of course I have to re-run all the tests with the Java class running on the i itself, but the fact that MySQL is on a par with DB2 (or better!) means that applications based on MySQL may well perform acceptably on the i.

This brings me to my next set of tests. In the very near future I plan to do a more stringent test of MySQL vs. DB2 in a variety of settings. While I'm pretty sure MySQL will hold its own in any Java-based test, I'm betting that native RPG access will blow it away. Also, I plan on installing and running a Java-based MySQL application (probably an open source forum) on my V5R4 machine, just to see how the i performs. I'll install the same application on one or two workstations to have a little baseline comparison. If you're interested in those sorts of comparisons, let us know and we'll try to report on them here.

It's interesting. I'm always a big believer in the best tool for the job, and while DB2 has it all over MySQL from a standpoint of enterprise integration, if MySQL can perform well, it may just be a good supplement to DB2 for specific tasks.

ABOUT THE AUTHOR: Joe Pluta is the founder and chief architect of Pluta Brothers Design Inc. Joe uses the Rational tool suite extensively, and is on the beta teams for most of its i-related products. He performs onsite mentoring and speaks at user groups around the country. Joe can be reached at joepluta@plutabrothers.com.

Dig Deeper on DB2 UDB (universal databases)