5 Replies Latest reply on Jun 3, 2011 4:21 AM by Claudio Venturini

    Can't get results when using Teiid as a source for Pentaho Data Integration (Kettle)

    Claudio Venturini Newbie

      Hi all,

       

      I can't tell if this is a Teiid bug, a Pentaho bug, or a configuration mistake.

       

      I use a VDB built with Teiid 7.4 as a source for Pentaho Data Integration (Pentaho Kettle) 4.1.0. The most of times it works correctly, but sometimes I can't get results back from Teiid. When Pentaho launches a query, Teiid starts to work and then extracts the records from its sources. But then it doesn't send back any result. In other words Pentaho DI runs the query and then starts waiting for results, that however don't arrive. At the end, the query is canceled due to timeout expiration.

       

      If I run the same query from DbVisualizer 7.4 it works normally. I've set the log level to DEBUG, but I'm not able to understand what is going wrong.

       

      Attached there are two log files. server_ok.log lists the log messages when the query works normally, and server_ko.log lists the log when the query is run from Pentaho.

       

      Could someone tell me what the problem is? I've no ideas...

       

      Thanks!

      Claudio

        • 1. Re: Can't get results when using Teiid as a source for Pentaho Data Integration (Kettle)
          Steven Hawkins Master

          Claudio,

           

          I see serveral differences in the log, but it's not immediately clear what the issue is.  The first is that ko log is running under a transation, but the ok is not.  You can see this in the ko log:

           

          2011-06-02 15:54:48,270 DEBUG [org.teiid.TXN_LOG] (Worker36_QueryProcessorQueue1380) after getOrCreateTransactionContext : 8cwyvDuFM78D LOCAL ID:TransactionImple < ac, BasicAction: 7f000101:a150:4de6c286:752 status: ActionStatus.RUNNING >

           

          The next difference then is that because of the transaction the ko log is executing in single threaded mode, whereas the ok log is spawing several threads to perform the source queries.

           

          Since it is running in single threaded mode the ko log has timeslice expired exceptions that the ok does not.

           

          Those differences don't indicate a problem.  However, given that all 9 of the source queries have completed as expected, I'm not sure why a blocked exception is being thrown in the KO at:

           

          2011-06-02 15:55:58,426 DEBUG [org.teiid.PROCESSOR] (Worker36_QueryProcessorQueue1382) Request Thread 8cwyvDuFM78D.0 - processor blocked

           

          Can you confirm that the issue is only happending under a transaction?

           

          There are relatively few places in the code that block, I'll look at adding more logging.  A diagnostic patch would help, or you could try to track down in a debugger where that last BlockedException.INSTANCE is being thrown from.

           

          Steve

          • 2. Re: Can't get results when using Teiid as a source for Pentaho Data Integration (Kettle)
            Claudio Venturini Newbie

            Steven, thanks for your help.

             

            The ko log is running under a transaction because Pentaho runs all the queries this way... and as far as I know there's no way to tell it to avoid transactions.

             

            In order to do a test, I created a simple Java class which connects to Teiid and runs the query. As you supposed, the problem seems to exists only if the query is run under a transaction. If I do setAutoCommit(false) the query runs fine!

             

            As a workaround, I'm planning to build a custom Java class that will be called by Pentaho. This class will run the query and save the results in a local MySQL temporary table. Pentaho will then access this temporary table, thus avoiding the direct call to Teiid.

             

            What I cannot understand is why only this query fails. It is not the first time that I use Pentaho to load records from Teiid, and I never ran into troubles. Maybe the problem is related also to this specific query, or to this specific VDB.

             

            Please tell me if I can help you to discover the bug. You said that I can track down where the last BlockedException.INSTANCE is being thrown from. I would do it, but I don't know how to do it... please can you give me some more specific instructions?

             

            Thanks a lot

            Claudio

            • 3. Re: Can't get results when using Teiid as a source for Pentaho Data Integration (Kettle)
              Ramesh Reddy Master

              Claudio,

               

              If Kettle uses transactions using autoCommit=FALSE, then you can add "disableLocalTxn=true"  to Teiid connection properties to turn off transactions. see http://docs.jboss.org/teiid/7.4.0.Final/client-developers-guide/en-US/html_single/#connection_properties

               

              If not, you can disable transactions at sources that are used by Teiid VDB.  The data sources you defined for VDB (all -ds.xml) files, you can define using "no-tx-datasource" instead of "xa-datasource", that will make the sources not to participate in the XA transaction.

               

              Your suggested workaround seem un-necessary, will be working like a datamart, that is exactly what Teiid designed to avoid.

               

              If you have Teiid source code, you can start the JBoss AS in debug mode by un-commenting the following line in "run.conf" file <jboss-as>/bin directory.

               

              JAVA_OPTS="$JAVA_OPTS -Xrunjdwp:transport=dt_socket,address=8787,server=y,suspend=n"

               

              Then start the JBoss AS, then using Eclipse Debugging, use 'Remote Java Application" on port 8787 to connect to the JBoss AS VM. Now you can set the breakpoints in any where in the code, and run the query in Kettle and see that code hits the break points.

               

              Ramesh..

              • 4. Re: Can't get results when using Teiid as a source for Pentaho Data Integration (Kettle)
                Claudio Venturini Newbie

                Thanks for the suggestions, I will try to add disableLocalTxn= true. If that doesn't work I will try to do a debug in Eclipse.

                 

                I'll make you know something as soon as possible.

                 

                Claudio

                • 5. Re: Can't get results when using Teiid as a source for Pentaho Data Integration (Kettle)
                  Claudio Venturini Newbie

                  Ok, with disableLocalTxn=true it works!

                   

                  Anyway, as soon as I have time I will try to do a debug and I will let you know.

                   

                  Claudio