19
Fri, Jul
2 New Articles

MySQL and IBM i: Where Does MySQL Fit in a DB2 World?

Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

DB2 is the only database many of us have ever known.  This article shows you how you can broaden your database options.

 

If you're an i programmer, you've been using DB2 from the beginning of your career (unless, of course, you started like me back in the cave computer days of the System/3). You didn't know you were using DB2; in fact, you didn't know that your database had a name, and you didn't care because it just worked. That in fact was one of the great selling points of the platform: the integrated database and by extension the nearly bare-metal speed of compiled languages like RPG, which could take advantage of that database. Nowadays, though, a single standalone database like DB2 for the i isn't enough for many shops. Commercial databases like SQL Server and Oracle have found their way into many shops. A more recent phenomenon is the appearance of databases such as MySQL and PostgreSQL, especially as components of open-source packages.

 

Getting Started

You can find quite a bit of information on the product on the Web. You can start with IBM's MySQL page, although that's more of a press release-y kind of thing. If you're more interested in just getting going, jump immediately to the download page. This page has a number of options, including both SAVF and TAR packages for i5/OS. I'm using the 64-bit SAVF version for this project.

 

 

The prerequisites are pretty well spelled out, but let me emphasize two points. First, you need to have PASE installed. If you're like me, one of the diehards out there who doesn't use PASE, then you might need to install it. To see if you do, execute the command DSPSFWRSC from your command line and look for i5/OS option 33. On my V5R4 machine, it looks like this:

 

 

 5722SS1  33  5111  Portable App Solutions Environment

 5722SS1  33  2924  Portable App Solutions Environment

 

You could also check for the QPASE library or try to call QP2TERM from the command line (this starts the PASE environment, much the same way that STRQSH starts the QShell environment). After verifying that you have PASE, you may also want to get the porting toolkit, 5799PTL, which allows you to run Perl scripts inside of PASE.

 

Anyway, once you've got the essentials downloaded and/or installed, you can continue on the MySQL installation itself. The MySQL documentation for this phase is quite good.

 

 

What I'm Trying to Accomplish

 

 

Note that I'm doing this outside the bounds of the Zend PHP download. This article is not about PHP; that's a different issue entirely. I'm focusing entirely on getting MySQL up and running, because if I'm successful, I've opened my machines to an entire spectrum of Java applications that use MySQL. I call this the MJ stack: MySQL and Java. Note that this stack has only two elements. That's because unlike LAMP or WIMP, the MJ stack needs just two pieces and can run anywhere. Now that I have MySQL working, my next step will be to install a Java open-source application.

 

 

An intriguing point is that the MySQL folks have stated they will have a DB2 engine. In MySQL lingo, an "engine" is the method used to store and access the actual data (as opposed to the metadata--the definitions of the table's columns and their properties--which is stored separately). MySQL has a variety of database engines, ranging from in-memory tables for performance to ISAM for speed to something called InnoDB, which supports many of the advanced relational features such as row-level locking. It even has support for a CSV (comma-delimited) file, which could be an interesting option for exchanging data with other systems: to populate a CSV table, you could use SQL rather than the CPYTOIMPF command.

 

 

However, none of these options will create data that is natively accessible to ILE programs. The only way to access the data is via the MySQL engine itself, and that means using either Java or PHP. When MySQL supports DB2 as an engine, though, you will be able to read and write MySQL tables using native I/O operations from RPG and COBOL, which will provide a whole additional level of integration.

 

 

Installing MySQL

 

 

This isn't rocket science. You upload the savefile, create a user profile named MYSQL, restore the library from the savefile, and then execute the install command. There is some discrepancy between the documentation on the Web site and the actual command. The command wants to install into /usr/local and store its data under /QOpenSys, while the documentation seems to prefer storing things under /opt/mysql. It seems to be smart enough to handle whatever directory naming you prefer, and I personally am a fan of not using generic names, so I'm going to install into /opt/mysql (and /opt/mysqldata).

 

 

The base installation took about half an hour. I was able to then go into PASE (by calling qp2term from a command line) and start and stop the server, as well as secure the users and all that good stuff. One helpful hint: in the various instructions for SQL, you'll often see two sections, one for Windows and one for UNIX. Use the UNIX version, since, after all, you are running in PASE, which is an AIX (UNIX) environment.

Now What?

This is where it got interesting. I have this database installed, so now what do I do with it? Well, it's not like DB2, where I can write an RPG program and read the files. I can't even access the data via good old STRSQL. So what's a guy to do?

 

 

Well, in the world of non-integrated databases, the next step is to get a tool. Two primary options exist: find a GUI tool that works directly with the database in question or else get a JDBC driver for the tool and use a generic JDBC tool. MySQL has the pieces for both, so I decided to try each approach.

 

 

Using MySQL GUI Tools

 

 

The MySQL GUI Tools is a complete set of tools designed to access MySQL databases directly. MySQL is committed to multi-platform support, so you will see versions for not only Windows, but also various Linux distributions and even Mac OSX.

 

 

Note: In order to use the GUI tools, you will have to use the MySQL command line on the host to add a user with the address of your PC. As installed, MySQL talks only to the machine it is installed on. In my case, I executed the following commands, which enable access as root from IP address 10.10.10.101, with password "secret":

 

 

  grant all privileges on *.* to 'root'@'10.10.10.101';

  set password for 'root'@'10.10.10.101' = password('secret');

 

That done, I was able to use the GUI tools. I've only really gotten familiar with MySQL Administrator and MySQL Query browser. Together, I'd say they've got similar functionality to iSeries Navigator.  Administrator is a very neat utility that lets you access the database, as you can see in Figure 1.

 

090808PlutaFigure1.png 

 

Figure 1: This is the MySQL Administrator.

 

 

As you can see, this tool is for managing the database itself. It's interesting to note all the things that are required to manage a relational database that we simply take for granted. For example, user security is managed here, as are backups. These are things that are handled system-wide on the i.

 

 

The Query Browser, on the other hand, is much more the traditional Web query tool. It can be used to access data, edit data, run queries...very much the complement of STRSQL.

 

090808PlutaFigure2.png 

 

 

Figure 2: This is an example of the MySQL Query Browser.

 

 

In Figure 2, you actually see a couple of things. You see the INSERT statement I used to move the data from MYDATA to MYCSV, which I used to see if MySQL would convert relational data to comma-delimited data. It worked quite well, by the way, the only issue being that the data file is locked, which means some applications can't get at it. But I was able to copy it, look at it with the DSPF command, and even open it with one of the smarter text editors (NoteTab Pro, my personal favorite).

 

 

You'll also see the results of querying the resulting CSV file. Note that this doesn't  happen as a direct result of running the INSERT statement. I first ran the INSERT statement to copy the record from MYDATA to MYCSV, and then I ran a SELECT statement on MYCSV. However, the Query Browser has a nice browser-like history feature, and I used the Go Back button on the upper left of the tool to bring the INSERT statement up again for your viewing pleasure.

 

090808PlutaFigure3.png

 

 

Figure 3: The Inline Help feature is quite extensive.

 

 

Another thing I like about the Query Browser is the help. SQL help is obviously abundant throughout the Internet, but the help in the Query Browser is very extensive. It goes far beyond the basics of syntax and delves deeply into the hows and whys of SQL statements, and especially explains how MySQL handles specific situations. An entire section, for example, goes into the topic of correlated subqueries and the exact mechanics of how MySQL processes them. This sort of detail really helps to remove some of the black magic that surrounds SQL in general. I think I'll be spending time in the Query Browser's help system even when I'm not using MySQL; it's as good a general SQL reference as many out there.

 

 

Integration with JDBC Tooling

 

 

A number of options exist to access a database via JDBC, including open-source tools such as Squirrel SQL. For this article, I'm going to focus on accessing the database through the Data perspective available in either Rational Business Developer (RBD) or Rational Developer for i for SOA (RDi-SOA).

 

 

To take advantage of the Data perspective, you'll need to add a connection for the database. First, you have to get the JDBC connector. Go to the MySQL Connector/J Web Page and download version 5.1. Get the .zip version and then extract the JAR file from it (the current version as of this writing is mysql-connector-java-5.1.6-bin.jar). Put it somewhere accessible as you'll be using it later to create your connection. In this example, I put it in a folder called MySQL on the C: drive.

 

090808PlutaFigure4.png

 

Figure 4: You have to specify the JAR file, the class name, and the host information.

 

 

Now you bring up RDi-SOA and open the Data Perspective. In the Data Perspective, you create a new Connection and fill in the information shown in Figure 4 (if you're unfamiliar with the Data Perspective, you'll see it in Figure 6). Some things to note: I specified the database name as "test," which is the schema in MySQL where I created my test files. This schema is automatically created when you install MySQL, and it's a good place to use as a sandbox. The JDBC driver class name should always be the same: com.mysql.jdbc.Driver. The JAR file name may change depending on the version you download and the directory into which you extract the JAR file. The connection URL has three parts, the protocol (jdbc:mysql), the host IP address (the address of your System i), and the port number (3306). The protocol and port number will probably not change. Technically, you can change the port number, usually for security reasons, but that has to be done on the server. I've left it at the default for this article. Hit the Test Connection button, and you should see the dialog in Figure 5.

 

 

 

 

090808PlutaFigure5.png 

Figure 5: This indicates a successful connection.

 

 

 

Once you've done this, you can now start using the connection. The connection has some direct capabilities of accessing the data, as shown in Figure 6.

 

090808PlutaFigure6.png

 

Figure 6: Use the Database Explorer to directly edit tables.

 

The Database Explorer view is an integral part of the Data Perspective; that's where you define the connections. Right-click on the Connections folder and select New Connection to create a new connection. Once the connection is created, you can then expand it as shown, drilling down into the schemas and tables. Right-click on a table and select Data > Edit, and you'll see an editable form such as the one shown.

 

090808PlutaFigure7.png

 

 

Figure 7: You can also create a Data Design project to execute ad hoc scripts.

 

To execute more-complex statements, create a Data Design project. This will allow you to create SQL scripts, save them, and execute them as shown in Figure 7.

 

Integration with High-Level Languages

 

As I mentioned earlier, until MySQL supports DB2 as an engine, traditional ILE programs written in RPG and COBOL cannot access the MySQL data directly. Integration will require an intermediary language. Until the introduction of PHP, the only real option would have been Java. I'll let someone else handle the PHP side of things; in fact, if you go the PHP route, my guess is that Zend will be able to provide you with lots of MySQL support; Zend offers a bundle of PHP and MySQL.

 

 

But for those of us firmly rooted in the IBM/Rational/Java camp, the only logical choice is to use Java and JDBC. Many options exist for the architecture of a multi-language application. You can use a Java driver to invoke JDBC to read the data and pass it to RPG or COBOL using IBM's Java Toolbox to call the program, or you can use an RPG driver to invoke a JDBC class to access the data. Either way works. Pros and cons exist regarding performance and design complexity, and it's really a business decision as to which way you'll design your application. It should be noted that either of the previous design options requires a separate JVM for each job and therefore can be expensive, especially for interactive programs. Another option is to use a Java program that reads a data queue and then does requests to the JDBC driver and returns results. It's a more complicated solution, but it avoids the resource and startup issues of multiple JVMs.

 

 

Of course if you're using Java or EGL, you can invoke the JDBC driver directly from either of those languages. Here's an example Java snippet, ignoring all the boring error checking (seriously, JDBC programs need a lot of error checking, but that's a different topic for a different day):

 

 

  Class.forName("com.mysql.jdbc.Driver");

  con = DriverManager.getConnection("jdbc:mysql://10.10.10.10/test",

    "root", PASSWORD);

  Statement s = con.createStatement();

  ResultSet rs = s.executeQuery("SELECT * FROM MYDATA");

  while (rs.next())

    System.out.println(

      "KEY1: " + rs.getString(1) +

      ", DATA1: " + rs.getString(2));

 

You'll see a result like this:

 

 

  KEY1: A, DATA1: A1

  KEY1: B, DATA1: B1

 

 

Note that this example specifies both the host and the schema in the connection URL. I don't bother with the port number because I'm using the default. I could have left off the schema and used a qualified name (TEST.MYDATA) for the table. And if the Java program were running on the same machine as the MySQL driver, I'd use LOCALHOST or 127.0.0.1 as my host. This is all standard JDBC programming; the point is that MySQL on the i is completely accessible to standard Java programming techniques and thus available to other ILE languages through the mechanisms I specified earlier.

MySQL, Today and Tomorrow

I was going to run some performance tests, but to be honest, I'm not really that worried about it; it really would be comparing apples and oranges, since MySQL doesn't use DB2 as its underlying format. I may still do so if I get some free time, but I'm more interested in seeing how MySQL performs using DB2 tables.

 

 

It's clear, though, that MySQL is a nicely featured database and has a broad and mature set of tools. It integrates as well as most other databases, especially if you already have Java skills in your repertoire. The big question is whether it will attach properly to DB2 tables, and for that we can only wait and see.

 

My next project will be to install an open-source MySQL-based Java application--a forum or a CRM--and see how well I can access the base data of the application from RPG. If you're interested, drop me a line, and I'll write an article on what I find.

Joe Pluta

Joe Pluta is the founder and chief architect of Pluta Brothers Design, Inc. He has been extending the IBM midrange since the days of the IBM System/3. Joe uses WebSphere extensively, especially as the base for PSC/400, the only product that can move your legacy systems to the Web using simple green-screen commands. He has written several books, including Developing Web 2.0 Applications with EGL for IBM i, E-Deployment: The Fastest Path to the Web, Eclipse: Step by Step, and WDSC: Step by Step. Joe performs onsite mentoring and speaks at user groups around the country. You can reach him at This email address is being protected from spambots. You need JavaScript enabled to view it..


MC Press books written by Joe Pluta available now on the MC Press Bookstore.

Developing Web 2.0 Applications with EGL for IBM i Developing Web 2.0 Applications with EGL for IBM i
Joe Pluta introduces you to EGL Rich UI and IBM’s Rational Developer for the IBM i platform.
List Price $39.95

Now On Sale

WDSC: Step by Step WDSC: Step by Step
Discover incredibly powerful WDSC with this easy-to-understand yet thorough introduction.
List Price $74.95

Now On Sale

Eclipse: Step by Step Eclipse: Step by Step
Quickly get up to speed and productivity using Eclipse.
List Price $59.00

Now On Sale

BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: