Topic
12 replies Latest Post - ‏2013-01-04T00:00:23Z by MatthiasNicola
amcintyre
amcintyre
21 Posts
ACCEPTED ANSWER

Pinned topic debugging intermittent SQL16110N Comment or PI 2200M

‏2012-12-28T22:00:38Z |
Out of millions of good XML inserts during the day I get about 200 to 300 of these:
SQL16110N XML syntax error. Expected to find "Comment or PI". SQLSTATE=2200M

My code displays the XML when an error occurs and there is nothing wrong with it that I can see, ie; it looks like all the millions of others that inserted without any error.

Any thoughts on debugging? or should I go straight to the XSR_GET_PARSING_DIAGNOSTICS procedure?

DB2 is 10.1 on Ubuntu x64 12.04.1 using node.js with node-odbc.

Thanks!!
Updated on 2013-01-04T00:00:23Z at 2013-01-04T00:00:23Z by MatthiasNicola
  • MatthiasNicola
    MatthiasNicola
    302 Posts
    ACCEPTED ANSWER

    Re: debugging intermittent SQL16110N Comment or PI 2200M

    ‏2012-12-28T22:19:58Z  in response to amcintyre
    Yes, the DB2 procedure XSR_GET_PARSING_DIAGNOSTICS is a good tool to investigate such a parsing or validation error.

    You could also examine the document in a development tool, such as Infosphere Data Architect, Altova XMLSPY, oXygen, Stylus Studio, or similar. Such tools can help you verify whether an XML document is well-formed or not.

    To get more help here in the forum it would be best to attach the failing XML document as a text file to your next forum post, if you are allowed to share it.

    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
    • amcintyre
      amcintyre
      21 Posts
      ACCEPTED ANSWER

      Re: debugging intermittent SQL16110N Comment or PI 2200M

      ‏2012-12-28T23:05:10Z  in response to MatthiasNicola
      Here is one from today that failed:
      <trade><cvol>700</cvol><datetime>2012-12-28T09:25:40-05:00</datetime><exch>Pacific</exch><last>81.4</last><symbol>UTX</symbol><tcond>10</tcond><timestamp>1356704740</timestamp><vl>100</vl><vwap>81.5357</vwap></trade>

      and here's a copy/paste from Data Studio XML Cell Editor showing one that worked (they look the same to me):
      <trade><cvol>86527</cvol><datetime>2012-12-28T08:39:07-05:00</datetime><exch>Pacific</exch><last>38.88</last><symbol>C</symbol><tcond>10</tcond><timestamp>1356701947</timestamp><vl>400</vl><vwap>38.7953</vwap></trade>

      Also, just fyi, I haven't set up any schema, mainly because it didn't look like I needed one (99.99% are inserting without error) and also because I can't find a schema that defines this xml. This doc is all I have:
      https://developers.tradeking.com/documentation/streaming
      https://developers.tradeking.com/documentation/streaming-market-quotes-get-post

      Could this be a DB2 load related issue? node.js with node-odbc is completely async and non-blocking so DB2 is getting hit hard with peaks of hundreds of inserts in a single second... and node and node-odbc have been written up as working with DB2 recently: (although I did an Ubuntu package install so I didn't have to follow this page exactly)
      https://www.ibm.com/developerworks/mydeveloperworks/blogs/pd/entry/using_ibm_db2_from_node_js34?lang=en_us

      I won't have any load that fails until market hours next week so I'll try the diag procedure then.

      And thanks for the assist!!
      • MatthiasNicola
        MatthiasNicola
        302 Posts
        ACCEPTED ANSWER

        Re: debugging intermittent SQL16110N Comment or PI 2200M

        ‏2012-12-29T01:33:28Z  in response to amcintyre
        >> Could this be a DB2 load related issue?

        Hmm, a load issue sounds unlikely to me. I have seen quite a few DB2 pureXML workloads with hundreds and even thousands of XML inserts per second, without any SQL16110N errors. A high load itself isn't known to cause parsing errors.

        Thanks for pasting your XML samples. Indeed, they look very simple and well-formed, which makes this problem more tricky.

        Looks can sometimes be deceiving, and the problem with copy & paste is that any special characters or code page issues that might exist in an XML document might be altered or removed during the copy & paste operation. For parsing errors it can be important to investigate the exact representation of the document as it was submitted for insert. Maybe you can store the failing documents in text files, or even in a BLOB column of an exception table.

        If the application code is manipulating the incoming XML before submitting the insert, e.g. to split and combine chunks of XML or to set the encoding, etc. then this is also an area for investigation. Can you examine the exact state of a given document string when it is passed into the INSERT statement?

        https://groups.google.com/forum/?fromgroups=#!topic/nodejs/uCVwmXaFev0

        Thanks,

        Matthias


        Matthias Nicola
        http://www.tinyurl.com/pureXML
        http://nativexmldatabase.com/
        • amcintyre
          amcintyre
          21 Posts
          ACCEPTED ANSWER

          Re: debugging intermittent SQL16110N Comment or PI 2200M

          ‏2012-12-29T02:47:22Z  in response to MatthiasNicola
          Yep, that's my post but that guy convinced me that node.js is ok so I thought I'd try your forum. :-)

          I've tried dumping the buffers and everything looks like normal, simple ascii. I've tried both ascii and utf8 encoding with no difference.

          I assume the only way I'm going to catch this is that after the error, I pass the exact same buffer that went to odbc to your diag procedure. That sound right?

          And thanks again!!
          • MatthiasNicola
            MatthiasNicola
            302 Posts
            ACCEPTED ANSWER

            Re: debugging intermittent SQL16110N Comment or PI 2200M

            ‏2012-12-30T10:47:07Z  in response to amcintyre
            >> I pass the exact same buffer that went to odbc to your diag procedure. That sound right?

            Yes, that's a good approach.

            When you pass the buffer to the diag procedure, you could additionally store the buffer in a BLOB column of an exception table that you may want to create. This will allow you to further examine that buffer later on. For example, you can then try an INSERT with subselect that reads the buffer from the BLOB and inserts it into an XML column, to see if the error is reproducible that way.

            Matthias


            Matthias Nicola
            http://www.tinyurl.com/pureXML
            http://nativexmldatabase.com/
            • amcintyre
              amcintyre
              21 Posts
              ACCEPTED ANSWER

              Re: debugging intermittent SQL16110N Comment or PI 2200M

              ‏2012-12-31T16:48:34Z  in response to MatthiasNicola
              Ok, I've tried the diag procedure and it runs without putting out any errors at all.

              I also put the string in an exception table and it looks just fine, exactly like the copy/paste.

              Got blob to work on the diag procedure (I think) but couldn't get it to work on the exception table (had to use varchar). Kept getting this on the insert when it was blob:
              SQL0301N The value of input variable, expression or parameter number "2" cannot be used because of its data type

              Which is strange because an XML column takes "str" without any issues 99.99% of the time but I can't put "str" into a blob.... hmmm...

              Code:
              if(err) {
              sqle = sqle + 1
              console.log(err)
              console.log(sqle,str)
              db.query("call xsr_get_parsing_diagnostics(blob('?'),'','','',0,?,?)",str,errd,errc,function(errp, rows) {

              if(errp) {
              console.log('errp',errp)
              }

              console.log('errd',errd)
              console.log('errc',errc)

              db.query("insert into p1se values(?,current_timestamp,?)",http://resp_ts.toString(),str,function(erri, rows) {

              if(erri) {
              console.log('erri',erri)
              }

              Anyway, the next approach on z/OS would be for me to open a pmr and get a slip trap set on the SQL16110N error. Is there such a debugging procedure on DB2 LUW available?

              Thanks again!!
              • amcintyre
                amcintyre
                21 Posts
                ACCEPTED ANSWER

                Re: debugging intermittent SQL16110N Comment or PI 2200M

                ‏2012-12-31T17:11:42Z  in response to amcintyre
                I also occasionally get this when trying different casts on the diag procedure first field (the packet):
                SQL1646N A routine failed because the fenced user ID cannot access required files in the sqllib directory or other instance or database directories.

                which leads me to believe that I don't actually have the diag procedure working yet. Not sure what this means or how to resolve.
                • amcintyre
                  amcintyre
                  21 Posts
                  ACCEPTED ANSWER

                  Re: debugging intermittent SQL16110N Comment or PI 2200M

                  ‏2012-12-31T18:56:11Z  in response to amcintyre
                  Have a better hex dump display now:

                  { state: '2200M',
                  error: 'node-odbc SQL_ERROR',
                  message: 'unixODBCIBMCLI DriverDB2/LINUXX8664 SQL16110N XML syntax error. Expected to find "Comment or PI". SQLSTATE=2200M\n',
                  query: 'insert into p1s values (?,current_timestamp,?)' }
                  1 '<quote><ask>41.59</ask><asksz>1</asksz><bid>41.58</bid><bidsz>6</bidsz><datetime>2012-12-31T13:54:55-05:00</datetime><exch>NASDAQ Regional/CTS</exch><qcond>Regular, two-sided open quote automated</qcond><symbol>ADI</symbol><timestamp>1356980095</timestamp></quote>'
                  00000000: 3c71 756f 7465 3e3c 6173 6b3e 3431 2e35 <quote><ask>41.5
                  00000010: 393c 2f61 736b 3e3c 6173 6b73 7a3e 313c 9</ask><asksz>1<
                  00000020: 2f61 736b 737a 3e3c 6269 643e 3431 2e35 /asksz><bid>41.5
                  00000030: 383c 2f62 6964 3e3c 6269 6473 7a3e 363c 8</bid><bidsz>6<
                  00000040: 2f62 6964 737a 3e3c 6461 7465 7469 6d65 /bidsz><datetime
                  00000050: 3e32 3031 322d 3132 2d33 3154 3133 3a35 >2012-12-31T13:5
                  00000060: 343a 3535 2d30 353a 3030 3c2f 6461 7465 4:55-05:00</date
                  00000070: 7469 6d65 3e3c 6578 6368 3e4e 4153 4441 time><exch>NASDA
                  00000080: 5120 5265 6769 6f6e 616c 2f43 5453 3c2f Q.Regional/CTS</
                  00000090: 6578 6368 3e3c 7163 6f6e 643e 5265 6775 exch><qcond>Regu
                  000000a0: 6c61 722c 2074 776f 2d73 6964 6564 206f lar,.two-sided.o
                  000000b0: 7065 6e20 7175 6f74 6520 6175 746f 6d61 pen.quote.automa
                  000000c0: 7465 643c 2f71 636f 6e64 3e3c 7379 6d62 ted</qcond><symb
                  000000d0: 6f6c 3e41 4449 3c2f 7379 6d62 6f6c 3e3c ol>ADI</symbol><
                  000000e0: 7469 6d65 7374 616d 703e 3133 3536 3938 timestamp>135698
                  000000f0: 3030 3935 3c2f 7469 6d65 7374 616d 703e 0095</timestamp>
                  00000100: 3c2f 7175 6f74 653e </quote>

                  There just doesn't seem to be anything wrong with the packet and since I can't get the diag procedure to work...

                  I really need a slip trap or another way to debug this. Isn't there any way to trap or trace the SQL16110N?
                  • amcintyre
                    amcintyre
                    21 Posts
                    ACCEPTED ANSWER

                    Re: debugging intermittent SQL16110N Comment or PI 2200M

                    ‏2013-01-02T16:31:26Z  in response to amcintyre
                    Better news. I had a javascript variable scope issue that was making it impossible to debug this... and the async nature of node.js and it's callbacks was also giving me a headache...

                    However, now I can see that each insert that fails has two complete xml "packets" in the string, both well formed. For example either two <quote> sets or two <trade> sets or one of each. And it's always consistent now.

                    So can you not insert but one xml packet in an xml column at a time? is that my problem? or do I have still have some encoding issue...
                    • MatthiasNicola
                      MatthiasNicola
                      302 Posts
                      ACCEPTED ANSWER

                      Re: debugging intermittent SQL16110N Comment or PI 2200M

                      ‏2013-01-02T20:14:55Z  in response to amcintyre
                      Ok, this is the cause of the problem. The concatenation of two well-formed XML documents is not well-formed. An XML document must have a single root element. A sequence of two XML documents does not have a single root.

                      This also explains the error message. When the XML parser reads the end tag for the root element, such as </trade>, then the document is completed and the only "thing" that is allowed to follow is an optional comment or processing instruction.

                      You can certainly insert multiple XML documents (rows) in a single insert, but then the documents must be provided as separate strings and separate rows.

                      If you want to insert two quote packets or two trades as a single document then you must combine them under a single new root root element, such as this:
                      
                      <quotes><quote>...</quote><quote>...</quote></quotes>
                      

                      Matthias

                      Matthias Nicola
                      http://www.tinyurl.com/pureXML
                      http://nativexmldatabase.com/
                      • amcintyre
                        amcintyre
                        21 Posts
                        ACCEPTED ANSWER

                        Re: debugging intermittent SQL16110N Comment or PI 2200M

                        ‏2013-01-02T21:45:50Z  in response to MatthiasNicola
                        Obviously I need to read up on XML more...

                        So rather than add string parsing before the INSERT to split these up, which I don't want to do because I'm worried about overhead, I was thinking about always adding this around each packet, which should take care of any double quote or trade sets or a combo of each:
                        <qt>....</qt> (qt stands for quote/trade)

                        So the vast majority will have these tags around a single packet with only 0.1% out of millions will have the tags around two packets:
                        <qt><quote>...</quote></qt>
                        <qt><trade>...</trade></qt>

                        <qt><quote>...</quote><quote>...</quote></qt>
                        <qt><trade>...</trade><trade>...</trade></qt>

                        <qt><quote>...</quote><trade>...</trade></qt>
                        <qt><trade>...</trade><quote>...</quote></qt>

                        I assume XML indexing will have no problem with this...

                        And thanks again for the help!! especially your blog!!