3 Replies Latest reply on Feb 14, 2014 11:49 AM by H B

    Teiid creates a new connection for each execution in correlated nested table where the nested expression is a procedure call with return type as "blob".

    H B Newbie

      Teiid creates a new connection for each execution in correlated nested table where the nested expression is a procedure call with return type as "blob".
      It happened for both resource adapter and datasource.

       

      I have created a view as follows:

       

      <model name="TEST" type="VIRTUAL" visible = "true">
                <metadata type="DDL"><![CDATA[
         CREATE VIEW V1 (
           id string PRIMARY KEY,
           next_val blob 
         )
         AS SELECT t1.id, t2.*
          from t1, TABLE(call p1(t1.id))t2
              
        ]]> </metadata>
      </model>

       

      t1 and p1 are a table and a procedure (return type is blob)  in the source model.

      Connection pool's max size is configured as 5:
      <pool>
              <min-pool-size>1</min-pool-size>
              <max-pool-size>5</max-pool-size>
              <prefill>true</prefill>
              <use-strict-min>false</use-strict-min>
          </pool>

      Execute "select * from V1" will throw following exception if the return rows are more than 5:

      10:40:42,737 ERROR [stderr] (Worker0_QueryProcessorQueue7) org.teiid.translator.TranslatorException: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:/EDMDB/5000.1.10
      10:40:42,737 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:281)
      10:40:42,737 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at com.lgc.edm.teiid.translator.EdmSqlServerExecutionFactory.getConnection(EdmSqlServerExecutionFactory.java:90)
      10:40:42,737 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at com.lgc.edm.teiid.translator.EdmSqlServerExecutionFactory.getConnection(EdmSqlServerExecution
      Factory.java:38)
      10:40:42,752 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.teiid.translator.ExecutionFactory.getConnection(ExecutionFactory.java:190)
      10:40:42,752 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.teiid.translator.BaseDelegatingExecutionFactory.getConnection(BaseDelegatingExecutionFactory.java:116)
      10:40:42,752 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:218)
      10:40:42,768 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:446)
      10:40:42,768 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:159)
      10:40:42,768 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:156)
      10:40:42,768 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
      10:40:42,768 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at java.util.concurrent.FutureTask.run(FutureTask.java:138)
      10:40:42,783 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58)
      10:40:42,783 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:269)
      10:40:42,783 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
      10:40:42,783 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214)
      10:40:42,799 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
      10:40:42,799 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
      10:40:42,799 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at java.lang.Thread.run(Thread.java:662)
      10:40:42,799 ERROR [stderr] (Worker0_QueryProcessorQueue7) Caused by: java.sql.SQLException: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:/EDMDB/5000.1.10
      10:40:42,815 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:147)
      10:40:42,815 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:279)
      10:40:42,815 ERROR [stderr] (Worker0_QueryProcessorQueue7)      ... 17 more
      10:40:42,815 ERROR [stderr] (Worker0_QueryProcessorQueue7) Caused by: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:/EDMDB/5000.1.10
      10:40:42,830 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractC
      onnectionManager.java:390)
      10:40:42,830 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.getManagedConnection(TxConnec
      tionManagerImpl.java:368)
      10:40:42,846 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractCon
      nectionManager.java:464)
      10:40:42,846 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:139)
      10:40:42,846 ERROR [stderr] (Worker0_QueryProcessorQueue7)      ... 18 more
      10:40:42,846 ERROR [stderr] (Worker0_QueryProcessorQueue7) Caused by: javax.resource.ResourceException: IJ000655: No managed connections available within configured blocking timeout (30000 [ms])
      10:40:42,862 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.getConnection(SemaphoreArrayListManagedConnectionPool.java:384)
      10:40:42,862 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getSimpleConnection(AbstractPool.java:
      397)
      10:40:42,877 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getConnection(AbstractPool.java:365)
      10:40:42,877 ERROR [stderr] (Worker0_QueryProcessorQueue7)      at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:329)

       

      During the troubleshooting, I found that if I changed return type to "string"/"integer" in the procedure, same query was successful because Teiid did't not create a new connection for each execution of procedure.

       

      Is this a defect? We are using Teiid 8.4.0 final.

       

      Thank you for the help.