License Agreement, Release Notes, and Additional Help

License Agreement

The P6Spy Software License, Version 1.2

This license is derived and fully compatible with the Apache Software license, see http://www.apache.org/LICENSE.txt.
Copyright (c) 2001-2003 Andy Martin, Ph.D. and Jeff Goke
All rights reserved.
Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:
1. Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
2. Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in
the documentation and/or other materials provided with the distribution.
3. The end-user documentation included with the redistribution, if any, must include the following acknowledgment:

The original concept and code base for P6Spy was conceived and developed by Andy Martin, Ph.D. who generously contributed the first complete release to the public under this license.
This product was due to the pioneering work of Andy that began in December of 1995 developing applications that could seamlessly be deployed with minimal effort but with dramatic results. This code is maintained and extended by Jeff Goke and with the ideas and contributions of other P6Spy contributors. (http://www.p6spy.com)
Alternately, this acknowledgment may appear in the software itself, if and wherever such third-party acknowledgments normally appear.
4. The names "P6Spy", "Jeff Goke", and "Andy Martin" must not be used to endorse or promote products derived from this software without prior written permission. For written permission, please contact license@p6spy.com.
5. Products derived from this software may not be called "P6Spy" nor may "P6Spy" appear in their names without prior written permission of Jeff Goke and Andy Martin.
THIS SOFTWARE IS PROVIDED ``AS IS'' AND ANY EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION OR ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Trademark Notices

Sun, the Sun logo, Sun Microsystems, JavaSoft, JavaBeans, JDK, Java, HotJava, HotJava Views, Visual Java, Solaris, NEO, Joe, Netra, NFS, ONC, ONC+, OpenWindows, PC-NFS, SNM, SunNet Manager, Solaris sunburst design, Solstice, SunCore, SolarNet, SunWeb, Sun Workstation, The Network Is The Computer, ToolTalk, Ultra, Ultracomputing, Ultraserver, Where The Network Is Going, Sun WorkShop, XView, Java WorkShop, the Java Coffee Cup logo, and Visual Java are trademarks or registered trademarks of Sun Microsystems, Inc. in the United States and other countries.
Windows, WindowsNT, and Win32 are registered trademarks of Microsoft Corp.

JBoss and JBoss Group are trademarks of Marc Fleury under operation by JBoss Group, LLC.

Sybase, SQL Anywhere, and Adaptive Server Anywhere are trademarks of Sybase, Inc.

Microsoft SQL Server is a trademark of the Microsoft Corporation.

BEA, WebLogic, Tuxedo, and Jolt are registered trademarks, and How Business Becomes E-Business, BEA WebLogic E-Business Platform, BEA Builder, BEA eLink, BEA WebLogic Enterprise, BEA WebLogic Express, BEA WebLogic Integration, BEA WebLogic Personalization Server, BEA WebLogic Portal, and BEA WebLogic Server are trademarks of BEA Systems, Inc.
Oracle is a registered trademark, and ConText, Oracle Alliance and Oracle8 are trademarks or registered trademarks of Oracle Corporation.

DB2, DB2 OLAP Server, WebSphere, and IBM are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries or both.

MySQL is a trademark of MySQL AB in the United States and other countries.

Other product names mentioned herein and throughout the this web site are the trademarks of their respective owners.

Release Notes

> Version 1.2

> - Driver initialization bug fix and package import cleanup contributed by Joe Fisher (Joe Fisher)
> - Further changes to better support JDK 1.2
> - Changed a DataSource class name to avoid conflict with an Oracle class of the same name (Alan Arvesen of IronGrid)
> - Allow unlimited SQL parameters (Bradley Johnson of IronGrid)

> Version 1.1

> - Added a highly requested feature contributed by Jeff Wolfe that only logs queries taking longer than a specified threshold. (Jeff Wolfe)
> - Added a bug fix that prevented modified property files from being persisted in Java environments prior to 1.4. (Jeff Wolfe)
> - Added JBoss 2.x JMX support, submitted by Ralph Harnden (Ralph Harnden)
> - Alan Arvesen of IronGrid added a driver patch that deregisters realdrivers using the same name as the P6 driver to avoid driver order registration problems (Alan Arvesen)

> Version 1.0.1

> - Added a bug fix to prevent a NullPointerException from being thrown when there is a space before or after the name of the realdriver. (Paolo De Carlo)

> Version 1.0 Production Release

> - In beta for over 6 months, P6Spy version 1.0 is a major rewrite of the P6Spy code. This release includes numerous new features, such as support for JDK 1.4, Datasources, log4j, JBoss 3.x, and WebSphere, as well as an overhauled, optimized architecture.

> Version 1.0 beta 9

> - Added full support for DataSources and created installation instructions for WebSphere 4.0. (Dennis Parker, IronGrid )

> Version 1.0 beta 8

> - Refactored options to support easier creation of new option files and clearer separation of file management from management of the actual properties.
> - Reduced deployment JAR file size by 25%.
> - P6Spy Documentation was overhauled. (Suzanne Patton)

> Version 1.0 beta 7

> - Created code to enable more robust property file loading, including the ability to use the ClassLoader to load the property file. (Scott Howlett)
> - Fixed a problem dealing with the log file being ignored.

> Version 1.0 beta 6

> - Debugged and fixed a problem in which applications calling newInstance(), instead of using DriverManager, were causing the driver to load improperly.

> Version 1.0 beta 5

> - Refactored the driver loading to first attempt the new classloader mechanism (implemented in Version 1 beta 2) and, upon failure, attempt the previously-used Name loading call. (Alan Arvesen, IronGrid )
> - P6SpyDriver now throws an exception immediately when realdriver fails to load, making diagnostics easier.

> Version 1.0 beta 4

> - Added error category and pushed error logging through the standard logging process instead of stderr. This should make it easier to diagnose problems when installation does not proceed as expected.

> Version 1.0 beta 3

> - Added a call to each P6 class called getJDBC() that returns the native driver. This enables a workaround for using non-standard JDBC calls included with some JDBC drivers. See Known Issues for more information. Thanks to Ralph Harnden for the suggestion. (Alan Arvesen, IronGrid )
> - Added an appender architecture which supports customizable logging. (Alan Arvesen, IronGrid )
> - Changed the reloading code to work as a separate thread, making it more efficient. (Alan Arvesen, IronGrid )
> - Enhanced module support, making it easier to create a new module. Now, the only required files for a new module are the factory, the driver, and the classes that are changing. If you want to intercept the Statement class only, that is the only class you need to create. In the past, you had to create an instance of every class, even if you did not want to override that class. The code to support driver loading has also been simplified.
> - Added JDK 1.4 support. (Matthew Wakeling)

> Version 1.0 beta 1 & 2

> - Added log4j support, which is one of the most requested feature enhancements. (Rafael Alvarez)
> - Due to problems reported with driver loading in the alpha version, driver loading has been rewritten. It is now more efficient.

> Version 1.0 alpha

> - Restructured code to inherit from a single core wrapper driver.
> - Introduced concepts of modules and stackable drivers that dynamically load the necessary code into memory at runtime.
> - Broke code into logical modules: P6Log and P6Outage.
> - Refactored large amount of code.
> - Rewrote JUnit tests and added more rigorous tests.

> Version 0.8

> - Created an Outage Detection module that reports database outages when database statements do not respond within a given period of time. (Peter Laird)
> - Created a JSP application that gives a visual control to P6Spy. The first version can be used to view configuration information about P6Spy and to create a demarcation in the log file. (Peter Laird)
> - Added support for multiple simultaneous databases, a highly requested feature. Currently, support is limited to three databases, but can easily be expanded. (Viktor Szathmary)
> - Added the logging of a connection ID, and enabled URLs to be prefixed with p6spy: to aid in debugging. A common mistake people make when installing is to have the real driver registered elsewhere; this feature avoids that problem. The default does not require this value, but in the future it may be mandatory to ease the install process.
> - Rewrote P6SpyOptions to allow new options to be added quickly and easily.
> - Additional JUnit tests added.

> Version 0.72

> - Added the ability to dynamically reload the property file after a specified period of time. (Philip Ogren)
> - Added logging to commit and rollback statements.

> Version 0.71

> - Added installation instructions for BEA WebLogic Portal and Server. (Philip Ogren)
> - Added Jakarta RegExp support. (Philip Ogren)
> - Ability to print stack trace of logged statements. This is useful in understanding where a logged query is being executed in the application. (Philip Ogren)
> - Simplified table monitoring property file option. (Philip Ogren)
> - Updated the RegExp documentation.

> Version 0.7

> - Added timing information to the log files, in order to better visualize bottleneck queries. (Simon Sadedin)
> - Added RegExp support for table filtering, allowing sophisticated custom filtering. (Simon Sadedin)
> - Added installation instructions for Sun iPlanet. (Michael Sgroi)
> - Added installation instructions for BEA WebLogic. (Richard Delbert)
> - Added support for callable statements.
> - Added support for batch statements.
> - Added a debug category that provides detailed debug information. By default, this is disabled. Refer to the Troubleshooting section for more information.
> - Changed the default log format to include more information.
> - Added a test target to Ant that works with JUnit to perform some basic tests, using Oracle as the test database.
> - Added ResultSet logging and timing information. By default, this is disabled. Refer to the Log File Format documentation for more information.
> - Fixed a number of bugs, in particular a bug that was causing an empty spy.log file to be created and never populated.

> Version 0.6

> - Fixed a bug in which null connections were not returning null, but rather empty connections. This was a problem for some applications that were expecting a null connection.
> - Added an option to allow the truncation/non-truncation of the log file, which can be specified within spy.options.

Known Issues

- Non-standard (driver specific) JDBC methods are not supported without using a workaround. Many drivers provide methods that expose driver-specific, non-standard functionality. Most developers do not use these features, but in the event that an application does use these features they are not natively supported by P6Spy. For example, the MySQL JDBC drivers allow you to call the auto-increment function as follows:

  > > ((org.gjt.mm.mysql.PreparedStatement)stmt).getLastInsertId();

> That cast fails when P6SpyDriver is in place, since the class it tries to cast is against P6SpyDriver itself. P6SpyDriver does not expose that driver-specific method and the P6Spy PreparedStatement class is not a subclass of org.gjt.mm.mysql.PreparedStatement. The one workaround available requires code changes in the application. Each P6Spy class exposes a method called getJDBC() that returns the real JDBC driver. With this method, an application can cast a class, such as PreparedStatement, to P6PreparedStatement, invoke getJDBC(), and then cast the returned value to the native driver class, as in OraclePreparedStatement. In this example, the non-JDBC statement is not logged but the application will continue to function.

> Ideally, a long-term, native solution will be provided. One idea under consideration is to provide vendor-specific versions (Oracle or MySQL, for example) of P6Spy that subclass the vendor driver, so a cast can be successfully executed. These versions would expose the vendor-specific methods. However, this is not yet under development, and other suggestions are welcome

Troubleshooting

- Application server does not start properly. This is probably because the p6spy.jar or spy.properties is not in the classpath. If using JBoss, look at the JBoss log file for clues. A message like the following usually means the p6spy.jar file is not found.

  > [JDBC provider] Initializing
  > [Service Control] Could not initialize DefaultDomain:service=JdbcProvider

> You may see that the driver is loaded, but a message will indicate that the spy.properties file is not found, and will list all of the directories searched for that file. In this case, move the spy.properties file to one of the searched directories listed.

- The spy.properties cannot be found. Often application servers have their own classpath and ignore the standard Java classpath. Make sure spy.properties is in the classpath that is listed in the error message stating spy.properties cannot be found, this is the classpath that P6Spy is searching.

- The spy.log file is not written. Searching your entire drive for spy.log. Remember, you can specify the default location using the spy.properties property file. See Common Property File Settings for more information.

- The spy.log file is generated, but nothing is logged. P6Spy has the ability to print debug statements that are very useful in determining why the driver is not working. To turn on these statements, remove the debug category and the info category from the excludecategories list in your spy.options file. Also, make sure your wrapped driver is registered correctly. Set useprefix=true in your spy.properties file and put p6spy: as a prefix for your connection URL for the P6Spy driver in your application server configuration. P6Spy will fail if the prefix is not present.
- The spy.log file is generated, but nothing is logged. The DriverManager class sequentially tries every driver that is registered to find the right driver. In some instances, it's possible to load up the realdriver before the p6spy driver, in which case your connections will not get wrapped as the realdriver will "steal" the connection before p6spy sees it. Set the deregisterdrivers property to "true" to cause p6spy to explicitily deregister the realdrivers

- P6Spy was invoked illegally. You probably specified an illegal P6Spy driver in your application configuration. You most likely got this message because you did not specify com.p6spy.engine.spy.SpyDriver as the driver to register in your application configuration. Specifying another class that extends P6SpyDriver is an illegal action.

  FAQ
  - Can I use log4j? Yes. See the Log File Format documentation for more information.
  - Can I use regular expressions to determine what is logged? Yes. See the Common Property File Settings documentation and refer to the stringmatcher section.
  - Once the application is running, can I change the properties and enable the system to use the new properties? Yes. See the Common Property File Settings documentation and refer to the reloadproperties section.
  - Can I use multiple database drivers? Yes. See the Common Property File Settings documentation for more information. Refer specifically to sections regarding realdriver2 and realdriver3.