isolation_level

[Ben De Luca]

Hi, 

 I am new to db2 and ibm_db_sa, I wondered if there is a way to change the isolation_level on when creating an engine. 

I think from the SQL alchemy documentation I would do some thing like ( with other databases)

create_engine("ibm_db_sa://<connectiondetails>, isolation_level="READ UNCOMMITTED") 

Invalid argument(s) 'isolation_level' sent to create_engine(), using configuration DB2Dialect_ibm_db/QueuePool/Engine.  Please check that the keyword arguments are appropriate for this combination of components.

[Rahul]

Hi Ben De,
Currently isolation level configuration argument is not supported
through ibm_db_sa dialect. But you can still control the isolation
lavel through db2cli.ini, for more detail please go thorugh
http://publib.boulder.ibm.com/infocenter/db2luw/v10r1/topic/com.ibm.db2.luw.apdv.cli.doc/doc/r0008832.html

Thanks,
Rahul

[Ben De Luca]

I am data mining another application data and so I dont know how and
when its storing information in its datastore.

engine = sqlalchemy.create_engine('ibm_db_sa://%s:%s@host:port/schema'
% (username, password))
engine.execute("set current isolation to UR")

I was trying this, which I think might set the current isolation
level, but not seeing the most up to date information. I cant tell
whether that hasn't affected other parts of sql alchemy, or whether
the other application is just not yet stored data.

Do people think what I did should work?

-bd

> --
> You received this message because you are subscribed to the Google Groups "ibm_db" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to ibm_db+un...@googlegroups.com.
> To post to this group, send email to ibm...@googlegroups.com.
> Visit this group at http://groups.google.com/group/ibm_db?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>

[Rahul]

Hi Ben De,
engine.execute() get a connection from connection pool, execute the
statement and then return back the connection to connection pool. When
you tried to view the most up to date information you might be using
other connection.

If you wants to set UR isolation level to all the connection then you
can implement the 'connect' event listener interface, in this you can
set the isolation level for the connection. connect event function get
called for each new connection. For more info you can go through
http://docs.sqlalchemy.org/en/rel_0_8/core/interfaces.html?highlight=connect#sqlalchemy.interfaces.PoolListener.connect.

Following sample code which will help you in setting isolation level
through 'connect' event listener

===
from sqlalchemy import *
from sqlalchemy import event

def my_connect(dbapi_con, con_record):
cur = dbapi_con.cursor()
cur.execute('set current isolation to UR')
cur.close()

db = create_engine('db2+ibm_db://user:password@hostname:port/
database')

event.listen(db, 'connect', my_connect)
===

Thanks,
Rahul

[Ben De Luca]

Thanks for your help Rahul.