9 Replies Latest reply on Feb 10, 2015 1:27 PM by shawkins

    Reading CLOB data that is over 4000 characters

    jstrathern

      Hello Teiid Community,

       

      I am having issues trying to read a data that is over 4000 characters from a CLOB column exposed through a custom translator that very closely extends the Microsoft SQL Server translator.

       

      Through this site:

      https://docs.jboss.org/author/display/teiid89final/Large+Objects


      I see that Teiid will convert LOB's to streaming objects automatically for performance reasons. I can also see that one to do this, it is necessary  to set ExecutionContext.keepAlive(). For the time being, i'm looking into alternatives to keeping alive the context.


      The other route i noticed in documentation is to set the CopyLobs tab on the translator:


      Translator Properties - Teiid 8.10 (draft) - Project Documentation Editor


      However, in both cases (CopyLobs set and otherwise), I still get a streaming object back from the Teiid ResultSet. When i attempt to read from that, either a single character, or several at a time, i get a Teiid exception stating the connection was closed (I'm guessing due to the ExecutionContext not being kept alive). Reading other data types, or CLOBS under 4000 characters cause no issues, only this particular data type and size.


      Is there any other steps necessary to get CopyLobs to work?


      Thanks,

      Joe

        • 1. Re: Reading CLOB data that is over 4000 characters
          rareddy

          In your translator are you reading the column as String? String data type has 4000 character limit to avoid OOM issues. Check to see that is not the case.

          • 2. Re: Reading CLOB data that is over 4000 characters
            jstrathern

            Hello Ramesh,

             

            Thanks for the response.

             

            I can confirm that the column is not being read as String. It is set as a CLOB, and through java code I can actually see the result set object change from a simple CLOB, to a streaming object when the size increases over 40000 characters.

             

            Thanks,

            Joe

            • 3. Re: Reading CLOB data that is over 4000 characters
              shawkins

              > Is there any other steps necessary to get CopyLobs to work?

               

              How is the property set in the vdb.xml?

               

              > and through java code I can actually see the result set object change from a simple CLOB, to a streaming object when the size increases over 40000 characters.

               

              If copy lobs is set you should see the replacement of the source lob with a Teiid lob backed by disk in org.teiid.dqp.internal.datamgr.ConnectorWorkItem

              • 4. Re: Reading CLOB data that is over 4000 characters
                jstrathern

                Hello Steven,

                 

                The property is set in a -vdb.xml, with the tag indicated in documentation:

                <translator name="my-translator-override" type="my-translator">

                    <property name="CopyLobs" value="true" />

                </translator>

                 

                Debugging with my source code, I can see that this is properly setting the CopyLob flag on the object, and our custom translator should not be overriding any code that would make use of the flag. Similarily, if it se the same flag using the base sql server translator, I get the same results.

                 

                Instead of a connectorworkitem attached, I can only see a stream factory with

                 

                org.teiid.client.lob.StreamingLobChunckProducer$Factory

                 

                Thanks,

                Joe

                • 5. Re: Reading CLOB data that is over 4000 characters
                  shawkins

                  Even if CopyLobs is used, the values will still be streamed to the client.  It's just that the data will come from temporary Teiid storage, rather than from the live lob value. 

                   

                  Can you provide the exact client/server side errors you are seeing when you are reading the lob?

                   

                  Steve

                  • 6. Re: Reading CLOB data that is over 4000 characters
                    jstrathern

                    Certainly, below is the full stack trace I receive when trying to read, either through substring or in a stream that reads characters:


                    java.sql.SQLException: java.io.IOException: Unable to read data from the stream: org.teiid.core.TeiidProcessingException: TEIID30495 The request /uMIfP2QFBYD.4 has been closed.
                    at org.teiid.core.types.ClobImpl.getSubString(ClobImpl.java:171)
                    ...
                    at javax.swing.SwingWorker$1.call(SwingWorker.java:296)
                    at java.util.concurrent.FutureTask.run(FutureTask.java:262)
                    at javax.swing.SwingWorker.run(SwingWorker.java:335)
                    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
                    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
                    at java.lang.Thread.run(Thread.java:744)
                    Caused by: java.io.IOException: Unable to read data from the stream: org.teiid.core.TeiidProcessingException: TEIID30495 The request /uMIfP2QFBYD.4 has been closed.
                    at org.teiid.client.lob.StreamingLobChunckProducer.getNextChunk(StreamingLobChunckProducer.java:74)
                    at org.teiid.client.lob.LobChunkInputStream.read(LobChunkInputStream.java:58)
                    at java.io.InputStream.read(InputStream.java:170)
                    at java.nio.channels.Channels$ReadableByteChannelImpl.read(Channels.java:385)
                    at org.teiid.core.util.InputStreamReader.read(InputStreamReader.java:78)
                    at org.teiid.core.util.ObjectConverterUtil.write(ObjectConverterUtil.java:159)
                    at org.teiid.core.util.ObjectConverterUtil.convertToCharArray(ObjectConverterUtil.java:316)
                    at org.teiid.core.types.ClobImpl.getSubString(ClobImpl.java:166)
                    Caused by: java.util.concurrent.ExecutionException: org.teiid.core.TeiidProcessingException: TEIID30495 The request /uMIfP2QFBYD.4 has been closed.
                    at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.convertResult(SocketServerInstanceImpl.java:366)
                    at org.teiid.client.util.ResultsFuture.get(ResultsFuture.java:120)
                    at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:395)
                    at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:380)
                    at org.teiid.client.lob.StreamingLobChunckProducer.getNextChunk(StreamingLobChunckProducer.java:72)
                    Caused by: org.teiid.core.TeiidProcessingException: TEIID30495 The request /uMIfP2QFBYD.4 has been closed.
                    at org.teiid.dqp.internal.process.DQPCore.getRequestWorkItem(DQPCore.java:412)
                    at org.teiid.dqp.internal.process.DQPCore.requestNextLobChunk(DQPCore.java:403)
                    at org.teiid.logging.LogManager$LoggingProxy.invoke(LogManager.java:121)
                    at org.teiid.jboss.TransportService$2.invoke(TransportService.java:224)
                    at org.teiid.transport.ServerWorkItem.run(ServerWorkItem.java:87)
                    at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274)
                    at org.teiid.transport.SocketClientInstance.processMessagePacket(SocketClientInstance.java:207)
                    at org.teiid.transport.SocketClientInstance.receivedMessage(SocketClientInstance.java:193)
                    ...


                    Joe

                    • 7. Re: Reading CLOB data that is over 4000 characters
                      shawkins

                      It would appear that the Teiid resultset or statement has already been closed.  Unfortunately Teiid streaming lobs are not readable after the resultset/statement has been closed, since we are trying to be as proactive as possible with resource cleanup.

                      • 8. Re: Reading CLOB data that is over 4000 characters
                        jstrathern

                        Steven,

                         

                        Does this mean that even with CopyLobs enabled, since it is still a steaming object, ExecutionContext.keepAlive() is still required to stream from the temporary Teiid storage location, particularly if we are reading from a remote Teiid Server?

                         

                        Or is this not the case, and you simply feel that the Teiid ResultSet/Statement is closed when it should still be open in JDBC?

                         

                        Joe

                        • 9. Re: Reading CLOB data that is over 4000 characters
                          shawkins

                          > Does this mean that even with CopyLobs enabled, since it is still a steaming object, ExecutionContext.keepAlive() is still required to stream from the temporary Teiid storage location, particularly if we are reading from a remote Teiid Server?

                           

                          No keep alive is not needed with copy lobs enabled.  With copy lobs enabled, we are free to close the source resultset/statement and allow the connection to return to the pool (which may or may not leave it open).

                           

                          > Or is this not the case, and you simply feel that the Teiid ResultSet/Statement is closed when it should still be open in JDBC?

                           

                          To minimize the number of live lob objects held in memory and/or the amount of temporary space held, we proactively cleanup when a Teiid statement/resultset is closed so that any non-inline lob become unusable.  Teiid lob streaming is used for any lob over the allowable inline size, which is typically 4000 characters - regardless of whether that lob is backed by Teiid or a source.

                          1 of 1 people found this helpful