Connect to IBM DB2 on Cloud using python api ibm_db

SahooAshis commented 3 years ago

Can you please let me know the steps to connect to IBM DB2 on cloud?

amukherjee28 commented 3 years ago

Are you facing any issue while connecting to IBM DB2 on cloud ?

In case you need help on how to connect to IBM DB2 cloud database I suggest you have a look at the examples of the ibm_db API documentation of the following API https://github.com/ibmdb/python-ibmdb/wiki/APIs#ibm_dbconnect

You need to replace the connection string with actual details.

SahooAshis commented 3 years ago

I have tried with below syntax

conn=ibm_db.connect("DATABASE=database;HOSTNAME=hostname;PORT=port;PROTOCOL=TCPIP;UID=username;PWD=password",'','')

But I'm getting below error

SQLCODE=-30082n: [IBM][CLI Driver] SQL30082N Security processing failed with reason "17" ("UNSUPPORTED FUNCTION"). SQLSTATE=08001

Can you please let me know the step by step process to connect to cloud db2?

amukherjee28 commented 3 years ago

@SahooAshis

Could you provide a few more details.

What is the environment you are running your application (windows/Linux/Mac)
Are you trying a normal connection to IBM DB2 on cloud or are you trying SSL connection to the secured port 50001.
What steps did you follow to install python driver (pip install ibm_db)
Are you using the clidriver shipped with the ibm_db installation or are you using any different driver specified using the environment variable IBM_DB_HOME

Could you please provide the following details in order to understand the issue better.

SahooAshis commented 3 years ago

@amukherjee28

Windows
Normal connection
Yes, I did the pip install ibm_db
Default driver installed with pip

I'm new to cloud connection and currently working on a proof of concept. Below are the steps I have performed

installed ibm_db using pip install ibm_db
Created a free tier Db2 service on IBM cloud
Generated Service credentials key
Trying to establish a connection with the database with the credential details (Database, host, port, user id, and password) extracted from the Service credentials key

amukherjee28 commented 3 years ago

Please try the below example :

import ibm_db

#creating connection

print("Creating connection.......")
conn_string = "DATABASE=BLUDB;HOSTNAME=HOSTNAME;PORT=50000;PROTOCOL=TCPIP;UID=USER;PWD=password;"
conn = ibm_db.connect(conn_string,"","")
if conn:
    print("Connection ...... [SUCCESS]")
else:
    print("Connection ...... [FAILURE]")

q = "select * from sysibm.sysdummy1"

try:
    exe = ibm_db.exec_immediate(conn,q)
    row = ibm_db.fetch_tuple(exe)

    while(row):
        print(row)
        row = ibm_db.fetch_tuple(exe)
except:
    print("No rows to fetch")

This should work when proper connection string is provided.

Expected output

python cloudConnect1.py
Creating connection.......
Connection ...... [SUCCESS]
('Y',)

alanzyt311 commented 3 years ago

@amukherjee28

Hi, I also try to use python to connect db2 on IBM cloud, and I get Segmentation Fault: 11.

Here are some details:

MAC OS X
Trying a normal connection to IBM DB2 on cloud
I did pip install ibm_db
Before install the ibm_db package, I install a DSDriver from IBM website v11.1.1fp1_macos_dsdriver.dmg. And here is the installation instructions that I follow to install the driver.

Does this segmentation fault error appears because of the dsdriver pre-installed?

I also try to get the stack trace by using db2trc following the instructions mentioned in other issue, but I get 0 trace record. This stack trace is under the dsdriver that installed independently but not ibm_db.

(base) Alans-MBP:adm alanzyt$ db2trc flw trc.dmp trc.flw Total number of trace records : 0 (base) Alans-MBP:adm alanzyt$ db2trc fmt trc.dmp trc.fmt Trace truncated : NO Trace wrapped : NO Total number of trace records : 0 (base) Alans-MBP:adm alanzyt$ db2trc fmt -c trc.dmp trc.fmtc Trace truncated : NO Trace wrapped : NO Total number of trace records : 0 (base) Alans-MBP:adm alanzyt$ db2trc fmt -cli trc.dmp trc.cli Trace truncated : NO Trace wrapped : NO Total number of trace records : 0 (base) Alans-MBP:adm alanzyt$ db2trc flw trc.dmp trc.flw Total number of trace records : 0 (base) Alans-MBP:adm alanzyt$ db2trc fmt trc.dmp trc.fmt Trace truncated : NO Trace wrapped : NO Total number of trace records : 0

Thanks very much in advance!

SahooAshis commented 3 years ago

@amukherjee28

I tried the code snippet shared by you, but it is going to a dead state in the connection part. I had to force ok the execution after 1hr and received below Exception.

SQLCODE=-30081n: [IBM][CLI Driver] SQL30081N  A communication error has been detected. 
Communication protocol being used: "TCP/IP".  
Communication API being used: "SOCKETS".  
Location where the error was detected: "169.60.146.125".  
Communication function detecting the error: "connect".  
Protocol specific error code(s): "10060", "*", "*".  SQLSTATE=08001

During handling of the above exception, another exception occurred:

KeyboardInterrupt                         Traceback (most recent call last)

amukherjee28 commented 3 years ago

@SahooAshis This is an issue from the server and you need to check the instance that was created. The communication error is thrown from the server connection string and hence the error SQL30081N.

Can you check the IBM DB2 on Cloud instance created.

SahooAshis commented 3 years ago

@amukherjee28 Can you please let me know if we want an SSL connection then what are the changes required?

amukherjee28 commented 3 years ago

@alanzyt311

Let me address you queries one by one.

In case you are using separate driver in MAC the link shows us the installation steps, however for using the same driver as part of the open source IBM_DB driver we need to set the environment path IBM_DB_HOME.
traces in mac can be collected using the following command which is bit different :

db2trc on -l 2M

reproduce the issue

db2trc dump trc.dmp
db2trc off

Coming to the issue that you are facing can you try installing pip install ibm_db and use the driver that comes with the installation and give it try.

amukherjee28 commented 3 years ago

@SahooAshis

For SSL connecting following changes would be required in the connecting string :

conn_string = "DATABASE=BLUDB;HOSTNAME=HOSTNAME;PORT=50001;PROTOCOL=TCPIP;UID=USER;PWD=password;SECURITY=SSL"

alanzyt311 commented 3 years ago

@alanzyt311

Let me address you queries one by one.

In case you are using separate driver in MAC the link shows us the installation steps, however for using the same driver as part of the open source IBM_DB driver we need to set the environment path IBM_DB_HOME.

traces in mac can be collected using the following command which is bit different :
db2trc on -l 2M

reproduce the issue

db2trc dump trc.dmp
db2trc off
Coming to the issue that you are facing can you try installing pip install ibm_db and use the driver that comes with the installation and give it try.

Thanks for your reply!

I've already pip install ibm_db before. Could you please tell me how to select which server to use when using ibm_db.connect()? Cuz I am not sure which driver did I use in my current connection. You mention that

for using the same driver as part of the open source IBM_DB driver we need to set the environment path IBM_DB_HOME

Is there any instruction that I could follow to set up the environment path IBM_DB_HOME and select the driver? Many thanks.

These commands of getting traces are exactly what I've tried. Since this trace is obtained from the separate driver installed, I guess that's why I cannot get any records. How could I locate the path of the driver that installed by ibm_db?

Many thanks.

alanzyt311 commented 3 years ago

@amukherjee28

Sry to bother again. I also need to set up the connection on another Windows machine. In that Windows machine, I only do pip install ibm_db, without installing any separate driver. Then my connection returns the following message that is similar to the SahooAshis's one.

SQLCODE=-30081ct database: [IBM][CLI Driver] SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "169.60.146.125". Communication function detecting the error: "recv". Protocol specific error code(s): "10054", "*", "0". SQLSTATE=08001

You said that is the problem with the server. But my DB2 cloud instance seems to work fine. Could you please tell me the details that I should check my IBM DB2 on Cloud instance?

What I did is that I create the DB2 instance and generate a credential. Then I use the info in credential to connect. Do I miss some steps such as I need to enabling for external connect?

Thank you.

SahooAshis commented 3 years ago

@alanzyt311 I'm also getting the same error. Please update me if you found the solution.

amukherjee28 commented 3 years ago

@SahooAshis @alanzyt311

Assuming that you created the free instance of Db2 on cloud, can you try opening the web console of the instance and see if thats working for you.

attached picture of the same.

Check if you are are able to log in to that console using the same user and password.

Also lets capture the trace for the same from windows

steps to capture the trace

1. Open a command prompt with administrative privilege.
2. set the path till clidriver/bin. The clidriver will be present in the site packages folder of the pip installation
set PATH=<path till site-package>\clidriver\bin;%PATH%
3. once the path is set run
db2trc on -f trc.dmp
<run the application>
db2trc off
db2trc fmt trc.dmp trc.fmt
db2trc flw trc.dmp trc.flw
db2trc fmt -cli trc.dmp trc.cli
db2trc fmt -c trc.dmp trc.drda

cloud1

SahooAshis commented 3 years ago

@amukherjee28

There is no web console option showing in IBM Cloud. Refer to the below

amukherjee28 commented 3 years ago

@SahooAshis I do see a button named "Go to UI" can you try there once.

SahooAshis commented 3 years ago

Yes With Go to UI I'm able to create a database in DB2 also able to fetch records

alanzyt311 commented 3 years ago

@amukherjee28

I click the open console and directly enter the console page. Seems don't need any authentication with username and pwd in the credential. Check the below plz.

amukherjee28 commented 3 years ago

@alanzyt311 then the connection via application should not be a an issue.

Can you capture the traces in that case and pass it to me. I can have a detailed look.

Just make sure you have the proper connection string copied from the Ibm Db2 cloud console and use the same while connection using Python ibm_db

alanzyt311 commented 3 years ago

Hi @amukherjee28 thx for your reply.

I did found the clidriver in my site-packages dir. Then you said that

set PATH=\clidriver\bin;%PATH%

I am sorry about I'm still confused that in which file exactly should I make this modification?

amukherjee28 commented 3 years ago

@alanzyt311

In order to collect trace you have to follow the following steps :

1. Open a command prompt with administrative privilege.
2. set the path till clidriver/bin. The clidriver will be present in the site packages folder of the pip installation
set PATH=<path till site-package>\clidriver\bin;%PATH%
3. once the path is set run
db2trc on -f trc.dmp
<run the application>
db2trc off
db2trc fmt trc.dmp trc.fmt
db2trc flw trc.dmp trc.flw
db2trc fmt -cli trc.dmp trc.cli
db2trc fmt -c trc.dmp trc.drda

do not make any modification. Just capture these trace files after reproducing the issue and send the trace files to us. These will help us understand why you are facing the communication error.

SahooAshis commented 3 years ago

I'm not able to upload the trace files

amukherjee28 commented 3 years ago

@SahooAshis can you create a zip with all the files and upload the zip folder here in the comment section

SahooAshis commented 3 years ago

trc.zip

amukherjee28 commented 3 years ago

SQLDriverConnectW( hDbc=0:1, hwnd=0:0, szConnStrIn="DRIVER={IBM DB2 ODBC DRIVER};DATABASE=BLUEDB;HOSTNAME=54a2f15b-5c0f-46df-8954-7e38e612c2bd.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud;PORT=32733;PROTOCOL=TCPIP;UID=ltl39623;PWD=**;"**, cbConnStrIn=198, szConnStrOut=, cbConnStrOutMax=0, pcbConnStrOut=, fDriverCompletion=SQL_DRIVER_NOPROMPT ) ---> Time elapsed - +0.000000E+000 seconds

In this connection string can you check if the database name is correct

I guess the database name for ibm db2 on cloud is usually BLUDB rather then BLUEDB.

can you check that once

SahooAshis commented 3 years ago

Yes, Database is bludb.

amukherjee28 commented 3 years ago

@alanzyt311 can you also check if you were making the same mistake.

SahooAshis commented 3 years ago

@amukherjee28 Shall send you the re-traced files after correction?

amukherjee28 commented 3 years ago

Are you still facing the issue after correcting the database name? In that case you can send me the trace files.

SahooAshis commented 3 years ago

@amukherjee28 Please find the trace files trc.zip

SahooAshis commented 3 years ago

I'm able to connect to the DB2 cloud. I was missing the SECURITY=SSL part @amukherjee28 Thank you

alanzyt311 commented 3 years ago

@amukherjee28

Hi, sry for the late reply. Here's my trace file. Could you help and have a look on it? The issue is Segmentation Fault: 11 with MAC OS. trc.zip

amukherjee28 commented 3 years ago

Hi @alanzyt311

I did go through the trace files and what I see that at the connection level itself your application is crashing and not moving forward.

What I will suggest is since this issue is closed lets keep it that way and meanwhile can you open another issue with your problem.

Will continue the discussion there.

Thanks

ibmdb / python-ibmdb

Connect to IBM DB2 on Cloud using python api ibm_db #643