Re: MySQL on i

Hi Scott,

Thanks for these posts - I learn something every time!

Are there any MySQL statements that are not supported by DB2 (or any of the other storage engines)? And if so, what happens?

*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
pdow@xxxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxxx> /

Scott Klement wrote:

Hi Bryan,

I wonder if, perhaps, you don't know what a MySQL Storage Engine is? MySQL is designed in a manner that allows you to change the actual technology used to store data. When you issue SQL statements (et al) against MySQL databaases, the system interprets the statement, etc, runs through the query plan, query optimizer, etc, just as it would with any SQL statement. But then, it can run against different underlying technology (transparent to the SQL statement) depending on which storage engine the table was created with.

MySQL already supports several storage engines, most notably MyISAM and InnoDB (but there are several others, such as one that implements the database in heap memory, and another that actually uses CSV files...)

That way, you can pick the database engine that's most suitable for what you're doing. Temporary tables might use the heap memory engine, for example. InnoDB is better for transactional tables. MyISAM is better for non-transactional tables... you can pick whichever storage engine is most appropriate for your needs.

Anyway, the idea behind the DB2 storage engine is that you can run your existing MySQL programs without any sort of modification. No changes to the SQL statement, etc, but under the covers it'll use IBM DB2 tables (i.e. physical and logical files) as the storage mechanism.

I'm guessing that you didn't understand that, since you asked about the "human interface" -- which makes no sense in the context of a storage engine.

Or to put it another way: The human interface is not changing. MySQL has a well-established human interface that people have been using for years and years. The addition of a DB2 storage engine has absolutely no impact on that human interface.

You can download and run MySQL today (or last year, or five years ago for that matter) with InnoDB or MyISAM and the human interface will be exactly the same when you run DB2.

MySQL comes with command-line tools named 'mysql' and 'mysqladmin' that can be used to administer a MySQL database. Using these tools would be similar to administering our traditional DB2 database with the STRSQL command.

However, every MySQL installation I've worked with uses a tool called 'phpmyadmin' which is a really nice web interface (written in PHP) for configuration and administration of mysql. That's the interface I always use -- and it's really powerful and easy to use. (More so than the GUI in iSeries Navigator, IMHO)

But, again... that human interface is the same thing it's always been.

The exciting part of the DB2 storage engine is that your existing tools (such as PHP applications) can use the IBM DB2 databases. So I can download SugarCRM or MediaWIKI and run them on the i, and without changing the code at all, they can use DB2 databases. Since they're regular DB2 databases, I can also write code that works with them using RPG or RUNSQLSTM or any other IBM-based tool (Which I can't do with MySQL today, since the underlying technology isn't DB2, I Can't use RPG's native DB2 support -- if I want to get to a MySQL database from RPG, I have to use a JDBC driver)