How to view Access SQL statements after ODBC

[pheffley]

I am running an MS Access 2K front end that hits a DB2 db. I need to view the translated/formatted SQL that DB2 gets to do some performance tuning. I can't simply copy and paste the Access SQL into the DB2 analyzer. I could change the statement manually, but I have a ton of queries so this option isn't practical. I connect to DB2 V6 via DB2 Connect V8.

Thanks for the help!

Paul

 

[bboffin]

Have you checked out the ODBC tracing facility? You can turn it on within the ODBC admin tool. It provides a detailed log of all the interactions between ODBC and the underlying database. I can't remember if this log includes the actual SQL statements but it might be worth a look.

 

[pheffley]

I tried it, but did not get any useful information.

 

[bboffin]

If you have control over the Access application you could change the queries to pass-through queries where the command is simply passed through to the underlying database. In this situation you don't need to link the tables, you just use the ODBC connection directly.

I realise that this would be inappropriate if what you are trying to do is work out what Access does normally.

The alternative is to use stored procedures wherever possible as these can be tuned purely at the DB2 end.