ODBC Errors When Linking Tables From Access to DB2

[dward10]

I am linking tables to a db2 database in ms access. When running queries and reports, I often receive the following error: "ODBC--Call Failed" ... "SQL0952N processing was cancelled due to an interrupt. SQLSTATE = 57014 (#-952)"

It seems that this is likely caused by the isolation level. However, anyone have ideas on how to set a global isolation level in access to prevent these errors.

 

[MarcLodge]

Dward10,
are you able to identify what SQL is running when this occurs?

Marc

 

[MarcLodge]

I asked about the statement, as I'd seen some stuff about this error sometimes being caused by the log being full, but in your case I suspect it is a timeout being caused by the ODBC connect parameter. Have a look at what either the default driver setting is, or what you code sets this up as, and increase it sufficiently. I think that will solve your occasional problem.
Marc

 

[blom0344]

In Access the default query properties for ODBC time-out are set to 60 seconds. I think you can increase them to max. 600.....

T. Blom
Information analyst
tbl@shimano-eu.com

 

[dward10]

I am not able to find what sql is causing the interrupt. I can tell you that it is a select query that uses date/time criteria to filter data. Off of that sql is another that sums the results and one other that averages them. The table it is retrieving information from has over 300,000 rows. I think this has to do with the isolation level. I know in Informix, you can set isolation to dirty read and prevent these types of errors. However, I am not aware of any vb code or statements that can set access to that kind of isolation level to db2 tables.

I also tried adjusting the time properties in access, but still get the errors.

Thanks for the help

Danny

 

[blom0344]

In DSN you can set the isolation level to various settings.
It is in the advanced tab of the CLI/ODBC settings:

CLI parameter : TNXISOLATION

Set it to 'No Commit'

T. Blom
Information analyst
tbl@shimano-eu.com

 

[dward10]

I set it to No commit and still get the same response. The thing about this problem is, sometimes it will go through without a hitch and others it gives me the error every time I try.

Thanks again

Danny

 

[leveyp]

If you are using a file DSN try using a machine DNS

 

[MarcLodge]

The fact that it is intermittent, but is usually on a 300,000+ row select, suggests again that it is a time out.

Are you sure that you are setting the ODBC time parameter correctly from within your VB? Have you tried turning on the ODBC trace from within the driver and had a look at what you get in the log file?

What type of DB2 are you connecting to by the way?