> Does anyone know where to override the default connection setting?
What you have looks correct. If you are getting your connections through that datasource (and not for example seeing the connection that is being made in Designer to read metadata) and you don't see that connection url being used then we should determine what is happening there first.
> Is it because Teiid uses connection pooling?
When you configure a datasource it is a connection pool. This is at the application server level and effectively independent of Teiid. The datasource configuration can also include min/max connections, etc.
> If yes, is there a way to turn off? Will Teiid ever release the connection at all?
I suppose you could set max connections to 0, but that I don't think that's what you should do. The Teiid layer should not be the one that is causing the connection to remain open as connections are returned to the pool as soon as possible.
> teiid will not run.
Can you be specific as to what you mean here?
> Could someone advise if this is possible in Teiid?
Yes that should be possible in the datasource configuration.
--The connection actually observed and used by teiid is through the data source defined in the standalone_teiid.xml, it is not through the teiid designer. So I assume Teiid runtime just ignores certain settings or does not pass to the sqljdbc4 driver layer. Is this a bug?
--When I added "InitializationString=(set transaction isolation level read uncommitted;set implicit_transactions off);", the teiid server will not start. If I just added one without parensis, it starts but just ignore the setting.
I have to use the Microsoft jdbc driver for the HA readonly routing. But I also need to set a few other connection settings ( uncommitted;set implicit_transactions, transaction isolation level, applicationIntent). Did anyone tried these settings before?
> So I assume Teiid runtime just ignores certain settings or does not pass to the sqljdbc4 driver layer. Is this a bug?
Just to clarify it's not Teiid that is in control of the connection pools, that is a function of JBoss AS / JCA. I cannot say if it's a bug. If you see behavior which looks incorrect then you can make a post on the AS forum or go straight to logging an issue against JBoss AS.
> the teiid server will not start
Can you clarify what this means? Does the application server fail to start? Does the vdb fail to deploy properly? etc.
> Did anyone tried these settings before?
Your best bet is to ask that on the AS forum.
By "> the teiid server will not start", I mean the application server error out. It seems that it cannot parse the "(". Thanks.
Can you show the xml that you are using? Perhaps there are escapes missing.