> 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:
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?
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
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?
> 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
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]
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?
> 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'
...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" />
> ... 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.