Mapepire: A new IBM i database client

IBM have been quietly working on brand new database clients for Db2 for IBM i, with the requirement that no native binaries will be required in the clients to connect to the database.

That is how ODBC currently works; you install the IBM i Db2 ODBC database driver, then install (and sometimes build) the ODBC client into the runtime you're using (for example, node-odbc or pyodbc). There were problems with this: there are some platforms that ODBC cannot run on and also in some instances is not able to run in a container, though it is possible.

Today, August 30th, is the first day that Mapepire (pronounced ‘mapəpɪə’ or ‘MAH-pup-ee’) is really usable. We've got all the documentation ready to go, multiple clients ready, and sample applications ready to go.

This post is co-written by Adam Shedivy, Sanjula Ganepola, and myself. Each of us worked on multiple components of Mapepire and we're coming together to write about Python, Java and Node.js respectively.

Why a new server/client?

Typically with IBM i, the database/server clients take on very heavy loads. For example, jtopen and ODBC clients have code inside of them to deal with CCSIDs - converting string columns to and from certain encodings. This makes client very large in code but also in complexity. Another ick that I've always had with ODBC and jtopen is that the protocol used to communicate between the server and clients is not publically documented (or at least, very well). So, if you want to create a new client in a new language you have to reverse engineer what is available - I tried it, it sucked.

Mapepire takes a brand new approach. Almost every part of Mapepire is open-source. This includes the server, the protocol and the clients. As well as that, we're letting the server piece take care of things like encodings, password levels, and much more, which makes the clients lighter. We're going to make it easier for people to write new applications in more languages that can run on more platforms.

Another benefit is that we're going to use yum and typical registires for different languages (npm for our Node.js client, pypi for Python, Maven for Java, etc). This means we can ship updates and fixes to you faster than before.

Getting the server ready

For your clients to use Mapepire, a new server component will need to be installed and started onto the server. This is a server just like many of the other system services - it's just managed differently because we are shipping through yum instead of PTF.

You can check if Mapepire is available and install it with yum:

-bash-5.2$ yum list available | grep mapepire
mapepire-server.noarch                     2.1.2-1       Artifactory_7.3

-bash-5.2$ yum install mapepire-server
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package mapepire-server.noarch 0:2.1.2-1 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=================================================================
 Package              Arch    Version   Repository          Size
=================================================================
Installing:
 mapepire-server      noarch  2.1.2-1   Artifactory_7.3    3.5 M

Transaction Summary
=================================================================
Install       1 Package

Total download size: 3.5 M
Installed size: 3.8 M
Is this ok [y/N]: y
Downloading Packages:
mapepire-server-2.1.2-1.ibmi7.3.noarch.rpm    | 3.5 MB  00:00:03     
Running Transaction Check
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : mapepire-server-2.1.2-1.noarch               1/1 

Installed:
  mapepire-server.noarch 0:2.1.2-1                                                                                                                     

Complete!

Following the installation, you can use Service Commander to start the Mapepire server:

-bash-5.2$ sc start mapepire
Performing operation 'START' on service 'mapepire'
Service 'Mapepire Server' successfully started

-bash-5.2$ sc list
...
mapepire (Mapepire Server)
...

Never heard of Service Commander? Install it with yum install service-commander and also checkout this blog entry by Jesse Gorzinski.

Building apps

The best way to show you working apps is to show off our app samples. As of this post being written, we only have Java, Python and Node.js samples. As more client libraries are built, more samples will be shown off.

The sample applications can be found on GitHub.

Python

To use the Python Client, first install the `mapepire-python` package: Python 3.9 or later required ```sh pip install mapepire-python ``` Setup the server credentials used to connect to the server. One way to do this is to create a `mapepire.ini` file in the root of your project with the following content: ```ini [mapepire] SERVER="SERVER" PORT="PORT" USER="USER" PASSWORD="PASSWORD" ``` The following script sets up a `DaemonServer` object that will be used to connect with the Server Component. Then a single `SQLJob` is created to facilitate the connection from the client side. ```python import configparser from mapepire_python.client.sql_job import SQLJob from mapepire_python.data_types import DaemonServer config = configparser.ConfigParser() config.read('mapepire.ini') creds = DaemonServer( host=config['mapepire']['SERVER'], port=config['mapepire']['PORT'], user=config['mapepire']['USER'], password=config['mapepire']['PASSWORD'], ignoreUnauthorized=True ) with SQLJob(creds) as sql_job: with sql_job.query("select * from sample.employee") as query: result = query.run(rows_to_fetch=1) print(result['data']) ``` Here is the output from the script above: ```sh { "EMPNO":"000010", "FIRSTNME":"CHRISTINE", "MIDINIT":"I", "LASTNAME":"HAAS", "WORKDEPT":"A00", "PHONENO":"3978", "HIREDATE":"01/01/65", "JOB":"PRES", "EDLEVEL":18, "SEX":"F", "BIRTHDATE":"None", "SALARY":52750.0, "BONUS":1000.0, "COMM":4220.0 } ``` For more in depth usage, refer to the Python Client docs: - [docs](https://mapepire-ibmi.github.io/guides/usage/python/) - [github](https://github.com/Mapepire-IBMi/mapepire-python) - [sample notebook](https://github.com/Mapepire-IBMi/samples/tree/main/python)

Java

Getting started with the Java client (`mapepire-sdk`) is as easy adding a `maven` dependency in your project: ```xml io.github.mapepire-ibmi mapepire-sdk 0.0.4 ``` **Note**: Make sure to check out the latest version on the [Maven Central Repository](https://central.sonatype.com/artifact/io.github.mapepire-ibmi/mapepire-sdk). From there, you can establish a connection, execute queries, and parse queries using the very intuitive APIs provided by the package: ```java public static void main(String[] args) throws Exception { // Initialize credentials DaemonServer creds = new DaemonServer("HOST", 8085, "USER", "PASSWORD", true, "CA"); // Establish connection SqlJob job = new SqlJob(); job.connect(creds).get(); // Initialize and execute query Query query = job.query("SELECT * FROM SAMPLE.DEPARTMENT"); QueryResult

worksofliam / blog