> We never see the first request execute Execution.close() nor ExecutionFactory.closeConnection()
Is the user query still open and does your source return lobs? If that's the case then by default we assume that the connection must be held so that lob references are readable. The possible solutions here are to have the ExecutionFactory return true for areLobsUsableAfterClose or set the copyLobs property to true (but that's only advisable with small lobs).
> I'm guessing that Teiid doesn't call Execution.close() nor ExecutionFactory.closeConnection() until the end of processing the plan
No that is not the case. For non-lob scenarios Teiid will try to close the result as soon as possible, which is typically just after the last result is read - see DataTierTupleSource.closeSource(). There are some scenarios however where we attempt to balance the amount of data that we need to buffer in the Teiid engine, for example with already sorted input values for a sort merge join. So there slower sources may dictate how much we read at a time, which will delay when you get to the end of the results.
> I think I saw some changes in 8.3 related to connection management. Is the behavior changed in 8.3?
No there wasn't anything generally changed around connection management. There was proactive buffering added in select join scenarios, which can cause a close to happen sooner and there was cleanup around DataNotAvailable being thrown from the reset method.
If you have a scenario not covered above where close of a source is not getting called after the last row, or not getting called at all even at the end of the user query, then let us know.
We only have VARCHAR(4000) and Integers, no XML, CLOB or BLOB types are surfaced from this translator. Other translators within the user query may return CLOBs. We've enabled trace logging for Teiid and I am attaching the log file. Below is my attempt at pulling out the relevant bits:
Line 41196: 29 Mar 2013 16:42:01,641 GMT DEBUG [org.teiid.CONNECTOR] (Worker22_QueryProcessorQueue2105) WWNueQuLaCC18.104.22.1680 Processing NEW request: SELECT GSV.SYSTEMS.Sysplex, GSV.SYSTEMS."System" FROM GSV.SYSTEMS
Line 41200: 29 Mar 2013 16:42:01,657 GMT DEBUG [com.ca.chorus.teiid.translator.sysview.SysviewExecutionFactory] (Worker22_QueryProcessorQueue2105) getConnection() CF class type = class com.ca.chorus.teiid.connector.sysview.SysviewConnectionFactory
Line 41460: 29 Mar 2013 16:42:03,268 GMT DEBUG [org.teiid.CONNECTOR] (Worker22_QueryProcessorQueue2105) WWNueQuLaCC22.214.171.1240 Obtained last batch, total row count: 2
After line 41460, I see no further reference to the request and, unlike other requests, I see no attempt to close the connection. The other request is blocking:
Line 41195: 29 Mar 2013 16:42:01,639 GMT DEBUG [org.teiid.CONNECTOR] (http--0_0_0_0_0_0_0_0-31304-4) WWNueQuLaCC126.96.36.1991 Create State
Line 41204: 29 Mar 2013 16:42:01,664 GMT DEBUG [org.teiid.CONNECTOR] (Worker15_QueryProcessorQueue2106) WWNueQuLaCC188.8.131.521 Processing NEW request: ...
Line 41264: 29 Mar 2013 16:42:01,927 GMT DEBUG [org.teiid.CONNECTOR] (Worker15_QueryProcessorQueue2106) WWNueQuLaCC184.108.40.2061 Obtained execution
Line 41471: 29 Mar 2013 16:42:03,961 GMT DEBUG [com.ca.chorus.teiid.translator.sysview.SysviewResultSetExecution] (Worker15_QueryProcessorQueue2106) Connection not available for addma03
At this point, the translator throws DataNotAvailable(1000) from its execute() method. It blocks in a loop forever. Weirdly, I see no further reference to WWNueQuLaCC220.127.116.111 in the Teiid log but I'm not sure I should.
server.log.zip 1.0 MB
I forgot there is one more possiblity, does the translator call ExecutionFactory.keepExecutionAlive(true)? If not and there is nothing else exceptional with WWNueQuLaCC18.104.22.1680, then you should see the close as soon as the engine receives the results. However if the processing plan is not resumed (which would only happen if the processing queue were backed up and all the processing threads engaged) then the closure would be delayed. The datanotavailableexception may be an issue if the plan hits that node prior to causing the closure of the other source.
Is ExecutionFactory.keepExecutionAlive() new? I don't see it in 8.1.
> However if the processing plan is not resumed (which would only happen if the processing queue were backed up and all the processing threads engaged) then the closure would be delayed. The datanotavailableexception may be an issue if the plan hits that node prior to causing the closure of the other source.
I'm a little lost. Are you saying that if node A has a connection open and node B throws DNA, node B will keep the connection open until processing resumes?
> Is ExecutionFactory.keepExecutionAlive() new? I don't see it in 8.1.
Sorry I meant ExecutionContext.
> I'm a little lost. Are you saying that if node A has a connection open and node B throws DNA, node B will keep the connection open until processing resumes?
Depending upon the structure of the plan it may be possible for only the first node to be consulted. If that happens to be the one throwing DNA, then that could be an issue.