What a Lifelong DB2 Fanatic Sees in MySQL
February 3, 2010 Susan Gantner
|
In my mind, you can’t beat our database. It’s not only integrated into the operating system, it’s also incredibly flexible. You can use SQL to create and access data just like databases on other systems but you can also use the native DDS, RPG, COBOL and other languages with the same data. No other database out there–not even other members of the DB2 family–has that kind of flexibility. It is currently known as DB2 for i, but I think most of us just think of it as “the database,” which is appropriate because, unlike just about every other system out there, our platform only has one serious option for a database. At least that was true until a couple of years ago when MySQL AB announced they were working with IBM to bring their MySQL database to the platform. I thought at the time that it was a pretty silly idea. Why would anyone on this platform even consider using any other database than DB2? After awhile, it occurred to me that a huge selection of open source (free or nearly free) PHP and/or Java applications are available out there and are written to the MySQL interface. Many commercial packages are written for MySQL as well. So I figured that must be the role of MySQL on i–to make it easy to implement those applications on our system. It’s a niche, but a logical reason to make use of MySQL on i. A bit later, there was talk of making a stronger connection between MySQL and DB2 by creating a DB2 for i storage engine for MySQL. No longer content to sit quietly in its little niche, MySQL wanted to be integrated into our “real” database. At that point, it was beginning to feel a bit threatening. Could this be a good idea? I decided to take a closer look at MySQL to understand more about it. After all, if it was threatening my platform’s native database, I wanted a clearer picture of the enemy. That’s when I began to see some parallels between this newfangled database and our system. MySQL has a lot of flexibility architected into it as well. Not in terms of the language one uses to create and access data–it uses SQL exclusively just as the other relational platforms do. But the idea that the database sits above and separated from the actual storage mechanism of the data (these storage engines) felt strangely familiar. A major advantage of this separation is that the database on top can take advantage of new or different implementations for storing the data without change to the data or applications required. That’s when I began to see the resemblance between MySQL with its storage engines and our own platform with its TIMI (Technology Independent Machine Interface). Or, as we called it for years before someone invented a cute name for it, the MI layer that separates the operating system from the hardware, allowing the OS and all our applications to be able to continue to run without code changes required. The major advantage? Generation after generation of hardware changes underneath have allowed us to adopt new technologies without requiring a rewrite of our applications. As I found we have some basic things in common, MySQL and i, I was beginning to feel less threatened by this strange new database. But aside from some basic architectural similarities, is there any practical advantage to bridging my DB2 and MySQL? Yes, a huge practical advantage. Earlier, I expounded on the unique advantage of DB2 on our platform; its flexibility of interface. As I see it, with a DB2 for i storage engine for MySQL, it just got even more flexible. Now, any of those MySQL applications one chooses to run on i can have the data from those applications stored in DB2 tables, or physical files, if you prefer that term. So now if I want to write RPG programs to access and update that same data, I just do it, using either SQL or native CHAIN, SETLL, etc. I don’t need to extract the data from one database and import it into DB2 first, or use some kind of middleware or special drivers with SQL to gain access to the “foreign” data. I can use my favorite native query tools against it to create my own reports as well. There are a number of i shops who are already doing just that today, with the popular open source SugarCRM package among others. Score yet one more big point for the flexibility of DB2 for i, and yet another exclusive feature of our flavor of DB2. Before I close, I should confess that the DB2 for i storage engine is not quite a panacea. There are clearly quite a few rough spots in the marriage between the two databases. For example, while you can certainly access the data using native RPG operation codes, there are quite a few idiosyncrasies to deal with, such as the fact that MySQL names are case-sensitive. Also the connection goes only in one direction at the moment. That is, you can access data created with MySQL from DB2, but you cannot access already existing DB2 data from MySQL directly. I’ll be sharing a bit more about how this DB2 for i storage engine for MySQL works in future tips and how to avoid (or at least smooth over) some of the rough spots that are there today. I believe this is a boon to our platform, making it far easier to integrate our traditional applications to a whole new world of both open source and commercial applications that are available. And making the case once again that DB2 for i is still the most powerful and flexible database out there. Susan Gantner is one of the most respected System i gurus in the world and is one of the co-founders of System i Developer, an organization dedicated to RPG, DB2, and other relevant software technologies for the IBM i platform that hosts the RPG & DB2 Summit conferences. Gantner, who has worked in IBM’s Rochester and Toronto labs, left IBM to focus on training development shops on the latest programming technologies. She is also a regular speaker at user groups and teaches customized on-site classes for i shops. Send your questions or comments for Susan to Ted Holt via the IT Jungle Contact page.
|
