ibm_db.callprocedure throwing UnicodeDecodeError: 'utf-8' codec can't decode byte
c-muncan opened this issue · 10 comments
- 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
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?
@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.
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.
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
no luck on 3.11.4. we ordered from ShopZ so we get the latest and greatest. can't pick the version you want.
@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
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
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!
@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...
@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?
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.