Naming Idiosyncrasies with the DB2 Storage Engine for MySQL
February 24, 2010 Susan Gantner
|
In a recent tip, I wrote about why I think MySQL is a great benefit to our platform. With its DB2 for i storage engine, MySQL allows yet one more way to access DB2 data. In this article, I’ll go into a few naming gotchas that can arise when using MySQL. But first I’ll describe some scenarios in which developers in today’s RPG shops may find themselves taking advantage of MySQL. The first scenario is one I touched on in my last tip: the availability of a huge array of open source (i.e., free or nearly free) applications written in PHP, Java, or C++, to use MySQL. Whatever your new application requirement may be, a quick Google search will often reveal some open source applications that may come close to meeting those needs. Since the data from those applications can now be stored in DB2 for i, then you can use RPG to fill in any gaps in application requirements. The second scenario is that you may decide to develop your own applications with at least the browser interface portion of the application written in PHP, perhaps utilizing calls to RPG code in the background. You may handcraft the PHP code yourself or you may decide to utilize a framework or a tool to generate PHP logic for you. In any case, there are several options for accessing DB2 data from the PHP code. But you may prefer to use MySQL due to the number of books and Internet articles that contain examples of using MySQL with PHP. Thanks to the DB2 storage engine, that data can also be stored in DB2, making it equally accessible to your RPG programs and/or query tools. The third scenario is similar to the second one. You could use one of the readily available open source frameworks out there to quickly build prototypes of applications or even completely build browser-based CRUD (Create, Read, Update, and Delete) applications using MySQL. Imagine having a user ask you for an application and going back to that person later the same day with a full-function data entry application from which you can begin working on a prototype of the application together. The data from the prototypes can be stored in DB2, freeing you to complete or enhance the application further using any combination of languages. One such framework that I’ve seen used in this fashion is called Xataface. Using Xataface, incredibly full-function CRUD applications can be created in minutes with as little as 10 to 20 lines of configuration code. Even the simplest Xataface applications have some really nice features, such as sorting or searching on any column, producing CSV or XML output, and drop-down lists for fields with specific values lists. Xataface is just one example. Many tools like this that are written to access MySQL databases are readily available. Assuming one of these scenarios sounds interesting to you, the common element in all of them is the ability to have the MySQL data stored in DB2 for i so that it’s easily accessible to RPG or other familiar languages and tools. How difficult is that to do? It’s very simple. When a table (a.k.a., a file) is created using MySQL, simply specify the storage engine name using “Engine=IBMDB2I” in your Create Table statement. You can also configure MySQL to default to the DB2 storage engine if you prefer. For those of you who have created tables in DB2 using SQL, most of the syntax in the following example will look familiar:
Create Table SUSAN.MYTABLE
(COMPANY Char(15), FIRSTNAME Char(10),
LASTNAME Char(10), CREDITLMT Dec(7,2))
Engine=IBMDB2i;
The statement above, when entered via MySQL, created a MySQL table called MYTABLE in a database called SUSAN. At the same time, it also created an SQL table (a.k.a., physical file) called MYTABLE in a library named SUSAN. The data for MYTABLE is not duplicated; it is stored in DB2 for i because of the IBMDB2I storage engine. Therefore, the same data is equally accessible from both MySQL and either SQL in DB2 or any native database interface, such as an RPG READ. Of course, in order to use CHAIN or SETLL, I would first need to issue a Create Index statement or add a primary key to MYTABLE. You may have noticed that the names of the database (library), table and columns (fields) in the Create Table statement above are in uppercase. That’s significant because unlike DB2 for i, MySQL is case sensitive. So a table named MYTABLE is a different table from one named MyTable. The reason I used all uppercase names in my example is that it makes it makes life a bit easier to use from RPG, as we’ll see in moment. This begs the question: how does DB2 handle the case sensitivity of MySQL? The answer is that DB2 places names that are not all uppercase in double quotes. So the following example creates something very different from the earlier one: Create Table Susan.MyTable (Company Char(15), FirstName Char(10), LastName Char(10), CreditLmt Dec(7,2)) Engine=IBMDB2i; This time, a table (physical file) named “MyTable” (complete with quotes) was created in a library named “Susan”. If you’re like me, you may find that a little hard to believe, so take a look at the PDM screen below:
As you can imagine, names like these cause problems in many native interfaces, such as in RPG F specs, so you may want to create an SQL view of the table with a more traditional uppercase name or use a file override at compile time to the mixed case name. Perhaps the best solution for situations with mixed case names is to use embedded SQL, which does support the double-quoted names, even in RPG. The column/field names in the “MyTable” example are also case sensitive and therefore double quoted as well when accessed via SQL. But an uppercase “system name” was generated for these fields, much like the shorter system name that is created by DB2 when a column name longer than 10 characters is used in Create Table. So a Display File Field Description (DSPFFD) command for “Susan”/”MyTable” shows the first field name to be COMPA0001 (no quotes) with an alternate SQL name of “Company”. As you can see from these simple examples, using embedded SQL in your RPG is often better choice for accessing your tables created from MySQL, particularly if you are not able to control the names used, such as in the first scenario above–when utilizing software packages. 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 System i platform that hosts the new RPG & DB2 Summit conference. Gantner, who has worked in IBM’s Rochester and Toronto labs, left IBM to focus on training OS/400 and i5/OS shops on the latest programming technologies. She is also a regular speaker at COMMON and other user groups. Send your questions or comments for Susan to Ted Holt via the IT Jungle Contact page. RELATED STORY What a Lifelong DB2 Fanatic Sees in MySQL
|
