How to Verify MySQL is Installed on your IBM i

This article can help you determine if MySQL has been installed on your IBM i, and whether it has been installed in a standard location that would be recognized by the Zend DBi updater.

Please log on to a 5250 session as QSECOFR or as a *SECOFR class user.  Enter this command:

call qp2term

This should bring you into the PASE shell.  You will see either a ‘#’ or ‘$’ in the upper left. The ‘#’ will be there for QSECOFR, the ‘$’ for any other user.

Please enter this command:

find / -name mysqld

This command will run a while.  It searches the entire IFS.  Unlike the 5250 command line, the PASE shell does not inhibit input while it runs a command.  This is because PASE runs commands in their own process.  The way you will know that the command is completed is that the ‘#’ or ‘$’ will be displayed.  While you are waiting, it will be possible to type in other commands and press enter, but don’t do it.  It just makes it harder to read the output from the find command.  If you get bored waiting, get a cup of coffee or answer some emails.  Really, this command takes a while.  It will output a line now and then, but remember to wait for the ‘#’ or ‘$’.
When the find command does finally complete the search, your output may be similar to this:

> find / -name mysqld

This indicates no files were found, and MySQL is not installed.

There may be one or more errors:

> find / -name mysqld
  find: 0652-019 The status on /QSYS.LIB/QQFENDSVR.PGM is not valid.

Errors referring to QSYS.LIB can be ignored here.

If your output is similar to this:

> find / -name mysqld

The mysqld file was found, and you have MySQL installed. This also indicates the version is 5.1.59. If the mysqld file is found in a path with this format, MySQL was probably installed from a Zend distribution, and should be safe to update using the ZendDBi updater. The format is:


If your version of MySQL was previously updated by Zend DBi, you might see two versions of the mysqld file, similar to this:

> find / -name mysqld

You can tell which version is currently in use by checking the symbolic link for /usr/local/mysql:

> ls -l /usr/local/mysql
  lrwxrwxrwx    1 qsecofr  0                58 Mar 19 2012  /usr/local/mysql ->

In this example, the version in use is 5.1.59.

Seven years at Zend


Has it really been seven years?  Time flies.  Looking back, not much in my prior career prepared me for this job.  It’s a bit like living in Oz.  It has been a definite adjustment from RPG and green screens.  My job has been to stumble through all the novelty to help others stumble a little less.  The pace of innovation is amazing here, so there is always something new to learn and explore.  The people I work with here are great, although “with” may not be the most accurate term for such a geographically dispersed group.  Getting any of this gang together for a beer after work usually involves a fair amount of air travel.  And my customers are the best.  AS/400 people have high expectations (thanks to IBM for setting that bar so very high), but they also have been around long enough to know that if everything does not go exactly right the first time around, they don’t have to panic.  So thanks to all my customers, coworkers, and partners from the AS/400 world for making this job so rewarding.  And thanks to all the non-AS/400 computer science wizards at Zend for creating all these amazing products that make it all possible!

Zend DBi, MySQL, and IBMDB2i introduction

MySQL is a popular open source data base used widely on the internet.  MySQL puts the ‘M” in LAMP stack (Linux, Apache, MySQL, PHP).  Taken together, these open source software products make it possible to build a complete web site, and to this day a large plurality of web sites are served on these technologies.

Zend Server users on IBM i typically use IBM i for the operating system, Apache as implemented by IBM HTTP Server for the web server, DB2 for the data base, and PHP as distributed in Zend Server as the programming language.  So our stack is more like IADP, which is not so much fun to say as LAMP stack.

MySQL can be installed on an IBM i.  A fair question to ask might be, “Given the general all around greatness that is DB2 on the IBM i, why on earth would anybody want to install an open source data base?”  That’s a really good question.  I’m glad you asked.

Because LAMP is so popular on the internet, and through the generosity of the open source community, there are a lot of open source applications available that run on LAMP, many of them free to use.  With Zend Server installed on your IBM i, you already have the ability to run many of these applications, but they do require MySQL.  Fortunately, Zend Server for IBM i includes MySQL in the installation package, branded as Zend DBi.  It is an optional installation, because a lot of IBM i customers may never desire to run LAMP applications on their IBM i.  But is is nice to have it available when it is needed.

One really interesting feature of MySQL is that it can use different storage engines.  A Storage Engine “is the underlying software component that a database management system (DBMS) uses to create, read, update and delete (CRUD) data from a database.”  Some familiar with DB2 may know there are two engines for IBM i, SQE and CQE.  One of the engines available for MySQL is IBMDB2i.  This engine uses DB2 on the IBM i to store data.  This provides the possibility of downloading and installing a free LAMP application, and being able to access the data using DB2 SQL, or even viewing the data as physical and logical files.

Are you ready to get started?

Install MySQL using the Zend DBi installer with Zend Server


Zend Dbi product page

MySQL 5.1 Reference Manual

MySQL Storage Engines Reference

MySQL IBMDB2I Reference

Wikipedia Database Engine reference

IBM i reference: SQE and CQE engines

Redbook: Using IBM DB2 for i as a Storage Engine of MySQL

Redbook: Discovering MySQL on IBM i5/OS

Find an available port

Port numbers are used to uniquely identify TCP services running on the same IP address.  Ports are 16-bit numbers, so will be in the range 0-65535.  Sometimes we need to find a unique port number to assign to a TCP service.  To do it, we need to identify any port numbers that are in use, and any port numbers that may be likely to be used, and then pick a port number that is not any of those.  Please note that this does not guarantee the unused port number is always available.  Some service not currently running may attempt to use this port at another time.  If you assign this port to your service, and then later some other service that needs this port fails to start, you may need to repeat the procedure to find another unused port for your service to use.

Step 1 – Talk to your Network Administrator

Some ports may always be blocked by policy at your company, and any port you choose may be blocked just because it is not in use.  Your Network Administrator will be able to determine if there are any ports that should not be used, and if the port selected will need to be unblocked before it can be used.

Step 2 – Think of a port number to use

If you have a specific port number or range of port numbers in mind, it can help you to more quickly determine if the number is in use.  The port number can be arbitrary, but often we like to use something that is close to a number that might be used for a similar purpose.  For example, if the MySQL service will not start because port 3306 is in use, we might decide to try port 3307.

Step 3 – Verify that the port number is not actively in use

You can use the netstat command to locate currently used local ports.  From a 5250 command line:

netstat *cnn

Use F13 and set the sort option to Local Port. Use F14 to display all ports as numbers.  Look at the local ports column.  Any number not present is not in use and is currently available.

                        Work with IPv4 Connection Status                        
                                                             System:   I71SUP2  
 Type options, press Enter.                                                     
   3=Enable debug   4=End   5=Display details   6=Disable debug                 
   8=Display jobs                                                               

      Remote           Remote  Local                                            
 Opt  Address           Port    Port  Idle Time  State                          
      *                     *   2001  +++++++++  Listen                         
      *                     *   2002  +++++++++  Listen                         
      *                     *   2006  +++++++++  Listen                         
      *                     *   2008  +++++++++  Listen                         
      *                     *   2015  +++++++++  Listen                         
      *                     *   2017  +++++++++  Listen                         
      *                     *   2018  +++++++++  Listen                         
      *                     *   3000  +++++++++  Listen                         
      *                     *   3306  000:41:13  Listen                         
      *                     *   4004  +++++++++  Listen                         
      *                     *   4800  +++++++++  Listen                         
      *                     *   4800  +++++++++  Listen                         
 F3=Exit   F5=Refresh   F9=Command line   F11=Display byte counts   F12=Cancel  
 F15=Subset   F20=Work with IPv6 connections   F24=More keys

In the above example, we used F13 to sort by local port, and F14 to show port numbers.  We paged down to see if port 3307 is in use.  Nothing appears between port 3306 and port 4004, so port 3307 is not being used by any active service.

Step 4 – Verify the port is not likely to be used by some other service

Use WRKSRVTBLE to see commonly used port numbers on the IBM i.  This shows all of the ports that have defined names on the IBM i.  The list is sorted by service name, so we need to output to a spool file to search for the desired port.  Port numbers on this list should be avoided when assigning ports for other services.  From a 5250 command line:


                              Display Spooled File                              
 File  . . . . . :   QSYSPRT                          Page/Line   1/1           
 Control . . . . .                                    Columns     1 - 78        
 Find  . . . . . .   3307                                                       
                                           Work with Service Table Entries      
 5770SS1 V7R1M0  100423                                                         
  Service . . . . . . . . . . . . . :   as-admin-http                           
  Port  . . . . . . . . . . . . . . :   2001                                    
  Protocol  . . . . . . . . . . . . :   tcp                                     
  Text  . . . . . . . . . . . . . . :   AS400 Admin HTTP server                 
    Alias . . . . . . . . . . . . . :   www-http-admin                          
    Alias . . . . . . . . . . . . . :                                           
    Alias . . . . . . . . . . . . . :                                           
    Alias . . . . . . . . . . . . . :                                           
  Service . . . . . . . . . . . . . :   as-admin-http                           
  Port  . . . . . . . . . . . . . . :   2001                                    
  Protocol  . . . . . . . . . . . . :   udp                                     
  Text  . . . . . . . . . . . . . . :   AS400 Admin HTTP server                 
 F3=Exit   F12=Cancel   F19=Left   F20=Right   F24=More keys                    
 Character string not found in file.

In the above example, we scanned the spool file for ‘3307’ and it was not found, so it is not reserved for a named port on the IBM i.

Wikipedia has a nice summary list of the most commonly used and registered port identifiers.  This list does not show any usage for port 3307.  We can see port 3306 is registered for MySQL.

The Service Name and Transport Protocol Port Number Registry of IANA provides the complete list of registered ports.  This list does show port 3307 is registered for OP Session Proxy.  A Google search for this term does not turn up much that we could find other than that it is the name for this port, so we might go ahead and use it for an IBM i application, or we could go back and try for another port number.

Learn more about ports at the Wikipedia Port (computer networking) page.

Waiting for ISON

Update:  Comet ISON is no more.  Or maybe it isn’t.  Anyway, looks like there won’t be anything to see.

One of the fun things about our house is making various astronomic observations looking out the back.  Our view is basically South by Southeast, sitting up on a hill.  We can predict where on the horizon the sun will rise on the morning of the Winter Solstice.  We get some pretty awesome moon rises in the summer, and have a pretty good view of some of the meteor showers that come around annually.  This year, we want to see comet ISON.  It will not be easy to see from the house, as it will be more to the east, where we don’t have a very good view.  Also, the best time to see it would seem to be 30 minutes before sunrise, pretty early even in the winter, and very cold.  Still, we will look out for it.   Anyway, the main reason for this post is to see if I can successfully include this infographic I found on Huffington Post:

Comet ISON

And there it is.  You can see the article it came from at this link, or just click on the graphic.

Welcome to

Hi All!

So basically I got tired of having a domain with no web page.  It has been a few years, and a lot has changed since I was “that guy”.  You know the one, that guy fooling around with all this Internet stuff and making web pages, back before everybody and their uncle was doing it.

This time I am doing it with WordPress to format everything, so I can focus more on the content.  Not sure what all I will be putting here.  Mostly I want to put up some support tips for my Zend on IBM i customers, maybe some personal stuff about my life, maybe I can figure out how to bring back some old pages from my old site that I liked, if I can find them laying around on my hard drive.  And if I can figure out how to include them here as raw HTML…

Anyway, I am writing this because I have to write something to get my page started.  So, here it is, my first post.