IBM_DB With Python Is Stuck during connect Related to Issue 763
timswiley opened this issue · 11 comments
#1. In Windows 11 WSL Unbuntu python hangs forever at db connect (never comes back). The same code when ran on the same machine via windows os connects fine.
#2. The issue i experience is just like what was reported with similar description issue 763 that was closed due to no reply. I followed the instructions to generate the trace files in that ticket.
I also successfully connected via Telnet to the db2 cloud instance/port from the wsl instance to confirm there were no firewall issues...
Ubuntu 22.04 via Windows 11 WSL
Python 3.10.12
ibm_db 3.2.3
Code
`#Generate the connection string
dbConStr = (
"DATABASE={0};"
"HOSTNAME={1};"
"PORT={2};"
"PROTOCOL={3};"
"SECURITY={4};"
"UID={5};"
"PWD={6};"
"SSLServerCertificate={7};"
).format(
dbName,
dbHost,
dbPort,
dbProtocol,
dbSecurity,
dbUserId,
dbPwd,
dbSSLServerCertificate
)
End Config Variables
import ibm_db_dbi as db
db.connect(dbConStr,"","")
print('yee')
`
trc.zip
The trace shows you are connecting to a cloud database, with SSL and an .arm file certificate, and there is no response from SQLDriverConnectW() .
Almost certainly the cause is WSL/Windows-configuration. Which WSL version is it, i.e. the build (from winver etc) and is it serviced with the latest fixes from microsoft?
It is NOT enough to only test with telnet within WSL - you have to test the same specific port-number being used for your cloud Db2-instance. Check the ubuntu firewall also (you dont mention if it is configured) to verify that port 31249 is opened in ubuntu for both inbound and outbound TCP traffic. Presumably you already have that port opened inbound/outbound on your MS-Windows-host if a local MS-Windows connection to the same Db2-host on the same (31249) portnumber will succeed in MS-Windows.
Most likely this is nothing to do with ibm_db - in fact - you might temporarily eliminate both ibm_db and python from the equation (only for the purpose of problem determination) , by creating/configuring a db2dsdriver.cfg with your target host/db/port/cert/security details inside your ibm_db environment (I presume you are using clidriver) , and testing the connection with the db2cli -validate -connect.... method. (Refer to the Db2-LUW docs for details of the exact command line). .
telnet allows you to pass in the port following the host name and you can do manual interactions with the response... So of course i tested the host and the port and got a response from the Cloud db2 instance from within WSL...
Lastly... As someone who is very familiar with db connections and working with database Oracle, SQL Server, Postgres, MySQL, DB2... python and DB2 seem to be extremely difficult or have crazy exceptions/workarounds... On my windows os it was also not straight fwd as i had to add some path env variables to the python program to the IBM Cli library because it would not intiially import the ibm_db module due to DLL library load failures... None of this is as simple as JDBC enabled apps and frankly... The process outside of networking issues/firewalls process really should much simpler than it is especially in python... Aka install python... pip install ibm_db... get the connection details and connect... But it never ever seems to be the case with Python and DB2... And don't get me started with DB2 Bigsql So just throwing that out there...
All true regarding Db2 complexity and general difficulty, but whining will not solve your current problem,
regardless of your frustrations.
But determined problem-determination and eliminations at your end might get to root cause.
Do you have a packet sniffer to observe what packets are going into/out-of your WSL when the connection attempt is in progress?
When you telnet (from inside WSL) to your cloud-host and specify the port 31249, what exactly is the response?
Separately, it might be helpful to do some eliminations at your end, to narrow down the problem.
By which I mean:
(0) Perform the db2clidriver -validate -connect... as previously mentioned, to verify clidriver is the component that is hanging . Some preconfiguration of db2dsdriver.cfg required, but deal with it.
(1) can you connect to any local (i.e. same network as the WSL-host) db2-instance from within WSL? (even if there is a Db2-server instance running on the local Ms-windows host). If you don't have any local Db2-instance on the same network as your MS-Windows machine, it's easy to run one up (Db2 community edition standalone). They are useful to have around.
(2) Can you connect to any local Db2-instance with ssl enabled from within WSL, and without SSL-enabled from within WSL?
Make sure your WSL is serviced at latest fixes from MS also.
The frustrations are from not so subtle dismissive/provoking responses where people think they dismissively know how Telnet works in threads like this with capital "not" rather than sincerely trying to help someone with a problem. The telnet response is below... immediate from within the WSL terminal
I will try the b2cli validate later today as i'm pressed for time at the moment.... DB2 community installation on Ubuntu 22.04 has proven ridiculously riddled with issues and lots of posted notes as of such to the point where folks indicate its just not supported and they had to go back on Ubuntu version. So i will try to install on windows os later for local... but i'm sure that too will be riddled with issues... My perspective is trying to enjoy the cloud promise of turning on a db2 cloud instance and seeing how simple it is to connect using python as compared to other cloud based services so it saved me time/complexity of having to install/run locally etc... I will say this years issue is new... As i do this once a year to test ease of use and overall complexity of vendor cloud envs... The previous 2 years while i had challenges it was not as bad as this year... Entirely different set of issues this year... I remember one year it was the wrong version of a cli libary file that had to be swapped out that was packeged with the ibm_db library...
Double check that the port you are using really is an SSL port (just temorarily remove security=ssl, and the sslServerCertificate keywords from the connection string).
I run Db2-LUW 11.5.8+ on 20.04 and 22.04 (but installation was on 18.04 which was then upgraded to 22.04 later on) for development purposes without issue. However I do not run WSL so I cannot try out what you are doing. I also run Db2-LUW on Win64 for development without issue, but try to avoid using MS-Windows where possible. Also I run Db2-LUW community-edition, both standalone, and also in the prebuilt linux container supplied by IBM (docker on linux however) which is useful for disposable environments.
They all "just work" but I'm familiar with all the crap that Db2 forces on you.
Ok so i ran the db2cli connect test and it was successful. See screenshot...
Connectivity works by uninstalling ibm_db 3.2.3 and going back to 3.2.1
I can see that the clidriver 11.5.9.0 special_36648 has successfully connected with db2cli validate -connect... (that is the clidriver version that comes currently with ibm_db 3.2.3).
The trace file seems to show that there was a problem (whie performing SQLConnectW()) in the GSKit (an additional ibm component, parts of which get bundled with clidriver, containing the encryption/decryption algorithms used with SSL/TLS).
If clidriver can connect, the python ibm_db must also connect (assuming all DSN details are identical and no external changes were made), because they use same underlying API in clidriver to connect ( and the same GSkit).
You imply that while db2cli validate -connect ... works, the matching ibm_db.connect() with the same dsn hangs. I've not seen that previously.
I cannot explain the behaviour that you observe, so I assume something has changed externally.
Each version of ibm_db might come with a different version of clidriver (it has a lifecycle/versioning that is independent/unrelated to that of ibm_db lifecycle, and clidriver gets used by several different programming languages, not only python). And inside that clidriver version might be a different version of the GSkit libraries although they change less frequently than clidriver. To see the version of the clidriver , run its db2level command (in the bin directory).
Perhaps the folks at rocketsoftware might shed some light on this.
Just out of curiosity i went ahead and re-installed 3.2.3 via pip uninstall ibm_db which was 3.2.1 and then of course pip install ibm_db which pulled 3.2.3 And connectivity works... no hanging... Very weird...
Thanks for the extended help... I'm not sure what happened but it seems to work now after uninstall reinstall the package.