Python - Group home

Using Python for z/OS to Work With Db2 Data

  

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 Python for z/OS, 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 Python for z/OS 
  2. IBM SDK for z/OS, Java Technology Edition 
  3. IBM XL C/C++ V2.3.1 for z/OS V2.3
  4. Db2 for z/OS JDBC Driver SMP/E Installed (FMID 5740XYR02)


Installation

  1. Verify that your environment has been configured correctly: 
  2. Set the IBM Open Enterprise Python for z/OS Environment variables 
  3. Set variables CC and CXX to point to your xlclang 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 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=<XL_INSTALL_DIR>/xlclang 
export CXX=<XL_INSTALL_DIR>/xlclang++ 
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 
pip3 install JayDeBeApi 

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

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. 

​