7 Replies Latest reply on Dec 23, 2015 9:58 AM by Steven Hawkins

    How to debug cancelled statements

    gadeyne.bram Master

      Hi,

       

      I'm using Teiid 8.11.3

       

      I've written a procedure that loops over some timepoints and generates periods from them. So using a cursor and based on some IF conditions a temporary table is filled and after the loop selected (and dropped) to return the results.

       

      I think that the procedure itself works fine.

       

      I then use teiids capability to use a join to pass parameters to the procedure.

       

      e.g.

       

      insert into #tmp_results

      select t.*

      from #tmp_ids i

      join teiid_procedure t on t.param1 = i.id

       

      The temporary table contains about 7500 rows and the procedure should thus be executed the same amount of times.

       

      However for some reason the process seems to stop at some random row. I notice the following in the logs:

       

      16:45:47,021 DEBUG [org.teiid.CONNECTOR] (Worker347_QueryProcessorQueue91430) LUQckbFGFz2/ The atomic request LUQckbFGFz2/.6.34.26424 has been canceled.

       

      However if I look for LUQckbFGFz2/.6.34.26424 I only find this previous to the cancel.

       

      16:45:46,980 DEBUG [org.teiid.CONNECTOR] (Worker345_QueryProcessorQueue91425) LUQckbFGFz2/ LUQckbFGFz2/.6.34.26424 Create State

       

      I'm not sure what issues a cancel here. Is it teiid, is it the database server?

       

      in the logs I can also see what id from #tmp_ids teiid was issuing the procedure for. If I run the procedure separatly with this id as parameters it works. That's why I think that it is not the procedure that fails.

       

      Does anyone know how I could debug this problem?

        • 1. Re: How to debug cancelled statements
          Steven Hawkins Master

          > However for some reason the process seems to stop at some random row.

           

          Stops as in the user query doesn't finish?  Or stops with an error?

           

          > I'm not sure what issues a cancel here. Is it teiid, is it the database server?

           

          The cancel is coming from Teiid.  Any pending/partially completed execution will be cancelled when a user statement has completed/errored.

           

          > Does anyone know how I could debug this problem?

           

          Do you see the user query as still active?  Are there any logs related to an error/warning condition?

          • 2. Re: How to debug cancelled statements
            gadeyne.bram Master

            >> However for some reason the process seems to stop at some random row.

             

            >Stops as in the user query doesn't finish?  Or stops with an error?

             

            Ok it seems that way before in the logs there is indeed an error. It was a few thousand lines above, that's why I did not see it. The problem seems to be that a query is generated against Sybase that containt double quote characters around the columns and sybase does not allow this.

             

            e.g.

             

            This does not work and complaint about an error around the word status

             

            SELECT g_0."Status", g_0."PatientID", g_0."VariableID", g_0."EnterTime", g_0."Value" FROM "Patient"."dbo"."P_ObservRec" g_0 WHERE g_0."VariableID" = 10000400 AND g_0."PatientID" = 0

             

            Removing the double quotes results in the query below that does work.

             

            SELECT g_0.Status, g_0.PatientID, g_0.VariableID, g_0.EnterTime, g_0.Value FROM Patient.dbo.P_ObservRec g_0 WHERE g_0.VariableID = 10000400 AND g_0.PatientID = 0

             

            Aparrently it is possible to use quoted columns in Sybase but then the following command should be executed before

            set quoted_identifier on;

             

            see SyBooks Online

             

             

            >> I'm not sure what issues a cancel here. Is it teiid, is it the database server?

             

            >The cancel is coming from Teiid.  Any pending/partially completed execution will be cancelled when a user statement has completed/errored.

             

            >> Does anyone know how I could debug this problem?

             

            >Do you see the user query as still active?  Are there any logs related to an error/warning condition?

            • 3. Re: How to debug cancelled statements
              Steven Hawkins Master

              > Aparrently it is possible to use quoted columns in Sybase but then the following command should be executed before

               

              Yes, we make that assumption since it makes the import logic consistent with the other JDBC sources: Sybase Translator - Teiid 9.0 (draft) - Project Documentation Editor

              1 of 1 people found this helpful
              • 4. Re: How to debug cancelled statements
                gadeyne.bram Master

                Hi Steven,

                 

                I'm using the jtds driver so I suppose adding <new-connection-sql> in the datasource definition is the same. Now I don't see the error message about the double quotes anymore but still the process stops.

                 

                I'll send you a private message containing the log file.

                • 5. Re: How to debug cancelled statements
                  Steven Hawkins Master

                  From the log there isn't enough to infer what is happening.  The processor is waiting for results to be delivered and it doesn't appear that they are.  Is it possible to get a thread dump from when this is occurring?  That would confirm what the source threads are doing.  And just to confirm this is not an asynch source correct?

                  • 6. Re: How to debug cancelled statements
                    gadeyne.bram Master

                    Hi Steven,

                     

                    What do you mean by an asynch source?

                     

                    I'm not sure how I could get a thread dump. Is there a way to do this from the console?

                     

                    Otherwise I'll try and reproduce this in a while when I upgrade teiid to a newer version. I'll keep you posted.

                    • 7. Re: How to debug cancelled statements
                      Steven Hawkins Master

                      > What do you mean by an asynch source?

                       

                      A non-JDBC source that is not holding a calling thread, but rather notifying the engine when results are available - which would not show up on a thread dump.

                       

                      > I'm not sure how I could get a thread dump. Is there a way to do this from the console?

                       

                      You can get a thread dump in a number of ways:

                       

                      On linux: kill -3 <pid>

                      jstack - http://docs.oracle.com/javase/7/docs/technotes/tools/share/jstack.html

                      visualvm - https://docs.oracle.com/javase/8/docs/technotes/guides/visualvm/applications_local.html