Welcome to the new home of PowerWire – PowerWire.uk

Update your bookmarks now!

Media Pack

Informing the IBM Community

the snake

Mapepire Introduction

—

by

Andy Youens
in ,
5
(5)

In my last couple of articles, I have shown python accessing data on the IBM i, accessing both DB2 for i and SQLite.

The SQLite article used an SQLite package to get the data, and for the DB2 for i example I used ODBC to retrieve data from the IBM i.

In this article I’ll be introducing and using a new way to access data.

IBM has released a new client called Mapepire.

The name is apparently, some kind of snake from South America. By the way, I hate snakes, I’m having problems just typing the word snake, let alone asking copilot to draw a snake!

Taking a quote from Mapepire GitHub page, which can be found here.

A cloud-friendly IBM i database access layer, built with simplicity and performance in-mind.
Mapepire an ideal choice for deployment of cloud-native applications, running seamlessly in Red Hat UBI deployments, Alpine Linux containers, and the magnitude of cloud services available (like IBM Cloud, IBM WatsonX.ai, Vercel, AWS, or Azure).

Simply put: Mapepire clients can be deployed anywhere!
IBM

Let us see if it really is that simple.

I’ve had enough of miss-spelling Mapepire in this article, from now on I will refer to it as the snake!

Server Install

Like other packages, MSSQL, MariaDB, etc, there are two parts to using a programming language to access data, the server, also known as the daemon, and there is the client.

The snake is no different. We must install the server part and then we can use the client to access the data.

Once again, for Open Source on the IBM i, I will be using YUM, from a shell session, to install the server part, but Open Source Package Management from ACS could have also been used.

Bash

yum install mapepire-server
Bash

All installed. Next we need to run the server before moving onto the client.

Starting the Server

The first step we must perform is starting the server daemon.

I will perform this task in a SSH session, executing the following command.

Bash

bash /QOpenSys/pkgs/bin/mapepire
Bash


By default, the server runs on port 8076, which will be fine for the majority of users, but it can be changed, if desired, by setting the PORT environment variable prior to starting the server. This can be seen below.

Bash

export PORT=8090
Bash

To check it is running, I will be using Service Commander, a great IBM i application which can be found on the ACS open-source Package Management list, or GitHub. It can be found at this link .

Using the SCOPENPORTS command, part of Service Commander, shows the Mapepire server running on TCP/IP port 8076

I could have also used the green screen command below to check the server job.

Bash

netstat *cnn
Bash

Service Commander

Service Commander can be used to start and manage the snakes’ server, nice and easy using

Bash

sc start mapepire
Bash

I have been hitting an error using this method, where a language error is thrown. This error occurs from 7.3 to 7.5 of the OS.

This error can be seen in the joblog of the server.

An issue has been raised on the GitHub page for this product. This can be found at this link.

I’ll let you know, by way of the comments below, when this issue has been resolved or updated.

Client Coding

So, with the server up and running, let us move onto the client code for this article. Once again, for its simplicity, I will be using the python programming language.

First, you need to install the Mapepire Python client. You can do this using pip:

Bash

pip install mapepire-python
Bash

Configure the Connection

To save having to hard-code security information, I will be using a separate file for these credentials.

Create a configuration file named mapepire.ini with the following content:

Bash
host=your_ibm_i_host
port=8076
user=your_username
password=your_password
database=your_database
ignoreunauthorized=True
Bash

Replace your_ibm_i_host, your_username, your_password, and your_database with your actual IBM i system details.

The next step is to connect to the Mapepire Server.

Use the following Python code to connect to the Mapepire server and access data:

Python

import mapepire
import configparser
from mapepire_python.client.sql_job import SQLJob

# Load configuration from the ini file
config = mapepire.ConfigParser()
config.read('mapepire.ini')

# Establish a connection to the Mapepire server using values from the ini file
connection = mapepire.connect(
  host=config['mapepire']['host'],
  port=config['mapepire']['port'],
  user=config['mapepire']['user'],
  password=config['mapepire']['password'],
  database=config['mapepire']['database']
  )
  
 with SQLJob("./mapepire.ini") as sql_job:
    with sql_job.query("select * from qiws.qcustcdt") as query:
      result = query.run(rows_to_fetch=10)
      
print(result)
Python
  • Line 2 to 4 imports all the heavy lifting our script needs
  • Line 11 uses our .ini file to connect to our snake server
  • Line 19 prepares our SQL statemen
  • Line 21 executes the SQL
  • And finally, the last line, 23, outputs the SQL results to the screen

Running the Script

Save your Python script and run it using the python interpreter

Bash

python mapapire.py
Bash


This script will connect to the snake server, execute the query and print the results.

JSON

{
  'id': 'query3', 
  'has_results': True, 
  'update_count': -1, 
  'metadata': {
    'column_count': 11, 
    'job': '585683/QUSER/QZDASOINIT', 
    'columns': [
            {'name': 'CUSNUM', 'type': 'NUMERIC', 'display_size': 8, 'label': 'CUSNUM'
            },
            {'name': 'LSTNAM', 'type': 'CHAR', 'display_size': 8, 'label': 'LSTNAM'
            },
            {'name': 'INIT', 'type': 'CHAR', 'display_size': 3, 'label': 'INIT'
            },
            {'name': 'STREET', 'type': 'CHAR', 'display_size': 13, 'label': 'STREET'
            },
            {'name': 'CITY', 'type': 'CHAR', 'display_size': 6, 'label': 'CITY'
            },
            {'name': 'STATE', 'type': 'CHAR', 'display_size': 2, 'label': 'STATE'
            },
            {'name': 'ZIPCOD', 'type': 'NUMERIC', 'display_size': 7, 'label': 'ZIPCOD'
            },
            {'name': 'CDTLMT', 'type': 'NUMERIC', 'display_size': 6, 'label': 'CDTLMT'
            },
            {'name': 'CHGCOD', 'type': 'NUMERIC', 'display_size': 3, 'label': 'CHGCOD'
            },
            {'name': 'BALDUE', 'type': 'NUMERIC', 'display_size': 8, 'label': 'BALDUE'
            },
            {'name': 'CDTDUE', 'type': 'NUMERIC', 'display_size': 8, 'label': 'CDTDUE'
            }
        ]
    }, 'data': [
        {'CUSNUM': 938472, 'LSTNAM': 'Henning', 'INIT': 'G K', 'STREET': '4859 Elm Ave', 'CITY': 'Dallas', 'STATE': 'TX', 'ZIPCOD': 75217, 'CDTLMT': 5000, 'CHGCOD': 3, 'BALDUE': 37.0, 'CDTDUE': 0.0
        },
        {'CUSNUM': 839283, 'LSTNAM': 'Jones', 'INIT': 'B D', 'STREET': '21B NW 135 St', 'CITY': 'Clay', 'STATE': 'NY', 'ZIPCOD': 13041, 'CDTLMT': 400, 'CHGCOD': 1, 'BALDUE': 100.0, 'CDTDUE': 0.0
        },
        {'CUSNUM': 392859, 'LSTNAM': 'Vine', 'INIT': 'S S', 'STREET': 'PO Box 79', 'CITY': 'Broton', 'STATE': 'VT', 'ZIPCOD': 5046, 'CDTLMT': 700, 'CHGCOD': 1, 'BALDUE': 439.0, 'CDTDUE': 0.0
        },
        {'CUSNUM': 938485, 'LSTNAM': 'Johnson', 'INIT': 'J A', 'STREET': '3 Alpine Way', 'CITY': 'Helen', 'STATE': 'GA', 'ZIPCOD': 30545, 'CDTLMT': 9999, 'CHGCOD': 2, 'BALDUE': 3987.5, 'CDTDUE': 33.5
        },
        {'CUSNUM': 397267, 'LSTNAM': 'Tyron', 'INIT': 'W E', 'STREET': '13 Myrtle Dr', 'CITY': 'Hector', 'STATE': 'NY', 'ZIPCOD': 14841, 'CDTLMT': 1000, 'CHGCOD': 1, 'BALDUE': 0.0, 'CDTDUE': 0.0
        },
        {'CUSNUM': 389572, 'LSTNAM': 'Stevens', 'INIT': 'K L', 'STREET': '208 Snow Pass', 'CITY': 'Denver', 'STATE': 'CO', 'ZIPCOD': 80226, 'CDTLMT': 400, 'CHGCOD': 1, 'BALDUE': 58.75, 'CDTDUE': 1.5
        },
        {'CUSNUM': 846283, 'LSTNAM': 'Alison', 'INIT': 'J S', 'STREET': '787 Lake Dr', 'CITY': 'Isle', 'STATE': 'MN', 'ZIPCOD': 56342, 'CDTLMT': 5000, 'CHGCOD': 3, 'BALDUE': 10.0, 'CDTDUE': 0.0
        },
        {'CUSNUM': 475938, 'LSTNAM': 'Doe', 'INIT': 'J W', 'STREET': '59 Archer Rd', 'CITY': 'Sutter', 'STATE': 'CA', 'ZIPCOD': 95685, 'CDTLMT': 700, 'CHGCOD': 2, 'BALDUE': 250.0, 'CDTDUE': 100.0
        },
        {'CUSNUM': 693829, 'LSTNAM': 'Thomas', 'INIT': 'A N', 'STREET': '3 Dove Circle', 'CITY': 'Casper', 'STATE': 'WY', 'ZIPCOD': 82609, 'CDTLMT': 9999, 'CHGCOD': 2, 'BALDUE': 0.0, 'CDTDUE': 0.0
        },
        {'CUSNUM': 593029, 'LSTNAM': 'Williams', 'INIT': 'E D', 'STREET': '485 SE 2 Ave', 'CITY': 'Dallas', 'STATE': 'TX', 'ZIPCOD': 75218, 'CDTLMT': 200, 'CHGCOD': 1, 'BALDUE': 25.0, 'CDTDUE': 0.0
        }
    ], 
		
	'is_done': False, 'success': True
}
JSON

The Json string returned is shown in the figure above. Here we can see the returned records are place in the data section.

Conclusion

Everyone said that retrieving data with the snake is easy and efficient, and I have to concur, it really was.

In future articles I will be putting the snake under more pressure and will see how it performs.

I hope you enjoyed this article and let me know how you will be using the snake.

How useful was this post?

Click on a star to rate it!

Average rating 5 / 5. Vote count: 5

No votes so far! Be the first to rate this post.

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *