-
1. Re: How to expose native oracle stored procedure with resultset as vdb table/procedure in teiid designer
shawkins Jan 18, 2016 12:21 PM (in response to onkar.dhuri)1 of 1 people found this helpful> 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?
-
2. Re: How to expose native oracle stored procedure with resultset as vdb table/procedure in teiid designer
onkar.dhuri Jan 20, 2016 3:53 AM (in response to shawkins)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 belowBelow 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 -
3. Re: How to expose native oracle stored procedure with resultset as vdb table/procedure in teiid designer
onkar.dhuri Jan 20, 2016 3:57 AM (in response to onkar.dhuri)Attached full server log
-
server.log.txt.zip 42.3 KB
-
-
4. Re: How to expose native oracle stored procedure with resultset as vdb table/procedure in teiid designer
shawkins Jan 20, 2016 10:40 AM (in response to onkar.dhuri)1 of 1 people found this helpful> 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
-
5. Re: How to expose native oracle stored procedure with resultset as vdb table/procedure in teiid designer
onkar.dhuri Jan 20, 2016 8:56 PM (in response to shawkins)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 ?
-
6. Re: How to expose native oracle stored procedure with resultset as vdb table/procedure in teiid designer
shawkins Jan 21, 2016 8:52 AM (in response to onkar.dhuri)> 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
-
7. Re: How to expose native oracle stored procedure with resultset as vdb table/procedure in teiid designer
shawkins Jan 21, 2016 12:05 PM (in response to 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
-
8. Re: How to expose native oracle stored procedure with resultset as vdb table/procedure in teiid designer
onkar.dhuri Jan 22, 2016 4:30 AM (in response to shawkins)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
-
9. Re: How to expose native oracle stored procedure with resultset as vdb table/procedure in teiid designer
shawkins Jan 22, 2016 10:57 AM (in response to onkar.dhuri)> 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?
-
10. Re: How to expose native oracle stored procedure with resultset as vdb table/procedure in teiid designer
shawkins Jan 22, 2016 11:08 AM (in response to shawkins)The round issue was captured as [TEIID-3924] Round should be marked as supported for Oracle - JBoss Issue Tracker
-
11. Re: How to expose native oracle stored procedure with resultset as vdb table/procedure in teiid designer
onkar.dhuri Jan 25, 2016 10:39 PM (in response to shawkins)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
-
Example.vdb.zip 111.4 KB
-
-
12. Re: How to expose native oracle stored procedure with resultset as vdb table/procedure in teiid designer
onkar.dhuri Jan 25, 2016 10:43 PM (in response to 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'
-
13. Re: How to expose native oracle stored procedure with resultset as vdb table/procedure in teiid designer
shawkins Jan 26, 2016 7:36 PM (in response to onkar.dhuri)1 of 1 people found this helpfulIt 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.
-
14. Re: How to expose native oracle stored procedure with resultset as vdb table/procedure in teiid designer
onkar.dhuri Jan 26, 2016 8:53 PM (in response to shawkins)Thanks a lot Steven.
Regards,
Onkar