Hello,
I'm trying to read data from some AS400 tables.
The connection itself works perectly fine, but whe I try to read the table I'm getting an error saying: "SQL0666 SQL Query exceeds specified time limit or storage limit".
The table itself contains ALOT of rows (over 100.000), but I've set ConnectionTimeout on 0 so that shouldn't be a problem, check the screenshot below for my code:
http://users.telenet.be/seti2k/sql0666.jpg
iDB2Connection idbc = new iDB2Connection("DataSource=10.2.x.x;Default Collection=casjisfil;User ID=xxxxxxx;Password=xxxxxxx;DataCompression=True;ConnectionTimeout=0;");
iDB2Command idbcCmd = new iDB2Command();
idbcCmd.Connection = idbc;
idbcCmd.CommandText = "SELECT * FROM jicomp WHERE carnco = 1916000";
idbc.Open();
iDB2DataReader idbcReader = idbcCmd.ExecuteReader(); // -----> ERROR COMES HERE !!!
while (idbcReader.Read())
{
MessageBox.Show(idbcReader.GetString(1) + "," + idbcReader.GetString(2));
}
2 answers
If you scroll down to the SQL0666 error on this page, there are some notes on what can cause it and some suggested solutions.
If you're only expecting one row to be returned by the query, you could try specifying an OPTIMIZE FOR 01 ROWS clause.
EDIT
It's difficult to see how you can make the query any simpler.
I suspect the problem here is that setting the ConnectionTimeout to zero in the connection string doesn't affect the query timeout limit for the ODBC driver on your client machine.
I found this page which shows how you can turn it off.
answered 4 months ago by:
11603
Hi again Vulpes,
I've did some more testing with several different queries, and I found something very intresting.
The query: "SELECT * FROM jicomp" works perfectly fine! If I read trough the records with reader.Read() I can get all values.
But the query: "SELECT * FROM jicomp WHERE carnco = 1916000" doesn't work, then I get the SQL0666 error, any ideas on that one?
thanks in advance!
best regards.
answered 4 months ago by:
15
11603
Please see my edit.
15
Also edited my reply :)
11603
When you don't specify WHERE, the server has no query processing to do - it just streams all the rows in the table to the client which then has to pick out the ones it wants. It makes a mockery of doing as much work as possible on the server but, if you can do all that at acceptable speed and without running into memory problems, then it's a solution of sorts :)
15
Thank you for your insights on this matter, I greatly appriciate it. Unfortunately it is still going about several million records so this is not a solution. I'll keep searching for an answer.