DB2 is the only database many of us have ever known.  This article shows you how you can broaden your database options.

If you're an i programmer, you've been using DB2 from the beginning of your career (unless, of course, you started like me back in the cave computer days of the System/3). You didn't know you were using DB2; in fact, you didn't know that your database had a name, and you didn't care because it just worked. That in fact was one of the great selling points of the platform: the integrated database and by extension the nearly bare-metal speed of compiled languages like RPG, which could take advantage of that database. Nowadays, though, a single standalone database like DB2 for the i isn't enough for many shops. Commercial databases like SQL Server and Oracle have found their way into many shops. A more recent phenomenon is the appearance of databases such as MySQL and PostgreSQL, especially as components of open-source packages.

Getting Started

You can find quite a bit of information on the product on the Web. You can start with IBM's MySQL page, although that's more of a press release-y kind of thing. If you're more interested in just getting going, jump immediately to the download page. This page has a number of options, including both SAVF and TAR packages for i5/OS. I'm using the 64-bit SAVF version for this project.

The prerequisites are pretty well spelled out, but let me emphasize two points. First, you need to have PASE installed. If you're like me, one of the diehards out there who doesn't use PASE, then you might need to install it. To see if you do, execute the command DSPSFWRSC from your command line and look for i5/OS option 33. On my V5R4 machine, it looks like this:

 5722SS1  33  5111  Portable App Solutions Environment

 5722SS1  33  2924  Portable App Solutions Environment

You could also check for the QPASE library or try to call QP2TERM from the command line (this starts the PASE environment, much the same way that STRQSH starts the QShell environment). After verifying that you have PASE, you may also want to get the porting toolkit, 5799PTL, which allows you to run Perl scripts inside of PASE.

Anyway, once you've got the essentials downloaded and/or installed, you can continue on the MySQL installation itself. The MySQL documentation for this phase is quite good.

What I'm Trying to Accomplish

Note that I'm doing this outside the bounds of the Zend PHP download. This article is not about PHP; that's a different issue entirely. I'm focusing entirely on getting MySQL up and running, because if I'm successful, I've opened my machines to an entire spectrum of Java applications that use MySQL. I call this the MJ stack: MySQL and Java. Note that this stack has only two elements. That's because unlike LAMP or WIMP, the MJ stack needs just two pieces and can run anywhere. Now that I have MySQL working, my next step will be to install a Java open-source application.

An intriguing point is that the MySQL folks have stated they will have a DB2 engine. In MySQL lingo, an "engine" is the method used to store and access the actual data (as opposed to the metadata--the definitions of the table's columns and their properties--which is stored separately). MySQL has a variety of database engines, ranging from in-memory tables for performance to ISAM for speed to something called InnoDB, which supports many of the advanced relational features such as row-level locking. It even has support for a CSV (comma-delimited) file, which could be an interesting option for exchanging data with other systems: to populate a CSV table, you could use SQL rather than the CPYTOIMPF command.

However, none of these options will create data that is natively accessible to ILE programs. The only way to access the data is via the MySQL engine itself, and that means using either Java or PHP. When MySQL supports DB2 as an engine, though, you will be able to read and write MySQL tables using native I/O operations from RPG and COBOL, which will provide a whole additional level of integration.

Installing MySQL

This isn't rocket science. You upload the savefile, create a user profile named MYSQL, restore the library from the savefile, and then execute the install command. There is some discrepancy between the documentation on the Web site and the actual command. The command wants to install into /usr/local and store its data under /QOpenSys, while the documentation seems to prefer storing things under /opt/mysql. It seems to be smart enough to handle whatever directory naming you prefer, and I personally am a fan of not using generic names, so I'm going to install into /opt/mysql (and /opt/mysqldata).

The base installation took about half an hour. I was able to then go into PASE (by calling qp2term from a command line) and start and stop the server, as well as secure the users and all that good stuff. One helpful hint: in the various instructions for SQL, you'll often see two sections, one for Windows and one for UNIX. Use the UNIX version, since, after all, you are running in PASE, which is an AIX (UNIX) environment.

Now What?

This is where it got interesting. I have this database installed, so now what do I do with it? Well, it's not like DB2, where I can write an RPG program and read the files. I can't even access the data via good old STRSQL. So what's a guy to do?

Well, in the world of non-integrated databases, the next step is to get a tool. Two primary options exist: find a GUI tool that works directly with the database in question or else get a JDBC driver for the tool and use a generic JDBC tool. MySQL has the pieces for both, so I decided to try each approach.

Using MySQL GUI Tools

The MySQL GUI Tools is a complete set of tools designed to access MySQL databases directly. MySQL is committed to multi-platform support, so you will see versions for not only Windows, but also various Linux distributions and even Mac OSX.

Note: In order to use the GUI tools, you will have to use the MySQL command line on the host to add a user with the address of your PC. As installed, MySQL talks only to the machine it is installed on. In my case, I executed the following commands, which enable access as root from IP address 10.10.10.101, with password "secret":

  grant all privileges on *.* to 'root'@'10.10.10.101';

  set password for 'root'@'10.10.10.101' = password('secret');

That done, I was able to use the GUI tools. I've only really gotten familiar with MySQL Administrator and MySQL Query browser. Together, I'd say they've got similar functionality to iSeries Navigator.  Administrator is a very neat utility that lets you access the database, as you can see in Figure 1.

Figure 1: This is the MySQL Administrator.

As you can see, this tool is for managing the database itself. It's interesting to note all the things that are required to manage a relational database that we simply take for granted. For example, user security is managed here, as are backups. These are things that are handled system-wide on the i.

The Query Browser, on the other hand, is much more the traditional Web query tool. It can be used to access data, edit data, run queries...very much the complement of STRSQL.

Figure 2: This is an example of the MySQL Query Browser.

In Figure 2, you actually see a couple of things. You see the INSERT statement I used to move the data from MYDATA to MYCSV, which I used to see if MySQL would convert relational data to comma-delimited data. It worked quite well, by the way, the only issue being that the data file is locked, which means some applications can't get at it. But I was able to copy it, look at it with the DSPF command, and even open it with one of the smarter text editors (NoteTab Pro, my personal favorite).

You'll also see the results of querying the resulting CSV file. Note that this doesn't  happen as a direct result of running the INSERT statement. I first ran the INSERT statement to copy the record from MYDATA to MYCSV, and then I ran a SELECT statement on MYCSV. However, the Query Browser has a nice browser-like history feature, and I used the Go Back button on the upper left of the tool to bring the INSERT statement up again for your viewing pleasure.

Figure 3: The Inline Help feature is quite extensive.

Another thing I like about the Query Browser is the help. SQL help is obviously abundant throughout the Internet, but the help in the Query Browser is very extensive. It goes far beyond the basics of syntax and delves deeply into the hows and whys of SQL statements, and especially explains how MySQL handles specific situations. An entire section, for example, goes into the topic of correlated subqueries and the exact mechanics of how MySQL processes them. This sort of detail really helps to remove some of the black magic that surrounds SQL in general. I think I'll be spending time in the Query Browser's help system even when I'm not using MySQL; it's as good a general SQL reference as many out there.

Integration with JDBC Tooling

A number of options exist to access a database via JDBC, including open-source tools such as Squirrel SQL. For this article, I'm going to focus on accessing the database through the Data perspective available in either Rational Business Developer (RBD) or Rational Developer for i for SOA (RDi-SOA).

To take advantage of the Data perspective, you'll need to add a connection for the database. First, you have to get the JDBC connector. Go to the MySQL Connector/J Web Page and download version 5.1. Get the .zip version and then extract the JAR file from it (the current version as of this writing is mysql-connector-java-5.1.6-bin.jar). Put it somewhere accessible as you'll be using it later to create your connection. In this example, I put it in a folder called MySQL on the C: drive.

Figure 4: You have to specify the JAR file, the class name, and the host information.

Now you bring up RDi-SOA and open the Data Perspective. In the Data Perspective, you create a new Connection and fill in the information shown in Figure 4 (if you're unfamiliar with the Data Perspective, you'll see it in Figure 6). Some things to note: I specified the database name as "test," which is the schema in MySQL where I created my test files. This schema is automatically created when you install MySQL, and it's a good place to use as a sandbox. The JDBC driver class name should always be the same: com.mysql.jdbc.Driver. The JAR file name may change depending on the version you download and the directory into which you extract the JAR file. The connection URL has three parts, the protocol (jdbc:mysql), the host IP address (the address of your System i), and the port number (3306). The protocol and port number will probably not change. Technically, you can change the port number, usually for security reasons, but that has to be done on the server. I've left it at the default for this article. Hit the Test Connection button, and you should see the dialog in Figure 5.

Figure 5: This indicates a successful connection.

Once you've done this, you can now start using the connection. The connection has some direct capabilities of accessing the data, as shown in Figure 6.

Figure 6: Use the Database Explorer to directly edit tables.

The Database Explorer view is an integral part of the Data Perspective; that's where you define the connections. Right-click on the Connections folder and select New Connection to create a new connection. Once the connection is created, you can then expand it as shown, drilling down into the schemas and tables. Right-click on a table and select Data > Edit, and you'll see an editable form such as the one shown.

Figure 7: You can also create a Data Design project to execute ad hoc scripts.

To execute more-complex statements, create a Data Design project. This will allow you to create SQL scripts, save them, and execute them as shown in Figure 7.

Integration with High-Level Languages

As I mentioned earlier, until MySQL supports DB2 as an engine, traditional ILE programs written in RPG and COBOL cannot access the MySQL data directly. Integration will require an intermediary language. Until the introduction of PHP, the only real option would have been Java. I'll let someone else handle the PHP side of things; in fact, if you go the PHP route, my guess is that Zend will be able to provide you with lots of MySQL support; Zend offers a bundle of PHP and MySQL.

But for those of us firmly rooted in the IBM/Rational/Java camp, the only logical choice is to use Java and JDBC. Many options exist for the architecture of a multi-language application. You can use a Java driver to invoke JDBC to read the data and pass it to RPG or COBOL using IBM's Java Toolbox to call the program, or you can use an RPG driver to invoke a JDBC class to access the data. Either way works. Pros and cons exist regarding performance and design complexity, and it's really a business decision as to which way you'll design your application. It should be noted that either of the previous design options requires a separate JVM for each job and therefore can be expensive, especially for interactive programs. Another option is to use a Java program that reads a data queue and then does requests to the JDBC driver and returns results. It's a more complicated solution, but it avoids the resource and startup issues of multiple JVMs.

Of course if you're using Java or EGL, you can invoke the JDBC driver directly from either of those languages. Here's an example Java snippet, ignoring all the boring error checking (seriously, JDBC programs need a lot of error checking, but that's a different topic for a different day):

  Class.forName("com.mysql.jdbc.Driver");

  con = DriverManager.getConnection("jdbc:mysql://10.10.10.10/test",

    "root", PASSWORD);

  Statement s = con.createStatement();

  ResultSet rs = s.executeQuery("SELECT * FROM MYDATA");

  while (rs.next())

    System.out.println(

      "KEY1: " + rs.getString(1) +

      ", DATA1: " + rs.getString(2));

You'll see a result like this:

  KEY1: A, DATA1: A1

  KEY1: B, DATA1: B1

Note that this example specifies both the host and the schema in the connection URL. I don't bother with the port number because I'm using the default. I could have left off the schema and used a qualified name (TEST.MYDATA) for the table. And if the Java program were running on the same machine as the MySQL driver, I'd use LOCALHOST or 127.0.0.1 as my host. This is all standard JDBC programming; the point is that MySQL on the i is completely accessible to standard Java programming techniques and thus available to other ILE languages through the mechanisms I specified earlier.

MySQL, Today and Tomorrow

I was going to run some performance tests, but to be honest, I'm not really that worried about it; it really would be comparing apples and oranges, since MySQL doesn't use DB2 as its underlying format. I may still do so if I get some free time, but I'm more interested in seeing how MySQL performs using DB2 tables.

It's clear, though, that MySQL is a nicely featured database and has a broad and mature set of tools. It integrates as well as most other databases, especially if you already have Java skills in your repertoire. The big question is whether it will attach properly to DB2 tables, and for that we can only wait and see.

My next project will be to install an open-source MySQL-based Java application--a forum or a CRM--and see how well I can access the base data of the application from RPG. If you're interested, drop me a line, and I'll write an article on what I find.