native query in teiid designer 7.8

[vivedha]

Hi all,

I am using Eclipse indigo 4.2.1, teiid designer 7.8,Teiid runtime 7.7.5 to create REST easy web service from tables spread across two different oracle data bases.When a query is executed the query optimizer try to optimize it and hence generate empty results.So I am trying to use native query option so that the query optimization is stopped and the query runs fine.

The syntax I used is:

select x.* FROM (call native('select Shares_Count, MONTHNAME(Purchase_Date) from Holdings')) w, ARRAYTABLE(w.tuple COLUMNS "Shares_Count" integer, "MonthPurchased" string ) AS x

But I get the error

error: native does not exists

Is my syntax correct?IS there any other steps that I have missed?Is the native query feature available in teiid designer 7.8?

[shawkins]

There is currently no Designer side ease of use integration of the native query procedure feature.  Even if you enable it on your translator, Designer does not know that the procedure exists on that model.  You would have to manually add the procedure definition to the model.  So right click on the desired source model and select to add a procedure.  Give it the name native or whatever you have configured on the translator.  You can use a single string parameter or a string with a vararg object parameter.  That should get you past an error that "native does not exist".

This is not the case with a dynamic vdb as the native procedure metadata is made available to the engine when the vdb is loaded. 

I make sure this is all touched on in the docs, and log an issue against Designer for enhanced support.

[vivedha]

Hi Steven,

Thanks for the quick reply,

My understanding from the reply is

1) Even when we define translator override to support native queries(SupportsNativeQueries=true), designer won't be able to recognize 'native' keyword.
2) Desired Query should be wrapped as a Stored Procedure at Oracle end.

3) This stored procedure should be imported to the source model in the designer.

4) This procedure can then be called from the view as native query.

How can I configure the name of the procedure in the translator?

[shawkins]

> 1) Even when we define translator override to support native queries(SupportsNativeQueries=true), designer won't be able to recognize 'native' keyword.

native is not a keyword.  It is the name of a procedure that you are exposing on the translator, not the database.  The translator will see an invocation of the native procedure as sql that should be passed directly to the source.

> 2) Desired Query should be wrapped as a Stored Procedure at Oracle end.

A procedure on the model, not in the database.

> 3) This stored procedure should be imported to the source model in the designer.

It should be manually added, not imported.  Right click on the source model and you should see an option to add a procedure.

> 4) This procedure can then be called from the view as native query.

Once exposed you can call the native procedure just as any other Teiid stored procedure.

> How can I configure the name of the procedure in the translator?

Translators have several properties related to the native procedure.  There is SupportsNativeQueries to enable the feature in general and there is NativeQueryProcedureName if you want to change the name of the procedure to something other than native.

[rareddy]

Vivedha,

1) On the source model create a procedure called "native" with string as input, and object as return type

2) Now define your view and use "native" procedure in there

3) build a vdb

4) Create translator override for the translator/source you created "native" procedure for.

5) add "supportsNativeQueries = true" property

6) deploy the vdb and execute

Ramesh..

[shawkins]

> 1) On the source model create a procedure called "native" with string as input, and object as return type

By return type Ramesh means a result set with a single column of type object - this will be the row results returned as an array from the native invocation.

Also, do you actually need the ability to issue arbitrary source sql?  Or will your queries be known in advance?

[vivedha]

Thanks for all the suggestions Steven,

I tried the steps suggested.The command call native is now recognized in the teiid designer.There are no errors in the designer.

But after deploying and executing the VDB I get the error

Error Code:65000 Message:Remote org.teiid.core.TeiidProcessingException: Error Code:65000 Message:SourceRMS: Error Code:65000 Message:'{ ?= call native(?)}' error executing statement(s): {1}

In the call native procedure at the Source model,I didn't get an option to to write a procedure or something.I just created a input parameter as string and output as object as suggested.

While previewing the procedure also I get the same error.

Should I also create a stored procedure in the oracle?

Error in the server log:

org.teiid.core.TeiidProcessingException: Error Code:65000 Message:SourceRMS: Error Code:65000 Message:'{ ?= call native(?)}' error executing statement(s): {1}
        at org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:430)
        at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:250)
        at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:271)
        at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:280)
        at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:155)
        at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:280)
        at org.teiid.query.processor.BatchIterator.finalRow(BatchIterator.java:70)
        at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(AbstractTupleSource.java:69)
        at org.teiid.query.processor.BatchIterator.getCurrentTuple(BatchIterator.java:85)
        at org.teiid.common.buffer.AbstractTupleSource.hasNext(AbstractTupleSource.java:91)
        at org.teiid.query.processor.relational.NestedTableJoinStrategy.process(NestedTableJoinStrategy.java:102)
        at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:210)
        at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:280)
        at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:155)
        at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:280)
        at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:149)
        at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:139)
        at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:105)
        at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:147)
        at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:377)
        at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:290)
        at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:49)
        at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:218)
        at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:244)
        at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:122)
        at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:292)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:895)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:918)
        at java.lang.Thread.run(Thread.java:662)
Caused by: org.teiid.translator.TranslatorException: Error Code:65000 Message:'{ ?= call native(?)}' error executing statement(s): {1}
        at org.teiid.translator.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:70)
        at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:276)
        at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:354)
        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:143)
        at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:140)
        at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
        at java.util.concurrent.FutureTask.run(FutureTask.java:138)
        at org.teiid.dqp.internal.process.DQPCore$FutureWork.run(DQPCore.java:120)
        ... 6 more
Caused by: java.sql.SQLException: ORA-06550: line 1, column 13:
PLS-00201: identifier 'NATIVE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

        at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
        at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
        at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
        at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
        at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:191)
        at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:950)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387)
        at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3488)
        at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:3857)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)
        at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:299)
        at org.teiid.translator.jdbc.JDBCExecutionFactory.executeStoredProcedure(JDBCExecutionFactory.java:753)
        at org.teiid.translator.jdbc.oracle.OracleExecutionFactory.executeStoredProcedure(OracleExecutionFactory.java:741)
        at org.teiid.translator.jdbc.JDBCProcedureExecution.execute(JDBCProcedureExecution.java:67)
        ... 13 more

[shawkins]

> In the call native procedure at the Source model,I didn't get an option to to write a procedure or something.

With this approach you are creating a procedure hook on the source model that will allow you to execute any sql against the source.  There are other options if you just want to use pre-defined source sql.

> I just created a input parameter as string and output as object as suggested.

Make sure that it's a result set with an object column, not just an object return parameter.

> While previewing the procedure also I get the same error.

This should mean that you do not have the translator properties set correctly - thus the translator does not know to perform special handling for the native procedure.  Make sure that SupportsNativeQueries is set to true.

> Should I also create a stored procedure in the oracle?

No.  The procedure you are creating on the model is just for the translator to know that the sql string passed in should be executed directly.

[TEIIDDES-1884] Enhanced support for the native query procedure - JBoss Issue Tracker was logged to help make this process simpler in Designer.

[shawkins]

> This should mean that you do not have the translator properties set correctly - thus the translator does not know to perform special handling for the native procedure.  Make sure that SupportsNativeQueries is set to true.

I missed something in your initial message.  If you are on Teiid 7.7 then there is no support of the native query procedure.  That was not added until Teiid 8.2.  I'm not sure how Designer would allow you to set a translator override property for SupportsNativeQueries when targeting a 7.7 instance.

Given that you are on 7.7, do you actually need the ability to issue arbitrary source sql?  Or will your queries be known in advance?

If they are known in advance, then there is a vastly simpler mechanism to issue source sql via an extension metadata property on source tables/procedures.

Steve

[vivedha]

Translator name was different in the VDB, after I changed it, I got a slight different error

Error Code:65000 Message:Remote org.teiid.core.TeiidProcessingException: Error Code:65000 Message:SourceRMS: Error Code:65000 Message:'{  call native(?)}' error executing statement(s): {1}

instead of Error Code:65000 Message:'{ ?= call native(?)}' error executing statement(s): {1}

>Make sure that it's a result set with an object column, not just an object return parameter.

I have attached a screen shot of my native query procedure at the source

>Given that you are on 7.7, do you actually need the ability to issue arbitrary source sql?  Or will your queries be known in advance?

My teiid version is 7.7.5(comes with EDS 5.3.1) and we have used the patch EDS_5.3.1_2_2013 (may be that why native query is supported)and the queries will be known in advance.

>If they are known in advance, then there is a vastly simpler mechanism to issue source sql via an extension metadata property on source tables/procedures.

It could be great if you could help me with that.

[rareddy]

>If they are known in advance, then there is a vastly simpler mechanism to issue source sql via an extension metadata property on source tables/procedures.

It could be great if you could help me with that.

See JDBC Translator   Native Queries section (not native procedures, that is what you have tried above). The instructions may be talking about extended metadata, but those are available in Designer.

For that  (note above applicable ONLY for JDBC sources)

1) create a source model, or import from your JDBC source

2) select the table, now in the properties dialog at bottom left hand corner, you will see "Native Query" property under Extension folder. You need to enter your "native query" there.

3) You must enter a valid query, that returns the exact same table columns as it is defined on, but you can use any criteria or joins etc, but column projections MUST match

then use that source table as before in your views etc, when it time to execute from source table, the engine will replace it with "native query"

Ramesh..

[shawkins]

Here is setting the native-query property in an image and the model/project in a zip.

[vivedha]

I tried as you suggested Ramesh but couldn't see "Native Query" property under Extension folder.I think there is problem in the native query property in the version that I am using(Teiid designer7.8).

When I use the IDE JBDS 5.0 with the teiid designer 7.7.4 I get the native query option in the Source model properties.

1. I added the Model Extension Definitions for Source model
2. I was able to see the relational:native query under extension folder

I couldn't add model Extension for View model(please find the details from screen shot below)

Do I have to provide the native query for each table in the Source model(currently I have some 10 tables)?

[shawkins]

> I couldn't add model Extension for View model(please find the details from screen shot below)

The native-query extension is not applicable to a view model.

> Do I have to provide the native query for each table in the Source model(currently I have some 10 tables)?

No.  You use native-query extension when you want to create a "software defined view" rather than creating a view in the source system itself.  To the Teiid engine a source model table with the native-query extension property looks just like any table, but once pushed down the translator knows to just replace the table reference with an inline view defined by the native-query property.  Presumably your other source tables map to existing source tables/views, so they would not need native-query properties.