[teiid-issues] [JBoss JIRA] (TEIID-5462) Netezza error: NzSQLException-netezza.max.stmt.handles
dalex dalex (JIRA)
issues at jboss.org
Wed Sep 5 10:49:01 EDT 2018
[ https://issues.jboss.org/browse/TEIID-5462?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13629333#comment-13629333 ]
dalex dalex commented on TEIID-5462:
------------------------------------
[~shawkins] ok, thx, will wait for your solution then
> 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=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}
> 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)
More information about the teiid-issues
mailing list