Cascaded teiid setup with Webservice produces TEIID30270 error

[ralfr68]

Dear all,

we are confused with a setup involving two cascaded teiid instances, an Oracle database DB1 and a webservice WS. The details are:

• Teiid1 is version 8.8.1 and has access to DB1 and publishes it as VDB1
• Teiid2 is version 8.13.3 and has access to WS and publishes it as VDB2
• Additionally, Teiid1 has access to VDB2 from teiid2. For this to work, we had to deploy the teiid driver 8.13.3 to Teiid1.

This unusual setup is required due to company rules. The problem now is, that a query of the form

SELECT db1.table1.xxx, db2.table2.yyy FROM DB1.table1

LEFT OUTER JOIN WS.view2 on (DB1.table1.key1=WS.view2.key2);

executed on teiid1 produces an error message saying "TEIID30270 No valid criteria specified for procedure parameter Teiid2Model.MyProcedure.Parameter" (which is the procedure that implements the WS for view2). I think the reason is, that teiid2 has only the ability to perform queries of the form "select * from … where key=value", since the WS procedure only takes one parameter (the key). Any JOIN would need to be rewritten into that form.

As a test, we have (temporarily) added DB1 to teiid2, and the JOIN query performed fine. In this case, teiid seems to be able to rewrite the query to a form that can be handled by the WS.

I am completely lost here: Do I need to reconfigure teiid1 or teiid2? What is missing here? I think it could be connected to the translator's "Base Execution Properties", e.g. SupportedJoinCriteria. But where to put it?

Thanks for any hint!

Ralf

[rareddy]

Ralf,

How is the VDB1 accessing the VDB2, using the JDBC? Did you use the "teiid" translator for it? I suspect the JOIN condition as parameter is not being pushed as parameter VDB2's WS. You can attach the query plan to see.

Ramesh..

[ralfr68]

Ramesh,

yes, teiid1 accesses teiid2 through JDBC and the teiid translator. Attached is the query plan. We have investigated a Little bit further and found out:

This query works:

SELECT DB1.table1.xxx, WS.view2.yyy FROM DB1.table1

LEFT OUTER JOIN WS.view2 on (DB1.table1.key1=WS.view2.key2)

WHERE DB1.table1.key1 = '1234567';

But These two queries do not work:

SELECT DB1.table1.xxx, WS.view2.yyy FROM DB1.table1

LEFT OUTER JOIN WS.view2 on (DB1.table1.key1=WS.view2.key2)

or:

SELECT DB1.table1.xxx, WS.view2.yyy FROM DB1.table1

LEFT OUTER JOIN WS.view2 on (DB1.table1.key1=WS.view2.key2)

WHERE DB1.table1.otherkey = 'ABCDEFG';

BUT, if we temporarily add DB1 to teiid2, then ALL of the above queries run fine on teiid2. Unfortunatly, this is not an Option for us.

It seems to me that the query optimizer is not aware of the fact that the web service can only handle exactly one condition.

Thanks for your support!

Ralf