Connect to IBM DB2 on Cloud using python api ibm_db

Question

Can you please let me know how to connect to DB2 on IBM Cloud using python?

I have tried the below steps.

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

import ibm_db

print("Creating connection.......")

conn_string = "DATABASE=bludb;HOSTNAME=54a2f15b-5c0f-46df-8954-7e38e612c2bd.c1ogj3sd0tgtu0lqde00.databases.appdomain.cloud;PORT=32733;PROTOCOL=TCPIP;UID=<userId>;PWD=<password>;"

conn = ibm_db.connect(conn_string,"","")
if conn:
    print("Connection ...... [SUCCESS]")
else:
    print("Connection ...... [FAILURE]")

I am getting below error message:

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

Please provide details on the type of credentials you have. Do they already include a database URI for SSL? Use that as input to your connect command. ibm_db.connect(ssldsn, "", "") — data_henrik
– data_henrik, Commented Jul 21, 2021 at 6:20
The port and hostname details I got from the service credentials key. — Ashis Sahoo
– Ashis Sahoo, Commented Jul 21, 2021 at 12:02

data_henrik · Accepted Answer · 2021-07-21 12:31:21Z

3

It seems like you are on the new Db2 on Cloud lite plan with non-standard ports and SSL enforced. When you connect to Db2 using the Python driver and use SSL, you have to add the SECURITY=SSL property, e.g.:

conn_string = "DATABASE=bludb;HOSTNAME=yourhostname;PORT=<port>;PROTOCOL=TCPIP;UID=<userId>;PWD=<password>;SECURITY=SSL"

answered Jul 21, 2021 at 12:31

data_henrik

17.3k2 gold badges33 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Wizard74 · Accepted Answer · 2021-07-29 20:42:29Z

1

I think the easy way to do this is with SQL Magic. This way, you can just type a SQL statement just by adding %sql before your query

First install the packages

!pip install sqlalchemy==1.3.9
!pip install ibm_db_sa

Then,

%load_ext sql

Finally, run the following code by replacing your username, password, hostname and SSL. You can find these in your IBM DB2 under credentials.

%sql ibm_db_sa://my-username:my-password@hostname:port/BLUDB?security=SSL

Now you can run any SQL by using %sql before or %%sql if the whole cell is going to be SQL. Example:

%sql SELECT * FROM TABLENAME;

or

%%sql 
SELECT *
FROM TABLENAME;

answered Jul 29, 2021 at 20:42

Wizard74

211 silver badge4 bronze badges

Comments

jkafrouni · Accepted Answer · 2021-07-20 19:46:07Z

0

There's a python package called ibm_db, does this link or (cited in the first one) this one help?

answered Jul 20, 2021 at 19:46

jkafrouni

1

4 Comments

Ashis Sahoo Over a year ago

I'm trying with ibm_db package but getting below error ``` SQLCODE=-30082n: [IBM][CLI Driver] SQL30082N Security processing failed with reason "17" ("UNSUPPORTED FUNCTION"). SQLSTATE=08001 ```

Carmoreno Over a year ago

Hi jkafrouni and Welcome to SO, from my point of view this is a comment, it's not an answer. Please check it out: stackoverflow.com/help/how-to-answer

Ashis Sahoo Over a year ago

@mao I have added the code snippet that I'm trying in the question. Can you please check and let me know what I'm missing.

jkafrouni Over a year ago

@Carmoreno indeed, meant to create a comment since I knew this wasn't a complete answer :) my bad!

Collectives™ on Stack Overflow

Connect to IBM DB2 on Cloud using python api ibm_db

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related