Connecting from Python to IBM Db2 for z/OS

4 min readAug 23, 2022

Here, we will see how to connect to Db2 for z/OS from Python programs on any platform, including Python running on z/OS.

In this story, we assume that you are using Python 3.8 or higher. The support for Db2 is provide by the ibm_db package. For more details about support platforms and versions of Python see the ibm_db documentation.

On Linux, macOS, and Windows

Installation

You can install IBM Db2 driver for Python using pip on any platform:

pip3 install ibm_db

The package and the ODBC driver are installed by this command. If you already have the ODBC driver on your machine, you can provide their location:

IBM_DB_HOME=<path-to-clidriver>
LIB=$IBM_DB_HOME/lib:$LIB

License Requirements

A Db2 Connect license is required to connect to Db2 for z/OS. The client-side license would need to be copied under license folder of your clidriver installation directory and for activating server-side license, you would need to purchase Db2 Connect Unlimited for System z®.

To know more about license and purchasing cost, please contact IBM Customer Support.

To know more about server-based licensing via db2connectactivate, follow below links:

Connecting to Db2

You can connect to Db2 using the ibm_db.connect() or pconnect() functions.

The advantage of pconnect() is it returns a persistent connection to an IBM Db2 for z/OS. Persistent connections are not closed when ibm_db.close() is called on them. Instead, they are returned to a process-wide connection pool. The next time ibm_db.pconnect() is called, the connection pool is searched for a matching connection. If one is found, it is returned to the application instead of attempting a new connection.

For more about the parameters in the connection string see: APIs · ibmdb/python-ibmdb Wiki.

The example above specifies all the connection parameters, but you can also define them in the configuration of the ODBC driver (file db2dsdriver.cfg ). Then you connection string will be just ibm_db.connect('DSN=alias-in-odbc-configuration', '', '') .

You can use TLS and client certificates for examples, see Example of SSL Connection String.

On z/OS

This story assumes that you have installed IBM Python 3.10 for z/OS and set environment variables as described in:

Installing IBM Open Enterprise Python 3.10 for z/OS

Python has been popular for a long time, and in the last few years, its popularity reached the top level. Can you use…

plape.medium.com

Namely:

# Set the auto conversion:
export _BPXK_AUTOCVT='ON'
export _CEE_RUNOPTS='FILETAG(AUTOCVT,AUTOTAG) POSIX(ON)'

Note: The instructions have been verified with Python 3.8 as well.

It is recommended to create and active a virtual environment so Db2 package does not interfere with other packages (db2 is name of the virtual environment):

python3 -m venv $HOME/python_venv/db2
. $HOME/python_venv/db2/bin/activate

You should upgrade pip (Package Installer for Python) to the latest version by the following command:

pip3 install --upgrade pip

This story has written with version 22.2.2.

You need install package named wheel:

pip3 install wheel

Without that package you will get the following error during the installation:

error: invalid command 'bdist_wheel'

Before installing the package you need to provide HLQ of your Db2 target libraries:

export IBM_DB_HOME=<your IBM Db2 HLQ>

The last segment of data sets under this HLQ should start with SDSN — for example:

Then you can install the ibm_db package using pip:

pip3 install ibm_db==3.1.1 --no-build-isolation -v

Note: The latest versions 3.1.2 and 3.1.3 has known problems when installing on z/OS, so we need to specify ==3.1.1 to use the last version that can be installed on z/OS.

Note: IBM XL C/C++ compiler is required to be installed.

There are few things to update before we can use the driver:

chmod 755 $VIRTUAL_ENV/lib/python*/site-packages/ibm_db.cpython-*.so

This makes the ibm_db shared object executable. If it is not done, you will get the following message while importing the package in your programs:

ImportError: CEE3512S An HFS load of module $VIRTUAL_ENV/lib/python*/site-packages/ibm_db.cpython-*.so failed. The system return code was 0000000111; the reason code was EF076015.

If you use Python 3.8, then you need to clear tags for the same shared object so it can be loaded properly:

chtag -r $VIRTUAL_ENV/lib/python*/site-packages/ibm_db.cpython-*.so

If it is not done, you will get this message:

ImportError: CEE3512S An HFS load of module $VIRTUAL_ENV/lib/python*/site-packages/ibm_db.cpython-*.so failed. The system return code was 0000000130; the reason code was 0BDF0C27

You can double check the attributes by:

ls -ET $VIRTUAL_ENV/lib/python3.*/site-packages

You see:

- untagged    T=off -rwxr-xr-x  --s-  1 . . . ibm_db.cpython-3*.so

More details about z/OS installation are provided in https://github.com/ibmdb/python-ibmdb/blob/master/install.md and https://github.com/ibmdb/node-ibm_db#configure-odbc-driver-on-zos.

Connecting the Db2 on z/OS

We will write a simple program db2.py that issues one query using DB-API:

Since we are on z/OS, ibm_db.pconnect() does need to get any connection and credential information if you want to connect as the current used on the current z/OS system.

Before running the program you need to set up the correct environment:

export IBM_DB_HOME=<your IBM Db2 HLQ>
export STEPLIB=$STEPLIB:$IBM_DB_HOME.SDSNEXIT:$IBM_DB_HOME.SDSNLOAD:$IBM_DB_HOME.SDSNLOD2
export DSNAOINI=$HOME/odbc.ini

Where $HOME/odbc.ini contains information about the subsystem and connection information:

[COMMON]
MVSDEFAULTSSID=<ssid>
FLOAT=IEEE
CURRENTAPPENSCH=ASCII
APPLTRACE=0 
APPLTRACEFILENAME=odbc_trace[D12A]
AUTOCOMMIT=1
MVSATTACHTYPE=CAF
PLANNAME=DSNACLI

Then you can run your program:

python3 db2.py

If you get ten lines of result, you program works!

That’s it for the initial connection to Db2, in the future we will do more complex queries and operations with Db2.