9 Replies Latest reply on Sep 9, 2016 12:30 PM by Steven Hawkins

    Can Teiid tell me, from client, if a VDB model or resource is available?

    Marco Ardito Master

      I mostly use multimodel dynamic vdbs, giving clients a unified approach to very different sources.

       

      It could  happen sometimes that even just one datasource or a resource is not reachable, or working properly: maybe the server has problems, is offline or there are just network problems preventing Teiid to reach just one (perfectly working on itself) resource...

       

      My client connects just fine to the Teiid VDB, but any query using an unavailable resource fails with errors like

       

      [unixODBC]ERROR: TEIID30504 sqlserver_apiOnCrm: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:/apiOnCrm org.teiid.jdbc.TeiidSQLException: TEIID30504 sqlserver_apiOnCrm: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:/apiOnCrm; Error while executing the query

       

      I could check in advance the "online" status of a vdb resource before effectively use it, with some test, catching exceptions, but I was thinking if Teiid could keep some sort of SYS table where I could check the availability of any VDB resource, like a "status" column for any model, local or imported... in a way some sort of automated VDB resource monitoring, so that I don't have to repeat my custom checks every time I need in client code.

       

      eg: I could need to adapt my client code, depending on what the app needs to do, to warn users that results are missing missing some data because a datasource is not available now, or even disable some app function because the missing data is absolutely needed, managing this unavailability in some better way...

       

      I looked at the VDB SYS tables, but I can't find a "status" (perhaps detected at connection time), even if now I have offline remote databases (some of them are just internal, and switched off during vacations)... if there was something like that, this task would be really simple,

       

      Maybe there's already something, that I missed, or a good practice about this? Does this make sense?

       

      Marco

        • 1. Re: Can Teiid tell me, from client, if a VDB model or resource is available?
          Steven Hawkins Master

          > I looked at the VDB SYS tables, but I can't find a "status" (perhaps detected at connection time), even if now I have offline remote databases (some of them are just internal, and switched off during vacations)... if there was something like that, this task would be really simple,

           

          There isn't anything in SYS/SYSADMIN, but the VDB Model objects coming back from the Admin interface to have some notion of status error messages on them.  However at this point that is really just checking for the existence of referenced JNDI datasources - it does not include connection checking. 

           

          In general checking to see if a connection pool is valid can be somewhat complicated / non-standard.  For JDBC sources with the pools properly configured and that don't require propagation, it is as simple as just getting a connection from the pool.  Is that the only case that you care about?

           

          Even if there isn't something built-in, the cli does provide a test mechanism How to Test DataSource connection in JBoss EAP 6.2 Managed Domain - Stack Overflow:

           

          /subsystem=datasources/xa-data-source=java\:jboss/datasources/XAOracleDS:test-connection-in-pool

          • 2. Re: Can Teiid tell me, from client, if a VDB model or resource is available?
            Marco Ardito Master

            Steven Hawkins wrote:

            In general checking to see if a connection pool is valid can be somewhat complicated / non-standard.

            I understand, jdbc pools have a connection test (sql or class), but other sources are much different...

             

            But I just wish I could define a custom "test" for each model and get an overall VDB connection status: a sort of connection test that Teiid could perform for me, on the server side. If that succeeded, it could mark the source "valid" (maybe in a SYS table), or not. In the end I need to execute SQL statements against each source (jdbc, files, google spreadsheets, web services), so I could need just a very simple SQL statement (SELECT or else) that SHOULD work, if the source is reachable, and working.

             

            I could do this in the client but it would be cumbersome and not centralized...

             

            Perhaps I could create in the VDB a single method, maybe a procedure, to execute sql statements against all my datasources or adapters, something like the jdbc sql pool connection test... like a checkSources() procedure in the VDB that returns a list of my models and in a column a status (up/down) about each of them, at least...  with that I could test my sources in advance, in the client, and manage that result in the application, warn users, or deny access, etc.

             

            Could this work?

             

            edit: this doc paragraph is about handling exceptions inside procedures: https://teiid.gitbooks.io/documents/content/reference/Procedure_Language.html#_exception_handling

             

            states: "Only processing exceptions, which are typically caused by errors originating at the sources or with function execution, are caught. A low-level internal Teiid error or Java RuntimeException will not be caught. "

             

            So... could I use a trial and error (catching) process in a procedure to get the status of each source?

            • 3. Re: Can Teiid tell me, from client, if a VDB model or resource is available?
              Marco Ardito Master

              I was thinking about a generic procedure in my VDB, like this below: could this work? Any better method?

               

              CREATE PROCEDURE getSourcesStatus() returns table (source_name string, status string)  AS
              BEGIN
                  #to get a list of the first alphabetical table from each VDB schema
                  #default status is "off"
                  DECLARE string VARIABLES.sources = '
                      SELECT
                      st.SchemaName, min(st.Name) as n, ''off'' as status
                      FROM SYS.Tables as st
                      WHERE st.IsPhysical
                      AND st.SchemaName NOT like ''SYS%''
                      GROUP BY st.SchemaName
                  ';
                
                  DECLARE string VARIABLES.source_test ='';
                  DECLARE string VARIABLES.set_source_status ='';
              
                  #gets the tables list
                  EXECUTE IMMEDIATE VARIABLES.sources AS schema string, table string, status string INTO #sources;
              
                  #loops on the list to check each source
                  LOOP ON (SELECT schema, table, status from #sources) as source
                  BEGIN
                      #builds a generic test on the current source
                      VARIABLES.source_test='SELECT * FROM ' || (source.schema || ''.'' || source.table) ;
              
                      #builds a generic status updater for the current source
                      VARIABLES.set_source_status ='
                          UPDATE #sources SET status=''on''
                          WHERE schema = ''' || source.schema || '''
                          AND table = ''' || source.table || '''
                      ';
              
                      #this tests if the source is working
                      EXECUTE IMMEDIATE VARIABLES.source_test; #it should raise exception TEIID30504, if not    
                    
                      #this should be executed only if there is no exception to the test above
                      EXECUTE IMMEDIATE VARIABLES.set_source_status;
                    
                  EXCEPTION e
                      #would this be better test? would ANDs work?
                      #IF (e.state = '50000' and e.errorcode='30504' and e.teiidcode='TEIID30504')
              
                      #if exception raises TEIID30504, logs but continues
                      IF (e.teiidcode='TEIID30504')
                          RAISE SQLWARNING e.exception;
                      ELSE
                          RAISE e.exception;      
                        
                  END
                
                  #after the loop, returns the tables list with all statuses updated (or not)
                  EXECUTE IMMEDIATE 'SELECT schema || ''.'' || table, status from #sources' AS source_name string, status string
              END
              
              
              • 4. Re: Can Teiid tell me, from client, if a VDB model or resource is available?
                Marco Ardito Master

                I am back to office (first week) after summer vacations, and I thought again about this issue, in particular because now I am already facing it, developing a new service: allow my web applications to query a REST service (through Teiid (see post Need to get data from Mailup email marketing platform).

                 

                Until now in this VDB I have materialized three tables from that REST service, and that speeds up a lot every request, building pages fast.

                But not every request is materialized, and it could be impossible to do that, due to the nature of the REST service.

                 

                But every now and then, I get a temporary "403 Forbidden" error like this below. This also happens in SquirrelSQL, sometimes.

                And then, after a few seconds, simply repeating the same query (or reload the web app page), gets the right result and expected records.

                 

                I don't know why the remote REST service should give me a 403 error, and a few seconds later respond nicely...

                Could it be a Teiid interpretation of some odd behaviour of the backend REST service?

                 

                If I could have a way to intercept such errors (the higher level, the better) and manage them, such as try at least 2 times, or else...

                I also had a web connection hiccup, even Google was unreachable, and I got a similar error but reporting a timeout failure (no screenshot, sorry).

                Even that can happen again, and I don't know how I could handle that better than crashing my service...

                 

                This is one of the reasons that led me to ask if I could "test" such Teiid data sources before using them...

                 

                I am still developing a procedure (building on the one I posted in other comments above, which doesn't work, btw), and I hope to be able to publish for others to comment, impreve and benefit, if possible.

                 

                Any consideration about the above 403, anyway, and how could I manage it better?

                 

                Marco

                • 5. Re: Can Teiid tell me, from client, if a VDB model or resource is available?
                  Steven Hawkins Master

                  > Could it be a Teiid interpretation of some odd behaviour of the backend REST service?

                   

                  I don't know what that would be other than the 403 error.  We will just report the exception straight from CXF.

                   

                  > If I could have a way to intercept such errors (the higher level, the better) and manage them, such as try at least 2 times, or else...

                   

                  In a procedure you can using an exception handling block.  It looks like CXF does have handling as well - http://cxf.apache.org/docs/failoverfeature.html

                  • 6. Re: Can Teiid tell me, from client, if a VDB model or resource is available?
                    Marco Ardito Master

                    Well, here is my testing procedure, as it is now.

                     

                    Its purpose is:

                    - to ask the vdb (SYS tables) one testing table for every source (schema) in the VBD, excluding system schema resources, of course

                    - create a temporary table listing every schema+table reported, to test later, initially stating its status as "off"

                    - then, in a loop, try to "reach" by basic, fast, SQL every testing schema+table that is supposed to be "on" in the VDB

                    > if it fails, it should raise an exception, and skip the rest of the loop, testing the next schema+table

                    > if it succedes, it should continue with the next step of the loop

                    - the next step is to update the temporary table setting the successful schema+table to "on"

                     

                    at the end the procedure returns the temporary table, so one could check before using the vdb with something like:

                    "select * from (call getSourcesStatus()) as test where status = 'off'

                     

                    If no results a re returned, all is ok and reachable, else you get a list of what is not reachabel, and you can plan some management...

                     

                    It is quite general, and it could be used in any VDB, since it finds all kind of resource that should be tested by itself (at least, that is my intent...)

                     

                    here it is:

                     

                    CREATE PROCEDURE getSourcesStatus() returns table (source_name string, status string)  AS
                    BEGIN
                        DECLARE string VARIABLES.sources = '
                            SELECT
                            st.SchemaName, min(st.Name) as n, ''off'' as status
                            FROM SYS.Tables as st
                            WHERE st.IsPhysical
                            AND st.SchemaName NOT like ''SYS%''
                            GROUP BY st.SchemaName
                        ';
                    
                        DECLARE string VARIABLES.source_test ='';
                        DECLARE string VARIABLES.set_source_status ='';
                    
                        EXECUTE IMMEDIATE VARIABLES.sources AS schema string, tablename string, status string INTO #sources;
                    
                        LOOP ON (SELECT schema, tablename, status from #sources) as source
                        BEGIN
                            VARIABLES.source_test='SELECT ''test'' FROM ' || (source.tablename) || ' LIMIT 1';
                    
                            EXECUTE IMMEDIATE VARIABLES.source_test AS st string WITHOUT RETURN;
                        
                            VARIABLES.set_source_status ='
                                UPDATE #sources SET status=''on''
                                WHERE schema = ''' || source.schema || '''
                                AND tablename = ''' || source.tablename || '''
                            ';
                    
                        EXECUTE IMMEDIATE VARIABLES.set_source_status WITHOUT RETURN;
                        
                        EXCEPTION e
                            IF (e.teiidcode='TEIID30504')
                                CONTINUE;
                            ELSE
                                RAISE e.exception;                    
                        
                                                        
                        END
                    
                        EXECUTE IMMEDIATE 'SELECT tablename, status from #sources' AS source_name string, status string;
                    END
                    
                    
                    

                     

                    And it seems to work, with basic tests:

                    - if I rename one of the schema+table in the loop, so that Teiid fails the query, my procedure leaves it marked as off, only it.

                    - if i set back the orginal schema+table name, Teiid query works, and the next step marks it as "on".

                     

                    Of course, here I am only targeting error TEIID30504, the first I got in the error reported in the first post:

                     

                    [unixODBC]ERROR: TEIID30504 sqlserver_apiOnCrm: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:/apiOnCrm org.teiid.jdbc.TeiidSQLException: TEIID30504 sqlserver_apiOnCrm: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:/apiOnCrm; Error while executing the query

                     

                    In my latest try, causing an error by renaming a table, gives, in squirrelSQL:

                    Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 mysql5_apifdbunica: 1146 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT 'test' AS c_0 FROM `unica`.`SQ_psoa` AS g_0 LIMIT 1]

                    SQLState:  50000

                    ErrorCode: 30504

                     

                    I wonder if other conditions, due to unreachable VDB resources that should be reachable, could raise different TEIIDXXXXX, or if I should also check other errors reported, when happening together, like SQL state or else.

                     

                    Teiid manual reports different error types can be exposed to exception handling (Procedure Language · Teiid Documentation), something like:

                     

                    STATE The SQL State

                    ERRORCODE The error or vendor code. In the case of Teiid internal exceptions this will be the integer suffix of the TEIIDxxxx code

                    TEIIDCODE The full Teiid event code. Typically TEIIDxxxx.

                    EXCEPTION The exception being caught, will be an instance of TeiidSQLException

                    CHAIN The chained exception or cause of the current exception

                     

                    Can you spot some mistakes, or missing things, or anything else useful?

                     

                    Thanks, Marco

                    • 7. Re: Can Teiid tell me, from client, if a VDB model or resource is available?
                      Steven Hawkins Master

                      > Can you spot some mistakes, or missing things, or anything else useful?

                       

                      You will likely run into situations where you don't want or can't test all tables (such as when an access pattern is required).  It may be better to associate a test query extension property on the schema rather than to test all tables - or to have a table extension property that defines whether it should be tested.  You can set an extension property on a schema with a property element on the model, then access it in SQL with:

                       

                      select "value" from SYS.Properties WHERE uid = (select uid from SYS.Schemas where name = 'schema name') and name = 'property key'

                       

                       

                      • 8. Re: Can Teiid tell me, from client, if a VDB model or resource is available?
                        Marco Ardito Master
                        ...You will likely run into situations where you don't want or can't test all tables (such as when an access pattern is required).
                        ... It may be better to associate a test query extension property on the schema

                        ... I don't even know what you're talking about! are there examples in the docs?

                         

                        I just guess "property element" on the model is what I could define like others I met before:

                         

                        <property name="UseConnectorMetadata" value="true" />

                        • 9. Re: Can Teiid tell me, from client, if a VDB model or resource is available?
                          Steven Hawkins Master

                          > ... I don't even know what you're talking about! are there examples in the docs?

                           

                          https://teiid.gitbooks.io/documents/content/reference/VDB_Definition.html documents model/schema properties and that they are available in the system metadata, but there isn't a full example of using them.

                           

                          > I just guess "property element" on the model is what I could define like others I met before

                           

                          Yes, like that under the relevant schema/model.  Then your procedure logic would look it up by property key using a select similar to the one shown above.