IBM AS/400 - Estimated Query Time too long. HELP? 2

[Silentz]

I am running a very simple query in ASP against an AS/400 database server. The table I am querying has
over 28000 records/rows, and when I execute the query it returns the following error:


Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[IBM][Client Access ODBC Driver (32-bit)][DB2/400 SQL]SQL0666 - Estimated query processing time 36 exceeds
limit 30.



As I said, the SQL query is very simple:

SELECT * FROM table WHERE field = 0;

If i import the as400 table into MS Access and run the query it works fine.

Obviously there a limit to the amount of time a query can use on an AS/400 server.

WHAT I WANT TO KNOW IS:

Can this setting be changed?
if so:
Where do I find this setting?
What problems might I face if i extend the time?

Please help as much as you can - even if the answer is "it cant be changed".

Thanks in advance.

 

[Guest_imported]

You must have specified a query.timeout parameter in ASP. If you haevnt then do that set it to a high value and it will work fine.

 

[Guest_imported]

One of my users is using Microsoft Access to run a query against a file with 1.7 million rows on the AS/400. The query is very simple with 2 condtions. The user gets the following error message:

[IBM][Client Access Express DBC Driver (32-bit)][DB2/400 SQL]SQL0666 - Estimated query processing time 428
exceeds limit 60.

When another condtion is applied the query results come back fine and no error message is displayed.

I checked the AS/400 system value for query procressing and it is set to *NOMAX. So where does this valie 60 come from ? And how I can fix this? I have to mention , we just upgraded to v5r1.

Please let me know. Thanks , Marius
.

 

[MarcLodge]

Silentz,
The answer is in your timeout for the ODBC driver. This can either be changed via the control panel or by software override when you access the ODBC driver. Try clicking on the control panel and locating the driver and then configuring the timeout parameters. It's probably best tot find out what the timeout parms are from the host, and copying them in the driver.
Let me know if you need more info.
Marc

 

[Guest_imported]

People,

if you are using ADO Connection Object, you can set the CommandTimeout attribute.

The default timeout is 30.
I've set CommandTimeout = 60 and I've got the message error related to this value.

I've changed to 600 and worked fine!

Jeff.

 

[Shaixpeare]

Increasing the timeout value is only masking the problem and may do more harm then good. What you really need to be doing is making sure your queries are coded to make use of indexes, making sure the tables have been recently reorged, etc; in other words, tuning your queries and databases.
I routinely run queries that execute in less than 2 seconds against tables that have millions of rows because I make sure indexes exist to support my query. If any query against an average sized database runs for more than 20 or 30 seconds (I strive for 1 or 2 seconds TOPS), you have serious performance problems and need to be oing "explains" on your queries. Just a DBA's "two cents worth"...

 

[cjkoontz]

Don't know if it has been posted anywhere else at tek-tips, but discovered from one of the news group forums that changing a registry value could remove the timeout factor --

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\ODBC (QueryTimeout)

Had it set at 60. Changed it to 0. Was able to pull-in an AS/400 view afterwards into Access.

 

[stevexff]

Shaixpeare gets a star from me. Don't fix the timeout, fix the query...

 

[DanAkaPharao]

The 60sec timeout in Access is easy to get rid off, just right click in the design view of the query and set "ODBC-Timeout" to 0 instead of 60.

Though that 30sec limit is hard one to crack, I have the problem using ASP-scripts and doing queries in SQL as well.

Of course one should use indexes and such, and I am, but the table contains 108 000 000 records and the three first indexes are used, one of which is date, still query processing time is exceeded, "229 exceeds limit of 30".

I can't narrow it down any more, this constraint does not exist while doing the same query in MS Access through a similar ODBC-connection, it only occurs when the ODBC-connection is run through ASP-script or SQL-server DTS packade.

Anyone?

/daniel - trying to extract transaction history from a DB2 database running ERP system Movex ver. 10.8 from Intentia.