Set isolation level using CWB_ATTR_TXN_ISOLATION

[kadler]

I added support in the next ODBC driver version to set the isolation level to *NONE via SQL_ATTR_TXN_ISOLATION, so conceivably we can set this using the set_attr on the connection object. However, Microsoft's ODBC driver manager only allows setting the 4 pre-defined isolation options and will not allow setting the value to 0 for *NONE.

To get around this, I added a new attribute that is an alias of SQL_ATTR_TXN_ISOLATION called CWB_ATTR_TXN_ISOLATION (value 2139). This works exactly the same as the ODBC version, except the driver manager just passes the value through to the driver so we can set it to 0.

This will only work when the driver level is 7.1.24 or higher, so we'll need to do some checking before we use it or pass *NONE through.

[cklat]

Hi There!

I wanted to check in if there's already the option to set the isolation_level in the connection string to "NO COMMIT"?
If not, you maybe know a temporal workaround if I want to use it together with ibmi?

Currently, when I'm trying to create a temporary table on a DB2 as/400 database I get a SQL7008 error saying the 'not valid for operation'

Appreciate your help, thank you!

[kadler]

Yes, you can set CMT=0 on the connection string. It is documented here

[cklat]

Thanks a lot for you reply!
Unfortunately, I'm not sure if I'm understanding you correctly. Is this equivalent to the parameter isolation_level? If so, based on the code documentation I was assuming that the NO COMMIT option is currently not supported.
Or am I confusing something?

[kadler]

Ohh, sorry I thought you were referring to direct ODBC connections. Through SQLAlchemy, you must use the options supported by sqlalchemy-ibmi. There is not yet support for NO COMMIT (that's what this issue is about).

[cklat]

Thank you for your response!
I understand. I thought that there might have been some changes in the meantime since the issue a bit older already.
Unfortunately, I have not been able to establish a connection through packages, except ibmi. With all the other combinations I have found googling, I get various errors when trying to connect to our as/400 database.

Do you maybe know a working connection string to pass to sqlalchemy that basically emulates the things that ibmi does?

[kadler]

Changes need to be made to https://github.com/IBM/sqlalchemy-ibmi/blob/main/sqlalchemy_ibmi/base.py#L871-L883 to add "*NC" and "NO COMMIT" to be associated with CWB_TXN_NO_COMMIT (ie 0).

Then in this block: https://github.com/IBM/sqlalchemy-ibmi/blob/main/sqlalchemy_ibmi/base.py#L893-L896

We need to add a check if the value is CWB_TXN_NO_COMMIT (0), then we need to use CWB_ATTR_TXN_ISOLATION (2139) instead of SQL_ATTR_TXN_ISOLATION on the set_attr call.

Conceivably, we could check the driver version before using CWB_TXN_NO_COMMIT, but we can probably just handle that as an error check as well.