UnicodeDecodeError: After upgrading from V7R2 to V7R3 ibm_db.result() is failing

[nadir-syed]

Versions/Env:
python 3.6.10
ibm-db 2.0.5.12
OS: IBM i 7.2
local database: DB2

ibm_db.result(stmt, 'STRMNAME') is failing with "UnicodeDecodeError: 'utf-8' codec can't decode byte 0xdd in position 38: invalid continuation byte". Data in STRMNAME does NOT have 38th position, infact Column itself is 30 char long.

Here is the data:

STREAMID | STRMNAME | TRANDT | TRANTOT | TRANCAT | TRANDESC
1 | DAD SMITH | 1/1/2019 | 5833.33 | PAYCHECK | DAD SMITH PAYROLL
1 | DAD SMITH | 2/1/2019 | 5833.33 | PAYCHECK | DAD SMITH PAYROLL
1 | DAD SMITH | 3/1/2019 | 5833.33 | PAYCHECK | DAD SMITH PAYROLL
1 | DAD SMITH | 4/1/2019 | 5833.33 | PAYCHECK | DAD SMITH PAYROLL
1 | DAD SMITH | 5/1/2019 | 5833.33 | PAYCHECK | DAD SMITH PAYROLL
1 | DAD SMITH | 6/1/2019 | 9724.33 | PAYCHECK | DAD SMITH PAYROLL

Same python script works in python 3.6.9 and ibm-db 2.0.5.9 as well as ibm-db 2.0.5.12 on other IBM i boxes.

Why is it failing? How can I make it work in ibm-db 2.0.5.12?

ibm_db.client_info(conn) result on IBM i is:

DRIVER_NAME: string(13) "QSQCLI.SRVPGM"
DRIVER_VER: string(5) "07030"
DATA_SOURCE_NAME: string(8) "XXXXXXXX"
DRIVER_ODBC_VER: string(4) "3.00"
ODBC_VER: string(0) ""
ODBC_SQL_CONFORMANCE: string(4) "CORE"
APPL_CODEPAGE: int(1208)
CONN_CODEPAGE: int(1208)

[kadler]

Just a heads up that I'll be out for the next few weeks, but I will look in to this when I get back. Seems like maybe it's not getting back the correct length or it isn't getting null-terminated correctly. Can you find the PTF level of QSQCLI with DSPOBJD QSYS/QSQCLI *SRVPGM DETAIL(*SERVICE)

[nadir-syed]

@kadler Here is PTF level of QSQCLI

For server having issues:

Source file  . . . . . . . . . . . . :                     
  Library  . . . . . . . . . . . . . :                     
Member . . . . . . . . . . . . . . . :                     
Attribute  . . . . . . . . . . . . . :                     
User-defined attribute . . . . . . . :                     
Freed  . . . . . . . . . . . . . . . :   NO                
Size . . . . . . . . . . . . . . . . :   5541888           
Creation date/time . . . . . . . . . :   09/02/20  22:48:51
Source file date/time  . . . . . . . :                     
System level . . . . . . . . . . . . :   V7R3M0            
Compiler . . . . . . . . . . . . . . :   CRTSRVP    V7R3M0 
Object control level . . . . . . . . :   02462248         
Changed by program . . . . . . . . . :   NO               
User modified  . . . . . . . . . . . :   NO               
Licensed program . . . . . . . . . . :   5770SS1    V7R3M0
PTF number . . . . . . . . . . . . . :   SI74238          
APAR ID  . . . . . . . . . . . . . . :                    
Text . . . . . . . . . . . . . . . . :                    

And for server working fine:

System level . . . . . . . . . . . . :   V7R2M0           
Compiler . . . . . . . . . . . . . . :   CRTSRVP    V7R2M0
Object control level . . . . . . . . :   91981328         
Changed by program . . . . . . . . . :   NO               
User modified  . . . . . . . . . . . :   NO               
Licensed program . . . . . . . . . . :   5770SS1    V7R2M0
PTF number . . . . . . . . . . . . . :   SI70581          
APAR ID  . . . . . . . . . . . . . . :                    
Text . . . . . . . . . . . . . . . . :                    

It is different on server having issue than all other servers not having issues. I am thinking to ask iSeries admins to update PTF to SAME as working servers SI70581.

[nadir-syed]

@kadler Server having issues is at a higher OS level of V7R3 with a fairly current set of groups installed like DB2 group (SF99703) is at level 22.
All other working servers are at V7R2 and DB2 group (SF99702) is at level 26.

So iSeries admins said it cannot have other servers PTF - SI70581 and question is: "which QSQCLI PTF is recommended for both V7R2 and V7R3 OS levels"?

[nadir-syed]

Issue is specifically in ibm_db.result(stmt, 'STRMNAME') when result set is fetched using ibm_db.fetch_row(stmt). Code works If I fetch result set using dict = ibm_db.fetch_assoc(stmt) and use dict['STRMNAME'].

ibm_db.result(stmt, xxx) is probably not getting correct length somehow. Not sure what is causing the issue in ibm_db.result method.

[kadler]

Yeah, my guess is that either the wrong length is being returned or it is not getting null-terminated properly.

What is the data type for STRMNAME?

[nadir-syed]

Yeah, my guess is that either the wrong length is being returned or it is not getting null-terminated properly.

What is the data type for STRMNAME?

STREAM_NAME FOR COLUMN STRMNAME
CHAR(30) NOT NULL
DEFAULT ' ',

[kadler]

Can you please tell me what the CCSID of the data is?

Also, what is the output of system dspjob | grep -E 'CNTRYID|LANGID|CCSID'?

Also, what is the PASE locale set to? (output of locale)

[nadir-syed]

Can you please tell me what the CCSID of the data is?

Also, what is the output of system dspjob | grep -E 'CNTRYID|LANGID|CCSID'?

Also, what is the PASE locale set to? (output of locale)

Data CCSID is: 37 and is same on ALL servers: working, not working.

system dspjob | grep -E 'CNTRYID|LANGID|CCSID'
Language identifier . . . . . . . . . . . . : LANGID ENU
Country or region identifier . . . . . . . . : CNTRYID US
Coded character set ID . . . . . . . . . . . : CCSID 37
Default coded character set ID . . . . . . . : DFTCCSID 37

locale
LANG=en_US
LC_COLLATE="en_US"
LC_CTYPE="en_US"
LC_MONETARY="en_US"
LC_NUMERIC="en_US"
LC_TIME="en_US"
LC_MESSAGES="en_US"
LC_ALL=

[kadler]

I was unable to recreate this, but it seems that PTF SI74238 was related to some UTF-8 changes and had some problems elsewhere as well and a new APAR was created to address those issues: https://www.ibm.com/support/pages/apar/SE75216

Would be worth a try to see if it fixes your issue.

[kadler]

I did some tracing of the data returned with SI74238 and it doesn't seem to null-terminate properly, which leads to unpredictable results. It's likely that in the initial testing I was doing the data following the buffer happened to be a null byte or valid UTF-8 data followed by a null byte. Either way, the results are unpredictable and incorrect.

AFAICT, this only occurs when the results were truncated, which occurs because ibm_db.result does not allocate enough space to handle UTF-8 expansion like we do when using ibm_db.fetch_tupleoribm_db.fetch_assoc`. (Note: Even when we expand the size of the buffer to handle UTF-8 expansion, CLI prior to SI74238 will still truncate the result)

I will need to add code to handle expansion as well as ensure that the result is null-terminated.

[kadler]

I've made some changes to handle UTF-8 expansion. You can test it out easily by unzipping ibm_db.so.zip to where your script is and re-running it (or unzip it somewhere and set PYTHONPATH=/path/to/directory/containing/unzipped python3 ...

[nadir-syed]

I've made some changes to handle UTF-8 expansion. You can test it out easily by unzipping ibm_db.so.zip to where your script is and re-running it (or unzip it somewhere and set PYTHONPATH=/path/to/directory/containing/unzipped python3 ...

@kadler How to unzip in PASE on IBM i? unzip, uncompress, unpack - no command is working.

unzip /home/hxxx/python/ibm_db.so.zip
/QOpenSys/usr/bin/-sh: unzip: not found.
unpack /home/hxxx/python/ibm_db.so.Z
unpack: /home/hxxx/python/ibm_db.so.Z.z: 0653-735 Cannot open this file.
uncompress /home/hxxx/python/ibm_db.so.Z
0653-052 The /home/hxxx/python/ibm_db.so.Z file is not compressed.

[kadler]

Unzip is available from our yum repo: https://ibmi-oss-docs.readthedocs.io/en/latest/yum/README.html

Otherwise, you can always uznip on a PC and upload it from there.

[nadir-syed]

@kadler yay! it's working! My company plans to upgrade live prod box to V7R3 OS level. When could your fix be available as official ibm_db install using yum or pip?