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?
 
Bill BachPresidentCommented:
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.
0
 
mirtheilCommented:
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?  
0
 
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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 =)
0
 
mirtheilCommented:
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.
0
 
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 :(
0
 
nabeelmoeenAuthor Commented:
Great help Bill
Saw your solution a bit late though
i'll definitely give it a shot
0
 
nabeelmoeenAuthor Commented:
Bill, whats the default location of the trace file?
0
 
mirtheilCommented:
The default location is "C:\PVSW\BIN\MKDE.TRA".  It's configurable in the PCC under Debugging (I think).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.