SQL0666 error from Microsoft Access using Client Access - DB2/400

[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 conditions. 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 value 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]

Maruis,
I've had this sort of thing when using ODBC drivers, and if it's the same, it's the time parameter in the driver, not in the server. Try locating the Access Express DBC driver, possibly in the control panel, and configure it correctly. There will be a timeout parameter which will be set at 60 and should be updated to something like 600

Marc

 

[Guest_imported]

Thanks for the reply, Marc. However, where do I find the timeout parameter for the driver. I am using Client Access. Pleaser step me through this. Thanks, Marius

 

[MarcLodge]

Marius,
Unfortunately where I did this was on another site, different from where I now work, which of course does not use ODBC!

From memory I recall that you click on the control panel and you can see an ODBC drivers which you can click on and config it there.

I've just remembered that you can set the parameter from within Access also. If you are doing it with VBA then the code goes something like this and the bit you are specifically interested in is the ODBCTimeout parameter:

Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.CreateQueryDef("&quot
' here follows the OBDC connection string to hook up to DB2
qdf.Connect = "ODBC;DSN="xxx";DBALIAS="xxx";UID="userid";PWD="pw";"
' set timeout equal to mainframe timeout
qdf.ODBCTimeout = 600
' Build the SQL into the query defined as a dynamic query above
strSQL = "SELECT SCHEME_NO "
strSQL = strSQL & "FROM TABLEA"
strSQL = strSQL & "WHERE PART_NO = '0000001' "
qdf.SQL = strSQL
' run the query and store the result
Dim rstUserdets As Recordset
Set rstUserdets = qdf.OpenRecordset()

 

[Ronze55]

Hi,

I have a similar problem, but i am not attaching programatically. I am using a user dsn.

what should I do?

thanks
Ronze

 

[MarcLodge]

Ronze55
Whatever connection method you use to access the DB2 system (Marius was going from Access to DB2 on an AS400) will have some timing parameters. It is there that you will need to change the parms to allow you more time.

Hope this helps.

Marc

 

[blom0344]

From within Access just open query in design and in menu --> view --> query properties. You can set the timeout there as well

Ties Blom
Information analyst