Common Property File Settings

Example property file:

#################################################################
# P6Spy Options File #
# See documentation for detailed instructions #
#################################################################

#################################################################
# MODULES #
# #
# Modules provide the P6Spy functionality. If a module, such #
# as module_log is commented out, that functionality will not #
# be available. If it is not commented out (if it is active), #
# the functionality will be active. #
# #
# Values set in Modules cannot be reloaded using the #
# reloadproperties variable. Once they are loaded, they remain #
# in memory until the application is restarted. #
# #
#################################################################

module_log=com.p6spy.engine.logging.P6LogSpyDriver
module_outage=com.p6spy.engine.outage.P6OutageSpyDriver

#################################################################
# REALDRIVER(s) #
# #
# In your application server configuration file you replace the #
# "real driver" name with com.p6spy.engine.P6SpyDriver. This is #
# where you put the name of your real driver P6Spy can find and #
# register your real driver to do the database work. #
# #
# If your application uses several drivers specify them in #
# realdriver2, realdriver3. See the documentation for more #
# details. #
# #
# Values set in REALDRIVER(s) cannot be reloaded using the #
# reloadproperties variable. Once they are loaded, they remain #
# in memory until the application is restarted. #
# #
#################################################################

# realdriver = org.gjt.mm.mysql.Driver
realdriver = oracle.jdbc.driver.OracleDriver
#specifies another driver to use
realdriver2=
#specifies a third driver to use
realdriver3=

################################################################
# P6LOG SPECIFIC PROPERTIES #
################################################################
# determines if p6log outputs information
trace = true

################################################################
# P6OUTAGE SPECIFIC PROPERTIES #
################################################################
# Outage Detection
#
# This feature detects long-running statements that may be indicative of
# a database outage problem. If this feature is turned on, it will log any
# statement that surpasses the configurable time boundary during its execution.
# When this feature is enabled, no other statements are logged except the long
# running statements. The interval property is the boundary time set in seconds.
# For example, if this is set to 2, then any statement requiring at least 2
# seconds will be logged. Note that the same statement will continue to be logged
# for as long as it executes. So if the interval is set to 2, and the query takes
# 11 seconds, it will be logged 5 times (at the 2, 4, 6, 8, 10 second intervals).
#
# outagedetection=true|false
# outagedetectioninterval=integer time (seconds)
#
outagedetection=false
outagedetectioninterval=


################################################################
# COMMON PROPERTIES #
################################################################

# comma separated list of tables to include
include =
# comma separated list of tables to exclude
exclude =

# sql expression to evaluate if using regex
sqlexpression =

# filter what is logged
filter=false

# turn on tracing
autoflush = true
# name of logfile if trace is on, use "stdout" for standard output.
logfile = spy.log
# append to the p6spy log file. if this is set to false the
# log file is truncated every time.
append=true

# sets the date format using Java's SimpleDateFormat routine
dateformat=

#list of categories to explicitly include
includecategories=

#list of categories to exclude: info, batch, debug, statement,
#commit, rollback and result are valid values
excludecategories=info,debug,result,batch

#allows you to use a regex engine or your own matching engine to determine
#which statements to log
#
#stringmatcher=com.p6spy.engine.common.GnuRegexMatcher
#stringmatcher=com.p6spy.engine.common.JakartaRegexMatcher
stringmatcher=

# prints a stack trace for every statement logged
stacktrace=false
# if stacktrace=true, specifies the stack trace to print
stacktraceclass=

# determines if property file should be reloaded
reloadproperties=false
# determines how often should be reloaded in seconds
reloadpropertiesinterval=60
#if=true then url must be prefixed with p6spy:
useprefix=false

#For log4j logging
log4j.appender.STDOUT=org.apache.log4j.ConsoleAppender
log4j.appender.STDOUT.layout=org.apache.log4j.PatternLayout
log4j.appender.STDOUT.layout.ConversionPattern=p6spy - %m%n

#log4j.appender.CHAINSAW_CLIENT=org.apache.log4j.net.SocketAppender
#log4j.appender.CHAINSAW_CLIENT.RemoteHost=localhost
#log4j.appender.CHAINSAW_CLIENT.Port=4445
#log4j.appender.CHAINSAW_CLIENT.LocationInfo=true

log4j.logger.p6spy=INFO,STDOUT

module_xxx

module_xxx is the name of a particular module to be loaded at system startup. A module contains a group of functionality. If a module line is not commented out, it is loaded into memory, and will remain in memory until the application is restarted. Modules can not be changed by using the reloadproperties function. If all modules are commented out, then nothing except the real database driver is loaded.

Currently the following modules are supported:

module_log=com.p6spy.engine.logging.P6LogSpyDriver
module_outage=com.p6spy.engine.outage.P6OutageSpyDriver

module_log is required for the logging functionality, see P6Log.
module_outage is required for the outage functionality, see P6Outage.

realdriver

realdriver is where you specify the real database driver that you use.  P6Spy wraps around your existing driver, intercepts the incoming database requests, and outputs them to a log file.  To achieve this without requiring any code changes, the P6Spy driver is listed with your application as the primary driver.  P6Spy then intercepts and logs the requests, and passes the request to the realdriver where it is processed as usual.  Example:

realdriver = oracle.jdbc.driver.OracleDriver

realdriver2, realdriver3

if you have multiple database drivers then you need a way to specify these drivers. This is where realdriver2 and realdriver3 are used. To monitor the first database, you modified your server settings or application settings to tell your application to use P6Spy as your database driver, which is: com.p6spy.engine.spy.P6SpyDriver    For the second database, specify the driver as com.p6spy.engine.spy.P6SpyDriver2 and set realdriver2 to the real driver. For the third database, specify the driver as com.p6spy.engine.spy.P6SpyDriver3 and set realdriver3 to the real driver.

Note that if you are using the same database driver with multiple connection strings, you should use the same driver. E.g. say you have two MySQL databses: mydb and testdb. You want to connect to both databases and log their activity. You have two datasources defined. You should specify com.p6spy.engine.spy.P6SpyDriver as the database driver for both of these and you should set the realdriver= the real mysql JDBC driver name. realdriver2, realdriver3 are only intended for use when you are connecting with two different drivers, for example Oracle versus MySQL.

To understand why this is the case, remember that Java uses the DriverManager to register all of the drivers. As each class is loaded, it registers itself with the DriverManager. When a connection is requested for a specific URL, the driver manager goes through the list of registered drivers and asks each in turn if it will handle that url (via the acceptURL method). The first driver that says yes becomes the driver that is used. Since an Oracle driver can handle any valid Oracle URL, registering the driver twice will just cause only one of the drivers (the first one that was registered) to handle the request. The second will be ignored. Therefore, if you specify the same driver in realdriver and realdriver2 you won't have any problems, but you will have the same driver registered multiple times.

filter, include, exclude

P6Spy lets you monitor specific tables or specific statement types.  By setting filter=true P6Spy will perform string matching on each statement to determine if it should be written to the log file.  include accepts a comma separated list of expressions which are required to be appear in a statement before it can appear in the log, exclude accepts a comma separated list to exclude. By default string matching is performed using a basic substring match. However Regex matching can also be used (see Custom Filtering below). Note that exclusion overrides inclusion, so that a statement matching both an include string and an exclude string will be excluded.

example (capture all select statements except the orders table):

filter = true
# comma separated list of tables to include
include = select
# comma separated list of tables to exclude
exclude = orders

example (only captures statements against order,order_details,price, and price_history):

filter = true
# comma separated list of tables to include
include = order,order_details,price,price_history
# comma separated list of tables to exclude
exclude =

example (captures all statements except statements against the order table):

filter = false
# comma separated list of tables to include
include =
# comma separated list of tables to exclude
exclude = order

filter, sqlexpression

If you plan on using a regex engine, a simple alternative to exclude and include is to use sqlexpression. It works like this:

filter = true
sqlexpression = your expression

If your expression matches the SQL string, it is logged, if not, it is not. Note, if you use sqlexpression, any values set in include and exclude are ignored.

stringmatcher (Custom Filtering)

If you want to apply more intelligence to what is logged or not logged by P6Spy you can specify a custom matching engine. P6Spy comes with support for several regex engines, though it is trivial to introduce another engine by implementing the StringMatcher interface. If a stringmatcher engine is specified, P6Spy will pass each statement to the class specified to determine if that statement should be logged.

Using GNU RegEx
P6Spy comes with support for a regular expression matcher based on the GNU Regex library. To use this, for example, you would specify:

# Use GNU Regex Matching for Filtering
stringmatcher =com.p6spy.engine.common.GnuRegexMatcher

Note that in order to do this you must download the GNU Java regex library and add it to your classpath so that P6Spy can find it.

Using Jakarta RegEx
P6Spy also includes support for the Apache Jakarta Regex library. To use this, you would specify:

# Use Apache Jakarta Regex Matching for Filtering
stringmatcher =com.p6spy.engine.common.JakartaRegexMatcher

Note that in order to do this you must download the Apache Jarkarta regex library and add it to your classpath so that P6Spy can find it.

autoflush

For standard development this value should be set to true.  When set to true every time a statement is intercepted it is immediately written to the log file.  In some cases, however, instant feedback on every statement is not a requirement, and the system will perform slightly faster with this value set to false

Example:

autoflush = true

logfile

Specifies the name of the file to generate.  Use "stdout" for standard output. Note, if you are in Windows and are specifying a pathname, make sure to use forward slashes (e.g. c:/mylog/spy.log)

Example:

logfile = spy.log

you can also use log4j for your logging

1.- Set the logfile property to "log4j"
2.- Put the configuration of the logger and all appenders in the spy.properties file

If for some reason log4j cannot be initialized, the logging will go to a file called log4jaux.log

append

Setting append=true causes p6spy to continue to append to the logfile, if it exists. Setting append=false causes the logfile to be truncated each time p6spy is restarted (typically when your application or application server is restarted).

excludecategories

The log includes category information that describes the type of statement. This property excludes the listed categories. Valid options include:

  • info includes driver startup information and property file information
  • debug is only intended for use when you cannot get your driver to work properly, it dumps out everything
  • statement includes Statements, PreparedStatements, and CallableStatements
  • batch addBatch() calls
  • commit commit() calls
  • rollback rollaback() calls
  • result are statements generated by ResultSet

Enter a comma separated list of categories to exclude from your log file. See filter,include,exclude for more details on how this process works.

includecategories

The log includes category information that describes the type of statement. This property is a comma separated list of categories to include. See excludecategories for a valid list of categories.

dateformat

Setting a value for dateformat changes the date format value printed in the log file. No value prints the current time in milliseconds, a useful feature for parsing the log. The date format engine is Java's SimpleDateFormat class. See the SimpleDateFormat class in the JavaDocs for information on setting this value. Example value: dateformat=MM-dd-yy HH:mm:ss:SS

stacktrace

If stack trace is set, prints out the stack trace for each sql statement logged.

stacktraceclass

Limits the stack traces printed out to those that contain this value. E.g. specifying stacktraceclass=com.mycompany.myclass would limit the printing of stack traces to the specified class value. The stack trace is converted to a String and string.indexOf(stacktraceclass) is performed.

reloadproperties and reloadpropertiesinterval

If reloadproperties is set to true, then the property file is reloaded every n seconds, where n is defined by the value set by reloadpropertiesinterval. For example, if reloadproperties=true and reloadpropertiesinterval=10 then the system checks the File.lastModified() property of the property file every 10 seconds, and if the file has been modified, then it will be reloaded.

Warning! If you have append=true the log will be suddenly truncated if you change your properties. This is because using reloadproperties is intended to be the equivalent of restarting your application server, and in the case of an application server restart your file would be truncated.

Limitations: reloadproperties will not reload any driver information (such as realdriver, realdriver2, and realdriver3) and will not change the modules that are in memory.

useprefix

When set to true requires you to prefix your URLs with p6spy:. The default is false, but may become true in a future release. This helps avoid a common pital when installing p6spy, that your real driver is registered elsewhere and because the URLs are the same, the system does not know which driver to use.

Command Line Options

Every parameter specified in the property file can be set and override at the command line using the Java -D flag.

Example:

java -Dp6logfile=my.log -Dp6trace=true

In addition, you can set the default directory to look for spy.properties:

java -Dp6.home=c:\orion\lib

Log File Format

The log file format of spy.log is as follows:

current time|execution time|category|statement SQL String|effective SQL string

  • Current time. The current time is obtained through System.getCurrentTimeMillis() and represents the number of milliseconds that have passed since January 1, 1970 00:00:00.000 GMT. See the J2SE documentation for further details. This can be changed to a different format using the dateformat property described later in this section.
  • Execution time. The time it took for that method to execute. It is important to note this is not the total cost for the SQL statement, but rather the time it takes to execute a particular method. For example, a statement SELECT * FROM MYTABLE WHERE THISCOL = ? might be executed as a prepared statement, in which the .execute() function will be measured. This is recorded as the "statement" category. Further, as you call .next() on the ResultSet, each .next() call is recorded in the result category.
  • Category. Currently there are three categories: debug (debug statements related to p6spy), statement (statement, preparedStatement, and CallableStatement) and result (ResultSet). You can manage your log by including and excluding categories, which is described below.
  • Statement SQL String. This is the SQL string passed to the statement object. If it is a prepared statement, it will be the prepared statement prior to the parameters being set, to see the complete statement see Effective SQL String.
  • Effective SQL String. If you are not using a prepared statement, this will be empty. Otherwise it will fill in the values of the Prepared Statement so you can see the effective SQL statement that is passed to the Database. Of course the Database still sees the Prepared Statement, but this string allows a convenient way to see the actual values being based to the Database.

The JSP Application

P6Spy now includes a JSP application that can be used to view configuration information about P6Spy and to create a demarcation in the log file.

The easiest way to use the JSP application is to copy p6spy.war into the deployment directory of your application server. (For example, in JBoss this might be C:\JBoss-2.4.4_Tomcat-4.0.1\jboss\deploy). Once the .war file is deployed, you can access the application by using the URL http://machine:port/p6spy

For example, if you are running the application on your own machine, and using Tomcat as the servlet engine, you would go to: http://localhost:8080/p6spy

FAQ

  1. Can I use log4j?  Yes. Please see the logfile section in the property file.
  2. Can I use regular expressions to determine what is logged?  Yes. Please see the Property section and look at the stringmatcher option.
  3. Once the application is running, can I change the properties and enable the system to use the new properties?  Yes. Please see the Property section and look at the reloadproperties section.
  4. Can I use multiple datasources?  Yes. Please see the Property section and look at the realdriver2, realdriver3 section.