FAQs for MySQL and the DB2 Storage Engine

April 21, 2010 Susan Gantner

In a couple of past tips, I’ve been exploring aspects about using MySQL on i and especially the ability to use the DB2 for i Storage Engine to make the MySQL data accessible from “native” DB2-based applications and tools. In this tip, I’ll cover a couple of questions that often arise related to this topic.

How does database object security work with MySQL and the DB2 for i storage engine?

MySQL has its own security implementation that governs how users may access data. Users and their authorities to databases and tables are set up in MySQL and are unrelated to IBM i system user profiles and authorities. When the DB2 for i storage engine is used, the same data is accessible from DB2 as well as MySQL. So when accessing the data via DB2, the normal IBM i system security model is used to govern access to the data.

What this means ultimately is that in order for the same person to be able to access the data using both MySQL and DB2 (by way of RPG programs or native query tools, for example) that person will need to be authorized in two different ways. They need an IBM i user profile with appropriate authority to access the DB2 data, and they need to be set up as a MySQL user with the necessary authority to access the MySQL table.

This duplication of user and authorization management can be a bit cumbersome. If you often have the same users who need access the same data from both interfaces, it may be worth writing your own interface to automate the process of setting up the authority in both environments. The user and authority information to the MySQL data is stored in database tables within MySQL, so one possible approach might be to have a trigger program react to changes to the security related tables in MySQL and apply those same changes to the native objects. I’m very hopeful that someone will create a nice set of tools to simplify authority management in the MySQL DB2 storage engine environment.

By the way, you may be wondering if there is a similar user to QSECOFR in MySQL. The answer is yes–its name is root. By default when first installed, root has no password associated with it. Therefore, a critical step in the MySQL installation and setup process should always include assigning a password to the root user. Since MySQL users are qualified by the system name and since on IBM i, the system name and the special name ‘localhost” are usually synonymous, assigning the password to root may involve 2 MySQL statements, similar to the ones shown below.

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
SET PASSWORD FOR 'root'@'YourHostName' = PASSWORD('newpwd');

How do I access my existing DB2 tables/files via MySQL?

The short answer is “you don’t.” The storage engine currently only works in one direction; i.e., the data for tables created via MySQL may be stored in DB2 for i. There is no support via the storage engine to allow MySQL to access pre-existing DB2 data.

The slightly longer answer is “with a bit of effort.” You could re-create the table via MySQL using the storage engine and then copy the data (using CPYF, SQL Insert, or any number of other simple options) from the original DB2 table/file into the new object created by the storage engine. Sounds simple enough, right?

Yes, but. . . what about level checks for all the programs already using the original table/file? What about all the logical files built on that table? They can be recreated, most likely via combinations of Create Index and/or Create View, but that takes time and planning, especially if they were all created using DDS originally. Now that SQL Views can have keys on them in the more recent releases of IBM i, at least most of the logical file features can be re-created using SQL. What about the fact that the new table in DB2 will have a record format name the same as the table? There are also currently some differences in data type support. The one most likely to be an issue in this scenario is a lack of zoned numeric data type with the storage engine.

A Few Rough Edges

The storage engine certainly makes it simpler to share data between applications using MySQL and applications using DB2 for i. It’s very powerful support and I plan to make use of it in many ways in my future applications. But it’s not a panacea. There are a few rough spots. I hope that over time the storage engine support from IBM may improve to help smooth some of these rough spots. Still others may be smoothed via tool vendors that make the scenario easier to manage and to exploit. I predict a bright future of more flexible application interfaces to IBM i data using a combination of MySQL and DB2.

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.