ibm_db.callprocedure throwing UnicodeDecodeError: 'utf-8' codec can't decode byte

[c-muncan]

• Operating System Name: z/OS 2.4
• db2level output from clidriver if in use:
• Target Db2 Server Version: V12 & V13
• Python Version: 3.11.5
• ibm_db version:
• TST1:/u/a463:->py -m pip list

Package Version

---

cffi 1.14.6
cryptography 3.3.2
dateutils 0.6.12
ebcdic 1.1.1
ibm-db 3.2.0
numpy 1.23.4.post0
pandas 1.5.1.post3
pip 23.2.1
pycparser 2.20
python-dateutil 2.8.2
pytz 2022.4
regex 2023.6.3
setuptools 65.6.3
six 1.16.0
XlsxWriter 3.1.2
zoautil-py 1.2.3
zos-util 1.0.1

• For non-Windows, output of below commands:
  uname
  OS/390
  uname -m
  8562
• Value of below environment variables if set:

IBM_DB_HOME=SFDB.DSN124

PATH=/bin:/usr/lpp/Printsrv/bin:/usr/lpp/java/current_31/bin:/usr/lpp/ixm/IBM/xml4c-5_7/bin:/usr/lpp/IBM/zoautil/bin:/usr/lpp/Rocket/rsusr/ported/bin:/usr/lpp/c
yp/v3r11/pyz/bin:.

CLASSPATH=.:/usr/lpp/mqm:/usr/lpp/smp/classes:/usr/lpp/IBM/zoautil/lib

LIBPATH=/lib:/usr/lib:/usr/lpp/mqm/:/usr/lpp/Printsrv/lib:/usr/lpp/ixm/IBM/xml4c-5_7/lib:/usr/lpp/IBM/zoautil/lib:/usr/lpp/Rocket/rsusr/ported/lib:/usr/lpp/cyp/
v3r11/pyz/lib:.

_CEE_RUNOPTS=FILETAG(AUTOCVT,AUTOTAG) POSIX(ON)

DSNAOINI=/u/a463/odbc_tst1.ini

ls -laT $DSNAOINI
b binary T=off -rw-r----- 1 A463 USSDFTG 209 Apr 1 14:20 odbc_tst1.ini

file contents

[COMMON]
MVSDEFAULTSSID=T2D2
CONNECTTYPE=1
MULTICONTEXT=2
FLOAT=IEEE
CURRENTAPPENSCH=ASCII
DIAGTRACE=1
DIAGTRACE_NO_WRAP=0
DIAGTRACE_BUFFER_SIZE=2000000

[T2D2]
AUTOCOMMIT=1
MVSATTACHTYPE=CAF
PLANNAME=DSNACLI

I am having errors for a few of the functions. I don't get this in my mainframe zDT ADCD environment. We are just rolling out Python and I'm having these new issues in this environment which I didn't have before.

ibm_db.tables sample code

import sys
import traceback
import os
import ibm_db

try:
    ibm_db.connect('','','')
except Exception:
    print(traceback.format_exc())
    print("------")

try:
    db2_conn = ibm_db.connect('','','')
except Exception:
    print(traceback.format_exc())
    print("------")
    exit(-1)

print("------------------------------------------------------")
print('FETCH ASSOC')
print("------------------------------------------------------")

schemaName = "ZBQSTSOT"
resultSet = ibm_db.tables(db2_conn, None, schemaName, '%', None)
dataRecord = ibm_db.fetch_assoc(resultSet)
while dataRecord:
#   print("Table schema  : {}" .format(dataRecord['TABLE_SCHEM']))
    print("Table name    : {}" .format(dataRecord['TABLE_NAME']))
    print("Table type    : {}" .format(dataRecord['TABLE_TYPE']))
    dataRecord = ibm_db.fetch_assoc(resultSet)
    print("------------------------------------------------------")
ibm_db.close(db2_conn)

output

------------------------------------------------------                                     
FETCH ASSOC                                                                                
------------------------------------------------------                                     
UnicodeDecodeError: 'utf-8' codec can't decode byte 0x85 in position 40: invalid start byte
                                                                                           
The above exception was the direct cause of the following exception:                       
                                                                                           
Traceback (most recent call last):                                                         
  File "/u/a463/db2fetch_assoc.py", line 25, in <module>                                   
    resultSet = ibm_db.tables(db2_conn, None, schemaName, '%', None)                       
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^                       
SystemError: <built-in function tables> returned a result with an exception set            
whenever calling stored procedures via the ibm_db.callprocedure

fetch_both works just fine. so does field_name, but callprocedure seems to be failing me always. I've even tried other scripts which yield similar results.

db2util.py

import sys
import traceback
import os
import ibm_db

conn=ibm_db.connect('','','')

stmtTxt='LISTDEF LIST_TS INCLUDE TABLESPACE ZBQSTSDZ.BQDMYTS RUNSTATS TABLESPACE LIST LIST_TS TABLE (ALL) INDEX(ALL) SHRLEVEL CHANGE UPDATE ALL'
utility_id = 'PYTHUTIL'
restart = 'NO'
retcode = 0
stmt1=None

stmt1, utility_id,restart,stmtTxt,retcode = \
ibm_db.callproc(conn, 'SYSPROC.DSNUTILU', (utility_id,restart,stmtTxt,retcode) )

output

TST1:/u/a463:->py db2util.py                                                        
Traceback (most recent call last):                                                  
  File "/u/a463/db2util.py", line 15, in <module>                                   
    ibm_db.callproc(conn, 'SYSPROC.DSNUTILU', (utility_id,restart,stmtTxt,retcode) )
Exception: Describe Param Failed:                                                   

db2zparm.py

import sys
import traceback
import os
import ibm_db

conn=ibm_db.connect('','','')

stmt1 =''
db2_member=None
retcode=0
message ='-'
spParms = (db2_member, retcode, message)
stmt1, db2_member, retcode, message = ibm_db.callproc(conn, "SYSPROC.ADMIN_INFO_SYSPARM", spParms)

if (stmt1):
  result = ibm_db.fetch_tuple(stmt1)
  print(result)

output

TST1:/u/a463:->py db2zparm.py                                                                         
Traceback (most recent call last):                                                                    
  File "/u/a463/db2zparm.py", line 13, in <module>                                                    
    stmt1, db2_member, retcode, message = ibm_db.callproc(conn, "SYSPROC.ADMIN_INFO_SYSPARM", spParms)
                                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Exception: Describe Param Failed:                                                                     

ultimately it seems to fail whenever calling a stored procedure.

now this does look similar to #852 but I've confirmed my settings quite a fair bit too and am fairly confident it isn't the same problem.

I captured the trace output by updating my ini file to include

DIAGTRACE=1
DIAGTRACE_NO_WRAP=0
DIAGTRACE_BUFFER_SIZE=2000000

It generated a lovely file called "DD:DSNAOTRC" in the directory where I ran my script. ran
./dsnao64t fmt DD\:DSNAOTRC trace.txt

C-MuncanDb2Trace.txt

set |grep BPX;set |grep CEE;set |grep STEPLIB;set |grep TAG;set |grep DSNAOINI

_BPXK_AUTOCVT="ON"                                                                                          
_BPX_SHAREAS="YES"                                                                                          
_BPX_SPAWN_SCRIPT="YES"                                                                                     
_BPX_TERMPATH="OMVS"                                                                                        
_CEE_DMPTARG="SYSOUT(7)"                                                                                    
_CEE_RUNOPTS="FILETAG(AUTOCVT,AUTOTAG) POSIX(ON)"                                                           
STEPLIB="T2D2.X00T.DSNEXIT:T2D2.X00T.SDSNLOD2:T2D2.X00T.DSNLOAD:TSAPF.TS1.MQ.SCSQAUTH:TSAPF.TS1.MQ.SCSQANLE"
_CEE_RUNOPTS="FILETAG(AUTOCVT,AUTOTAG) POSIX(ON)"                                                           
_TAG_REDIR_ERR="txt"                                                                                        
_TAG_REDIR_IN="txt"                                                                                         
_TAG_REDIR_OUT="txt"                                                                                        
DSNAOINI="/u/a463/odbc_tst1.ini"

please take a look and let me know if there's anything else I'm missing?

[bimalkjha]

@jthyssenrocket Please have your comment on this issue. It seems upgrade to python 3.11.5 on z/OS is causing UnicodeDecodeError: 'utf-8' codec can't decode byte 0x85 in position 40: invalid start byte. Thanks.

[jthyssenrocket]

I don't have IBM Python 3.11.5, but @c-muncan's programs run fine for me with Python 3.11.4 + ibm_db 3.2.0.

[c-muncan]

Thanks for checking @jthyssenrocket. If you have a PAX install of 3.11.4 handy, I'd love that.

I've asked our systems folks to see if we can install 3.11.4 or to provide me with the installs so I can install it on our zDT ADCD environment. Presently it is working in our ADCD environment, but there we have Python 3.11.0 + ibm_db 3.2.0

[c-muncan]

no luck on 3.11.4. we ordered from ShopZ so we get the latest and greatest. can't pick the version you want.

[c-muncan]

@Earammak not sure if you maybe had a chance to look further into this? Any advice from anyone? it is a huge show stopper for us

[bchoudhary6415]

Hello @c-muncan
Can you please try to set below content in your odbc_tst1.ini file

[COMMON]
MVSDEFAULTSSID=T2D2
CURRENTAPPENSCH=UNICODE
FLOAT=IEEE
APPLTRACE=1
APPLTRACEFILENAME=u/a463/odbc_appl_trace
DIAGTRACE=0
DIAGTRACE_NO_WRAP=0
DIAGTRACE_BUFFER_SIZE=60000000
[T2D2]
MVSATTACHTYPE=RRSAF
MULTICONTEXT=0
PLANNAME=DSNACLI
[database name of "T2D2" Subsystem]
AUTOCOMMIT=1
CURSORHOLD=1

Also in your .profile file try to set

export _BPXK_AUTOCVT='ON'
export _CEE_RUNOPTS='FILETAG(AUTOCVT,AUTOTAG) POSIX(ON) XPLINK(ON)'
export PATH=/rsusr/python/bin:$PATH
export LIBPATH=/rsusr/python/lib:$PATH
export STEPLIB=T2D2.X00T.DSNEXIT:T2D2.X00T.SDSNLOD2:T2D2.X00T.DSNLOAD:TSAPF.TS1.MQ.SCSQAUTH:TSAPF.TS1.MQ.SCSQANLE
export IBM_DB_HOME=SFDB.DSN124
export DSNAOINI=$/u/a463/odbc_tst1.ini

Please refer the below link to install ibm_db on zos and try to set all the environment variables properly
https://github.com/ibmdb/python-ibmdb/blob/master/INSTALL.md#inszos

Also you can upgrade your ibm_db from 3.2.0 to 3.2.3 and try.

Thank you

[c-muncan]

I found the problem! My issue was 2-fold.

First, I updated encoding scheme from EBCDIC to UNICODE as per @bchoudhary6415
--> CURRENTAPPENSCH=UNICODE

This then actually output an error message. It turns out, it was a -805 reason code 03 (no matching dbrm) for package DSNCLIMS. I went to bind the package as per DSNTIJCL but was getting -189 explicitly for DSNCLIMS. This is apparently a known issue and has its own KB https://www.ibm.com/support/pages/sqlcode189-rebind-package-dsnaoclidsnclims

Bind with SQLERROR(CONTINUE) to bypass this error.

I did that and then magically everything was working!!!

Just an FYI, in my ADCD environment, I did not have to do any of this, in fact, my CURRENTAPPENCH is set to EBCDIC.

Thank you @jthyssenrocket, @bchoudhary6415 & @bimalkjha ... this saved our project!

[jthyssenrocket]

@c-muncan I think the root cause for the error message is the missing bind.

We have a known issue with error messages sometimes being returned in an unexpected code page (see #876 (comment)). This issue is still unresolved (CC @bchoudhary6415 ).

Can you retry with CURRENTAPPENCH=ASCII after having done the missing bind? It should work...

[c-muncan]

@jthyssenrocket yes it does work using CURRENTAPPENSCH=ASCII now...but is this correct? should I be using ASCII instead of UNICODE? Can you help explain the difference as I would have thought UNICODE was correct rather than ASCII?

[jthyssenrocket]

Unfortunately, we inconsistently use the regular ODBC APIs which are affected by CURRENTAPPENSCH and the "W" (wide, UTF-16) ODBC APIs which are not affected by CURRENTAPPENSCH.

Where we use the regular APIs we implicitly assume that ODBC returns ASCII, so I strongly recommend CURRENTAPPENSCH=ASCII. For a North American customer it probably doesn't matter if you're using UNICODE (UTF-8) or ASCII.

We have an internal requirement open to switch to consistently use of the ODBC "W" (UTF-16) APIs, but it is a large development item, so no delivery date at this point.

Reference: https://www.ibm.com/docs/en/db2-for-zos/13?topic=data-db2-odbc-unicode-support.