By chance, I had to retrieve data from IBM i (also known as AS400).
I want to retrieve data on IBM i and use it on Linux or Windows Sometimes you want to use the data in your system for machine learning or analysis. If you simply use Python, you can also process it on IBM i.
However, this time I want to use GPU for DL or ML, so I need to bring the data on Linux. For that, I'm going to pull the data with JDBC + Python.
Of course, you can use Java to fetch the data. This time I decided to use JDBC from Python because I am better at Python. (DL and ML are often written in Python, so it may be easier to include in that process)
This time, we will use JayDeBeApi as a Python library for hitting the JDBC API.
https://github.com/baztian/jaydebeapi
It supports quite a lot of types of DB. There is also DB2!
Supported databases
In theory every database with a suitable JDBC driver should work. It is confirmed to work with the following databases:
SQLite Hypersonic SQL (HSQLDB) IBM DB2 IBM DB2 for mainframes Oracle Teradata DB Netezza Mimer DB Microsoft SQL Server MySQL PostgreSQL many more...
This time, it runs on Python 3.6.
The version of each software is as follows
You can install it with pip.
pip install JayDeBeApi
If the version of JPype is the latest, an error occurred at the time of execution, so I reinstalled it with 0.6.3.
pip install JPype1==0.6.3 --force-reinstall
This time, we will use the Jar file provided as standard on IBM i.
Since jt400.jar is stored in the following directory, copy it from IBM i to the Linux environment to be used.
/QIBM/ProdData/HTTP/Public/jt400/lib/jt400.jar
Set <hostname>, <user>, and <password> according to your environment.
import jaydebeapi
conn = jaydebeapi.connect("com.ibm.as400.access.AS400JDBCDriver", "jdbc:as400://<hostname>", ["<user>", "<password>"], "/home/IBMi/jt400.jar",)
cur = conn.cursor()
cur.execute("select * from TSLIB.WORKDAY")
cur.fetchall()
curs.close()
conn.close()
I was able to get the data from IBM i by running this Python script. By changing the SQL statement of ʻexecute`, you can also change the operation on the DB.
Recommended Posts