Specifying CURRENTSCHEMA in connection string for Db2z results in [IBM][CLI Driver] SQL0518N The statement named in the EXECUTE statement is not in a prepared state or is a SELECT or VALUES statement. SQLSTATE=07003 SQLCODE=-518

Question

Specifying CURRENTSCHEMA in connection string for Db2z results in [IBM][CLI Driver] SQL0518N The statement named in the EXECUTE statement is not in a prepared state or is a SELECT or VALUES statement. SQLSTATE=07003 SQLCODE=-518

b-tsao opened this issue 7 months ago · 8 comments

Operating System Name: Ubuntu 24.04
db2level output from clidriver if in use:
Target Db2 Server Version: Db2z
Python Version: 3.11.4
ibm_db version: 3.2.3
For non-Windows, output of below commands:
uname
Linux
uname -m
x86_64
Value of below environment variables if set:
IBM_DB_HOME:
PATH:
LIB/LD_LIBRARY_PATH/DYLD_LIBRARY_PATH:
Test script to reproduce the problem.

import ibm_db

conn_str = "HOSTNAME=<host>;PORT=<port>;DATABASE=<location>;UID=<user>;PWD=<password>;CURRENTSCHEMA=OTHERUSER"
conn = ibm_db.connect(conn_str, "", "")
stmt = ibm_db.exec_immediate(conn, "SELECT * FROM SYSTABLES")
result = ibm_db.fetch_both(stmt)
while (result):
    print("Result:", result[0])
    result = ibm_db.fetch_both(stmt)

Steps to Reproduce:

Run the script
See the error

Traceback (most recent call last):
  File "/test.py", line 25, in <module>
    conn = ibm_db.connect(conn_str, "", "")
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Exception: [IBM][CLI Driver] SQL0518N  The statement named in the EXECUTE statement is not in a prepared state or is a SELECT or VALUES statement.  SQLSTATE=07003 SQLCODE=-518

Answer 1 · 2024-06-19T07:29:49.000Z

Please show the db2level output from your cli driver.

When the cli packages were bound into your Db2z environment, what options were used?

It would help if you supplied the db2trc files (.fmt, .flw, .fmtcli) from your workstation captured while the connection attempt is happening, to allow to see what is happening under the covers.

Answer 2 · 2024-06-19T07:35:26.000Z

@b-tsao The latest ibm_db version is 3.2.3 and you mentioned 3.2.4. Kindly check once.

Answer 3 · 2024-06-19T22:11:39.000Z

@b-tsao The latest ibm_db version is 3.2.3 and you mentioned 3.2.4. Kindly check once.

Apologies, it's using 3.2.3

Answer 4 · 2024-06-19T22:50:19.000Z

Please show the db2level output from your cli driver.

When the cli packages were bound into your Db2z environment, what options were used?

It would help if you supplied the db2trc files (.fmt, .flw, .fmtcli) from your workstation captured while the connection attempt is happening, to allow to see what is happening under the covers.

Hi @imavo, thanks for responding.

When I ran db2level from my cli driver I got
Segmentation fault (core dumped)

I ran /-DB2A DISPLAY GROUP from my z system and got this db2level

I didn't supply any additional options to db2cli for bind
./db2cli bind $IBM_DB_HOME/bnd/@db2cli.lst -database <database>:<host>:<port> -user <user> -passwd <password>

I'm not sure what the .fmtcli stands for but I have the dump file (trace.dmp) and the other files generated from trace.dmp using db2trc
trace.zip

Answer 5 · 2024-06-20T07:43:47.000Z

The db2level should not cause segmentation-violation, but that is not the cause of your symptom here.

By fmtcli, I meant: db2cli fmt -cli trace.dmp trace.fmtcli which gives a shorter trace that can be helpful to navigate bigger trace files. However I was able to generate that file because you supplied the trace.dmp.

I suspect you are using the clidriver version that comes with the python ibm_db release 3.2.3.

It seems the problem is not in python ibm_db itself, but either in the clidriver or in the packages on Db2z.

Please can you prove this, by attempting a connection directly from db2cli (i.e. not involving python or ibm_db) , by runing db2cli validate -connect -connstr .... (append the same connection string) , and please report the result.

Additionally, can you make these further checks in your environment below:

does same error happen with a different database on z/os.
does same error happen with other permitted keyword-attribute pairs in the connection-string (other than currentschema)
if you omit currentschema does the connection succeed?
Does using the ibm_db.set_option() to change the current schema work?

Answer 6 · 2024-06-20T22:21:31.000Z

./db2cli validate -connect -connstring "HOSTNAME=<host>;PORT=<port>;DATABASE=<location>;UID=<user>;PWD=<password>;CURRENTSCHEMA=OTHERUSER"

===============================================================================
Client information for the current copy:
===============================================================================

Client Package Type       : IBM Data Server Driver For ODBC and CLI
Client Version (level/bit): DB2 v11.5.9.0 (special_36648/64-bit)
Client Platform           : Linux/X8664
Install/Instance Path     : /usr/local/lib/python3.9/site-packages/clidriver
DB2DSDRIVER_CFG_PATH value: <not-set>
db2dsdriver.cfg Path      : /usr/local/lib/python3.9/site-packages/clidriver/cfg/db2dsdriver.cfg
DB2CLIINIPATH value       : <not-set>
db2cli.ini Path           : /usr/local/lib/python3.9/site-packages/clidriver/cfg/db2cli.ini
db2diag.log Path          : /usr/local/lib/python3.9/site-packages/clidriver/db2dump/db2diag.log

...

===============================================================================
Connection attempt for database "<db>":
===============================================================================

[FAILED]: [IBM][CLI Driver][DB2] SQL0518N  The statement named in the EXECUTE statement is not in a prepared state or is a SELECT or VALUES statement.  SQLSTATE=07003


===============================================================================
Error: The validation operation failed.
===============================================================================

===============================================================================
The validation is completed.
===============================================================================

does same error happen with a different database on z/os.

I don't have another database set up with ODBC so not entirely sure

./db2cli validate -connect -connstring "HOSTNAME=<host>;PORT=<port>;DATABASE=<location>;UID=<user>;PWD=<password>;AUTHENTICATION=SERVER;PROTOCOL=TCPIP"

...

[SUCCESS]

Output Connection String : 
"UID=ADMF001;PWD=********;HOSTNAME=<host>;PORT=<port>;DATABASE=<location>;AUTHENTICATION=SERVER;PROTOCOL=TCPIP;"

===============================================================================
The validation is completed.
===============================================================================

if you omit currentschema does the connection succeed?

Yes

Does using the ibm_db.set_option() to change the current schema work?

When I use success = ibm_db.set_option(conn, {ibm_db.SQL_ATTR_CURRENT_SCHEMA: "OTHERUSER"}, 1)
- success prints True but the schema OTHERUSER isn't used

Answer 7 · 2024-06-21T05:45:43.000Z

So the symptom is happening independently of the python and ibm_db.

This means there may be a potential problem either in the clidriver or in the cli-packages that are bound on Db2z.

These are made by IBM (but currently supported/maintained by rocketsoftware) and have a lifecycle that is independent of python, and are used by many other non-python languages ( e.g. perl, r, php, nodejs, etc) . So the chances are that IBM already knows about this issue and possibly has a solution or workaround.

I don't have any other suggestions or questions, except to advise to follow this up with IBM.

If you have a support contract for Db2z (which all Z customers will have) then I would suggest you open a ticket with Db2-support, in fact open 2 or maybe 3 different tickets. First ticket is for the db2cli validate -connect -connstring... failing (they will need the tracefiles as before).

Second ticket is for the db2level of the clidriver causing a segmentation violation on your workstation (they might need the strace output, along with the db2trc trace).

A possible third ticket is to uncover why the ibm_db.set_option for changing the schema is not apparently having the desired effect, but that would need to be proved at CLI level before Db2-support would accept it (and such proof is not currently visible to me). You might want to spend a bit more time examining why the set_option is not having an effect (use trace again, and carefully examime the fmtcli output), and show a full testcase showing that dynamic-sql with an unqualified reference is not picking up the currentschema.

Answer 8 · 2024-06-21T18:52:41.000Z

Got it, thanks @imavo !