Monitor queries executing on Pervasive SQL at run time

Hi,
I have an application whihc uses Pervasive SQL as a backend.
Is there any tool for Pervasive similar to Microsoft's Profiler for MS SQL server?
i would like to monitor the queries being executed behind the application?
can this be achieved using Audit master?
So far that i have checked .. i think that Audit Master only displays modifications made to the database, not the retrievals or the queries being used.

Regards,
Nabeel Moeen
nabeelmoeenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mirtheilSoftware DeveloperCommented:
Query Plan Viewer is about the only thing for monitoring SQL queries.  A PDF that Pervasive sent me is at http://www.mirtheil.com/files/queryplanviewer.pdf.  
AuditMaster only tracks changes.
The Pervasive Monitor will give you general information about the database.

On thing to consider is that most PSQL applications don't use SQL and so the QPV won't help.  Does the application use SQL?  
nabeelmoeenAuthor Commented:
i'm assuming you mean executin sQL directly from the App as opposed to Stored procedures?
i guess it does for generating reports, as there arent any sp's in the database.
And what i want to determine is the tables its accessing to fetch the data.
nabeelmoeenAuthor Commented:
and the queries used to fetch the data.
I'll take a look at the pdf. btw, the first page mentions its for Pervasive 2000 ... would it work with version 8.x even if this is THE tool =)
SolarWinds® VoIP and Network Quality Manager(VNQM)

WAN and VoIP monitoring tools that can help with troubleshooting via an intuitive web interface. Review quality of service data, including jitter, latency, packet loss, and MOS. Troubleshoot call performance and correlate call issues with WAN performance for Cisco and Avaya calls

mirtheilSoftware DeveloperCommented:
The QPV applies to all versions of PSQL (2000, v8, and v9).  The QPV monitors all SQL queries executed at the engine.  This means standard SQL and Stored Procedures.

THe application may not use SQL. It may use the Btrieve API.  If it does, the QPV will NOT help.  

To determine which interface the application uses, call the vendor of the application.
Bill BachPresident and Btrieve GuruCommented:
Here's how to tell for sure:

First, start the Pervasive Monitor utility (either from the server or from a workstation, then use Options/Connect to get to the server).  Go to the Microkernel/Active Users screen.  If all of the users are showing up as regular user names, then your application is purely Btrieve-based.  If you see users listed like "SRDE:<anonymous>", then you will *ALSO* have SQL/ODBC users accessing the data.  You can tell for sure the exact location of each user by looking at the "Network Address" line in the Monitor -- this should give you the exact address of the computer related to that connection.

If you are working on a SQL application, then the Query Plan Viewer is the first/best tool to start with.  However, you can ALSO watch the connections via the SQL/Active Connections window on the Monitor to get some basic idea of queries that are taking a long time to run (i.e. they show "Active" for a long time).  Finally, you can watch the related SRDE:<...> connections in the Microkernel/Active Users screen to see how many database requests are being issued by the query.  An app that reads 10 million records on a small database can probably be optimized somehow.

If you are monitoring a Btrieve-based application, then the rules change somewhat.  You'll have to monitor the connection carefully from ther Monitor while the user is working.  This will show you the files being accessed (on the right side of the screen) at the time the app is running.  This will also show you basics about the number of records Read, Inserted, Updated, and Deleted.  However, it doesn't go any further than that.

To TRUELY figure out a Btrieve app, the process is fairly "simple", but execution can be complicated.  First, get all users out of the database, then get one user to get the application to the point that you are interested in.  In the PCC, go to the Configuration/Server/Debugging  settings and enable MKDE Tracing for all operations.  (Don't forget to "Apply" the change.)  Next, run the application for the process you want to check out.  When it is done, do back to disable MKDE Tracing and Apply again.  We also have a free program oon our web site called SetTrace that can flip the trace flag on and off for you.  Get it from http://www.goldstarsoftware.com/download.asp if you want to try it instead of using the PCC.

Now, you can review the contents of the MKDE.TRA log file you just created.  This is a developer-level log file, but any Btrieve developer can read most of the information in there to let you know exactly what transpired which that process was running.  You will see operation codes, data buffers, and such.  See the online manuals for exact details if you need help with the analysis, or post samples here for help.

Two last tips:
1) You must trace with only one user in.  If you don't, you'll have to manually figure out which calls are from which user.
2) Do not leave tracing enabled for too long -- it is a huge performance drain, especially with mutiple users in the system.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nabeelmoeenAuthor Commented:
thanx for the help mirthell,
i got the QPV working, though it just displayed the top level query
it might be that the application is using Btrieve APIs :(
nabeelmoeenAuthor Commented:
Great help Bill
Saw your solution a bit late though
i'll definitely give it a shot
nabeelmoeenAuthor Commented:
Bill, whats the default location of the trace file?
mirtheilSoftware DeveloperCommented:
The default location is "C:\PVSW\BIN\MKDE.TRA".  It's configurable in the PCC under Debugging (I think).
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.