Using the P6spy module (open source), you can redirect the JDBC calls happening from ZENworks to the database through the P6spy driver. By passing the JDBC calls through the p6spy driver, you can record the queries submitted to the database servers and capture the result set. JDBC logging will be useful to identify the top queries and their response times.
IMPORTANT:Performance of the ZENworks Primary Server is affected when JDBC logging is enabled. Disable or do not enable the JDBC logging when you are not debugging.
To enable the JDBC logging:
Download the p6spy.jar file.
Copy the p6spy.jar file to the Primary Server in following location:
/opt/microfocus/zenworks/java/lib/ (Linux)
%ZENSERVER_HOME%\lib\java\ (Windows)
Download the sample spy.properties file.
Edit the spy.properties file for the database that you are using and the log file path.
To log JDBC calls that happen from the ZENServer web services, copy the spy.properties file to:
Linux: /opt/microfocus/zenworks/share/tomcat/conf/
Windows: %ZENSERVER_HOME%\share/tomcat/conf
On Linux, to log JDBC calls that happen from ZENworks Control Center, copy the spy.properties file to:
/opt/microfocus/zenworks/share/tomcat/webapps/zenworks/WEB-INF/classes/
On Windows, to log JDBC calls that happen from ZENLoader, copy the spy.properties file to:
%ZENSERVER_HOME%\share\tomcat\webapps\zenworks\WEB-INF\classes\
%ZENSERVER_HOME%\conf\loader-logging\
Edit the zdm.xml and the zenaudit.xml files on the Primary Server to add the following line:
<entry key="ConnectionDriverClass">com.p6spy.engine.spy.P6SpyDriver</entry>
The zdm.xml and the zenaudit.xml files can be found at the following location:
Linux: /etc/opt/microfocus/zenworks/datamodel/
Windows: %ZENSERVER_HOME%\conf\datamodel\
Restart the ZENworks services.
HINT:If JDBC logging is not happening for ZENServer, or if ZENServer is not working properly after enabling JDBC logging, there could be compatibility issues with the Tomcat process on Windows and the p6spy.jar file.
To avoid this issue, perform the following steps:
Create a folder p6spy under C:\
Copy the spy.properties file in C:\p6spy\
Launch the ZENworksServerw.exe using the run command.
Add the following line Java Options at the end:
-Dp6.home=C:\\p6spy
Restart the ZENworks services.
To disable JDBC logging:
Remove the following entry in the zdm.xml and the zenaudit.xml files and restart the ZENworks services:
<entry key="ConnectionDriverClass">com.p6spy.engine.spy.P6SpyDriver</entry>