Pervasive SQL (PSQL) capture SQL statements
Is there a way to capture SQL statements sent to a PSQL 11 database? Can this be done in PCC?
ASKER
Will this capture SQL statements coming from an application and not just from PCC itself?
If the application issues a SET QRYPLAN, it'll capture them.
There was a way to set a registry setting but I haven't tried it recently. Here's the details:
The settings that affect whether the relational engine gathers query plan
information are QryPlan and QryPlanOutput. These settings are associated with
the DSN, and the DSN information is saved in the registry, or in a file,
depending on the type of DSN. I will only describe how to set these settings
for system and user DSNs, which are stored in the registry. The process for
setting these for a file DSN is similar.
For a system DSN, look in the following section of the registry:
\\HKEY_LOCAL_MACHINE\SOFTW
For a user DSN, look in the following section of the registry:
\\HKEY_CURRENT_USER\Softwa
In the appropriate section, find and select the name of the DSN for which you
want to gather query plan information. On the right side of the editor, you
will see all of the settings for the DSN, similar to the information in Figure
1.
Figure 1
Add two string values for the DSN as shown in Table 1.
Registry Setting Value
QryPlan 1
QryPlanOutput <directory and file name to place query plan information>
Table 1
Notice that the QryPlanOutput value is not the directory where you want the
information placed, but the directory and the file name. You can use any
extension you want, but I suggest using an extension of "QPF", as this is the
default extension that the query plan viewer uses.
NOTE: You can disable query plan generation by setting QryPlan=0.
There was a way to set a registry setting but I haven't tried it recently. Here's the details:
The settings that affect whether the relational engine gathers query plan
information are QryPlan and QryPlanOutput. These settings are associated with
the DSN, and the DSN information is saved in the registry, or in a file,
depending on the type of DSN. I will only describe how to set these settings
for system and user DSNs, which are stored in the registry. The process for
setting these for a file DSN is similar.
For a system DSN, look in the following section of the registry:
\\HKEY_LOCAL_MACHINE\SOFTW
For a user DSN, look in the following section of the registry:
\\HKEY_CURRENT_USER\Softwa
In the appropriate section, find and select the name of the DSN for which you
want to gather query plan information. On the right side of the editor, you
will see all of the settings for the DSN, similar to the information in Figure
1.
Figure 1
Add two string values for the DSN as shown in Table 1.
Registry Setting Value
QryPlan 1
QryPlanOutput <directory and file name to place query plan information>
Table 1
Notice that the QryPlanOutput value is not the directory where you want the
information placed, but the directory and the file name. You can use any
extension you want, but I suggest using an extension of "QPF", as this is the
default extension that the query plan viewer uses.
NOTE: You can disable query plan generation by setting QryPlan=0.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
It's fully documented at http://docs.pervasive.com/products/database/psqlv11/wwhelp/wwhimpl/js/html/wwhelp.htm#href=ODBC/qpv.14.4.html
The easiest way is to use the SET QRYPLAN statement:
¿
To create a query plan
1
Execute SET QRYPLAN = on to turn on the creation of a query plan.
2
Execute the SET QRYPLANOUTPUT statement and specify the location and name of the query plan file.
See Query Plan Settings.
3
Execute an SQL SELECT, INSERT, UPDATE, or DELETE statement (which creates its corresponding query plan).
4
Execute SET QRYPLAN = off to turn off the creation of query plans.