Remote Query Execution

[aswathkhan]

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

[rareddy]

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.

[shawkins]

> 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?

[aswathkhan]

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"/>

[aswathkhan]

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

[rareddy]

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..

[aswathkhan]

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

[rareddy]

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

[aswathkhan]

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.

[rareddy]

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..

[rareddy]

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

[aswathkhan]

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

[aswathkhan]

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

[rareddy]

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..

[shawkins]

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