Mapepire: A new IBM i database client

[worksofliam]

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

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:

[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.

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:

{
    "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
• github
• sample notebook

Java

Getting started with the Java client (mapepire-sdk) is as easy adding a maven dependency in your project:

<dependency>
    <groupId>io.github.mapepire-ibmi</groupId>
    <artifactId>mapepire-sdk</artifactId>
    <version>0.0.4</version>
</dependency>

Note: Make sure to check out the latest version on the Maven Central Repository.

From there, you can establish a connection, execute queries, and parse queries using the very intuitive APIs provided by the package:

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<Object> result = query.execute(3).get();

    // Convert to JSON string and output
    ObjectMapper mapper = new ObjectMapper();
    mapper.enable(SerializationFeature.INDENT_OUTPUT);
    String jsonString = mapper.writeValueAsString(result);
    System.out.println(jsonString);
}

We also put together several sample project's that can also be used as a starting point.

1. simple-app

For those looking to play around with the different APIs, the simple-app project is the best place to have a try with using the various APIs and observing what the output is through the command line.

1. Ensure the Mapepire Server is installed and running on the IBM i.

2. Clone the repository:

   git clone https://github.com/Mapepire-IBMi/samples.git
   cd java/simple-app

3. Copy and fill out the configuration properties:

   cp src/main/resources/config.properties.sample src/main/resources/config.properties

4. Build the application:

   mvn clean package

5. Run the application:

   cd target
   java -jar simple-app-1.0-SNAPSHOT-jar-with-dependencies.jar <demo-type>

   Replace <demo-type> in the above command with one of the demo types below:

   Demo Type	Description
   --sql	Execute a SQL query
   --prepareStatement	Execute a prepared SQL query with parameters
   --clCommand	Execute a CL command
   --paginatingResults	Fetch a specific number of rows and paginate results
   --pooling	Execute a SQL query from a job pool
   --jdbcOptions	Execute a SQL query with a job having JDBC options

2. company-web-server

For those looking for a more advanced project, definitly check out the company-web-server which is a simply a Jetty webserver that can be used to manage departments, employees, and sales.

1. Ensure the Mapepire Server is installed and running on the IBM i.

2. Create the SAMPLE schema on the IBM i:

   QSYS.CREATE_SQL_SAMPLE('sample')

3. Clone the repository and build the application:

   git clone https://github.com/Mapepire-IBMi/samples.git
   cd java/company-web-server
   mvn clean package

4. Start the company web server:

   cd target
   java -jar company-web-server-1.0-SNAPSHOT-jar-with-dependencies.jar

5. Start a web server to launch the Swagger UI:

   npm install -g http-server
   cd swagger
   http-server --cors

6. Access the Swagger UI at http://localhost:8080 in the browser.

7. Connect to a database using the /connect endpoint.

8. Set the server trace level using the /tracing endpoint.

9. Experiment with the various endpoints:

   Method	Endpoint	Description
   GET	/departments	Get a list of departments
   GET	/departments/{deptNo}	Get a department by department number
   GET	/employees	Get a list of employees
   GET	/employees/{empNo}	Get an employee by employee number
   GET	/sales	Get a list of sales
   GET	/sales/{salesPerson}	Get a list of sales associated with a sales person

10. Disconnect from the database using the /disconnect endpoint.

For a more in-depth guide on how to setup and use the Java client, check out the dedicated documentation page here

Node.js

The Node.js sample app is a small express API which talks to IBM i. It has examples of running statements and calling simple RPGLE programs.

Setup

First step is to make sure you clone the samples repository. Inside of the samples repo is the typescript directory and that's where you should run npm install. To debug this app, we are going to use Visual Studio Code, so you can open this folder up via the command line or in VS Code itself.

git clone https://github.com/Mapepire-IBMi/samples.git
cd samples/typescript
npm i
code .

Environment setup

Make a copy of .env.sample and name it .env. This file will be used by the VS Code debugger to determine which port to use for the APIs and which credentials to use to connect to the database. Ensure you change the variables that start with DB_ to where the Mapepir server is running.

PORT=3000
DB_HOST=hostname
DB_ID=userprofile
DB_PASSWORD=x

Also, if you want to make use of some sample data without your existing objects, then create this sample schema using the following SQL statement. The SAMPLE schema is also used in this Node.js sample.

QSYS.CREATE_SQL_SAMPLE('sample')

Start the app

Using the VS Code debugger, there is a 'Launch Program' option. If the app launch successfully, then you will see this in the Debug Console:

/Users/barry/.nvm/versions/node/v20.14.0/bin/node ./build/index.js
Example app listening on port 3000

Then, you can call to the APIs that are available using your browser, curl, an HTTP client, etc:

barry@Liams-MBP typescript % curl localhost:3000
Hello world!
barry@Liams-MBP typescript % curl localhost:3000/test
[{"EMPNO":"000010","FIRSTNME":"CHRISTINE","MIDINIT":"I","LAST...

Notable parts of the code

In src/index.ts, this is where the connection pool is made and the express app is started up. The express app cannot start unless the pool is created:

db.connect(DatabaseServer).then(() => {
  app.listen(port, () => {
    console.log(`Example app listening on port ${port}`)
  });
});

Here are other two important files in the TypeScript sample:

• src/routes/root.ts is where the API routes are defined and includes executing database statements.
• src/db/index.ts manages the database connection pool and has methods to execute SQL statements against the database.

For a more in-depth guide on how to setup and use the Node.js client, check out the dedicated documentation page here

[ITMentalist]

Nice one !

Cant wait to play around with it when I get a chance.

[GajenderI]

Will this be the ultimate and only ODBC driver from IBM and get regular updates?

[GajenderI]

Getting an unknown host acceprtion, how to resolve this?

java.net.UnknownHostException: hiddenthename.COM: hiddenthename.COM: Hostname and service name not provided or found
at java.net.InetAddress.getLocalHost(InetAddress.java:1685)
at com.github.ibm.mapepire.certstuff.SelfSignedCertGenerator.generate(SelfSignedCertGenerator.java:24)
at com.github.ibm.mapepire.certstuff.ServerCertGetter.get(ServerCertGetter.java:102)
at com.github.ibm.mapepire.MapepireServer.main(MapepireServer.java:74)
Caused by: java.net.UnknownHostException: hiddenthename.COM: Hostname and service name not provided or found
at java.net.Inet6AddressImpl.lookupAllHostAddr(Native Method)
at java.net.InetAddress$2.lookupAllHostAddr(InetAddress.java:1048)
at java.net.InetAddress.getAddressesFromNameService(InetAddress.java:1462)
at java.net.InetAddress.getLocalHost(InetAddress.java:1680)
... 3 more

[worksofliam]

@GajenderI please raise your issue in the Mapepire-server repo. Thanks!

[GajenderI]

Will this be the ultimate and only ODBC driver from IBM and get regular updates?

@worksofliam would you please confirm this.

[worksofliam]

@GajenderI ODBC and Mapepire will both exist. ODBC is not going away.

[chiragsanghavi]

it would be great if a JDBC driver is built to leverage Mapepire java client.

[SanjulaGanepola]

it would be great if a JDBC driver is built to leverage Mapepire java client.

@chiragsanghavi Implementing a driver will be looked at soon. The status of this can be tracked here

[willisaacllc]

Hi @worksofliam,
Does running Mapepire improve on how SQL jobs that utilize the IBMi ODBC to process them more efficiently for QZDASOINIT jobs?