1 2 Previous Next 18 Replies Latest reply on Aug 14, 2013 8:53 AM by Ramesh Reddy

    Remote Query Execution

    Aswath Khan Newbie

      I have Deployed the VDB remotely with the source as a CSV file on Teiid Server 8.3 Final version (JBOSS 7.1.1 AS).

       

      The Customer table has 30,000 rows .

       

      When I Query like "Select * from Customer Limit 100" it works fine , but when I try to  fetch 20,000 records I get the below error

       

      org.teiid.jdbc.TeiidSQLException: org.teiid.core.TeiidProcessingException: TEIID30495 The request X2c170KLQExg.101 has been closed.

       

      Similarly, when I execute the query lisitng the column names like,

       

      select "first name","last name" from customer limit 10000, I get the different error as,

       

      org.teiid.jdbc.TeiidSQLException: TEIID30179 java.nio.charset.MalformedInputException: Input length = 1

       

      I even tried changing the character set from UTF-8 to ISO-8859-1, but I see the same result.

      Let me know if any ideas to past this.

       

      Refer: The attached VDB file.

       

      Thanks,

      Aswath

        • 1. Re: Remote Query Execution
          Ramesh Reddy Master

          Do you have international characters in your data file? can you post the transformation you are using for customer table, i.e the TEXTTABLE construct you used.

          • 2. Re: Remote Query Execution
            Steven Hawkins Master

            > When I Query like "Select * from Customer Limit 100" it works fine , but when I try to  fetch 20,000 records I get the below error

             

            That message appears whenever you attempt to do an operation (cursor request, lob read, or metadata request) after the result set/statement has been closed.  I don't see lobs on your customer table (correct?), so that shouldn't be the issue. What is your client environment? If you are not explicitly closing from the client side, you'll want to check the server side logs to see if something is causing an unexpected termination.

             

            > select "first name","last name" from customer limit 10000, I get the different error as,

             

            Do you know the encoding of your file?  Are you changing the encoding through the file translator encoding property?

            • 3. Re: Remote Query Execution
              Aswath Khan Newbie

              Steve,

                       

              1. My Client is SquirrelSQL, if limit the records upto 8192 it works fine, but anything more than that it fails.

                  I do not have LOBs as part of the data, but I do have couple of columns defined as String(4000)

                  Also, Server.log has no extra details apart form the error description.

               

              2. I did not change any encoding property. But I had defined the character set as UTF-8 and then changed it to ISO-8859-1 just to chk whether it fixes the issue..

               

              I changed the char set in source.xmi file as below,

                    <tags xmi:uuid="mmuuid:aa90da05-a8eb-4d56-b048-a2c6e5199207" key="connection:FlatFileCharSet" value="ISO-8859-1"/>

              • 4. Re: Remote Query Execution
                Aswath Khan Newbie

                Ramesh,  

                 

                There is no international characters in the Customer Data, just plain english.

                 

                   Following is the transformation text,

                 

                 

                SELECT

                  "First Name", "Last Name", Gender, "Date of Birth", "NINO Number", Cliref, "Address 1", "Address 2", "Address 3", "Address 4", "Address 5", "Address 6", "Post Code", "Phone Number", Email, Role, "Policy Number"

                FROM

                  (EXEC UnisureCustomer_source.getTextFiles('UnisureCustomer.csv')) AS f, TEXTTABLE(file COLUMNS "First Name" string, "Last Name" string, Gender string, "Date of Birth" string, "NINO Number" string, Cliref string, "Address 1" string, "Address 2" string, "Address 3" string, "Address 4" string, "Address 5" string, "Address 6" string, "Post Code" string, "Phone Number" string, Email string, Role string, "Policy Number" string HEADER) AS A

                 

                • 5. Re: Remote Query Execution
                  Ramesh Reddy Master

                  Aswath,

                   

                  The place to change the "encoding" is on the translator. In the VDB explorer windwow, when you select the source model for file, you can set translator override property called "encoding". The XMI file MUST NOT be edited manually, they are to be used only by the Designer. Also, XMI files are converted to another metadata format before they are consumed in the Teiid Server, and XMI files are not consulted.

                   

                  Ramesh..

                  • 6. Re: Remote Query Execution
                    Aswath Khan Newbie

                    Ramesh,

                         I could not figure out where the VDB explorer window is.Can you help me figuring out.

                     

                    Note: I had to manually edit the xmi file as I could not  deploy it using the console/Admin Shell.

                     

                    Thanks,

                    Aswath

                    • 7. Re: Remote Query Execution
                      Ramesh Reddy Master

                      vdb.jpg

                       

                      What errors did you see with Console and Admin Shell? What is procedure you followed to deploy?

                      • 8. Re: Remote Query Execution
                        Aswath Khan Newbie

                        encoding.jpg

                        Ramesh,

                         

                        I did set the encoding property to ISO-8859-1 in the vdb explorer as seen in the scr shot attached. But I still could not query any records more than 8192 (strange number 1024 x 8). Refer the documnet attached for the scr shots. Also it happens only if I deploy and access remotely.

                         

                        Regarding deployment,

                           The problem with my scenario is that th source location is different from the development and the test environment as development happend in windows and test environment is in Linux, so I had to change the path in the .xmi file as in the linux environment.

                         

                        I did not get error while I deployed using the admin console,  but when I tried accessing the VDB, I got the error like "Source file not found".

                         

                        But then I did it manually using "*.dodeploy" placeholder file, it did worked. Not sure what was the problem.

                        • 9. Re: Remote Query Execution
                          Ramesh Reddy Master

                          Aswath,

                           

                          That looks ok, can you share your text file for me to test?

                           

                          Deployment: Again, you should NOT change .xmi files manually, if the path for your file source is different in your development and test environment, then you should develop and test fully in development environment, and when you are trying to deploy to test environment, the datasource configuration should reflect the path specific to that environment. Otherwise you place the file on shared drive where the path is same in both. So, it is not an error.

                           

                          When deploying a VDB manually by copying it to the "deployments" folder, you are required to create to .dodeploy file to deploy the vdb, but if it was same environment, you should have seen the "source file not found" error. So, fix the data source configuration. You can edit "standalone-teiid.xml" file under "resource-adapters" subsystem to reflect the correct path in that particular server and restart.

                           

                          Ramesh..

                          • 10. Re: Remote Query Execution
                            Ramesh Reddy Master

                            Also what is the error that you see when accessed remotely? Is limiting rows to 8192 in both local and remote?

                            • 11. Re: Remote Query Execution
                              Aswath Khan Newbie

                              Ramesh,

                                As I said earlier, the following is error messages we get when we access the remotely deployed VDB. The below errors occurs only if we access the remote VDB, local VDB works fine.

                               

                               

                              When I Query like "Select * from Customer Limit 100" it works fine , but when I try to  fetch 20,000 records I get the below error

                               

                              org.teiid.jdbc.TeiidSQLException: org.teiid.core.TeiidProcessingException: TEIID30495 The request X2c170KLQExg.101 has been closed.

                               

                              Similarly, when I execute the query lisitng the column names like,

                               

                              select "first name","last name" from customer limit 10000, I get the different error as,

                               

                              org.teiid.jdbc.TeiidSQLException: TEIID30179 java.nio.charset.MalformedInputException: Input length = 1

                              • 12. Re: Remote Query Execution
                                Aswath Khan Newbie

                                Ramesh,

                                   Due to data complaince reasons, I cannot share the text files with you.

                                 

                                Can you tell me, if you have seen this kind of behaviour with TEIID VDBs before, that the same data works fine locally but not when deployed remotely. What are the possible reasons?

                                 

                                -Aswath

                                • 13. Re: Remote Query Execution
                                  Ramesh Reddy Master

                                  Aswath,

                                   

                                  Clarify what you mean by Local VDB and Remote VDB? There is no such terminology. However, Teiid supports "local" and "remote" JDBC connections, where "local" means in-VM as to client JDBC application is in the same VM as the server.

                                   

                                  According to the code and your exception there is an error during reading your file, which probably does not match the encoding or you have a corrupt file. I suggest may be trimming the file from top of number of rows that you can read, then re-reading it, until you reach to offending line.

                                   

                                  No, I do not remember I seen error like this one.

                                   

                                  Ramesh..

                                  • 14. Re: Remote Query Execution
                                    Steven Hawkins Master

                                    Aswath,

                                     

                                    Can you provide the corresponding server side log entries for these exceptions?  That should provide a stacktrace and give more of indication of when the exceptions are happening.

                                     

                                    Steve

                                    1 2 Previous Next