Hello Maria! – I can’t say that without sounding like Leslie Philips – showing my age again!
One of the cutting-edge recent additions from IBM for their Open Source offerings is Maria DB.
In my opinion, this is a great addition, and will open the IBM i to even more applications than are readily available now on our favourite server.
Who is Maria?
Maria DB is a replacement for MySQL. It is open source and is developed by the original MySQL developers.
MariaDB is one of the most popular database servers in the world. MariaDB is used because it is fast, scalable, and robust.
MariaDB is a community developed branch of MySQL..
You will notice many of the commands use the MySQL naming convention.
How do we install it?
Nice and easy, either use Yum from a shell session, or Access for Client Solutions (ACS) open source package management and look for the two mariadb packages and install those.
See my previous PowerWire articles, or videos, on how to use these products, if you need further help on installing Open Source.
Once Yum has installed the packages, we need to open a Shell session, with QSECOFR authority.
Once your shell session is open, run the MariaDB setup procedure, by running the command below.
Once this is complete, the next step is to set the root password, obviously replacing the stars!
mysqladmin -u root password ******
Next, we need to issue the GRANT command to apply the appropriate permissions.
In a shell session we can use the following command
mysql -u root -p
then we can grant root authority to
GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ IDENTIFIED BY ‘your password’;
With that command, we have told MariaDB to:
- GRANT the PRIVILEGES of type ALL
- These privileges are for all databases and it applies to all tables of that database, which is indicated by the *.*
- These privileges are assigned to username root, when that username is connected through all IP addresses, as specified by @%
Configuring Maria DB Server
Before starting the database server, we need to ensure it is running on the correct IP address and port number.
The file my.cnf holds the configuration. This can be seen in the figure below.
Here I have added to listen on all our IP addresses. By default it uses port 3306. If that port is in use by your corporate network, it can be changed here.
Starting Maria DB Server
That is all the configuration complete, now to start the server, just run the following command from a shell session:
Once this has been run, it will tie up the terminal, if this is closed it will stop the database server from running.
Once we are happy with testing our database it would be better to submit the starting of the server as a batch job. This command for this would be.
SBMJOB CMD(QSH CMD(‘/QOpenSys/pkgs/bin/mysqld_safe –datadir=/QOpenSys/var/lib/mariadb/data’)) JOB(STR_MARIA)
If we check the error log, we can see the server is ready and waiting for connections.
If you only trust 5250, you can run the NETSTAT *CNN command, and look for port 3306.
Or look for the job in WRKACTJOB, the server job will run by default in the QUSRWRK subsystem.
Stopping the Server
If you need to stop the database server for any reason, run the following command in a shell session.
mysqladmin -u root -p shutdown
this command will prompt for the root password, then shutdown the server job.
Now we have Maria installed, how do we go about using this database?
Firstly, we need a database client to access it. There are loads of options out there, Google is your friend for once.
I will be using DBeaver, as I have used it before, and I love the name!
DBeaver can be downloaded from https://dbeaver.io/download/
When we first open the beaver, we need to create a database connection to our IBM i.
This is achieved by the following.
We take the new connection icon, then select the MariaDB type and fill the connection settings for both the server and user details. Best to test the connection while we are here.
And now we can see our MariaDB database on our IBM i.
Creating a Database and Table
Now we have our database server running and a client that connects to it, let me create a database and then a couple of tables within it.
All very easy using the beaver!
I have taken a SQL script from the MariaDB site, that will create these database object for us.
Use Ctrl+] to open a new SQL window, then Alt+X to execute the script.
Before it runs, I get a warning that the server properties state this is a production server, yes, I know!
Use the connection properties to change this if you need to.
A nice touch from the beaver!
Our new database and three tables now exist.
To see if my inserts worked, I would highlight the books table and take F4 to view – Yes, all there!
And that is it. We have installed and used a MariaDB database on the IBM i. This really does open a wealth of applications we can now use. Our clients will be well happy with this one!
In my next article, we will look at what is available to us now we have the Maria database installed.
Once again, many thanks to IBM for providing us with rich open source opportunities on the IBM i, keep them coming!
If you have any questions, either on this article, or anything else open source, use the comments below, or send me a message on twitter @AndyYouens
Andy Youens is an IBM i consultant/instructor at Milton Keynes, UK-based FormaServe Systems with over 40 years IBM midrange experience.
Jorge Sanguinetti says
Thanks for your article. I only have a question and is regarding the MariaDB feature to replicate the tables inside the 5250 environment so that we can access them using regular RPG code. Where can I find information about it? Is it a licensed feature or does it come free of charge? Thanks again.
Andy Youens says
As far as I know, you cannot access the MariaDB tables from RPG, you would need some middleware to access those tables, PHP, NodeJS etc – wouldnt be difficult to do.
Hope this helps