5 Replies Latest reply on May 27, 2014 7:15 PM by Steven Hawkins

    Teiid JNDI sql server connection settings - implicit_transactions  OFF and persistent connection

    lightsail pro Newbie

      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.