Mssql, DB Link, DB2 and INSERT/UPDATE

Hi,

We need Yii to work with DB2/AS400 databases.

The only way we found is to query the DB through a DB-Link (based on ODBC DB2/AS400 drivers) on an MSSQL Server as there is no DB2 Active Record Schema implemented yet.

We wrote a new ActiveRecord class to bypass the schema requirements (it’s not an ideal situation but it works…) as we don’t manage DB2 so good (if a good willing people can implement it - there is an existing project https://github.com/rmanola/Db2-schema-for-Yii but don’t want to wait).

So, we can easily do SELECT from DB2 through MSSQL, but the INSERT/UPDATE transactions don’t work. DB2 sends back an error that says Journaling has to be enabled to allow these transactions.

I said it before, we don’t know AS400 so much. We will not enable journaling as we don’t know how it will impact on DB performances (and it will be an exhausting task to do it on each table).

After searches (we deserve nearly a PhD for that - hum hum), we found that we need to explicitly launch this query before an insert or an update : ‘SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED’.

The question is : is there an option on the CDbConnection to set the transaction isolation level on MSSql? Or have we to extend the Mssql class to implement this option?

Many thanks!

Not sure this is what you want, but CDbConnection has a initSQLs property.

This array may contain all SQL statements that should be executed just after the connection is initialized…

I’ll test it and send you feedback!

That works!

Summary :

I just added in my configuration file’s db section the parameter :

  • ‘initSQLs’=>array(‘SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED’)

Voilà!

So, now I’m able to make INSERT and UPDATES on DB-linked DB2 tables through MSSQL.

Try my extension: YiiDB2