12 Replies Latest reply on Apr 9, 2018 11:03 AM by Ramesh Reddy

    Issue with REST web service for specific calls (TEIID30151)

    Andreas Krück Newbie

      Hi all,

       

      I'm using an existing Teiid 10 instance to retrieve data using automatically created statements from an Oracle database and a REST web service. Based on the created query, it works or creates this error message:

      TEIID30020 Processing exception for request XOEEB85RAoHm.10 'TEIID30151 Error building Source for context item.'. Originally TeiidProcessingException XMLSystemFunctions.java:911.: org.teiid.core.TeiidProcessingException: TEIID30151 Error building Source for context item.

       

      This is a sample SQL producing this error (key SQL marked in red):

       

      SELECT MY_SCHEMA.ID,

        MY_REST.FILE_ID,

        MY_REST.FILE_NAME

      FROM ORACLE_DB.SCHEMA MY_SCHEMA

      LEFT OUTER JOIN REST_WS.WS_VIEW MY_REST ON MY_SCHEMA.ID = MY_REST.SEARCH_STRING

      WHERE MY_SCHEMA.ID IN (

        SELECT MY_SCHEMA.ID

        FROM ORACLE_DB.SCHEMA MY_SCHEMA

        INNER JOIN ORACLE_DB.SCHEMA2

        ON MY_SCHEMA.ID = ORACLE_DB.SCHEMA2.ID_FK

        INNER JOIN ORACLE_DB.SCHEMA3 MY_SCHEMA3

        ON ORACLE_DB.SCHEMA2.ID_FK = MY_SCHEMA3.BIA_SID

        WHERE MY_SCHEMA3.ID = 'ABC' AND ORACLE_DB.SCHEMA2.ID_FK = 12345

      )

      OPTION MAKEDEP REST_WS.WS_VIEW

       

      This is the same statement with a pre-defined key and this produces no errors:

       

      SELECT MY_SCHEMA.ID,

        MY_REST.FILE_ID,

        MY_REST.FILE_NAME

      FROM ORACLE_DB.SCHEMA MY_SCHEMA

      LEFT OUTER JOIN REST_WS.WS_VIEW MY_REST ON MY_SCHEMA.ID = MY_REST.SEARCH_STRING

      WHERE MY_SCHEMA.ID IN (56789)

      OPTION MAKEDEP REST_WS.WS_VIEW

       

      What can I do to find the origin of this error?

       

      Many thanks in advance.

        • 1. Re: Issue with REST web service for specific calls (TEIID30151)
          Ramesh Reddy Master

          Typically the rest access will be in procedural forms, like "exec REST_WS.WS_VIEW(...)", above you show in view form. Typically one can use relational procedure transformation where the criteria get converted back into procedure parameters at the time of execution.

           

          What happens when you provide multiple IN clause values as shown in the statement without dynamic select expression? Also, try moving the MAKEDEP right after "LEFT OUTER JOIN" in the comment form and see if that makes any difference.

          • 2. Re: Issue with REST web service for specific calls (TEIID30151)
            Andreas Krück Newbie

            The EXEC part is done in the procedure, the REST_WS.WS_VIEW is defined like this:

             

            CREATE VIEW WS_VIEW AS SELECT * FROM REST_WS.WS_PROCEDURE;

             

            with the procedure defined like this:

             

            CREATE VIRTUAL PROCEDURE WS_PRECEDURE(SEARCH_INPUT varchar) RETURNS (

              FILE_ID varchar,

              FILE_NAME string

            )

            AS

            SELECT

              A.FILE_ID, A.FILE_NAME

            FROM

            (

              EXEC MY_WS.INVOKEHTTP(

              ...

              )

            ) AS F,

            XMLTABLE(

              '/myxml/response/docs' PASSING JSONTOXML('myxml', F.RESULT) COLUMNS

              FILE_ID string PATH 'fileid',

              FILE_NAME string PATH 'filename'

            )

            AS A;

             

            With two or three values in the IN clause, the results are returned as usual, just like for one.

             

            The position of the MAKEDEP seems to be not possible, since no other option worked for me.

            • 3. Re: Issue with REST web service for specific calls (TEIID30151)
              Ramesh Reddy Master

              I guess procedure gets called for each SCHEMA_ID. Can you provide query plan for this?

              • 4. Re: Issue with REST web service for specific calls (TEIID30151)
                Andreas Krück Newbie

                I noticed, when changing the identifier in yellow, the query plan is identical, but sometimes it works, sometimes I get the mentioned error message. The only difference was that the statement in red delivered more or less identifiers. With ~800 it works, with ~3000 not.

                 

                 

                SELECT MY_SCHEMA.ID,

                  MY_REST.FILE_ID,

                  MY_REST.FILE_NAME

                FROM ORACLE_DB.SCHEMA MY_SCHEMA

                LEFT OUTER JOIN REST_WS.WS_VIEW MY_REST ON MY_SCHEMA.ID = MY_REST.SEARCH_STRING

                WHERE MY_SCHEMA.ID IN (

                  SELECT MY_SCHEMA.ID

                  FROM ORACLE_DB.SCHEMA MY_SCHEMA

                  INNER JOIN ORACLE_DB.SCHEMA2

                  ON MY_SCHEMA.ID = ORACLE_DB.SCHEMA2.ID_FK

                  INNER JOIN ORACLE_DB.SCHEMA3 MY_SCHEMA3

                  ON ORACLE_DB.SCHEMA2.ID_FK = MY_SCHEMA3.BIA_SID

                  WHERE MY_SCHEMA3.ID = 'ABC' AND ORACLE_DB.SCHEMA2.ID_FK = 12345

                )

                OPTION MAKEDEP REST_WS.WS_VIEW

                 

                Any idea?

                • 5. Re: Issue with REST web service for specific calls (TEIID30151)
                  Ramesh Reddy Master

                  As a stop-gap measure, you can always control how many IN values you want to support in one go by setting the translator property for your Oracle to control the number at 800.

                   

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

                       <property name="MaxInCriteriaSize" value="800"/>

                  </translator>

                      

                  And then use "oracle-override" as your translator name on your Oracle model.

                   

                  Ramesh..

                  • 6. Re: Issue with REST web service for specific calls (TEIID30151)
                    Andreas Krück Newbie

                    The Oracle connection seems to be not the issue. The combination of the Oracle with the REST service data source is.

                    I can retrieve multiple million rows from the Oracle connection without any issue, but as soon as I add the related web service information, the statement fails.

                    Any idea why it is only failing for a larger number of records?

                    • 7. Re: Issue with REST web service for specific calls (TEIID30151)
                      Ramesh Reddy Master

                      Provide full error stack from logs. If needs to be run in DEBUG mode to get it.

                      • 8. Re: Issue with REST web service for specific calls (TEIID30151)
                        Andreas Krück Newbie

                        13:13:19,994 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1717) Created intermediate sort buffer 3301

                        13:13:20,010 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1717) Created intermediate sort buffer 3302

                        13:13:20,010 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1717) Created intermediate sort buffer 3303

                        13:13:20,025 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue1718) Running task for parent thread Worker1_QueryProcessorQueue1717

                        13:13:20,025 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1717) Request Thread ikch7WEdCSyt.10 - processor blocked

                        13:13:21,478 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1719) Request Thread ikch7WEdCSyt.10 with state PROCESSING

                        13:13:21,478 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue1720) Using stream processing for evaluation of /myxml/response/docs

                        13:13:21,494 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1719) QueryProcessor: closing processor

                        13:13:21,494 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1719) QueryProcessor: closing processor

                        13:13:21,494 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue1721) Running task for parent thread Worker1_QueryProcessorQueue1719

                        13:13:21,494 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1719) Request Thread ikch7WEdCSyt.10 - processor blocked

                        13:13:22,978 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1722) Request Thread ikch7WEdCSyt.10 with state PROCESSING

                        13:13:22,978 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue1723) Using stream processing for evaluation of /myxml/response/docs

                        13:13:22,978 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1722) QueryProcessor: closing processor

                        13:13:22,978 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1722) QueryProcessor: closing processor

                        13:13:22,978 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue1724) Running task for parent thread Worker1_QueryProcessorQueue1722

                        13:13:22,978 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1722) Request Thread ikch7WEdCSyt.10 - processor blocked

                        13:13:24,478 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1725) Request Thread ikch7WEdCSyt.10 with state PROCESSING

                        13:13:24,478 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue1726) Using stream processing for evaluation of /myxml/response/docs

                        13:13:24,478 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1725) QueryProcessor: closing processor

                        13:13:24,494 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1725) Created intermediate sort buffer 3322

                        13:13:24,494 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue1727) Running task for parent thread Worker1_QueryProcessorQueue1725

                        13:13:24,494 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1725) Request Thread ikch7WEdCSyt.10 - processor blocked

                        13:13:24,557 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1728) Request Thread ikch7WEdCSyt.10 with state PROCESSING

                        13:13:24,557 DEBUG [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue1729) Using stream processing for evaluation of /myxml/response

                        13:13:24,572 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1728) QueryProcessor: closing processor

                        13:13:24,572 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1728) QueryProcessor: closing processor

                        13:13:24,572 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1728) QueryProcessor: closing processor

                        13:13:24,572 WARN  [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1728) TEIID30020 Processing exception for request ikch7WEdCSyt.10 'TEIID30151 Error building Source for context item.'. Originally TeiidProcessingException '12' XMLSystemFunctions.java:911.: org.teiid.core.TeiidProcessingException: TEIID30151 Error building Source for context item.

                        at org.teiid.query.xquery.saxon.XQueryEvaluator.evaluateXQuery(XQueryEvaluator.java:148)

                        at org.teiid.query.processor.relational.XMLTableNode$1.run(XMLTableNode.java:269)

                        at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:284)

                        at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)

                        at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)

                        at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

                        at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

                        at java.lang.Thread.run(Unknown Source)

                        Caused by: nu.xom.ParsingException: 12

                        at nu.xom.Builder.build(Unknown Source)

                        at nu.xom.Builder.build(Unknown Source)

                        at org.teiid.query.xquery.saxon.XQueryEvaluator.evaluateXQuery(XQueryEvaluator.java:142)

                        ... 7 more

                        Caused by: java.lang.ArrayIndexOutOfBoundsException: 12

                        at org.teiid.query.function.source.XMLSystemFunctions.escapeName(XMLSystemFunctions.java:911)

                        at org.teiid.query.function.source.XMLSystemFunctions$JsonToXmlContentHandler.startObjectEntry(XMLSystemFunctions.java:226)

                        at org.teiid.json.simple.JSONParser.parse(JSONParser.java:232)

                        at org.teiid.query.function.source.XMLSystemFunctions$JsonToXmlContentHandler.nextEvent(XMLSystemFunctions.java:351)

                        at org.teiid.query.xquery.saxon.XMLEventStreamReader.next(XMLEventStreamReader.java:194)

                        at net.sf.saxon.evpull.StaxToEventBridge.next(StaxToEventBridge.java:159)

                        at net.sf.saxon.evpull.EventStackIterator.next(EventStackIterator.java:61)

                        at net.sf.saxon.evpull.EventIteratorToReceiver.copy(EventIteratorToReceiver.java:44)

                        at net.sf.saxon.event.Sender.sendPullEventSource(Sender.java:559)

                        at net.sf.saxon.event.Sender.send(Sender.java:132)

                        at net.sf.saxon.Configuration.buildDocument(Configuration.java:3361)

                        at net.sf.saxon.Configuration.buildDocument(Configuration.java:3303)

                        at org.teiid.query.xquery.saxon.SaxonReader.parse(StreamingUtils.java:179)

                        ... 10 more

                         

                         

                        13:13:24,572 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1728) Removing tuplesource for the request ikch7WEdCSyt.10

                        13:13:24,572 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1728) ikch7WEdCSyt.10 Finished Processing

                        13:13:24,572 DEBUG [org.teiid.PROCESSOR] (Worker1_QueryProcessorQueue1728) org.teiid.core.TeiidProcessingException: TEIID30151 Error building Source for context item. Sending error to client ikch7WEdCSyt.10

                        • 9. Re: Issue with REST web service for specific calls (TEIID30151)
                          Steven Hawkins Master

                          Andreas,

                           

                          This looks like a bug with the name correction logic - if the json name ends in an underscore this exception will be thrown.  Can you log an issue for this? - JBoss Issue Tracker

                           

                          Steve

                          • 12. Re: Issue with REST web service for specific calls (TEIID30151)
                            Ramesh Reddy Master

                            Looks like the issue has been resolved, can you not use the one the fixed versions?