4 Replies Latest reply on Oct 16, 2015 7:48 PM by shawkins

    How to specify validation query for VDB to verify connectivity to underlying data source(s)?

    guydavis

      Good day,  I was wondering if there was a way to specify validation queries for underlying data sources in a Teiid VDB.  For example, a VDB exposing a MS SQL Server database.  One can use 'SELECT 1' as the validation query, however this returns successfully (at the Teiid layer) whether the underlying MS SQL Server database is actively running or not.  Only a query against the actual tables/views in source model will return an error if MS SQL Server is down.

       

      Is there a way to have a generic (model independent) query always execute against the source model(s), allowing for true connection validation.  Can it be specified as a property in the translator and/or VDB definition?  Can one specify a validation query per data source in case of a federated VDB?

       

      Thanks in advance,

      Guy

        • 1. Re: How to specify validation query for VDB to verify connectivity to underlying data source(s)?
          rareddy

          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

            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:

            1. Start up MS SQL Server
            2. Start up JBoss with Teiid 8.7
            3. Connect to Teiid VDB of MS SQL Server database using Squirrel SQL over JDBC
              1. Query: SELECT 1;  <- Passes, returns '1' as result.
              2. Query: SELECT * FROM MyTable;  <-  Passes, returns table data.
            4. Shutdown the MS SQL Server database.  When using background-validation, waited a few minutes.
            5. Query: SELECT 1;  <- Expected error, but still passes, returns '1' as result.
            6. 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

              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

                > 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.