How to expose native oracle stored procedure with resultset as vdb table/procedure in teiid designer

[onkar.dhuri]

Hello,

I have a static VDB created by Teiid designer 9.0.2 that connects to two underlying oracle datasources. We are exposing this vdb through odata (V3) with Jboss AS 6.1.0 and Teiid 8.7.0 with some patches. While applying filters through odata, the where clause is not appended/added to transformation query within vdb table thus getting entire results back from oracle and then applying filters on that later.

I am looking for possible options to pass these filters/criteria all the way to oracle so that we get the result in much faster way than existing. I have created a procedure that returns resultset in oracle which constructs the query dynamically by checking if parameters are null or not. Now I want to expose this procedure as either a table or procedure in teiid designer so that I can access/call it through odata url/filter like -

.../odata/ProcedureName?Parameter1Name='Parameter1Value'&Parameter2Name='Parameter2Value'.

I tried to look for documentation but no luck. The reason why I need to pass these filters to oracle is we have so need to have a query with aggregate functions which should include the column in select/where/group by clause only if it is passed.

Any inputs how can I do this or is there any other better way than this ?

Thanks in advance,

Onkar

[shawkins]

> While applying filters through odata, the where clause is not appended/added to transformation query within vdb table thus getting entire results back from oracle and then applying filters on that later.

That's a good place to start.  It would be good to diagnose why the filters are not being pushed down.  From a debug log you should be able to see the query that the odata layer is issuing to the engine and in turn the query plan.  Would it be possible to post the relevant log section - especially looking for lines that report "was not pushed ..."?

> Now I want to expose this procedure as either a table or procedure in teiid designer so that I can access/call it through odata url/filter like

A Teiid procedure exposed to the odata layer will be callable as an odata function exactly as you expect.

> Any inputs how can I do this or is there any other better way than this ?

Are you having an issue in Designer getting the procedure to import or with manually defining the source procedure?

[onkar.dhuri]

Would it be possible to post the relevant log section - especially looking for lines that report "was not pushed ..."?

I looked into the log section only to find that the filters are being pushed down however my Aggregate function is not applied on filtered resultset.

Below is the oracle query which calculates the average based on filter (where clause) dynamically.

SELECT YEARVAL, MONTHVAL, MONTHNAME, OPERATOR, REGION, FIELD, WELL_TYPE, ALL_WELLS,

STRING_STATUS_LEVEL1, STRING_STATUS_LEVEL2, STRING, EXECUTION_STATUS, PLAN_COST_RM, LIP,

AVG(LIP) OVER() AS AVERAGE, LIP - AVG(LIP) OVER () AS DELTA_LIP,

(100 * ABS(LIP - AVG(LIP) OVER ()) / AVG(LIP) OVER ()) AS DELTA_PERCENT,

FROM IWMRS.WEM_SSM_RESTORTN_EXCEPTN_LIP_V

The query I am using in VDB is as below -

SELECT

    UUID() AS RestExpLIPPk, YEARVAL, MONTHVAL, MONTHNAME, OPERATOR, REGION, FIELD, WELL_TYPE, ALL_WELLS,

    STRING_STATUS_LEVEL1, STRING_STATUS_LEVEL2, "STRING", EXECUTION_STATUS,

    ROUND(CAST(PLAN_COST_RM AS FLOAT), 0) AS PLAN_COST_RM, ROUND(CAST(LIP AS FLOAT), 0) AS LIP,

    ROUND(CAST(AVG(LIP) OVER( ) AS FLOAT), 0) AS AVERAGE, ROUND(CAST((LIP - AVG(LIP) OVER ( )) AS FLOAT), 0) AS DELTA_LIP,

    ROUND(CAST((100 * ABS(LIP - AVG(LIP) OVER ( )) / AVG(LIP) OVER ( )) AS FLOAT), 0) AS DELTA_PERCENT

FROM SSM.WEM_SSM_RESTORTN_EXCEPTN_LIP_V

I want this AVERAGE to be calculated run time depending on the filters that are passed through my odata however I am always getting same AVERAGE (which is AVG(All rows) ) irrespective of filters I pass.

I am attaching the server log for the reference here.

When accessed first time (w/o filters), seems like the aggregated functions are pushed down

2016-01-20 15:55:42,478 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue25) ProcessTree for 7GMGWZzY8dvH.1 ProjectNode(0) output=[Example.RestExc.RestExpLIPPk, Example.RestExc.ALL_WELLS, Example.RestExc.AVERAGE, Example.RestExc.DELTA_LIP, Example.RestExc.DELTA_PERCENT, Example.RestExc.EXECUTION_STATUS, Example.RestExc.FIELD, Example.RestExc.LIP, Example.RestExc.MONTHNAME, Example.RestExc.MONTHVAL, Example.RestExc.OPERATOR, Example.RestExc.PLAN_COST_RM, Example.RestExc.REGION, Example.RestExc."STRING", Example.RestExc.STRING_STATUS_LEVEL1, Example.RestExc.STRING_STATUS_LEVEL2, Example.RestExc.WELL_TYPE, Example.RestExc.YEARVAL] [Example.RestExc.RestExpLIPPk, Example.RestExc.ALL_WELLS, Example.RestExc.AVERAGE, Example.RestExc.DELTA_LIP, Example.RestExc.DELTA_PERCENT, Example.RestExc.EXECUTION_STATUS, Example.RestExc.FIELD, Example.RestExc.LIP, Example.RestExc.MONTHNAME, Example.RestExc.MONTHVAL, Example.RestExc.OPERATOR, Example.RestExc.PLAN_COST_RM, Example.RestExc.REGION, Example.RestExc."STRING", Example.RestExc.STRING_STATUS_LEVEL1, Example.RestExc.STRING_STATUS_LEVEL2, Example.RestExc.WELL_TYPE, Example.RestExc.YEARVAL]

  ProjectNode(1) output=[Example.RestExc.RestExpLIPPk, Example.RestExc.ALL_WELLS, Example.RestExc.AVERAGE, Example.RestExc.DELTA_LIP, Example.RestExc.DELTA_PERCENT, Example.RestExc.EXECUTION_STATUS, Example.RestExc.FIELD, Example.RestExc.LIP, Example.RestExc.MONTHNAME, Example.RestExc.MONTHVAL, Example.RestExc.OPERATOR, Example.RestExc.PLAN_COST_RM, Example.RestExc.REGION, Example.RestExc."STRING", Example.RestExc.STRING_STATUS_LEVEL1, Example.RestExc.STRING_STATUS_LEVEL2, Example.RestExc.WELL_TYPE, Example.RestExc.YEARVAL] [UUID() AS RestExpLIPPk, ALL_WELLS, ROUND(convert(AVG(LIP) OVER (), FLOAT), 0) AS AVERAGE, ROUND(convert((LIP - AVG(LIP) OVER ()), FLOAT), 0) AS DELTA_LIP, ROUND(convert(((100 * ABS((LIP - AVG(LIP) OVER ()))) / AVG(LIP) OVER ()), FLOAT), 0) AS DELTA_PERCENT, EXECUTION_STATUS, FIELD, ROUND(convert(LIP, FLOAT), 0) AS LIP, MONTHNAME, MONTHVAL, OPERATOR, ROUND(convert(PLAN_COST_RM, FLOAT), 0) AS PLAN_COST_RM, REGION, "STRING", STRING_STATUS_LEVEL1, STRING_STATUS_LEVEL2, WELL_TYPE, YEARVAL]

    WindowFunctionProjectNode(2) output=[AVG(LIP) OVER (), ALL_WELLS, LIP, EXECUTION_STATUS, FIELD, MONTHNAME, MONTHVAL, OPERATOR, PLAN_COST_RM, REGION, "STRING", STRING_STATUS_LEVEL1, STRING_STATUS_LEVEL2, WELL_TYPE, YEARVAL]

      AccessNode(3) output=[ALL_WELLS, LIP, EXECUTION_STATUS, FIELD, MONTHNAME, MONTHVAL, OPERATOR, PLAN_COST_RM, REGION, "STRING", STRING_STATUS_LEVEL1, STRING_STATUS_LEVEL2, WELL_TYPE, YEARVAL] SELECT g_0.ALL_WELLS, g_0.LIP, g_0.EXECUTION_STATUS, g_0.FIELD, g_0.MONTHNAME, g_0.MONTHVAL, g_0.OPERATOR, g_0.PLAN_COST_RM, g_0.REGION, g_0."STRING", g_0.STRING_STATUS_LEVEL1, g_0.STRING_STATUS_LEVEL2, g_0.WELL_TYPE, g_0.YEARVAL FROM SSM.WEM_SSM_RESTORTN_EXCEPTN_LIP_V AS g_0

However when filters are applied, below is the query that is formed -

2016-01-20 15:56:15,736 DEBUG [org.teiid.COMMAND_LOG] (Worker2_QueryProcessorQueue179)     START DATA SRC COMMAND:    startTime=2016-01-20 15:56:15.736    requestID=8wBSrjw8SEDV.2    sourceCommandID=4    executionID=2    txID=null    modelName=SSM    translatorName=oracle    sessionID=8wBSrjw8SEDV    principal=edm@dsds-security-domain    sql=SELECT 1 FROM SSM.WEM_SSM_RESTORTN_EXCEPTN_LIP_V AS g_0 WHERE (g_0.YEARVAL = '2015') AND (g_0.MONTHVAL = '10') AND (g_0.OPERATOR = 'PCSB')

2016-01-20 15:56:15,736 DEBUG [org.teiid.COMMAND_LOG] (Worker2_QueryProcessorQueue179)     START DATA SRC COMMAND:    startTime=2016-01-20 15:56:15.736    requestID=8wBSrjw8SEDV.2    sourceCommandID=4    executionID=2    txID=null    modelName=SSM    translatorName=oracle    sessionID=8wBSrjw8SEDV    principal=edm@dsds-security-domain    sql=SELECT 1 FROM SSM.WEM_SSM_RESTORTN_EXCEPTN_LIP_V AS g_0 WHERE (g_0.YEARVAL = '2015') AND (g_0.MONTHVAL = '10') AND (g_0.OPERATOR = 'PCSB')

Am I missing anything or do I need to tweak something in teiid designer to make the aggregate functions as pushdown functions ?

Regarding procedure, I am exposing it through teiid designer as below

Below is the oracle Procedure -

CREATE OR REPLACE PROCEDURE TestProc (

   string_name IN VARCHAR2 DEFAULT NULL,

   prc OUT sys_refcursor)

AS

    stmt varchar2(5000);

    whereclause varchar2(2000);

    groupby varchar2(2000);

   BEGIN

    stmt := 'SELECT STRING, PDATE, ROUND(PDOIL) AS OIL , ROUND(PDGAS) AS GAS , ROUND(PDWATER) AS WATER

        FROM PRODUCTION WHERE 1=1 ';

    if  string_name is not null then

          stmt := stmt || ' AND STRING = ''' || string_name || '''';

     end if;

    stmt := stmt || ' ORDER BY PDATE DESC ';

    dbms_output.put_line(stmt);

    open prc for stmt;

   END;

/

When I try to access the procedure through Odata,

/Sample/ALL_PROJECTS/SampleProc?STRING_NAME='ABC'

I am getting the obvious exception -

java.sql.SQLException: ORA-06550: line 1, column 14: PLS-00306: wrong number or types of arguments in call to 'TESTPROC'

How do we publish this procedure which returns resultset through teiid designer so that it can be accessed like the way above.

Thanks,
Onkar

[onkar.dhuri]

Attached full server log

[shawkins]

> When accessed first time (w/o filters), seems like the aggregated functions are pushed down

Unfortunately they are not.  It looks like the ROUND function is not marked as supported by the Oracle translator that needs to be updated.  An issue will be needed to correct that.

There are several considerations.  The first is that the UUID function is also inhibiting the project node from being fully pushed and our partial projection logic does not yet account for window functions, so that will require another change.

Also when you use OVER () as the window specification that mean that all rows are part of a frame.  If you are logically applying predicates above this, the aggregation will still need to be computed over all rows, then the affect of the filter would be applied.  It sounds like your intent may be to have the affect of the aggregation after the filtering.

> How do we publish this procedure which returns resultset through teiid designer so that it can be accessed like the way above.

Teiid needs to be aware that an out parameter is being used.  On the source procedure add an out parameter and set the native type to "REF CURSOR", then Teiid will know that is where to expect the result set.  See [TEIID-2644] Support retrieving result sets from any out parameter - JBoss Issue Tracker and Oracle Translator - Teiid 9.0 (draft) - Project Documentation Editor

[onkar.dhuri]

An issue will be needed to correct that.

Should I create a Jira issue for it ? Will it be pushed if I just take out ROUND() out from query ?

Also when you use OVER () as the window specification that mean that all rows are part of a frame.  If you are logically applying predicates above this, the aggregation will still need to be computed over all rows, then the affect of the filter would be applied.  It sounds like your intent may be to have the affect of the aggregation after the filtering.

I think ideally all rows shouldn't be part of a frame. The aggregation should be computed after filters are applied and this is exactly how the oracle works producing the result what I intent.

Is it something that can be fixed easily in my Teiid subsystem by applying some patch ?

[shawkins]

> Should I create a Jira issue for it ? Will it be pushed if I just take out ROUND() out from query ?

There are two issues - round needs to be pushed and windowed aggregates need to be able to be partially pushed.  If you could create the issue for ROUND that would be good.  I'll handle the other one.

> I think ideally all rows shouldn't be part of a frame. The aggregation should be computed after filters are applied and this is exactly how the oracle works producing the result what I intent.

That will happen when the where clause is logically below the select clause where the aggregation is being computed.  However in this case the query that you are issuing is:

SELECT "RestExpLIPPk", "ALL_WELLS", "AVERAGE", "DELTA_LIP", "DELTA_PERCENT", "EXECUTION_STATUS", "FIELD", "LIP", "MONTHNAME", "MONTHVAL", "OPERATOR", "PLAN_COST_RM", "REGION", "STRING", "STRING_STATUS_LEVEL1", "STRING_STATUS_LEVEL2", "WELL_TYPE", "YEARVAL" FROM "Example.RestExc" WHERE ((("YEARVAL" = ?) AND ("MONTHVAL" = ?)) AND ("OPERATOR" = ?))

Which means that the filter is applied above the inlinve view containing the aggregation, so unless the window is partitioned over the columns of the predicate, the predicate must be evaluated after the nested select.

> Is it something that can be fixed easily in my Teiid subsystem by applying some patch ?

If you need to perform the filtering first, then compute the window over the resulting rows, that would probably be best expressed as a virtual procedure

[shawkins]

Pusing the window functions even when the whole select clause cannot be pushed is addressed by: [TEIID-3917] Allow partial projection of window functions - JBoss Issue Tracker

[onkar.dhuri]

Thanks a lot Steven.

I tried to use same oracle native procedure as defined in earlier thread and added out parameter with native type as "REF CURSOR" in both source and view.

However I am receiving this "java.sql.SQLException: Invalid column type: 2000" exception.

java.sql.SQLException: Invalid column type: 2000 oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3950) oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:135) oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:304) oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:393) oracle.jdbc.driver.OracleCallableStatementWrapper.registerOutParameter(OracleCallableStatementWrapper.java:1569) org.jboss.jca.adapters.jdbc.CachedCallableStatement.registerOutParameter(CachedCallableStatement.java:142) org.jboss.jca.adapters.jdbc.WrappedCallableStatement.registerOutParameter(WrappedCallableStatement.java:807) org.teiid.translator.jdbc.JDBCExecutionFactory.registerSpecificTypeOfOutParameter(JDBCExecutionFactory.java:828)

I even tried adding the resultset back but still receiving same exception.

Thanks,

Onkar

[shawkins]

> I even tried adding the resultset back but still receiving same exception.

The result set still needs to be defined so we know what columns to expect on the cursor.  With the result set and an out parameter on the source procedure with the native type set to "REF CURSOR", you would expect this to work.  Make sure that you are using the oracle translator.

If it still doesn't seem to work, is it possible to attach the vdb?

[shawkins]

The round issue was captured as [TEIID-3924] Round should be marked as supported for Oracle - JBoss Issue Tracker

[onkar.dhuri]

Hello Steven,

> With the result set and an out parameter on the source procedure with the native type set to "REF CURSOR"

Yes, Even after doing so I get the same exception.

java.sql.SQLException: Invalid column type: 2000 oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3950) oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:135) oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:304) oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:393) oracle.jdbc.driver.OracleCallableStatementWrapper.registerOutParameter(OracleCallableStatementWrapper.java:1569) org.jboss.jca.adapters.jdbc.CachedCallableStatement.registerOutParameter(CachedCallableStatement.java:142) org.jboss.jca.adapters.jdbc.WrappedCallableStatement.registerOutParameter(WrappedCallableStatement.java:807) org.teiid.translator.jdbc.JDBCExecutionFactory.registerSpecificTypeOfOutParameter(JDBCExecutionFactory.java:828) org.teiid.translator.jdbc.oracle.OracleExecutionFactory.registerSpecificTypeOfOutParameter(OracleExecutionFactory.java:830) org.teiid.translator.jdbc.JDBCExecutionFactory.executeStoredProcedure(JDBCExecutionFactory.java:798) org.teiid.translator.jdbc.oracle.OracleExecutionFactory.executeStoredProcedure(OracleExecutionFactory.java:836)

Please find attached vdb for your reference.

Regards,

Onkar

[onkar.dhuri]

Below is my oracle procedure

CREATE OR REPLACE PROCEDURE TestProc (

   string_name IN VARCHAR2 DEFAULT NULL,

   prc OUT sys_refcursor)

AS

    stmt varchar2(5000);

    whereclause varchar2(2000);

    groupby varchar2(2000);

   BEGIN

    stmt := 'SELECT STRING, PDATE, ROUND(PDOIL) AS OIL , ROUND(PDGAS) AS GAS , ROUND(PDWATER) AS WATER

        FROM PRODUCTION WHERE 1=1 ';

    if  string_name is not null then

          stmt := stmt || ' AND STRING = ''' || string_name || '''';

     end if;

    stmt := stmt || ' ORDER BY PDATE DESC ';

    dbms_output.put_line(stmt);

    open prc for stmt;

   END;

/

I am trying to call the vdb procedure with Odata format as below -

.../Example/1/Example-SSM/ALL_PROJECTS/SampleProc?STRING_NAME='ABC'

[shawkins]

It would appear to be a Teiid Designer issue.  [TEIIDDES-1863] Add native type to procedure parameters - JBoss Issue Tracker was supposed to have added the native type as an extension property, but I don't see this in the attached vdb.  I'll refer someone from the Designer team to see if the issue needs reopened.

[onkar.dhuri]

Thanks a lot Steven.

Regards,

Onkar