Volume 10, Number 7 -- February 24, 2010

Naming Idiosyncrasies with the DB2 Storage Engine for MySQL

Published: February 24, 2010

by 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:

   (COMPANY Char(15), FIRSTNAME Char(10),
    LASTNAME Char(10), CREDITLMT Dec(7,2))

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))

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.


What a Lifelong DB2 Fanatic Sees in MySQL

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot

Sponsored By

Do you need area code information?
Do you need ZIP Code information?
Do you need ZIP+4 information?
Do you need city name information?
Do you need county information?
Do you need a nearest dealer locator system?

We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

Just call us and we'll arrange for 30 days FREE use of either

WorksRight Software, Inc.
Phone: 601-856-8337
Fax: 601-856-9432
E-mail: software@worksright.com
Web site: www.worksright.com

Senior Technical Editor: Ted Holt
Technical Editor: Joe Hertvik
Contributing Technical Editors: Erwin Earley, Brian Kelly, Michael Sansoterra
Publisher and Advertising Director: Jenny Thomas
Advertising Sales Representative: Kim Reed
Contact the Editors: To contact anyone on the IT Jungle Team
Go to our contacts page and send us a message.

Sponsored Links

PowerTech:  Strengthen your security. Get a FREE Compliance Assessment today
looksoftware:  Learn more and get your complementary ilook licences
COMMON:  Join us at the annual 2010 conference, May 3 - 6, in Orlando, Florida


IT Jungle Store Top Book Picks

Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
The iSeries Express Web Implementer's Guide: List Price, $49.95
The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
The System i Pocket RPG & RPG IV Guide: List Price, $69.95
The iSeries Pocket Database Guide: List Price, $59.00
The iSeries Pocket SQL Guide: List Price, $59.00
The iSeries Pocket Query Guide: List Price, $49.00
The iSeries Pocket WebFacing Primer: List Price, $39.00
Migrating to WebSphere Express for iSeries: List Price, $49.00
Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
Getting Started with WebSphere Express for iSeries: List Price, $49.00
Can the AS/400 Survive IBM?: List Price, $49.00
Chip Wars: List Price, $29.95

The Four Hundred
The Power7 Systems Sales Pitch

i/OS Gets Short Sheeted with Power7 Thread Counts

The System iWant, 2010 Edition: Clustered Boxes

As I See It: Opinions Are Like ISPs--Everybody Has One

Spend on the Old, Scrimp on the New

Four Hundred Stuff
Visual LANSA Gets Expanded Interoperability, Developer Convenience

Linoma Bolsters Surveyor/400 with Excel Capabilities, SSL

Lawson Updates M3 Suite of ERP Apps for i/OS

Customers Begin Using ilook to Web-Enable i/OS System Screens

Shield Adds IFS Replication to HA Offering

Four Hundred Monitor
Four Hundred Monitor's
Full iSeries Events Calendar

System i PTF Guide
February 20, 2010: Volume 12, Number 08

February 13, 2010: Volume 12, Number 07

February 6, 2010: Volume 12, Number 06

January 30, 2010: Volume 12, Number 05

January 23, 2010: Volume 12, Number 04

January 16, 2010: Volume 12, Number 03

TPM at The Register
AMD's 'Magny-Cours' Opterons surface on eBay

IBM flicks out HS22V Xeon blade

Cray swings profit on Q4 revenue dive

Teradata ekes out sales and profit growth

SGI spins up Cyclone HPC cloud

Unisys parts with server chief

Red Hat projects to seed cloudy IT

Big Blue says Power7 will make world smarter

Hitachi inks Xsigo pact for server I/O virt

IBM chills sealed data center with outside air

Riverbed WAN optimizers get SSDs, 10 GE

Intel's 'Tukwila' Itaniums - hot n' pricey


WorksRight Software
Profound Logic Software
Northeast User Groups Conference

Printer Friendly Version

Naming Idiosyncrasies with the DB2 Storage Engine for MySQL

How To Use the Inhibit Write Keyword?

Hunting Down Storage Hogs

Four Hundred Guru


Subscription Information:
You can unsubscribe, change your email address, or sign up for any of IT Jungle's free e-newsletters through our Web site at http://www.itjungle.com/sub/subscribe.html.

Copyright © 1996-2010 Guild Companies, Inc. All Rights Reserved.
Guild Companies, Inc., 50 Park Terrace East, Suite 8F, New York, NY 10034

Privacy Statement