Taming Python and Db2 for

z/OS
Jørn Thyssen jthyssen @ [Link]
Rocket Software

November 2020
Session 6BA
 Python and
What is python Why use python databases – the DB-
API

Today’s Python on LUW

• Installing ibm_db on LUW
Python on z/OS
• Install ibm_db on z/OS Our first python

agenda
program

Our second python Bonus topic: Interact

Calling stored
program… a few with datasets, z/OS
procedures
iterations services, etc.

2
What is python
• Interpreted programming language
• First released in 1991 by Guido van Rossum
• Designed to be simple (See “The Zen of Python”)

3
Why Python
• Python continuously ranks in the top 3 over popular programming
languages
• TIOBE: #3 #2, now overtaken java, is still behind C
• PYPL: #1
• Jobs available: #1, average salary for jobs offered: #1 £££
• It is widely taught at educational institutions

• If you’re my age or younger you’ve probably been exposed to Python

• I learned it at university in the mid-1990s

4
Why Python, cont’ed
• Widely used in
• Machine Learning
• Data Science
• Infrastructure provisioning
• Scientific computing
• Web sites
• Google, Instagram, Spotify, Netflix, Dropbox, Uber, …

5
 • Python DB-API v2.0 specification
• “Identical” code to access databases across
Python and all platforms & database vendors
databases
• Python_ibmdb (ibm_db) is IBM’s
implementation of the DB-API v2.0
• Maintained by Db2 Connect development
team
• Uses ODBC drivers
Source code: [Link]
API doc: [Link]

6
ibm_db – distributed platform
• On distributed platform:
• Uses Db2 Connect CLI drivers (IBM Data Server Driver)
• Connect to Db2 for z/OS using DRDA
• Need Db2 Connect for z/OS license to connect to Db2 for z/OS

ODBC
Python
ibm_db Db2 for z/OS
LUW IBM Data Server DRDA
Driver
License required

7
ibm_db – installing on distributed platform
• pip install ibm_db

• Installs ibm_db and ODBC drivers

• If you already have Db2 Connect CLI drivers on your machine:
• set IBM_DB_HOME=c:/path/to/clidriver
set LIB = %IBM_DB_HOME%/lib;%LIB%
•
A Db2 Connect license is required to connect to Db2 for z/OS
• Enterprise Edition:
• get license file ([Link]) from IBM Passport Advantage
• Copy to license folder in %IBM_DB_HOME%
• Unlimited Enterprise Edition
• Activate license on z/OS side with [Link] Recommended!

8
ibm_db – z/OS
• On z/OS:
• CAF (Call Attachment Facility)
• RRSAF (Resource Recovery Services attachment Facility)
• Default plan DSNACLI
• Bind ODBC plans & packages: [Link](DSNTIJCL)
• No additional licenses required !

ODBC
Python
ibm_db Db2 for z/OS
USS CAF or RRSAF
Db2 for z/OS

FMID JDBCC17

9
Installing ibm_db on z/OS
• Pip install ibm_db will not work on z/OS
• We need to download & compile
• Summary:
• Install pre-reqs
• Download code for ibm_db
• Build ibm_db

10
Installing ibm_db on z/OS
• Prereqs:
• Python 3.6 or later with some Rocket enhancements
• The Python dist must understand “//’[Link]’” syntax
• Available from Rocket with S&S ($$$) or from public conda channel
[Link]
• Untested(!): IBM Open Enterprise Python for z/OS
• Python PIP
• Bash
• xlc (the C compilers from IBM)
• xlc-wrapper (from Rocket – xlc productivity enhancements)
• (Git – clone ibm_db code from github)

11
Installing ibm_db on z/OS, cont’ed
• Rocket have switched to “conda” as the method for delivering open source tools
• I’ll use “conda” to setup my environment

$ bash
$ source /rsusr/ported/etc/profile.d/[Link]
$ conda create -n test2 python=3.7 pip xlc-wrapper
…
$ conda activate test2
(test2) $

• I now have an environment with python, pip and xlc-wrapper

12
Installing ibm_db on z/OS, cont’ed
• Retrieve ibm_db from github
• The z/OS branch is not yet merged into the official distribution
• It does not pass the test suite, but will work for almost all scenarios
(test2) $ mkdir ibmdb
(test2) $ cd ibmdb
(test2) $ git clone –b v3.0.1-anaconda
[Link]
…
• No internet access from your LPAR?
• Run the git commands on your laptop or download zip from github
• Zip, upload to USS, and unzip
• Tag all files as ASCII text: find . –type f | xargs chtag –tc ISO8859-1

13
Installing ibm_db on z/OS, cont’ed
• Build it
• Set USS autoconvert (recommendation: add to your .profile)
• Set the HLQ for your Db2 datasets (the hlq for SDSNC.H, SDSNLOAD, etc)
(test2) $ export _BPXK_AUTOCVT=ON
(test2) $ export IBM_DB_HOME='[Link].VC10’
(test2) $ cd ibmdb/python-ibmdb/IBM_DB/ibm_db
(test2) $ pip install .
• In this example I am installing ibm_db into my conda environment
• If you’re not using conda or using the base conda environment you might hit
permissions issues
• Try pip install –user . (this will install ibm_db into the Python user path in your home
directory)
• Might need
export PYTHONPATH=/u/xxxx/.local/lib/python3.7/site-packages
before running python programs that import ibm_db
14
 First ibm_db program
• Set STEPLIB so Python can find the ODBC Db2 z/OS drivers
• Create an ODBC ini file
• Set environment variable DSNAOINI to point to the ODBC ini file
(test2) $ export STEPLIB=[Link]:$IBM_DB_HOME.SDSNLOD2:$IBM_DB_HOME.SDSNLOAD
(test2) $ export DSNAOINI=="$HOME/ODBC_PDS1_CAF"
ODBC_PDS1_CAF file:
[COMMON]
MVSDEFAULTSSID=RS01PDS1
FLOAT=IEEE
CURRENTAPPENSCH=ASCII
APPLTRACE=0
[Link] APPLTRACEFILENAME=/u/xxxxx/odbc_trace
0/odbc/src/tpc/db2z_hdckeyw.html [RS01PDS1]
AUTOCOMMIT=1
MVSATTACHTYPE=CAF 15
PLANNAME=DSNACLI
First ibm_db program, cont’ed
• Simple program
import ibm_db
conn=ibm_db.connect('DSN=RS01PDS1','','')

if conn:
sql = "SELECT * FROM [Link] FETCH FIRST 10 ROWS ONLY"
stmt = ibm_db.exec_immediate(conn, sql)
result = ibm_db.fetch_both(stmt)
while( result ):
print(result[1].strip()+"."+result[0].strip())
result = ibm_db.fetch_both(stmt)

ibm_db.close(conn)

16
First ibm_db program, cont’ed
(test2) $ python [Link]
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]
[Link]

17
Ok, what if I am running on distributed
platform?
• The code is the same! (assuming DSN=RS01PDS1 is configured Db2
Connect setup)
• On distributed platform you must provide user ID/password as it is
not using local attach
import ibm_db
conn=ibm_db.connect('DSN=RS01PDS1;uid=xxxxx;pwd=yyyyyy','','')

18
Bonus slide: what about SSL?
• No problem -- just run Python on z/OS with local attach 
• … or use SSL from distributed
• I haven’t configured an ODBC entry for my SSL connection, so I’ll use
the alternate connection string syntax
import ibm_db
conn=ibm_db.connect('database=RS01PDS1;hostname=[Link]
;port=3713;protocol=tcpip;uid=xxxx;pwd=yyyy;security=ssl;sslServerCerti
ficate=c:\\users\\jthyssen\\documents\\certificates\\[Link]','','
')

[Link] is the certificate in .CER (BASE64) format

19
Second ibm_db program
• Vendor program writes log file in USS
• I need program that extract interesting part of log file and insert into Db2
table for further analysis
• Log entry is partial text and partial JSON
[2020.10.05 10:53:58.844][INFO] : IZPSC0003I - Request received: {"userName":"xx
xxxx","requestId":"b66a02df-58ac-4408-a353-9cdc1ace661a","httpMethod":"GET","pat
h":"/ws/security/users/current-user","isSecure":true,"clientIP":"[Link]","
requestParams":{},"requestHeaders":{"referer":["[Link]
8644/ZLUX/plugins/[Link]/web/"],"cookie":["jedHTTPSession=Dm0c+
S6XAjTqJAxalEWlJJdW5BWvDBKOdokXeW3t4EZeZBR9/vdFxw=="],"accept-language":["en-US,
en;q=0.5"],"host":["[Link]"],"connection":["keep-alive"],
"accept-encoding":["gzip, deflate, br"],"user-agent":["Mozilla/5.0 (Windows NT 1
0.0; Win64; x64; rv:81.0) Gecko/20100101 Firefox/81.0"],"accept":["application/j
son"]}}
20
Second ibm_db program, cont’ed
if len([Link]) < 3:
print("Usage: %s <DSN name> <IZP log file name>" % ([Link][0]))
exit(-1)

filepath=[Link][2]
dsn='DSN='+[Link][1]

print("Extracting REST calls from file: %s" % (filepath) )

print("Inserting into table [Link] on %s" % (dsn) )

conn=ibm_db.connect(dsn,'','')
if conn:
print("Succesfully connected to %s" %(dsn) )
else:
print("Connection to %s failed" % (dsn) )
exit

21
Second ibm_db program, cont’ed
with open(filepath,'r') as fp:
line = [Link]()
if debug:
print(line)
reccnt = 0
reqcnt = 0
rspcnt = 0
while line:
reccnt += 1
if [Link]("IZPSC0003I") > 0:
log_request(conn,line)
reqcnt += 1
elif [Link]("IZPSC0004I") > 0:
log_response(conn,line)
rspcnt += 1
22
Second ibm_db program, cont’ed
def log_request(conn,line):
timestamp=line[1:[Link]("]")]
start_json=[Link]("{",[Link]("IZPSC0003I"))
req=[Link](line[start_json:])

sql = "MERGE INTO [Link] L "

"USING( "
"VALUES ( "
" '" + str(req["requestId"]) + "' "
" ,'" + str(req["userName"]) + "' "
" ,TIMESTAMP_FORMAT('" + timestamp + "','[Link] HH24:[Link].FF3') "

" ,'" + str(req["httpMethod"]) + "' "

" ,'" + str(req["path"]) + "' "
[code snipped]
" ,REQUESTPARAMS = [Link] "

if debug:
print(sql)

stmt = ibm_db.exec_immediate(conn, sql)

return 23
Second ibm_db program, cont’ed
• Run program from USS command line
(test2) $ python [Link] RS01PDS1 izp-server-2020-10-05_11-[Link]
Extracting REST calls from file: izp-server-2020-10-05_11-[Link]
Inserting into table [Link] on DSN=RS01PDS1
Successfully connected to DSN=RS01PDS1
1000 records processed (23 REST reqs, 22 REST resps)
I declare I have
2000 records processed (142 REST reqs, 141 REST resps)
…
won!
48000 records processed (4507 REST reqs, 4503 REST resps)
49000 records processed (4602 REST reqs, 4598 REST resps)
50000 records processed (4686 REST reqs, 4686 REST resps)
Number of log records analyzed : 50535
Number of REST requests found : 4686
Number of REST responses found : 4686
24
Second ibm_db program, cont’ed
• Output from monitor
• MERGE statement repeated 9372 times with different literals
• 18745 COMMITS
• Note: PLAN = DSNACLI

25
Second ibm_db program, iteration #2
• My DBA will not be happy 
• Must do:
• Need to use host variables
• Better commit strategy
• Stretch goal: Cache prepare

26
Host variables & cache prepare
if not stmt1:
sql = "MERGE INTO [Link] L "
"USING( "
"VALUES ( ?, ?, TIMESTAMP_FORMAT(?,'[Link] HH24:[Link].FF3'), ?, "
" ?, ?, ?, ?)) "
[code snipped]
" ,REQUESTPARAMS = [Link] "

if debug:
print(sql)

stmt1 = ibm_db.prepare(conn, sql)

ibm_db.bind_param(stmt1, 1, str(req["requestId"]))
ibm_db.bind_param(stmt1, 2, str(req["userName"]))
[code snipped]
ibm_db.bind_param(stmt1, 8, str(req["requestParams"]))

ibm_db.execute(stmt1)
27
Auto commit
• Disable auto commit
• Modify ODBC INI file
• AUTOCOMMIT=0
• In code
ibm_db.autocommit(ibm_db.SQL_AUTOCOMMIT_OFF)

• Add explicit commits

ibm_db.commit(conn)

• Remember: must commit before disconnect !

28
Second ibm_db program, iteration #2, cont’ed
• Output from monitor
• Only two different statements
• Only 53 commits

• … oh BTW, program is significantly faster now 

29
Error handling try:
ibm_db. prepare(conn, sql)
except:
• Python has “try … except … else” print("log_request PREPARE MERGE failed: ",ibm_db.stmt_errormsg())

• ibm_db.conn_errormsg() – connection error message

• ibm_db.stmt_error() – SQLSTATE (integer)
• ibm_db.stmt_errormsg() – SQLCA (string, formatted)

log_request PREPARE MERGE failed: {DB2 FOR OS/390}{ODBC DRIVER}{DSN12015} DSNT408I

SQLCODE = -440, ERROR: NO AUTHORIZED FUNCTION NAMED XIMESTAMP_FORMAT HAVING
COMPATIBLE ARGUMENTS WAS FOUND DSNT418I SQLSTATE = 42884
SQLSTATE RETURN CODE DSNT415I SQLERRP = DSNXORFN SQL
PROCEDURE DETECTING ERROR DSNT416I SQLERRD = -100 0 0 -1 0 0 SQL
DIAGNOSTIC INFORMATION DSNT416I SQLERRD = X'FFFFFF9C' X'00000000'
X'00000000' X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC
INFORMATION ERRLOC=5:10:2 SQLCODE=-440

[Link]
.0/[Link]/doc/[Link]
Calling a stored procedure
• Issue a REORG from a python program
• Call [Link]

• ibm_db.callproc – call procedure

• ibm_db.fetch_assoc – fetch row from result set
• ibm_db.next_result – next result set

31
 Calling a stored procedure, cont’ed
utility_id = 'REORGDEF'
restart = 'NO'
utstmt = 'TEMPLATE FCOPY DSN [Link].&SSID..&SN..P&PA..&UNIQ. UNIT 3390 TEMPLATE
REC1 DSN &USERID..&SSID..UNLD.&DB..&TS. UNI
T 3390 DISP(OLD,CATLG,CATLG) TEMPLATE WORK1 DSN &USERID..&SSID..SYSUT1 UNIT 3390
TEMPLATE WORK2 DSN &USERID..&SSID..SORTOUT U
NIT 3390 REORG TABLESPACE [Link] LOG NO SORTDATA SHRLEVEL CHANGE
KEEPDICTIONARY STATISTICS TABLE(ALL) SAMPLE 60 INDEX(
ALL) COPYDDN(FCOPY) UNLDDN(REC1), WORKDDN(WORK1,WORK2)'
retcode = 0

stmt, utility_id,restart,utstmt,retcode = \
ibm_db.callproc(conn, '[Link]', (utility_id,restart,utstmt,retcode) )

32
 Calling a stored procedure, cont’ed
while stmt1:
# result set available

row = ibm_db.fetch_assoc(stmt1)
while row:
print(row[“TEXT”])
row = ibm_db.fetch_assoc(stmt1)

stmt1 = ibm_db.next_result(stmt)

33
 Calling a stored procedure, cont’ed
(test2) $ python [Link] RS01PDS1 [Link]
Running REORG for tablespace [Link] on DSN=RS01PDS1
Successfully connected to DSN=RS01PDS1
1DSNU000I 297 11:43:16.65 DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = REORGDEF
DSNU1045I 297 11:43:16.66 DSNUGTIS - PROCESSING SYSIN AS UNICODE UTF-8
0DSNU050I 297 11:43:16.66 DSNUGUTC - TEMPLATE FCOPY DSN [Link].&SSID..&SN..P&PA..&UNIQ. UNIT 3390
DSNU1035I 297 11:43:16.67 DSNUJTDR - TEMPLATE STATEMENT PROCESSED SUCCESSFULLY
[snip]
DSNU610I !PCA1 297 11:43:18.33 DSNUSUCD - SYSCOLDIST CATALOG UPDATE FOR TS5941.GLWXDPT2 SUCCESSFUL
DSNU620I !PCA1 297 11:43:18.33 DSNUSEOF - RUNSTATS CATALOG TIMESTAMP = 2020-10-23-11.43.17.393547
DSNU3357I 297 11:43:18.71 DSNUGUTC - MAXIMUM SORT AMOUNT ESTIMATION VARIATION WAS 0 PERCENT
DSNU3355I 297 11:43:18.71 DSNUGUTC - TOTAL SORT MEMORY BELOW THE BAR: OPTIMAL 24 MB, USED 24 MB
DSNU010I 297 11:43:18.73 DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST RETURN CODE=4

34
 Calling a stored procedure –
ADMIN_COMMAND_DB2
(test2) $ python [Link] RS01PDS1 “-DIS GROUP”
Issue command on DSN=RS01PDS1: -DIS GROUP
Successfully connected to DSN=RS01PDS1
DSN7100I !PCA1 DSN7GCMD
*** BEGIN DISPLAY OF GROUP(PDS1 ) CATALOG LEVEL(V12R1M507)
CURRENT FUNCTION LEVEL(V12R1M508)
HIGHEST ACTIVATED FUNCTION LEVEL(V12R1M508)
HIGHEST POSSIBLE FUNCTION LEVEL(V12R1M508)
PROTOCOL LEVEL(2)
GROUP ATTACH NAME(PDS1)
[snip]
*** END DISPLAY OF GROUP(PDS1 )
DSN9022I !PCA1 DSN7GCMD 'DISPLAY GROUP ' NORMAL COMPLETION
35
Bonus topic – interact with z/OS
• Datasets
• Jobs
• Other z/OS services

Unfortunately no de-facto standard library for z/OS stuff 

36
Interact with z/OS
• Use [Link], [Link], [Link]
import os

stream = [Link]('uname')
output = [Link]()
if [Link]("OS/390"):
print("Yeah, running on z/OS!")

import os

stream = [Link]('tsocmd "alloc da([Link]) space(1,1) new"')

output = [Link]()

print(output)
37
Interact with z/OS
• Rocket python
• Supports f = open(“//’[Link]’”,”r”) syntax
• Has “zos” module with dynalloc, load a module, ps

• IBM Z Open Automation Utilities

• No charge
• Wraps MVS utilities like IEBCOPY, IDCAMS, IKJEFT01, etc
• Wraps SDSF REXX API
• Supports shell scripts, Java and Python
• A bit inefficient to call IDCAMS LISTCAT to check if dataset exists…

38
Interact with z/OS
import requests
url =
• z/OSMF REST APIs '[Link]
s?prefix=IZPS*&owner=*'
• Also expensive to call auth = [Link]('userid', 'password')
print(auth)
• Will work on both z/OS
and distributed resp = [Link](url,auth=auth,verify=False)
if [Link]:
print("%8s %8s %8s %8s %8s" % \
("Jobname", "JobId", "Owner", "Status","Retcode"))
for j in [Link]():
print("%8s %8s %8s %8s %8s" % \
( j["jobname"], j["jobid"], j["owner"],
j["status"], j["retcode"] ))

39
Interact with z/OS
import requests
url =
• z/OSMF REST APIs
Jobname JobId
IZPSRV STC01178
Owner
DOESTC
Status Retcode
'[Link]
OUTPUT CC 0000
s?prefix=IZPS*&owner=*'
• Also expensive to call
IZPSRV STC04386
IZPSRVP STC04120
DOESTC
DOESTC
OUTPUT CC 0000
auth = [Link]('userid', 'password')
OUTPUT CC 0000
print(auth)
• Will work IZPSRVP
on both z/OS
STC04121
IZPSRVP STC04113
DOESTC
DOESTC
OUTPUT CC 0000
OUTPUT CC 0000
and distributed
IZPSRVP STC04119
resp = [Link](url,auth=auth,verify=False)
DOESTC OUTPUT CC 0000
if [Link]:
IZPSRVP STC04112 DOESTC OUTPUT CC 0000
print("%8s %8s %8s %8s %8s" % \
IZPSRVP STC04111 DOESTC OUTPUT CC 0000
("Jobname", "JobId", "Owner", "Status","Retcode"))
IZPSRVP STC04110 DOESTC OUTPUT CC 0000
for j in [Link]():
IZPSRV STC04387 DOESTC ACTIVE None
print("%8s %8s %8s %8s %8s" % \
IZPSRVP STC04122 DOESTC ACTIVE None
( j["jobname"], j["jobid"], j["owner"],
IZPSRV2 STC07395 DOESTC ACTIVE None
j["status"], j["retcode"] ))

40
Interact with z/OS
• CFFI
• Create python wrapper over C libraries, e.g., “access()” to check if file exists

41
 Python is popular

Python can talk to Db2 for

z/OS
Conclusion
Python can run on z/OS
(read: USS)

Python can run on z/OS and

talk to Db2 for z/OS

42
Please submit your session feedback!
• Do it online at [Link]

• This session is 6BA

GSE UK Conference 2020 Charity
• The GSE UK Region team hope that you find this presentation and
others that follow useful and help to expand your knowledge of z
Systems.
• Please consider showing your appreciation by kindly donating a small
sum to our charity this year, NHS Charities Together. Follow the link
below or scan the QR Code:
[Link]