-
1. Re: How to specify validation query for VDB to verify connectivity to underlying data source(s)?
rareddy Oct 16, 2015 4:36 PM (in response to guydavis)If you are working with JBoss EAP, in the data source creation there is <check-valid-connection> element you can set https://access.redhat.com/documentation/en-US/JBoss_Enterprise_BRMS_Platform/5/html/BRMS_Administrator_Guide/Configuring_a_Datasource_for_JBoss_Enterprise_Application_Platform_6.html
When you do this, when the connection is being taken from the pool, the connection will be validated with the query, and if the source is down the connection will be thrown out of the pool automatically. If no connection can be made then the your VDB query will end with an error unless you designed for partial results mode.
-
2. Re: How to specify validation query for VDB to verify connectivity to underlying data source(s)?
guydavis Oct 16, 2015 6:50 PM (in response to rareddy)Hi Ramesh, thanks for the quick response. So I've tested a couple of approaches to validating the MS SQL server data source connection. My JBoss EAP 6.1alpha config was:
<validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"/>
<check-valid-connection-sql>select 1</check-valid-connection-sql>
<validate-on-match>true</validate-on-match>
<use-fast-fail>false</use-fast-fail>
<exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.novendor.NullExceptionSorter"/>
</validation>
I also tried testing with this instead:
<validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"/>
<check-valid-connection-sql>select 1</check-valid-connection-sql>
<validate-on-match>false</validate-on-match>
<use-fast-fail>false</use-fast-fail>
<exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.novendor.NullExceptionSorter"/>
<background-validation>true</background-validation>
<background-validation-millis>60000</background-validation-millis>
</validation>
So, my test was:
- Start up MS SQL Server
- Start up JBoss with Teiid 8.7
- Connect to Teiid VDB of MS SQL Server database using Squirrel SQL over JDBC
- Query: SELECT 1; <- Passes, returns '1' as result.
- Query: SELECT * FROM MyTable; <- Passes, returns table data.
- Shutdown the MS SQL Server database. When using background-validation, waited a few minutes.
- Query: SELECT 1; <- Expected error, but still passes, returns '1' as result.
- Query: SELECT * FROM MyTable; <- Fails, unable to get managed connection as expected.
As best I can tell, the 'SELECT 1' connection validation query is being intercepted at the Teiid layer and always returning successfully even when the MS SQL Server data source is down (even when JBoss log reports connection failure). This happens even if I reconnect the JDBC session to Teiid after the SQL Server DB is down.
Any other thoughts on how to configure generic validation query (model-independent) is pushed down to the underlying database(s)?
Thanks,Guy
-
3. Re: How to specify validation query for VDB to verify connectivity to underlying data source(s)?
rareddy Oct 16, 2015 7:38 PM (in response to guydavis)Guy,
Where are you issuing the "Select 1", if it is on the Teiid connection, as along as Teiid server is up and running this will return a result. This has NOTHING to do with the physical sources underneath, as SELECT 1 does not need any source access.
If you wanting to make sure your MS SQL Server is up or not from the Teiid connection, issue any query on the one of the source tables. As per configuring a generic validation, there is no such view you can create in Teiid. However, I think if you use, Direct Query Procedure you can write queries from Teiid JDBC client or embed in a view. You can even design a view (say STATUS) such that each source is up/down flags based on multiple calls to this procedure one for each source. Then you can do "SELECT * FROM STATUS", but you would need to handle the error situation in the view.
Ramesh..
-
4. Re: How to specify validation query for VDB to verify connectivity to underlying data source(s)?
shawkins Oct 16, 2015 7:48 PM (in response to rareddy)> Query: SELECT 1; <- Expected error, but still passes, returns '1' as result.
An error is not expected. Teiid will not hit a source unless necessary.
> As best I can tell, the 'SELECT 1' connection validation query is being intercepted at the Teiid layer and always returning successfully even when the MS SQL Server data source is down (even when JBoss log reports connection failure).
SELECT 1 will validate if the connection to Teiid is not valid, but will not validate the sources. While this may seem appealing when there is a single source, a vdb could be connected to hundreds of sources and it wouldn't be ideal to test them all.
> Any other thoughts on how to configure generic validation query (model-independent) is pushed down to the underlying database(s)?
To validate the source currently that would require actually utilizing the source. As Ramesh says this could be done with a direct query procedure call, or you could create a source table with the native-query set to "select 1", or make the case for adding some kind of higher level validation call in Teiid SQL in an enhancement request.