I am testing the database connection using the Microsoft sqljdbc4 driver. I noticed that teiid uses the following connection settings by default:

     transaction isolation level read committed

     implicit_transactions on

I want to change them to so that I do not need to worry about the blocking.

     set transaction isolation level read uncommitted

     set implicit_transactions off

1. Teiid designer does not support changing these settings. So I modified the data source in the standalone_teiid.xml file as below. But from SQL server profiler, it still uses the default settings. Does anyone know where to override the default connection setting? By the way applicationIntent=ReadOnly is for the SQL 2012 HA.

<datasource jndi-name="java:/AdventureWorks" pool-name="AdventureWorks" enabled="true">

      <connection-url>jdbc:sqlserver://server1:1433;databaseName=AdventureWorks2012;applicationIntent=ReadOnly;InitializationString=set transaction isolation level read uncommitted;</connection-url>

                    <driver>sqljdbc4.jar</driver>

                    <security>

                        <user-name>user1</user-name>

                        <password>xxxxx</password>

                    </security>

</datasource>

2. After I disconnect connection (e.g.SQL workbenchfrom Teiid, I noticed that Teiid runtime still have a connection on the SQL server. Why Teiid still hold the connection after client application disconnects? Is it because Teiid uses connection pooling? If yes, is there a way to turn off? Will Teiid ever release the connection at all?

3. By this MS doc - http://docs.oracle.com/cd/E13157_01/wlevs/docs30/jdbc_drivers/mssqlserver.html, we can set multiple options for the InitializationString parameter, but if I do that, teiid will not run. Could someone advise if this is possible in Teiid? As I mentioned, I mainly use Teiid for data warehousing type of read. The default settings can cause performance issue on SQL server.

InitializationString=(set transaction isolation level read uncommitted;set implicit_transactions off);

Thanks in advance.