[JBoss JIRA] (TEIID-5462) Netezza error: NzSQLException-netezza.max.stmt.handles
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5462?page=com.atlassian.jira.plugin... ]
Steven Hawkins updated TEIID-5462:
----------------------------------
Fix Version/s: 11.2
Priority: Major (was: Critical)
> Netezza error: NzSQLException-netezza.max.stmt.handles
> ------------------------------------------------------
>
> Key: TEIID-5462
> URL: https://issues.jboss.org/browse/TEIID-5462
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Affects Versions: 11.1
> Environment: teiid-11.1.0 (from 01.09.2018) on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final)
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Fix For: 11.2
>
>
> Running the following query:
> {code:sql}
> insert into dwh.test_my_stream select t1.* from views.v t1 join (call views.pr()) t2 on t1.a=t2.a
> where
> t1.a >= (select max(t1.a) from views.v t1 join (call views.pr()) t2 on t1.a=t2.a)
> and
> t1.a1 >= (select min(t1.a1) from views.v t1 left join (call views.pr()) t2 on t1.a=t2.a) limit 10000 ;;
> {code}
> teiid server throws out the following stacktrace:
> {code:noformat}
> 2018-09-03 18:56:47,795 WARN [org.teiid.PROCESSOR] (Worker4_QueryProcessorQueue32) AQhJm01u0x7c TEIID30020 Processing exception for request AQhJm01u0x7c.13 'TEIID30312 Unable to eva
> luate right expression of o__3.a >= (SELECT MAX(t1.a) FROM views.v AS t1 INNER JOIN (EXEC views.pr()) AS t2 ON t1.a = convert(t2.a, integer))'. Originally ExpressionEvaluationExcepti
> on 'netezza.max.stmt.handles' QueryExecutor.java:444. Enable more detailed logging to see the entire stacktrace.
> 2018-09-03 18:57:03,963 WARN [org.teiid.CONNECTOR] (Worker4_QueryProcessorQueue35) AQhJm01u0x7c Connector worker process failed for atomic-request=AQhJm01u0x7c.14.5.47: org.teiid.tr
> anslator.jdbc.JDBCExecutionException: 11403 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT 1 AS c_0 FROM "TEST"."ADMIN"."TEST_STREAM" AS g_0 WHE
> RE g_0."RULE_ID" = 1 LIMIT 1]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:127)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:382)
> at sun.reflect.GeneratedMethodAccessor101.invoke(Unknown Source)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:229)
> at com.sun.proxy.$Proxy36.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
> at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:138)
> at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:401)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector$BatchProducerTupleSource.nextTuple(BatchCollector.java:90)
> at org.teiid.query.processor.relational.GroupingNode.groupPhase(GroupingNode.java:593)
> at org.teiid.query.processor.relational.GroupingNode.nextBatchDirect(GroupingNode.java:389)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
> at org.teiid.query.processor.relational.SubqueryAwareEvaluator.evaluateSubquery(SubqueryAwareEvaluator.java:354)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:1323)
> at org.teiid.query.eval.Evaluator.internalEvaluate(Evaluator.java:763)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:707)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:344)
> at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:185)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:297)
> at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:181)
> at org.teiid.query.eval.Evaluator.evaluateTVL(Evaluator.java:174)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:168)
> at org.teiid.query.processor.relational.SelectNode.nextBatchDirect(SelectNode.java:118)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
> at org.teiid.query.processor.relational.SourceState.getTupleBuffer(SourceState.java:244)
> at org.teiid.query.processor.relational.EnhancedSortMergeJoinStrategy.loadLeft(EnhancedSortMergeJoinStrategy.java:227)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:228)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:200)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.ProjectIntoNode.nextBatchDirect(ProjectIntoNode.java:140)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
> at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:492)
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:362)
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:47)
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:285)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: org.netezza.error.NzSQLException: netezza.max.stmt.handles
> at org.netezza.internal.QueryExecutor.sendQuery(QueryExecutor.java:444)
> at org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:73)
> at org.netezza.sql.NzConnection.execute(NzConnection.java:2904)
> at org.netezza.sql.NzPreparedStatament._execute(NzPreparedStatament.java:1163)
> at org.netezza.sql.NzPreparedStatament.prepare(NzPreparedStatament.java:1180)
> at org.netezza.sql.NzPreparedStatament.<init>(NzPreparedStatament.java:96)
> at org.netezza.sql.NzConnection.prepareStatement(NzConnection.java:1624)
> at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:757)
> at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:743)
> at org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:454)
> at org.teiid.translator.jdbc.JDBCBaseExecution.getPreparedStatement(JDBCBaseExecution.java:192)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:117)
> ... 89 more
> {code}
> which is (according to https://www-01.ibm.com/support/docview.wss?uid=swg21570296 link) the following:
> "When using the JDBC driver, one connection instance can only execute a single query at a time. This means that if one query is already executing on a specific connection instance, no other queries can be sent for parallel execution. If you run multiple queries using the same connection instance, you might encounter an error similar to the following:
> Error[1]: Failed retrieving bean metadata.netezza.max.stmt.handles"
> Another interesting thing is that running pretty the same query but not like insert, just usual select:
> {code:sql}
> select t1.* from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer)
> where
> t1.a >= (select max(t1.a) from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer))
> and
> t1.a1 >= (select min(t1.a1) from views.v t1 left join (call views.pr()) t2 on t1.a=cast(t2.a as integer)) limit 10000 ;;
> {code}
> will work without any errors.
> Setting the threadBound translator property on the netezza source to true helps to solve the problem but
> actually we should introduce a dynamic way to allow for thread bound to be turned on based upon a transactional request.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 6 months
- 1
- 0
[JBoss JIRA] (TEIID-4455) Impala Translator - Change planning step for from_unixtime() pushdown
by Johnathon Lee (JIRA)
[ https://issues.jboss.org/browse/TEIID-4455?page=com.atlassian.jira.plugin... ]
Johnathon Lee updated TEIID-4455:
---------------------------------
Fix Version/s: (was: 8.12.15.6_4)
> Impala Translator - Change planning step for from_unixtime() pushdown
> ---------------------------------------------------------------------
>
> Key: TEIID-4455
> URL: https://issues.jboss.org/browse/TEIID-4455
> Project: Teiid
> Issue Type: Feature Request
> Components: JDBC Connector
> Reporter: Don Krapohl
> Assignee: Kylin Soong
> Priority: Minor
> Labels: Impala_Translator
> Fix For: 9.2
>
>
> Impala and Teiid both have from_unixtime() functions and per the forum reference the mapping to timestampadd() appears to come before the test for pushdown support of the impala native function.
> As a query request we require the impala from_unixtime() function be executed instead of the Teiid-native version.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 6 months
- 1
- 0
[JBoss JIRA] (TEIID-5462) Netezza error: NzSQLException-netezza.max.stmt.handles
by dalex dalex (JIRA)
[ https://issues.jboss.org/browse/TEIID-5462?page=com.atlassian.jira.plugin... ]
dalex dalex edited comment on TEIID-5462 at 9/4/18 10:28 AM:
-------------------------------------------------------------
[~shawkins] I changed type of the issue from Bug to Enhancement, also added the phrase in the issue's description field: "Setting the threadBound translator property on the netezza source to true helps to solve the problem but actually we should introduce a dynamic way to allow for thread bound to be turned on based upon a transactional request".
was (Author: dalex005):
[~shawkins] I changed type of the issue from Bug to Enhancement, also added the phrase in the issue's description field: "Actually we should introduce a dynamic way to allow for thread bound to be turned on based upon a transactional request."
> Netezza error: NzSQLException-netezza.max.stmt.handles
> ------------------------------------------------------
>
> Key: TEIID-5462
> URL: https://issues.jboss.org/browse/TEIID-5462
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Affects Versions: 11.1
> Environment: teiid-11.1.0 (from 01.09.2018) on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final)
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Critical
>
> Running the following query:
> {code:sql}
> insert into dwh.test_my_stream select t1.* from views.v t1 join (call views.pr()) t2 on t1.a=t2.a
> where
> t1.a >= (select max(t1.a) from views.v t1 join (call views.pr()) t2 on t1.a=t2.a)
> and
> t1.a1 >= (select min(t1.a1) from views.v t1 left join (call views.pr()) t2 on t1.a=t2.a) limit 10000 ;;
> {code}
> teiid server throws out the following stacktrace:
> {code:noformat}
> 2018-09-03 18:56:47,795 WARN [org.teiid.PROCESSOR] (Worker4_QueryProcessorQueue32) AQhJm01u0x7c TEIID30020 Processing exception for request AQhJm01u0x7c.13 'TEIID30312 Unable to eva
> luate right expression of o__3.a >= (SELECT MAX(t1.a) FROM views.v AS t1 INNER JOIN (EXEC views.pr()) AS t2 ON t1.a = convert(t2.a, integer))'. Originally ExpressionEvaluationExcepti
> on 'netezza.max.stmt.handles' QueryExecutor.java:444. Enable more detailed logging to see the entire stacktrace.
> 2018-09-03 18:57:03,963 WARN [org.teiid.CONNECTOR] (Worker4_QueryProcessorQueue35) AQhJm01u0x7c Connector worker process failed for atomic-request=AQhJm01u0x7c.14.5.47: org.teiid.tr
> anslator.jdbc.JDBCExecutionException: 11403 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT 1 AS c_0 FROM "TEST"."ADMIN"."TEST_STREAM" AS g_0 WHE
> RE g_0."RULE_ID" = 1 LIMIT 1]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:127)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:382)
> at sun.reflect.GeneratedMethodAccessor101.invoke(Unknown Source)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:229)
> at com.sun.proxy.$Proxy36.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
> at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:138)
> at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:401)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector$BatchProducerTupleSource.nextTuple(BatchCollector.java:90)
> at org.teiid.query.processor.relational.GroupingNode.groupPhase(GroupingNode.java:593)
> at org.teiid.query.processor.relational.GroupingNode.nextBatchDirect(GroupingNode.java:389)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
> at org.teiid.query.processor.relational.SubqueryAwareEvaluator.evaluateSubquery(SubqueryAwareEvaluator.java:354)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:1323)
> at org.teiid.query.eval.Evaluator.internalEvaluate(Evaluator.java:763)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:707)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:344)
> at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:185)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:297)
> at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:181)
> at org.teiid.query.eval.Evaluator.evaluateTVL(Evaluator.java:174)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:168)
> at org.teiid.query.processor.relational.SelectNode.nextBatchDirect(SelectNode.java:118)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
> at org.teiid.query.processor.relational.SourceState.getTupleBuffer(SourceState.java:244)
> at org.teiid.query.processor.relational.EnhancedSortMergeJoinStrategy.loadLeft(EnhancedSortMergeJoinStrategy.java:227)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:228)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:200)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.ProjectIntoNode.nextBatchDirect(ProjectIntoNode.java:140)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
> at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:492)
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:362)
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:47)
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:285)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: org.netezza.error.NzSQLException: netezza.max.stmt.handles
> at org.netezza.internal.QueryExecutor.sendQuery(QueryExecutor.java:444)
> at org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:73)
> at org.netezza.sql.NzConnection.execute(NzConnection.java:2904)
> at org.netezza.sql.NzPreparedStatament._execute(NzPreparedStatament.java:1163)
> at org.netezza.sql.NzPreparedStatament.prepare(NzPreparedStatament.java:1180)
> at org.netezza.sql.NzPreparedStatament.<init>(NzPreparedStatament.java:96)
> at org.netezza.sql.NzConnection.prepareStatement(NzConnection.java:1624)
> at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:757)
> at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:743)
> at org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:454)
> at org.teiid.translator.jdbc.JDBCBaseExecution.getPreparedStatement(JDBCBaseExecution.java:192)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:117)
> ... 89 more
> {code}
> which is (according to https://www-01.ibm.com/support/docview.wss?uid=swg21570296 link) the following:
> "When using the JDBC driver, one connection instance can only execute a single query at a time. This means that if one query is already executing on a specific connection instance, no other queries can be sent for parallel execution. If you run multiple queries using the same connection instance, you might encounter an error similar to the following:
> Error[1]: Failed retrieving bean metadata.netezza.max.stmt.handles"
> Another interesting thing is that running pretty the same query but not like insert, just usual select:
> {code:sql}
> select t1.* from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer)
> where
> t1.a >= (select max(t1.a) from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer))
> and
> t1.a1 >= (select min(t1.a1) from views.v t1 left join (call views.pr()) t2 on t1.a=cast(t2.a as integer)) limit 10000 ;;
> {code}
> will work without any errors.
> Setting the threadBound translator property on the netezza source to true helps to solve the problem but
> actually we should introduce a dynamic way to allow for thread bound to be turned on based upon a transactional request.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 6 months
- 1
- 0
[JBoss JIRA] (TEIID-5462) Netezza error: NzSQLException-netezza.max.stmt.handles
by dalex dalex (JIRA)
[ https://issues.jboss.org/browse/TEIID-5462?page=com.atlassian.jira.plugin... ]
dalex dalex updated TEIID-5462:
-------------------------------
Description:
Running the following query:
{code:sql}
insert into dwh.test_my_stream select t1.* from views.v t1 join (call views.pr()) t2 on t1.a=t2.a
where
t1.a >= (select max(t1.a) from views.v t1 join (call views.pr()) t2 on t1.a=t2.a)
and
t1.a1 >= (select min(t1.a1) from views.v t1 left join (call views.pr()) t2 on t1.a=t2.a) limit 10000 ;;
{code}
teiid server throws out the following stacktrace:
{code:noformat}
2018-09-03 18:56:47,795 WARN [org.teiid.PROCESSOR] (Worker4_QueryProcessorQueue32) AQhJm01u0x7c TEIID30020 Processing exception for request AQhJm01u0x7c.13 'TEIID30312 Unable to eva
luate right expression of o__3.a >= (SELECT MAX(t1.a) FROM views.v AS t1 INNER JOIN (EXEC views.pr()) AS t2 ON t1.a = convert(t2.a, integer))'. Originally ExpressionEvaluationExcepti
on 'netezza.max.stmt.handles' QueryExecutor.java:444. Enable more detailed logging to see the entire stacktrace.
2018-09-03 18:57:03,963 WARN [org.teiid.CONNECTOR] (Worker4_QueryProcessorQueue35) AQhJm01u0x7c Connector worker process failed for atomic-request=AQhJm01u0x7c.14.5.47: org.teiid.tr
anslator.jdbc.JDBCExecutionException: 11403 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT 1 AS c_0 FROM "TEST"."ADMIN"."TEST_STREAM" AS g_0 WHE
RE g_0."RULE_ID" = 1 LIMIT 1]
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:127)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:382)
at sun.reflect.GeneratedMethodAccessor101.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:229)
at com.sun.proxy.$Proxy36.execute(Unknown Source)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:138)
at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:401)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.BatchCollector$BatchProducerTupleSource.nextTuple(BatchCollector.java:90)
at org.teiid.query.processor.relational.GroupingNode.groupPhase(GroupingNode.java:593)
at org.teiid.query.processor.relational.GroupingNode.nextBatchDirect(GroupingNode.java:389)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
at org.teiid.query.processor.relational.SubqueryAwareEvaluator.evaluateSubquery(SubqueryAwareEvaluator.java:354)
at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:1323)
at org.teiid.query.eval.Evaluator.internalEvaluate(Evaluator.java:763)
at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:707)
at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:344)
at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:185)
at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:297)
at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:181)
at org.teiid.query.eval.Evaluator.evaluateTVL(Evaluator.java:174)
at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:168)
at org.teiid.query.processor.relational.SelectNode.nextBatchDirect(SelectNode.java:118)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
at org.teiid.query.processor.relational.SourceState.getTupleBuffer(SourceState.java:244)
at org.teiid.query.processor.relational.EnhancedSortMergeJoinStrategy.loadLeft(EnhancedSortMergeJoinStrategy.java:227)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:228)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:200)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.ProjectIntoNode.nextBatchDirect(ProjectIntoNode.java:140)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:492)
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:362)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:47)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:285)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.netezza.error.NzSQLException: netezza.max.stmt.handles
at org.netezza.internal.QueryExecutor.sendQuery(QueryExecutor.java:444)
at org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:73)
at org.netezza.sql.NzConnection.execute(NzConnection.java:2904)
at org.netezza.sql.NzPreparedStatament._execute(NzPreparedStatament.java:1163)
at org.netezza.sql.NzPreparedStatament.prepare(NzPreparedStatament.java:1180)
at org.netezza.sql.NzPreparedStatament.<init>(NzPreparedStatament.java:96)
at org.netezza.sql.NzConnection.prepareStatement(NzConnection.java:1624)
at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:757)
at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:743)
at org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:454)
at org.teiid.translator.jdbc.JDBCBaseExecution.getPreparedStatement(JDBCBaseExecution.java:192)
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:117)
... 89 more
{code}
which is (according to https://www-01.ibm.com/support/docview.wss?uid=swg21570296 link) the following:
"When using the JDBC driver, one connection instance can only execute a single query at a time. This means that if one query is already executing on a specific connection instance, no other queries can be sent for parallel execution. If you run multiple queries using the same connection instance, you might encounter an error similar to the following:
Error[1]: Failed retrieving bean metadata.netezza.max.stmt.handles"
Another interesting thing is that running pretty the same query but not like insert, just usual select:
{code:sql}
select t1.* from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer)
where
t1.a >= (select max(t1.a) from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer))
and
t1.a1 >= (select min(t1.a1) from views.v t1 left join (call views.pr()) t2 on t1.a=cast(t2.a as integer)) limit 10000 ;;
{code}
will work without any errors.
Setting the threadBound translator property on the netezza source to true helps to solve the problem but
actually we should introduce a dynamic way to allow for thread bound to be turned on based upon a transactional request.
was:
Running the following query:
{code:sql}
insert into dwh.test_my_stream select t1.* from views.v t1 join (call views.pr()) t2 on t1.a=t2.a
where
t1.a >= (select max(t1.a) from views.v t1 join (call views.pr()) t2 on t1.a=t2.a)
and
t1.a1 >= (select min(t1.a1) from views.v t1 left join (call views.pr()) t2 on t1.a=t2.a) limit 10000 ;;
{code}
teiid server throws out the following stacktrace:
{code:noformat}
2018-09-03 18:56:47,795 WARN [org.teiid.PROCESSOR] (Worker4_QueryProcessorQueue32) AQhJm01u0x7c TEIID30020 Processing exception for request AQhJm01u0x7c.13 'TEIID30312 Unable to eva
luate right expression of o__3.a >= (SELECT MAX(t1.a) FROM views.v AS t1 INNER JOIN (EXEC views.pr()) AS t2 ON t1.a = convert(t2.a, integer))'. Originally ExpressionEvaluationExcepti
on 'netezza.max.stmt.handles' QueryExecutor.java:444. Enable more detailed logging to see the entire stacktrace.
2018-09-03 18:57:03,963 WARN [org.teiid.CONNECTOR] (Worker4_QueryProcessorQueue35) AQhJm01u0x7c Connector worker process failed for atomic-request=AQhJm01u0x7c.14.5.47: org.teiid.tr
anslator.jdbc.JDBCExecutionException: 11403 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT 1 AS c_0 FROM "TEST"."ADMIN"."TEST_STREAM" AS g_0 WHE
RE g_0."RULE_ID" = 1 LIMIT 1]
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:127)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:382)
at sun.reflect.GeneratedMethodAccessor101.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:229)
at com.sun.proxy.$Proxy36.execute(Unknown Source)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:138)
at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:401)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.BatchCollector$BatchProducerTupleSource.nextTuple(BatchCollector.java:90)
at org.teiid.query.processor.relational.GroupingNode.groupPhase(GroupingNode.java:593)
at org.teiid.query.processor.relational.GroupingNode.nextBatchDirect(GroupingNode.java:389)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
at org.teiid.query.processor.relational.SubqueryAwareEvaluator.evaluateSubquery(SubqueryAwareEvaluator.java:354)
at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:1323)
at org.teiid.query.eval.Evaluator.internalEvaluate(Evaluator.java:763)
at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:707)
at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:344)
at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:185)
at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:297)
at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:181)
at org.teiid.query.eval.Evaluator.evaluateTVL(Evaluator.java:174)
at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:168)
at org.teiid.query.processor.relational.SelectNode.nextBatchDirect(SelectNode.java:118)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
at org.teiid.query.processor.relational.SourceState.getTupleBuffer(SourceState.java:244)
at org.teiid.query.processor.relational.EnhancedSortMergeJoinStrategy.loadLeft(EnhancedSortMergeJoinStrategy.java:227)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:228)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:200)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.ProjectIntoNode.nextBatchDirect(ProjectIntoNode.java:140)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:492)
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:362)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:47)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:285)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.netezza.error.NzSQLException: netezza.max.stmt.handles
at org.netezza.internal.QueryExecutor.sendQuery(QueryExecutor.java:444)
at org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:73)
at org.netezza.sql.NzConnection.execute(NzConnection.java:2904)
at org.netezza.sql.NzPreparedStatament._execute(NzPreparedStatament.java:1163)
at org.netezza.sql.NzPreparedStatament.prepare(NzPreparedStatament.java:1180)
at org.netezza.sql.NzPreparedStatament.<init>(NzPreparedStatament.java:96)
at org.netezza.sql.NzConnection.prepareStatement(NzConnection.java:1624)
at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:757)
at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:743)
at org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:454)
at org.teiid.translator.jdbc.JDBCBaseExecution.getPreparedStatement(JDBCBaseExecution.java:192)
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:117)
... 89 more
{code}
which is (according to https://www-01.ibm.com/support/docview.wss?uid=swg21570296 link) the following:
"When using the JDBC driver, one connection instance can only execute a single query at a time. This means that if one query is already executing on a specific connection instance, no other queries can be sent for parallel execution. If you run multiple queries using the same connection instance, you might encounter an error similar to the following:
Error[1]: Failed retrieving bean metadata.netezza.max.stmt.handles"
Another interesting thing is that running pretty the same query but not like insert, just usual select:
{code:sql}
select t1.* from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer)
where
t1.a >= (select max(t1.a) from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer))
and
t1.a1 >= (select min(t1.a1) from views.v t1 left join (call views.pr()) t2 on t1.a=cast(t2.a as integer)) limit 10000 ;;
{code}
will work without any errors.
Actually we should introduce a dynamic way to allow for thread bound to be turned on based upon a transactional request.
> Netezza error: NzSQLException-netezza.max.stmt.handles
> ------------------------------------------------------
>
> Key: TEIID-5462
> URL: https://issues.jboss.org/browse/TEIID-5462
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Affects Versions: 11.1
> Environment: teiid-11.1.0 (from 01.09.2018) on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final)
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Critical
>
> Running the following query:
> {code:sql}
> insert into dwh.test_my_stream select t1.* from views.v t1 join (call views.pr()) t2 on t1.a=t2.a
> where
> t1.a >= (select max(t1.a) from views.v t1 join (call views.pr()) t2 on t1.a=t2.a)
> and
> t1.a1 >= (select min(t1.a1) from views.v t1 left join (call views.pr()) t2 on t1.a=t2.a) limit 10000 ;;
> {code}
> teiid server throws out the following stacktrace:
> {code:noformat}
> 2018-09-03 18:56:47,795 WARN [org.teiid.PROCESSOR] (Worker4_QueryProcessorQueue32) AQhJm01u0x7c TEIID30020 Processing exception for request AQhJm01u0x7c.13 'TEIID30312 Unable to eva
> luate right expression of o__3.a >= (SELECT MAX(t1.a) FROM views.v AS t1 INNER JOIN (EXEC views.pr()) AS t2 ON t1.a = convert(t2.a, integer))'. Originally ExpressionEvaluationExcepti
> on 'netezza.max.stmt.handles' QueryExecutor.java:444. Enable more detailed logging to see the entire stacktrace.
> 2018-09-03 18:57:03,963 WARN [org.teiid.CONNECTOR] (Worker4_QueryProcessorQueue35) AQhJm01u0x7c Connector worker process failed for atomic-request=AQhJm01u0x7c.14.5.47: org.teiid.tr
> anslator.jdbc.JDBCExecutionException: 11403 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT 1 AS c_0 FROM "TEST"."ADMIN"."TEST_STREAM" AS g_0 WHE
> RE g_0."RULE_ID" = 1 LIMIT 1]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:127)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:382)
> at sun.reflect.GeneratedMethodAccessor101.invoke(Unknown Source)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:229)
> at com.sun.proxy.$Proxy36.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
> at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:138)
> at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:401)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector$BatchProducerTupleSource.nextTuple(BatchCollector.java:90)
> at org.teiid.query.processor.relational.GroupingNode.groupPhase(GroupingNode.java:593)
> at org.teiid.query.processor.relational.GroupingNode.nextBatchDirect(GroupingNode.java:389)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
> at org.teiid.query.processor.relational.SubqueryAwareEvaluator.evaluateSubquery(SubqueryAwareEvaluator.java:354)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:1323)
> at org.teiid.query.eval.Evaluator.internalEvaluate(Evaluator.java:763)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:707)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:344)
> at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:185)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:297)
> at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:181)
> at org.teiid.query.eval.Evaluator.evaluateTVL(Evaluator.java:174)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:168)
> at org.teiid.query.processor.relational.SelectNode.nextBatchDirect(SelectNode.java:118)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
> at org.teiid.query.processor.relational.SourceState.getTupleBuffer(SourceState.java:244)
> at org.teiid.query.processor.relational.EnhancedSortMergeJoinStrategy.loadLeft(EnhancedSortMergeJoinStrategy.java:227)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:228)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:200)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.ProjectIntoNode.nextBatchDirect(ProjectIntoNode.java:140)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
> at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:492)
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:362)
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:47)
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:285)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: org.netezza.error.NzSQLException: netezza.max.stmt.handles
> at org.netezza.internal.QueryExecutor.sendQuery(QueryExecutor.java:444)
> at org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:73)
> at org.netezza.sql.NzConnection.execute(NzConnection.java:2904)
> at org.netezza.sql.NzPreparedStatament._execute(NzPreparedStatament.java:1163)
> at org.netezza.sql.NzPreparedStatament.prepare(NzPreparedStatament.java:1180)
> at org.netezza.sql.NzPreparedStatament.<init>(NzPreparedStatament.java:96)
> at org.netezza.sql.NzConnection.prepareStatement(NzConnection.java:1624)
> at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:757)
> at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:743)
> at org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:454)
> at org.teiid.translator.jdbc.JDBCBaseExecution.getPreparedStatement(JDBCBaseExecution.java:192)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:117)
> ... 89 more
> {code}
> which is (according to https://www-01.ibm.com/support/docview.wss?uid=swg21570296 link) the following:
> "When using the JDBC driver, one connection instance can only execute a single query at a time. This means that if one query is already executing on a specific connection instance, no other queries can be sent for parallel execution. If you run multiple queries using the same connection instance, you might encounter an error similar to the following:
> Error[1]: Failed retrieving bean metadata.netezza.max.stmt.handles"
> Another interesting thing is that running pretty the same query but not like insert, just usual select:
> {code:sql}
> select t1.* from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer)
> where
> t1.a >= (select max(t1.a) from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer))
> and
> t1.a1 >= (select min(t1.a1) from views.v t1 left join (call views.pr()) t2 on t1.a=cast(t2.a as integer)) limit 10000 ;;
> {code}
> will work without any errors.
> Setting the threadBound translator property on the netezza source to true helps to solve the problem but
> actually we should introduce a dynamic way to allow for thread bound to be turned on based upon a transactional request.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 6 months
- 1
- 0
[JBoss JIRA] (TEIID-5462) Netezza error: NzSQLException-netezza.max.stmt.handles
by dalex dalex (JIRA)
[ https://issues.jboss.org/browse/TEIID-5462?page=com.atlassian.jira.plugin... ]
dalex dalex edited comment on TEIID-5462 at 9/4/18 10:26 AM:
-------------------------------------------------------------
[~shawkins] I changed type of the issue from Bug to Enhancement, also added the phrase in the issue's description field: "Actually we should introduce a dynamic way to allow for thread bound to be turned on based upon a transactional request."
was (Author: dalex005):
[~shawkins] changed type of the issue from Bug to Enhancement, also added the phrase in the issue's description field: "Actually we should introduce a dynamic way to allow for thread bound to be turned on based upon a transactional request."
> Netezza error: NzSQLException-netezza.max.stmt.handles
> ------------------------------------------------------
>
> Key: TEIID-5462
> URL: https://issues.jboss.org/browse/TEIID-5462
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Affects Versions: 11.1
> Environment: teiid-11.1.0 (from 01.09.2018) on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final)
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Critical
>
> Running the following query:
> {code:sql}
> insert into dwh.test_my_stream select t1.* from views.v t1 join (call views.pr()) t2 on t1.a=t2.a
> where
> t1.a >= (select max(t1.a) from views.v t1 join (call views.pr()) t2 on t1.a=t2.a)
> and
> t1.a1 >= (select min(t1.a1) from views.v t1 left join (call views.pr()) t2 on t1.a=t2.a) limit 10000 ;;
> {code}
> teiid server throws out the following stacktrace:
> {code:noformat}
> 2018-09-03 18:56:47,795 WARN [org.teiid.PROCESSOR] (Worker4_QueryProcessorQueue32) AQhJm01u0x7c TEIID30020 Processing exception for request AQhJm01u0x7c.13 'TEIID30312 Unable to eva
> luate right expression of o__3.a >= (SELECT MAX(t1.a) FROM views.v AS t1 INNER JOIN (EXEC views.pr()) AS t2 ON t1.a = convert(t2.a, integer))'. Originally ExpressionEvaluationExcepti
> on 'netezza.max.stmt.handles' QueryExecutor.java:444. Enable more detailed logging to see the entire stacktrace.
> 2018-09-03 18:57:03,963 WARN [org.teiid.CONNECTOR] (Worker4_QueryProcessorQueue35) AQhJm01u0x7c Connector worker process failed for atomic-request=AQhJm01u0x7c.14.5.47: org.teiid.tr
> anslator.jdbc.JDBCExecutionException: 11403 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT 1 AS c_0 FROM "TEST"."ADMIN"."TEST_STREAM" AS g_0 WHE
> RE g_0."RULE_ID" = 1 LIMIT 1]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:127)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:382)
> at sun.reflect.GeneratedMethodAccessor101.invoke(Unknown Source)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:229)
> at com.sun.proxy.$Proxy36.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
> at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:138)
> at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:401)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector$BatchProducerTupleSource.nextTuple(BatchCollector.java:90)
> at org.teiid.query.processor.relational.GroupingNode.groupPhase(GroupingNode.java:593)
> at org.teiid.query.processor.relational.GroupingNode.nextBatchDirect(GroupingNode.java:389)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
> at org.teiid.query.processor.relational.SubqueryAwareEvaluator.evaluateSubquery(SubqueryAwareEvaluator.java:354)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:1323)
> at org.teiid.query.eval.Evaluator.internalEvaluate(Evaluator.java:763)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:707)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:344)
> at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:185)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:297)
> at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:181)
> at org.teiid.query.eval.Evaluator.evaluateTVL(Evaluator.java:174)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:168)
> at org.teiid.query.processor.relational.SelectNode.nextBatchDirect(SelectNode.java:118)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
> at org.teiid.query.processor.relational.SourceState.getTupleBuffer(SourceState.java:244)
> at org.teiid.query.processor.relational.EnhancedSortMergeJoinStrategy.loadLeft(EnhancedSortMergeJoinStrategy.java:227)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:228)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:200)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.ProjectIntoNode.nextBatchDirect(ProjectIntoNode.java:140)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
> at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:492)
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:362)
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:47)
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:285)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: org.netezza.error.NzSQLException: netezza.max.stmt.handles
> at org.netezza.internal.QueryExecutor.sendQuery(QueryExecutor.java:444)
> at org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:73)
> at org.netezza.sql.NzConnection.execute(NzConnection.java:2904)
> at org.netezza.sql.NzPreparedStatament._execute(NzPreparedStatament.java:1163)
> at org.netezza.sql.NzPreparedStatament.prepare(NzPreparedStatament.java:1180)
> at org.netezza.sql.NzPreparedStatament.<init>(NzPreparedStatament.java:96)
> at org.netezza.sql.NzConnection.prepareStatement(NzConnection.java:1624)
> at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:757)
> at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:743)
> at org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:454)
> at org.teiid.translator.jdbc.JDBCBaseExecution.getPreparedStatement(JDBCBaseExecution.java:192)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:117)
> ... 89 more
> {code}
> which is (according to https://www-01.ibm.com/support/docview.wss?uid=swg21570296 link) the following:
> "When using the JDBC driver, one connection instance can only execute a single query at a time. This means that if one query is already executing on a specific connection instance, no other queries can be sent for parallel execution. If you run multiple queries using the same connection instance, you might encounter an error similar to the following:
> Error[1]: Failed retrieving bean metadata.netezza.max.stmt.handles"
> Another interesting thing is that running pretty the same query but not like insert, just usual select:
> {code:sql}
> select t1.* from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer)
> where
> t1.a >= (select max(t1.a) from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer))
> and
> t1.a1 >= (select min(t1.a1) from views.v t1 left join (call views.pr()) t2 on t1.a=cast(t2.a as integer)) limit 10000 ;;
> {code}
> will work without any errors.
> Actually we should introduce a dynamic way to allow for thread bound to be turned on based upon a transactional request.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 6 months
- 1
- 0
[JBoss JIRA] (TEIID-5462) Netezza error: NzSQLException-netezza.max.stmt.handles
by dalex dalex (JIRA)
[ https://issues.jboss.org/browse/TEIID-5462?page=com.atlassian.jira.plugin... ]
dalex dalex commented on TEIID-5462:
------------------------------------
[~shawkins] changed type of the issue from Bug to Enhancement, also added the phrase in the issue's description field: "Actually we should introduce a dynamic way to allow for thread bound to be turned on based upon a transactional request."
> Netezza error: NzSQLException-netezza.max.stmt.handles
> ------------------------------------------------------
>
> Key: TEIID-5462
> URL: https://issues.jboss.org/browse/TEIID-5462
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Affects Versions: 11.1
> Environment: teiid-11.1.0 (from 01.09.2018) on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final)
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Critical
>
> Running the following query:
> {code:sql}
> insert into dwh.test_my_stream select t1.* from views.v t1 join (call views.pr()) t2 on t1.a=t2.a
> where
> t1.a >= (select max(t1.a) from views.v t1 join (call views.pr()) t2 on t1.a=t2.a)
> and
> t1.a1 >= (select min(t1.a1) from views.v t1 left join (call views.pr()) t2 on t1.a=t2.a) limit 10000 ;;
> {code}
> teiid server throws out the following stacktrace:
> {code:noformat}
> 2018-09-03 18:56:47,795 WARN [org.teiid.PROCESSOR] (Worker4_QueryProcessorQueue32) AQhJm01u0x7c TEIID30020 Processing exception for request AQhJm01u0x7c.13 'TEIID30312 Unable to eva
> luate right expression of o__3.a >= (SELECT MAX(t1.a) FROM views.v AS t1 INNER JOIN (EXEC views.pr()) AS t2 ON t1.a = convert(t2.a, integer))'. Originally ExpressionEvaluationExcepti
> on 'netezza.max.stmt.handles' QueryExecutor.java:444. Enable more detailed logging to see the entire stacktrace.
> 2018-09-03 18:57:03,963 WARN [org.teiid.CONNECTOR] (Worker4_QueryProcessorQueue35) AQhJm01u0x7c Connector worker process failed for atomic-request=AQhJm01u0x7c.14.5.47: org.teiid.tr
> anslator.jdbc.JDBCExecutionException: 11403 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT 1 AS c_0 FROM "TEST"."ADMIN"."TEST_STREAM" AS g_0 WHE
> RE g_0."RULE_ID" = 1 LIMIT 1]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:127)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:382)
> at sun.reflect.GeneratedMethodAccessor101.invoke(Unknown Source)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:229)
> at com.sun.proxy.$Proxy36.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
> at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:138)
> at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:401)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector$BatchProducerTupleSource.nextTuple(BatchCollector.java:90)
> at org.teiid.query.processor.relational.GroupingNode.groupPhase(GroupingNode.java:593)
> at org.teiid.query.processor.relational.GroupingNode.nextBatchDirect(GroupingNode.java:389)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
> at org.teiid.query.processor.relational.SubqueryAwareEvaluator.evaluateSubquery(SubqueryAwareEvaluator.java:354)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:1323)
> at org.teiid.query.eval.Evaluator.internalEvaluate(Evaluator.java:763)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:707)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:344)
> at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:185)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:297)
> at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:181)
> at org.teiid.query.eval.Evaluator.evaluateTVL(Evaluator.java:174)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:168)
> at org.teiid.query.processor.relational.SelectNode.nextBatchDirect(SelectNode.java:118)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
> at org.teiid.query.processor.relational.SourceState.getTupleBuffer(SourceState.java:244)
> at org.teiid.query.processor.relational.EnhancedSortMergeJoinStrategy.loadLeft(EnhancedSortMergeJoinStrategy.java:227)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:228)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:200)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.ProjectIntoNode.nextBatchDirect(ProjectIntoNode.java:140)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
> at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:492)
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:362)
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:47)
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:285)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: org.netezza.error.NzSQLException: netezza.max.stmt.handles
> at org.netezza.internal.QueryExecutor.sendQuery(QueryExecutor.java:444)
> at org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:73)
> at org.netezza.sql.NzConnection.execute(NzConnection.java:2904)
> at org.netezza.sql.NzPreparedStatament._execute(NzPreparedStatament.java:1163)
> at org.netezza.sql.NzPreparedStatament.prepare(NzPreparedStatament.java:1180)
> at org.netezza.sql.NzPreparedStatament.<init>(NzPreparedStatament.java:96)
> at org.netezza.sql.NzConnection.prepareStatement(NzConnection.java:1624)
> at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:757)
> at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:743)
> at org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:454)
> at org.teiid.translator.jdbc.JDBCBaseExecution.getPreparedStatement(JDBCBaseExecution.java:192)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:117)
> ... 89 more
> {code}
> which is (according to https://www-01.ibm.com/support/docview.wss?uid=swg21570296 link) the following:
> "When using the JDBC driver, one connection instance can only execute a single query at a time. This means that if one query is already executing on a specific connection instance, no other queries can be sent for parallel execution. If you run multiple queries using the same connection instance, you might encounter an error similar to the following:
> Error[1]: Failed retrieving bean metadata.netezza.max.stmt.handles"
> Another interesting thing is that running pretty the same query but not like insert, just usual select:
> {code:sql}
> select t1.* from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer)
> where
> t1.a >= (select max(t1.a) from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer))
> and
> t1.a1 >= (select min(t1.a1) from views.v t1 left join (call views.pr()) t2 on t1.a=cast(t2.a as integer)) limit 10000 ;;
> {code}
> will work without any errors.
> Actually we should introduce a dynamic way to allow for thread bound to be turned on based upon a transactional request.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 6 months
- 1
- 0
[JBoss JIRA] (TEIID-5462) Netezza error: NzSQLException-netezza.max.stmt.handles
by dalex dalex (JIRA)
[ https://issues.jboss.org/browse/TEIID-5462?page=com.atlassian.jira.plugin... ]
dalex dalex updated TEIID-5462:
-------------------------------
Issue Type: Enhancement (was: Bug)
Description:
Running the following query:
{code:sql}
insert into dwh.test_my_stream select t1.* from views.v t1 join (call views.pr()) t2 on t1.a=t2.a
where
t1.a >= (select max(t1.a) from views.v t1 join (call views.pr()) t2 on t1.a=t2.a)
and
t1.a1 >= (select min(t1.a1) from views.v t1 left join (call views.pr()) t2 on t1.a=t2.a) limit 10000 ;;
{code}
teiid server throws out the following stacktrace:
{code:noformat}
2018-09-03 18:56:47,795 WARN [org.teiid.PROCESSOR] (Worker4_QueryProcessorQueue32) AQhJm01u0x7c TEIID30020 Processing exception for request AQhJm01u0x7c.13 'TEIID30312 Unable to eva
luate right expression of o__3.a >= (SELECT MAX(t1.a) FROM views.v AS t1 INNER JOIN (EXEC views.pr()) AS t2 ON t1.a = convert(t2.a, integer))'. Originally ExpressionEvaluationExcepti
on 'netezza.max.stmt.handles' QueryExecutor.java:444. Enable more detailed logging to see the entire stacktrace.
2018-09-03 18:57:03,963 WARN [org.teiid.CONNECTOR] (Worker4_QueryProcessorQueue35) AQhJm01u0x7c Connector worker process failed for atomic-request=AQhJm01u0x7c.14.5.47: org.teiid.tr
anslator.jdbc.JDBCExecutionException: 11403 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT 1 AS c_0 FROM "TEST"."ADMIN"."TEST_STREAM" AS g_0 WHE
RE g_0."RULE_ID" = 1 LIMIT 1]
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:127)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:382)
at sun.reflect.GeneratedMethodAccessor101.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:229)
at com.sun.proxy.$Proxy36.execute(Unknown Source)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:138)
at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:401)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.BatchCollector$BatchProducerTupleSource.nextTuple(BatchCollector.java:90)
at org.teiid.query.processor.relational.GroupingNode.groupPhase(GroupingNode.java:593)
at org.teiid.query.processor.relational.GroupingNode.nextBatchDirect(GroupingNode.java:389)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
at org.teiid.query.processor.relational.SubqueryAwareEvaluator.evaluateSubquery(SubqueryAwareEvaluator.java:354)
at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:1323)
at org.teiid.query.eval.Evaluator.internalEvaluate(Evaluator.java:763)
at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:707)
at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:344)
at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:185)
at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:297)
at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:181)
at org.teiid.query.eval.Evaluator.evaluateTVL(Evaluator.java:174)
at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:168)
at org.teiid.query.processor.relational.SelectNode.nextBatchDirect(SelectNode.java:118)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
at org.teiid.query.processor.relational.SourceState.getTupleBuffer(SourceState.java:244)
at org.teiid.query.processor.relational.EnhancedSortMergeJoinStrategy.loadLeft(EnhancedSortMergeJoinStrategy.java:227)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:228)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:200)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.ProjectIntoNode.nextBatchDirect(ProjectIntoNode.java:140)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:492)
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:362)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:47)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:285)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.netezza.error.NzSQLException: netezza.max.stmt.handles
at org.netezza.internal.QueryExecutor.sendQuery(QueryExecutor.java:444)
at org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:73)
at org.netezza.sql.NzConnection.execute(NzConnection.java:2904)
at org.netezza.sql.NzPreparedStatament._execute(NzPreparedStatament.java:1163)
at org.netezza.sql.NzPreparedStatament.prepare(NzPreparedStatament.java:1180)
at org.netezza.sql.NzPreparedStatament.<init>(NzPreparedStatament.java:96)
at org.netezza.sql.NzConnection.prepareStatement(NzConnection.java:1624)
at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:757)
at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:743)
at org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:454)
at org.teiid.translator.jdbc.JDBCBaseExecution.getPreparedStatement(JDBCBaseExecution.java:192)
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:117)
... 89 more
{code}
which is (according to https://www-01.ibm.com/support/docview.wss?uid=swg21570296 link) the following:
"When using the JDBC driver, one connection instance can only execute a single query at a time. This means that if one query is already executing on a specific connection instance, no other queries can be sent for parallel execution. If you run multiple queries using the same connection instance, you might encounter an error similar to the following:
Error[1]: Failed retrieving bean metadata.netezza.max.stmt.handles"
Another interesting thing is that running pretty the same query but not like insert, just usual select:
{code:sql}
select t1.* from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer)
where
t1.a >= (select max(t1.a) from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer))
and
t1.a1 >= (select min(t1.a1) from views.v t1 left join (call views.pr()) t2 on t1.a=cast(t2.a as integer)) limit 10000 ;;
{code}
will work without any errors.
Actually we should introduce a dynamic way to allow for thread bound to be turned on based upon a transactional request.
was:
Running the following query:
{code:sql}
insert into dwh.test_my_stream select t1.* from views.v t1 join (call views.pr()) t2 on t1.a=t2.a
where
t1.a >= (select max(t1.a) from views.v t1 join (call views.pr()) t2 on t1.a=t2.a)
and
t1.a1 >= (select min(t1.a1) from views.v t1 left join (call views.pr()) t2 on t1.a=t2.a) limit 10000 ;;
{code}
teiid server throws out the following stacktrace:
{code:noformat}
2018-09-03 18:56:47,795 WARN [org.teiid.PROCESSOR] (Worker4_QueryProcessorQueue32) AQhJm01u0x7c TEIID30020 Processing exception for request AQhJm01u0x7c.13 'TEIID30312 Unable to eva
luate right expression of o__3.a >= (SELECT MAX(t1.a) FROM views.v AS t1 INNER JOIN (EXEC views.pr()) AS t2 ON t1.a = convert(t2.a, integer))'. Originally ExpressionEvaluationExcepti
on 'netezza.max.stmt.handles' QueryExecutor.java:444. Enable more detailed logging to see the entire stacktrace.
2018-09-03 18:57:03,963 WARN [org.teiid.CONNECTOR] (Worker4_QueryProcessorQueue35) AQhJm01u0x7c Connector worker process failed for atomic-request=AQhJm01u0x7c.14.5.47: org.teiid.tr
anslator.jdbc.JDBCExecutionException: 11403 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT 1 AS c_0 FROM "TEST"."ADMIN"."TEST_STREAM" AS g_0 WHE
RE g_0."RULE_ID" = 1 LIMIT 1]
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:127)
at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:382)
at sun.reflect.GeneratedMethodAccessor101.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:229)
at com.sun.proxy.$Proxy36.execute(Unknown Source)
at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:138)
at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:401)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.BatchCollector$BatchProducerTupleSource.nextTuple(BatchCollector.java:90)
at org.teiid.query.processor.relational.GroupingNode.groupPhase(GroupingNode.java:593)
at org.teiid.query.processor.relational.GroupingNode.nextBatchDirect(GroupingNode.java:389)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
at org.teiid.query.processor.relational.SubqueryAwareEvaluator.evaluateSubquery(SubqueryAwareEvaluator.java:354)
at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:1323)
at org.teiid.query.eval.Evaluator.internalEvaluate(Evaluator.java:763)
at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:707)
at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:344)
at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:185)
at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:297)
at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:181)
at org.teiid.query.eval.Evaluator.evaluateTVL(Evaluator.java:174)
at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:168)
at org.teiid.query.processor.relational.SelectNode.nextBatchDirect(SelectNode.java:118)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
at org.teiid.query.processor.relational.SourceState.getTupleBuffer(SourceState.java:244)
at org.teiid.query.processor.relational.EnhancedSortMergeJoinStrategy.loadLeft(EnhancedSortMergeJoinStrategy.java:227)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:228)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:200)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.ProjectIntoNode.nextBatchDirect(ProjectIntoNode.java:140)
at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:492)
at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:362)
at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:47)
at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:285)
at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.netezza.error.NzSQLException: netezza.max.stmt.handles
at org.netezza.internal.QueryExecutor.sendQuery(QueryExecutor.java:444)
at org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:73)
at org.netezza.sql.NzConnection.execute(NzConnection.java:2904)
at org.netezza.sql.NzPreparedStatament._execute(NzPreparedStatament.java:1163)
at org.netezza.sql.NzPreparedStatament.prepare(NzPreparedStatament.java:1180)
at org.netezza.sql.NzPreparedStatament.<init>(NzPreparedStatament.java:96)
at org.netezza.sql.NzConnection.prepareStatement(NzConnection.java:1624)
at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:757)
at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:743)
at org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:454)
at org.teiid.translator.jdbc.JDBCBaseExecution.getPreparedStatement(JDBCBaseExecution.java:192)
at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:117)
... 89 more
{code}
which is (according to https://www-01.ibm.com/support/docview.wss?uid=swg21570296 link) the following:
"When using the JDBC driver, one connection instance can only execute a single query at a time. This means that if one query is already executing on a specific connection instance, no other queries can be sent for parallel execution. If you run multiple queries using the same connection instance, you might encounter an error similar to the following:
Error[1]: Failed retrieving bean metadata.netezza.max.stmt.handles"
Another interesting thing is that running pretty the same query but not like insert, just usual select:
{code:sql}
select t1.* from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer)
where
t1.a >= (select max(t1.a) from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer))
and
t1.a1 >= (select min(t1.a1) from views.v t1 left join (call views.pr()) t2 on t1.a=cast(t2.a as integer)) limit 10000 ;;
{code}
will work without any errors.
> Netezza error: NzSQLException-netezza.max.stmt.handles
> ------------------------------------------------------
>
> Key: TEIID-5462
> URL: https://issues.jboss.org/browse/TEIID-5462
> Project: Teiid
> Issue Type: Enhancement
> Components: Query Engine
> Affects Versions: 11.1
> Environment: teiid-11.1.0 (from 01.09.2018) on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final)
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Critical
>
> Running the following query:
> {code:sql}
> insert into dwh.test_my_stream select t1.* from views.v t1 join (call views.pr()) t2 on t1.a=t2.a
> where
> t1.a >= (select max(t1.a) from views.v t1 join (call views.pr()) t2 on t1.a=t2.a)
> and
> t1.a1 >= (select min(t1.a1) from views.v t1 left join (call views.pr()) t2 on t1.a=t2.a) limit 10000 ;;
> {code}
> teiid server throws out the following stacktrace:
> {code:noformat}
> 2018-09-03 18:56:47,795 WARN [org.teiid.PROCESSOR] (Worker4_QueryProcessorQueue32) AQhJm01u0x7c TEIID30020 Processing exception for request AQhJm01u0x7c.13 'TEIID30312 Unable to eva
> luate right expression of o__3.a >= (SELECT MAX(t1.a) FROM views.v AS t1 INNER JOIN (EXEC views.pr()) AS t2 ON t1.a = convert(t2.a, integer))'. Originally ExpressionEvaluationExcepti
> on 'netezza.max.stmt.handles' QueryExecutor.java:444. Enable more detailed logging to see the entire stacktrace.
> 2018-09-03 18:57:03,963 WARN [org.teiid.CONNECTOR] (Worker4_QueryProcessorQueue35) AQhJm01u0x7c Connector worker process failed for atomic-request=AQhJm01u0x7c.14.5.47: org.teiid.tr
> anslator.jdbc.JDBCExecutionException: 11403 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT 1 AS c_0 FROM "TEST"."ADMIN"."TEST_STREAM" AS g_0 WHE
> RE g_0."RULE_ID" = 1 LIMIT 1]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:127)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:382)
> at sun.reflect.GeneratedMethodAccessor101.invoke(Unknown Source)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:229)
> at com.sun.proxy.$Proxy36.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
> at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:138)
> at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:401)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector$BatchProducerTupleSource.nextTuple(BatchCollector.java:90)
> at org.teiid.query.processor.relational.GroupingNode.groupPhase(GroupingNode.java:593)
> at org.teiid.query.processor.relational.GroupingNode.nextBatchDirect(GroupingNode.java:389)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
> at org.teiid.query.processor.relational.SubqueryAwareEvaluator.evaluateSubquery(SubqueryAwareEvaluator.java:354)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:1323)
> at org.teiid.query.eval.Evaluator.internalEvaluate(Evaluator.java:763)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:707)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:344)
> at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:185)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:297)
> at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:181)
> at org.teiid.query.eval.Evaluator.evaluateTVL(Evaluator.java:174)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:168)
> at org.teiid.query.processor.relational.SelectNode.nextBatchDirect(SelectNode.java:118)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
> at org.teiid.query.processor.relational.SourceState.getTupleBuffer(SourceState.java:244)
> at org.teiid.query.processor.relational.EnhancedSortMergeJoinStrategy.loadLeft(EnhancedSortMergeJoinStrategy.java:227)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:228)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:200)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.ProjectIntoNode.nextBatchDirect(ProjectIntoNode.java:140)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
> at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:492)
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:362)
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:47)
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:285)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: org.netezza.error.NzSQLException: netezza.max.stmt.handles
> at org.netezza.internal.QueryExecutor.sendQuery(QueryExecutor.java:444)
> at org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:73)
> at org.netezza.sql.NzConnection.execute(NzConnection.java:2904)
> at org.netezza.sql.NzPreparedStatament._execute(NzPreparedStatament.java:1163)
> at org.netezza.sql.NzPreparedStatament.prepare(NzPreparedStatament.java:1180)
> at org.netezza.sql.NzPreparedStatament.<init>(NzPreparedStatament.java:96)
> at org.netezza.sql.NzConnection.prepareStatement(NzConnection.java:1624)
> at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:757)
> at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:743)
> at org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:454)
> at org.teiid.translator.jdbc.JDBCBaseExecution.getPreparedStatement(JDBCBaseExecution.java:192)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:117)
> ... 89 more
> {code}
> which is (according to https://www-01.ibm.com/support/docview.wss?uid=swg21570296 link) the following:
> "When using the JDBC driver, one connection instance can only execute a single query at a time. This means that if one query is already executing on a specific connection instance, no other queries can be sent for parallel execution. If you run multiple queries using the same connection instance, you might encounter an error similar to the following:
> Error[1]: Failed retrieving bean metadata.netezza.max.stmt.handles"
> Another interesting thing is that running pretty the same query but not like insert, just usual select:
> {code:sql}
> select t1.* from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer)
> where
> t1.a >= (select max(t1.a) from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer))
> and
> t1.a1 >= (select min(t1.a1) from views.v t1 left join (call views.pr()) t2 on t1.a=cast(t2.a as integer)) limit 10000 ;;
> {code}
> will work without any errors.
> Actually we should introduce a dynamic way to allow for thread bound to be turned on based upon a transactional request.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 6 months
- 1
- 0
[JBoss JIRA] (TEIID-5462) Netezza error: NzSQLException-netezza.max.stmt.handles
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5462?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5462:
---------------------------------------
Yes, you could change this issue to an enhancement to allow for thread bound to be turned on based upon a transactional request.
> Netezza error: NzSQLException-netezza.max.stmt.handles
> ------------------------------------------------------
>
> Key: TEIID-5462
> URL: https://issues.jboss.org/browse/TEIID-5462
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 11.1
> Environment: teiid-11.1.0 (from 01.09.2018) on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final)
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Critical
>
> Running the following query:
> {code:sql}
> insert into dwh.test_my_stream select t1.* from views.v t1 join (call views.pr()) t2 on t1.a=t2.a
> where
> t1.a >= (select max(t1.a) from views.v t1 join (call views.pr()) t2 on t1.a=t2.a)
> and
> t1.a1 >= (select min(t1.a1) from views.v t1 left join (call views.pr()) t2 on t1.a=t2.a) limit 10000 ;;
> {code}
> teiid server throws out the following stacktrace:
> {code:noformat}
> 2018-09-03 18:56:47,795 WARN [org.teiid.PROCESSOR] (Worker4_QueryProcessorQueue32) AQhJm01u0x7c TEIID30020 Processing exception for request AQhJm01u0x7c.13 'TEIID30312 Unable to eva
> luate right expression of o__3.a >= (SELECT MAX(t1.a) FROM views.v AS t1 INNER JOIN (EXEC views.pr()) AS t2 ON t1.a = convert(t2.a, integer))'. Originally ExpressionEvaluationExcepti
> on 'netezza.max.stmt.handles' QueryExecutor.java:444. Enable more detailed logging to see the entire stacktrace.
> 2018-09-03 18:57:03,963 WARN [org.teiid.CONNECTOR] (Worker4_QueryProcessorQueue35) AQhJm01u0x7c Connector worker process failed for atomic-request=AQhJm01u0x7c.14.5.47: org.teiid.tr
> anslator.jdbc.JDBCExecutionException: 11403 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT 1 AS c_0 FROM "TEST"."ADMIN"."TEST_STREAM" AS g_0 WHE
> RE g_0."RULE_ID" = 1 LIMIT 1]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:127)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:382)
> at sun.reflect.GeneratedMethodAccessor101.invoke(Unknown Source)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:229)
> at com.sun.proxy.$Proxy36.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
> at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:138)
> at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:401)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector$BatchProducerTupleSource.nextTuple(BatchCollector.java:90)
> at org.teiid.query.processor.relational.GroupingNode.groupPhase(GroupingNode.java:593)
> at org.teiid.query.processor.relational.GroupingNode.nextBatchDirect(GroupingNode.java:389)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
> at org.teiid.query.processor.relational.SubqueryAwareEvaluator.evaluateSubquery(SubqueryAwareEvaluator.java:354)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:1323)
> at org.teiid.query.eval.Evaluator.internalEvaluate(Evaluator.java:763)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:707)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:344)
> at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:185)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:297)
> at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:181)
> at org.teiid.query.eval.Evaluator.evaluateTVL(Evaluator.java:174)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:168)
> at org.teiid.query.processor.relational.SelectNode.nextBatchDirect(SelectNode.java:118)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
> at org.teiid.query.processor.relational.SourceState.getTupleBuffer(SourceState.java:244)
> at org.teiid.query.processor.relational.EnhancedSortMergeJoinStrategy.loadLeft(EnhancedSortMergeJoinStrategy.java:227)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:228)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:200)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.ProjectIntoNode.nextBatchDirect(ProjectIntoNode.java:140)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
> at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:492)
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:362)
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:47)
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:285)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: org.netezza.error.NzSQLException: netezza.max.stmt.handles
> at org.netezza.internal.QueryExecutor.sendQuery(QueryExecutor.java:444)
> at org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:73)
> at org.netezza.sql.NzConnection.execute(NzConnection.java:2904)
> at org.netezza.sql.NzPreparedStatament._execute(NzPreparedStatament.java:1163)
> at org.netezza.sql.NzPreparedStatament.prepare(NzPreparedStatament.java:1180)
> at org.netezza.sql.NzPreparedStatament.<init>(NzPreparedStatament.java:96)
> at org.netezza.sql.NzConnection.prepareStatement(NzConnection.java:1624)
> at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:757)
> at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:743)
> at org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:454)
> at org.teiid.translator.jdbc.JDBCBaseExecution.getPreparedStatement(JDBCBaseExecution.java:192)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:117)
> ... 89 more
> {code}
> which is (according to https://www-01.ibm.com/support/docview.wss?uid=swg21570296 link) the following:
> "When using the JDBC driver, one connection instance can only execute a single query at a time. This means that if one query is already executing on a specific connection instance, no other queries can be sent for parallel execution. If you run multiple queries using the same connection instance, you might encounter an error similar to the following:
> Error[1]: Failed retrieving bean metadata.netezza.max.stmt.handles"
> Another interesting thing is that running pretty the same query but not like insert, just usual select:
> {code:sql}
> select t1.* from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer)
> where
> t1.a >= (select max(t1.a) from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer))
> and
> t1.a1 >= (select min(t1.a1) from views.v t1 left join (call views.pr()) t2 on t1.a=cast(t2.a as integer)) limit 10000 ;;
> {code}
> will work without any errors.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 6 months
- 1
- 0
[JBoss JIRA] (TEIID-5462) Netezza error: NzSQLException-netezza.max.stmt.handles
by dalex dalex (JIRA)
[ https://issues.jboss.org/browse/TEIID-5462?page=com.atlassian.jira.plugin... ]
dalex dalex commented on TEIID-5462:
------------------------------------
[~shawkins]
> deally it would be dynamic based upon the usage scenario - if transactional and a possibility that the connection could be reused, then make it thread bound.
It means that you're planning to introduce the dynamic behavior, correct (in scope of the issue)?
> Netezza error: NzSQLException-netezza.max.stmt.handles
> ------------------------------------------------------
>
> Key: TEIID-5462
> URL: https://issues.jboss.org/browse/TEIID-5462
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 11.1
> Environment: teiid-11.1.0 (from 01.09.2018) on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final)
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Critical
>
> Running the following query:
> {code:sql}
> insert into dwh.test_my_stream select t1.* from views.v t1 join (call views.pr()) t2 on t1.a=t2.a
> where
> t1.a >= (select max(t1.a) from views.v t1 join (call views.pr()) t2 on t1.a=t2.a)
> and
> t1.a1 >= (select min(t1.a1) from views.v t1 left join (call views.pr()) t2 on t1.a=t2.a) limit 10000 ;;
> {code}
> teiid server throws out the following stacktrace:
> {code:noformat}
> 2018-09-03 18:56:47,795 WARN [org.teiid.PROCESSOR] (Worker4_QueryProcessorQueue32) AQhJm01u0x7c TEIID30020 Processing exception for request AQhJm01u0x7c.13 'TEIID30312 Unable to eva
> luate right expression of o__3.a >= (SELECT MAX(t1.a) FROM views.v AS t1 INNER JOIN (EXEC views.pr()) AS t2 ON t1.a = convert(t2.a, integer))'. Originally ExpressionEvaluationExcepti
> on 'netezza.max.stmt.handles' QueryExecutor.java:444. Enable more detailed logging to see the entire stacktrace.
> 2018-09-03 18:57:03,963 WARN [org.teiid.CONNECTOR] (Worker4_QueryProcessorQueue35) AQhJm01u0x7c Connector worker process failed for atomic-request=AQhJm01u0x7c.14.5.47: org.teiid.tr
> anslator.jdbc.JDBCExecutionException: 11403 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT 1 AS c_0 FROM "TEST"."ADMIN"."TEST_STREAM" AS g_0 WHE
> RE g_0."RULE_ID" = 1 LIMIT 1]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:127)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:382)
> at sun.reflect.GeneratedMethodAccessor101.invoke(Unknown Source)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:229)
> at com.sun.proxy.$Proxy36.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
> at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:138)
> at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:401)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector$BatchProducerTupleSource.nextTuple(BatchCollector.java:90)
> at org.teiid.query.processor.relational.GroupingNode.groupPhase(GroupingNode.java:593)
> at org.teiid.query.processor.relational.GroupingNode.nextBatchDirect(GroupingNode.java:389)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
> at org.teiid.query.processor.relational.SubqueryAwareEvaluator.evaluateSubquery(SubqueryAwareEvaluator.java:354)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:1323)
> at org.teiid.query.eval.Evaluator.internalEvaluate(Evaluator.java:763)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:707)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:344)
> at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:185)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:297)
> at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:181)
> at org.teiid.query.eval.Evaluator.evaluateTVL(Evaluator.java:174)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:168)
> at org.teiid.query.processor.relational.SelectNode.nextBatchDirect(SelectNode.java:118)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
> at org.teiid.query.processor.relational.SourceState.getTupleBuffer(SourceState.java:244)
> at org.teiid.query.processor.relational.EnhancedSortMergeJoinStrategy.loadLeft(EnhancedSortMergeJoinStrategy.java:227)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:228)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:200)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.ProjectIntoNode.nextBatchDirect(ProjectIntoNode.java:140)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
> at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:492)
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:362)
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:47)
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:285)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: org.netezza.error.NzSQLException: netezza.max.stmt.handles
> at org.netezza.internal.QueryExecutor.sendQuery(QueryExecutor.java:444)
> at org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:73)
> at org.netezza.sql.NzConnection.execute(NzConnection.java:2904)
> at org.netezza.sql.NzPreparedStatament._execute(NzPreparedStatament.java:1163)
> at org.netezza.sql.NzPreparedStatament.prepare(NzPreparedStatament.java:1180)
> at org.netezza.sql.NzPreparedStatament.<init>(NzPreparedStatament.java:96)
> at org.netezza.sql.NzConnection.prepareStatement(NzConnection.java:1624)
> at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:757)
> at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:743)
> at org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:454)
> at org.teiid.translator.jdbc.JDBCBaseExecution.getPreparedStatement(JDBCBaseExecution.java:192)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:117)
> ... 89 more
> {code}
> which is (according to https://www-01.ibm.com/support/docview.wss?uid=swg21570296 link) the following:
> "When using the JDBC driver, one connection instance can only execute a single query at a time. This means that if one query is already executing on a specific connection instance, no other queries can be sent for parallel execution. If you run multiple queries using the same connection instance, you might encounter an error similar to the following:
> Error[1]: Failed retrieving bean metadata.netezza.max.stmt.handles"
> Another interesting thing is that running pretty the same query but not like insert, just usual select:
> {code:sql}
> select t1.* from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer)
> where
> t1.a >= (select max(t1.a) from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer))
> and
> t1.a1 >= (select min(t1.a1) from views.v t1 left join (call views.pr()) t2 on t1.a=cast(t2.a as integer)) limit 10000 ;;
> {code}
> will work without any errors.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 6 months
- 1
- 0
[JBoss JIRA] (TEIID-5462) Netezza error: NzSQLException-netezza.max.stmt.handles
by Steven Hawkins (JIRA)
[ https://issues.jboss.org/browse/TEIID-5462?page=com.atlassian.jira.plugin... ]
Steven Hawkins commented on TEIID-5462:
---------------------------------------
I don't like it as a default since in non-transactional processing it leads to greater resource consumption.
Ideally it would be dynamic based upon the usage scenario - if transactional and a possibility that the connection could be reused, then make it thread bound.
> Netezza error: NzSQLException-netezza.max.stmt.handles
> ------------------------------------------------------
>
> Key: TEIID-5462
> URL: https://issues.jboss.org/browse/TEIID-5462
> Project: Teiid
> Issue Type: Bug
> Components: Query Engine
> Affects Versions: 11.1
> Environment: teiid-11.1.0 (from 01.09.2018) on WildFly Full 11.0.0.Final (WildFly Core 3.0.8.Final)
> Reporter: dalex dalex
> Assignee: Steven Hawkins
> Priority: Critical
>
> Running the following query:
> {code:sql}
> insert into dwh.test_my_stream select t1.* from views.v t1 join (call views.pr()) t2 on t1.a=t2.a
> where
> t1.a >= (select max(t1.a) from views.v t1 join (call views.pr()) t2 on t1.a=t2.a)
> and
> t1.a1 >= (select min(t1.a1) from views.v t1 left join (call views.pr()) t2 on t1.a=t2.a) limit 10000 ;;
> {code}
> teiid server throws out the following stacktrace:
> {code:noformat}
> 2018-09-03 18:56:47,795 WARN [org.teiid.PROCESSOR] (Worker4_QueryProcessorQueue32) AQhJm01u0x7c TEIID30020 Processing exception for request AQhJm01u0x7c.13 'TEIID30312 Unable to eva
> luate right expression of o__3.a >= (SELECT MAX(t1.a) FROM views.v AS t1 INNER JOIN (EXEC views.pr()) AS t2 ON t1.a = convert(t2.a, integer))'. Originally ExpressionEvaluationExcepti
> on 'netezza.max.stmt.handles' QueryExecutor.java:444. Enable more detailed logging to see the entire stacktrace.
> 2018-09-03 18:57:03,963 WARN [org.teiid.CONNECTOR] (Worker4_QueryProcessorQueue35) AQhJm01u0x7c Connector worker process failed for atomic-request=AQhJm01u0x7c.14.5.47: org.teiid.tr
> anslator.jdbc.JDBCExecutionException: 11403 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT 1 AS c_0 FROM "TEST"."ADMIN"."TEST_STREAM" AS g_0 WHE
> RE g_0."RULE_ID" = 1 LIMIT 1]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:127)
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:382)
> at sun.reflect.GeneratedMethodAccessor101.invoke(Unknown Source)
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:229)
> at com.sun.proxy.$Proxy36.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:302)
> at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:138)
> at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:401)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector$BatchProducerTupleSource.nextTuple(BatchCollector.java:90)
> at org.teiid.query.processor.relational.GroupingNode.groupPhase(GroupingNode.java:593)
> at org.teiid.query.processor.relational.GroupingNode.nextBatchDirect(GroupingNode.java:389)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
> at org.teiid.query.processor.relational.SubqueryAwareEvaluator.evaluateSubquery(SubqueryAwareEvaluator.java:354)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:1323)
> at org.teiid.query.eval.Evaluator.internalEvaluate(Evaluator.java:763)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:707)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:344)
> at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:185)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:297)
> at org.teiid.query.eval.Evaluator.internalEvaluateTVL(Evaluator.java:181)
> at org.teiid.query.eval.Evaluator.evaluateTVL(Evaluator.java:174)
> at org.teiid.query.eval.Evaluator.evaluate(Evaluator.java:168)
> at org.teiid.query.processor.relational.SelectNode.nextBatchDirect(SelectNode.java:118)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
> at org.teiid.query.processor.relational.SourceState.getTupleBuffer(SourceState.java:244)
> at org.teiid.query.processor.relational.EnhancedSortMergeJoinStrategy.loadLeft(EnhancedSortMergeJoinStrategy.java:227)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:228)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:200)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.relational.SourceState.prefetch(SourceState.java:207)
> at org.teiid.query.processor.relational.SourceState.rowCountLE(SourceState.java:154)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirectInternal(JoinNode.java:252)
> at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:195)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.LimitNode.nextBatchDirect(LimitNode.java:98)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:146)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.ProjectIntoNode.nextBatchDirect(ProjectIntoNode.java:140)
> at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:277)
> at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:141)
> at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:148)
> at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:111)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:160)
> at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:142)
> at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:492)
> at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:362)
> at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:47)
> at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:285)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:277)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:115)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:206)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: org.netezza.error.NzSQLException: netezza.max.stmt.handles
> at org.netezza.internal.QueryExecutor.sendQuery(QueryExecutor.java:444)
> at org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:73)
> at org.netezza.sql.NzConnection.execute(NzConnection.java:2904)
> at org.netezza.sql.NzPreparedStatament._execute(NzPreparedStatament.java:1163)
> at org.netezza.sql.NzPreparedStatament.prepare(NzPreparedStatament.java:1180)
> at org.netezza.sql.NzPreparedStatament.<init>(NzPreparedStatament.java:96)
> at org.netezza.sql.NzConnection.prepareStatement(NzConnection.java:1624)
> at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.doPrepareStatement(BaseWrapperManagedConnection.java:757)
> at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.prepareStatement(BaseWrapperManagedConnection.java:743)
> at org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:454)
> at org.teiid.translator.jdbc.JDBCBaseExecution.getPreparedStatement(JDBCBaseExecution.java:192)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:117)
> ... 89 more
> {code}
> which is (according to https://www-01.ibm.com/support/docview.wss?uid=swg21570296 link) the following:
> "When using the JDBC driver, one connection instance can only execute a single query at a time. This means that if one query is already executing on a specific connection instance, no other queries can be sent for parallel execution. If you run multiple queries using the same connection instance, you might encounter an error similar to the following:
> Error[1]: Failed retrieving bean metadata.netezza.max.stmt.handles"
> Another interesting thing is that running pretty the same query but not like insert, just usual select:
> {code:sql}
> select t1.* from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer)
> where
> t1.a >= (select max(t1.a) from views.v t1 join (call views.pr()) t2 on t1.a=cast(t2.a as integer))
> and
> t1.a1 >= (select min(t1.a1) from views.v t1 left join (call views.pr()) t2 on t1.a=cast(t2.a as integer)) limit 10000 ;;
> {code}
> will work without any errors.
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
6 years, 6 months
- 1
- 0