Imagine for a moment that you have some data stored in Db2 on your z/OS machine that you would love to work with using Python – but you don’t want that data escaping the safety of your IBM Z server. With the release of IBM Open Enterprise SDK for Python, you now have the capability to connect Python running on z/OS with your Db2 data without it ever leaving the system. Read on below to find the steps for getting Python connected to Db2. 

JayDeBeApi is a PyPI package that is used to connect Python to various databases using JDBC. It does this by providing a compliant implementation of DB-API v2.0. For our use case, we want to connect to Db2 and run various SQL queries.  

Product Requirements 

1. IBM Open Enterprise SDK for Python
2. IBM SDK for z/OS, Java Technology Edition 
3. Db2 for z/OS JDBC Driver SMP/E Installed (FMID 5740XYR02)

JayDeBeAPI can be installed directly from the Python AI Toolkit for IBM z/OS. Alternatively, you can install JayDeBeAPI from PyPI, but this requires a compatible C/C++ compiler. See here for installation instructions for setting up your C/C++ compiler with Python. A list of compatible compilers:

1. IBM C/C++ For Open Enterprise Languages on z/OS 2.0
2. IBM Open XL C/C++ 2.1 z/OS
3. IBM XL C/C++ 2.4.1 for z/OS 2.5

Installation

1. Verify that your environment has been configured correctly: 
2. Set the IBM Open Enterprise SDK for Python Environment variables 
3. Set environment variables CC, CXX and LDSHARED to point to your C/C++ compiler installation binaries 
4. JAVA_HOME has been configured to point to your java installation 
5. Add <JAVA_INSTALL_DIR>/bin/j9vm to your LIBPATH 
6. Set CLASSPATH to point to db2jcc.jar/db2jcc4.jar from your Db2 JDBC driver installation
7. Create a venv and install the package JayDeBeApi 

A full example: 

export PATH=<PYTHON_INSTALL_DIR>/bin:$PATH 
export LIBPATH=<PYTHON_INSTALL_DIR>/lib:<PATH_TO_JAVA>/bin/j9vm:$LIBPATH 
export CC=<C Compiler Binary>
export CXX=<C++ Compiler Binary>
export LDSHARED=<C Compiler Binary>
export _BPXK_AUTOCVT='ON' 
export _CEE_RUNOPTS='FILETAG(AUTOCVT,AUTOTAG) POSIX(ON)' 
export _TAG_REDIR_ERR='TXT' 
export _TAG_REDIR_IN='TXT' 
export _TAG_REDIR_OUT='TXT' 
export CLASSPATH=<JDBC_DRIVER_PATH>/db2jcc.jar 
unset JAVA_HOME

python3 -m venv venv --system-site-packages 
. ./venv/bin/activate 
# If you want to install from the Python AI Toolkit instead, use the instructions found here:
# https://ibm-z-oss-oda.github.io/python_ai_toolkit_zos/
pip3 install JayDeBeApi 

# When running a python script using JayDeBeApi, JAVA_HOME needs to be set
# export JAVA_HOME=<PATH_TO_JAVA_INSTALL> 
python3 script.py

Connecting to the database 

At this point, JayDeBeApi and all dependencies should be installed. We can verify that it can connect by running a simple python script: 

import jaydebeapi 
conn = jaydebeapi.connect("com.ibm.db2.jcc.DB2Driver", "jdbc:db2://<db2_ip>:50000/<dbname>", ["<username>", "<password>"]) 

if (conn): 
    print('Connected') 
else: 
    print('Failed to connect') 

Usage Example 

Now that we can connect to the database, here’s an example of how to interact with the data – showing both how to insert and query your data: 

import jaydebeapi 
conn = jaydebeapi.connect("com.ibm.db2.jcc.DB2Driver", "jdbc:db2://<db2_ip>:50000/<dbname>", ["<username>", "<password>"]) 

try: 
    with conn.cursor() as cursor: 
        # Create + populate the table 
        cursor.execute("CREATE TABLE PY_EXAMPLE(NAME VARCHAR(32), AGE INT)") 

        # Insert data directly – passing in a None will insert a NULL into the database 
        cursor.execute("INSERT INTO PY_EXAMPLE VALUES(?, ?)", ('Unknown', None))  

        # Insert data using prepared statements 
        cursor.execute('INSERT INTO PY_EXAMPLE(NAME, AGE) VALUES(?, ?)', ('Emily', 35)) 
        cursor.execute('INSERT INTO PY_EXAMPLE(NAME, AGE) VALUES(?, ?)', ('William', 30)) 

        # Run a query + process the results 
        cursor.execute("SELECT * FROM PY_EXAMPLE WHERE AGE IS NOT NULL") 

        print('Results gathered:') 
        for (name, age) in cursor.fetchall(): 
            print('  Name: {}  Age: {}'.format(name, age)) 

        # Clean up our example 
        cursor.execute("DROP TABLE PY_EXAMPLE") 
except Exception as e: 
    print('Error detected: {}'.format(e)) 
finally: 
    conn.close() 

Which after running will display the following: 

Results gathered: 
  Name: Emily  Age: 35 
  Name: William  Age: 30 

More Info / Troubleshooting 

For more usage examples and explanations, see JayDeBeApi’s GitHub. 

​