Connect to DB2 using python

Question

I'm using python 2.7 and I want to connect to a DB2 database and insert data into it. Here is what I did so far :

import sqlalchemy
from sqlalchemy import *
import ibm_db_sa

db2 = sqlalchemy.create_engine('ibm_db_sa://user:pswd@localhost:50001/prm')
metadata = MetaData()
users = Table('users', metadata, 
Column('user_id', Integer, primary_key = True),
Column('user_name', String(16), nullable = False),
Column('email_address', String(60), key='email'),
Column('password', String(20), nullable = False)
)
metadata.bind = db2
metadata.create_all()
users_table = Table('users', metadata, autoload=True, autoload_with=db2)
users_table

The problem is when I execute the code above I get this the following error :

ProgrammingError: (ProgrammingError) ibm_db_dbi::ProgrammingError: [IBM][CLI Driver]
SQL1042C  An unexpected system error occurred.  SQLSTATE=58004\r SQLCODE=-1042 None None

Can anyone help me to figure this out ?

mdob · Accepted Answer · 2015-06-04 00:08:47Z

2

Although problem solved I write my bit as it might be helpful for someone.

In my case LD_LIBRARY_PATH and all lib folders were OK.

Reinstalling ibm_db and ibm_db_sa in virtualenv solved issue.

pip uninstall ibm_db ibm_db_sa
pip install ibm_db ibm_db_sa

My guess is that ibm_db was installed before there was a DB2 driver installed in system (maybe someone ignored errors/warning).

It's good to check if you can connect to database with db2cli first

db2cli execsql -connstring "DATABASE=database_name;HOSTNAME=db_host;PORT=50000;PROTOCOL=TCPIP;UID=db_user;PWD=db_pass;"

> select 'db_name' from sysibm.sysdummy1

answered Jun 4, 2015 at 0:08

mdob

2,3643 gold badges23 silver badges25 bronze badges

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

1 Comment

ExactaBox Over a year ago

I tried following the official docs, installing with easy_install... big mistake. pip just works

deltascience · Accepted Answer · 2015-03-10 16:38:55Z

0

I finally got it to work by adding LD_LIBRARY_PATH to environment variables with the value : <YOUR_DB2_HOME>\SQLLIB\lib. It works perfectly now.

answered Mar 10, 2015 at 16:38

deltascience

3,4006 gold badges45 silver badges78 bronze badges

2 Comments

Ian Bjorhovde Over a year ago

It looks like you're not properly setting up your application environment. You should be sourcing <YOUR_DB2_HOME>/sqllib/db2profile, which will set LD_LIBRARY_PATH and other necessary variables.

deltascience Over a year ago

I looked for db2profile directory but I found none ! Another question, why did it work if I didn't set the variable correctly ?

Collectives™ on Stack Overflow

Connect to DB2 using python

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related