Python is one of the most commonly used programming language. Its interactive mode is ideal for prototype development and ad-hoc tasks. There is also a development GUI. Both are free to download or use under open source license. In this blog, I am going to show you how to use Python to connect to DB2 11 for z/OS and perform some basic database operations.
- Python 2.5 or later*, excluding Python 3.X (see below)
- DB2 Python driver (see below)
- Activation program is ran in the DB2 server. Otherwise, ODBC license file for DB2 for z/OS is required (https://www.ibm.com/support/knowledgecenter/SSEPGG_9.5.0/com.ibm.db2.luw.apdv.cli.doc/doc/r0024162.html)
Step 1: Set up Python Environment for DB2
- Download Python 2.5 or later*, excluding Python 3.X from
* I highly recommend to install Python 2.7.9 or higher (but not 3.X). If you have installed Python 2 >=2.7.9, you already have pip and setuptools (that is used to install other modules/packages)
I have download Python 2.7.13 for my Windows machine. I took all default options, only change the install location to C:\Python\Python27.
To verify your installation,
- Install DB2 Python driver: ibm_db
If you have installed Python 2.7.9 or higher, setuptools like easy_install, pip are located in C:\Python\Python27\Scripts
ibm_db (egg) will be installed in C:\Python\Python27\Lib\site-packages\ibm_db-2.0.7-py2.7.egg
From the job output, you can tell the package installed also include IBM ODBC and CLI Driver from IBM.
In addition to ibm_db, you can also download/install ibm_db_dbi, ibm_db_sa adapter for SQLAlchemy, ibm_db_django adapter for Django. See Resource section for more details. For the exercise in this blog, we only need ibm_db.
- Activate your DB2 for z/OS server (if you have not done so)
If the DB2 for z/OS server you planned to connect is not activated yet, you can run db2connectactivate utility to activate the server (see Resources section). Another option is copy the DB2 Connect license to installation_path/license directory.
If your server is not activated yet and you don’t have a DB2 Connect license in the license directory, you will get the following error when you try to connect to DB2.
Exception: [IBM][CLI Driver] SQL1598N An attempt to connect to the database ser
SQLCODE=-1598ause of a licensing problem. SQLSTATE=42968
- set PYTHONPATH environment variable to ibm_db(egg)
(You may need to customize this according to the “egg” version you have installed).
Step 2: Test ibm_db using interactive shell
We are going to use Python interactive shell to SELECT from a catalog table, SYSIBM.SYSXMLSTRINGS table. SYSIBM.SYSXMLSTRINGS contains a mapping of string id (4 bytes integer) to an actual string.
In the python install directory, type python to launch the interactive shell.
Python 2.7.13 (v2.7.13:a06454b1afa1, Dec 17 2016, 20:42:59) [MSC v.1500 32 bit (Intel)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import ibm_db
>>> conn = ibm_db.connect("DATABASE=<database>;HOSTNAME=<host.co
m>;PORT=<port>;PROTOCOL=TCPIP;UID=<userid>;PWD=<password>;", "", "")
>>> sql = "SELECT STRINGID, SUBSTR(STRING,1,60) AS STRING FROM SYSIBM.SYSXMLSTRINGS ORDER BY STRINGID FETCH FIRST 3 ROWS ONLY"
>>> stmt = ibm_db.exec_immediate(conn, sql)
>>> result = ibm_db.fetch_both(stmt)
>>> while result != False:
... print "The STRING ID is : ", result["STRINGID"]
... print "The actual string is : ", result
... result = ibm_db.fetch_both(stmt)
Inside ibm_db.connect(), you need to customize the parameters according to your DB2 setting. The statements above are easy to understand:
- Import the required module
- Connect to DB2
- Create a SQL statement
- Execute the SQL statement
- Loop through the result set and print out each row. There are different way of fetch: ibm_db.fetch_tuple(), ibm_db.fetch_assoc(), ibm_db.fetch_both(), ibm_db.fetch_row(). See Resources section for more details.
Output may be different from different DB2. Below is what I get from my DB2:
The STRING ID is : 1001
The actual string is : product
The STRING ID is : 1002
The actual string is : description
The STRING ID is : 1003
The actual string is : name
Type exit() to exit the interactive shell.
Step 3: Write your own Python program to connect to DB2 for z/OS
We can create a Python program containing the statements that we have tested above (using interactive shell). Suppose we want to create a Python program called testDBz.py.
The content of testDBz.py is:
conn = ibm_db.connect("DATABASE=<database>;HOSTNAME=<host.com>;PORT=<port>;PROTOCOL=TCPIP;UID=<userid>;PWD=<password>;", "", "")
sql = "SELECT STRINGID, SUBSTR(STRING,1,60) AS STRING FROM SYSIBM.SYSXMLSTRINGS ORDER BY STRINGID FETCH FIRST 3 ROWS ONLY"
stmt = ibm_db.exec_immediate(conn, sql)
result = ibm_db.fetch_both(stmt)
while result != False:
print "The STRING ID is : ", result["STRINGID"]
print "The actual string is : ", result
result = ibm_db.fetch_both(stmt)
In this blog, I have shown you how to set up your environment to implement and execute a simple Python program to connect to DB2 for z/OS and do some simple database operations. There are also a lot of other Python database features that you can explore. In short, there are a lot of options in implementing modern applications to connect to DB2 for z/OS, not only COBOL or JAVA!
- Python downloads and related(DB2) resources
- Setting up the Python environment for IBM data servers
- db2connectactivate - Server license activation utility
- Fetching rows or columns from result sets