General Installation Instructions

The P6Spy software is very easy to install.  There are explicit step-by-step installation instructions for many application servers (see the table of contents). If you create instructions for other servers, please send us a copy and we will include them in the documentation. Once the software is installed, P6Spy offers many options to let you tailor the software to your needs. See the property file settings for details.

For those of you who have been using the closed-source version of p6spy, there is only one significant change. In the previous release the package name was "com.p6.engine.spy.P6SpyDriver" in this release it is "com.p6spy.engine.spy.P6SpyDriver". Notice "com.p6" has been changed to "com.p6spy". This means a small adjustment to your configuration file. This should not change again.

  1. Unzip the distribution. Unjar the p6spy-install.jar file.  This contains two files: p6spy.jar and spy.properties
  2. Put the p6spy.jar file in your classpath. Either copy the p6spy.jar file into your ext direction (if you are using and EJB server it probably has its own ext directory, if you are not, then you probably want to use the JDK ext directory which is JDK_HOME/jre/lib/ext) or add p6spy.jar to your classpath.  Remember that if you are adding p6spy.jar to your classpath, you must reference the jar filename directly.  In Windows, for example, it might look like: CLASSPATH=c:\p6spy\p6spy.jar.  Note that if you are using an EJB server, adding the p6spy.jar file to your classpath may not work.  Some EJB servers ignore the classpath and set their own when they start up.
  3. Put spy.properties and spy.forms in your classpath. Copy the spy.properties and spy.form files into a directory listed in your classpath.  For example, if your classpath is C:\p6spy\p6spy.jar;c:\java you can copy it to the java directory.  Unlike jar files, you do not directly reference your property file in the classpath.
  4. Tell your application or server to use the P6Spy database driver. Modify your server settings or application settings to tell your application to use P6Spy as your database driver, which is: com.p6spy.engine.spy.P6SpyDriver    This does not impact your application, it simply allows P6Spy to read in the database statements before they reach your database driver.  Since the P6Spy driver passes through all requests to your real driver, you leave all of the other configuration information, such as the connection address, user, password, etc. the same.
  5. Tell P6Spy about your real database driver.  Modify the spy.properties file and change the realdriver line to reflect your real database driver.  For example: realdriver = oracle.jdbc.driver.OracleDriver
  6. Run your application.  A log file will be generated in the starting directory of your application (you can change this, see the property file section of the documentation).  If you can't find the log file, try searching for spy.log
  7. View the log file.  The log file contains a list of all of the database statements that are executed.

JBoss Installation

These instructions have been tested with various Jboss versions ranging from Jboss 2.2.2-2.4.4 with Tomcat 3.2.2-4.0.1 integration.

  1. Unzip the distribution. Unjar the p6spy-install.jar file.  This contains the files: p6spy.jar, spy.properties, and spy.forms
  2. Put the p6spy.jar file in your ext directory. For example: C:\JBoss-2.2.2_Tomcat-3.2.2\jboss\lib\ext
  3. Put spy.properties and spy.forms in your db directory (which is part of the JBoss classpath). For example: C:\JBoss-2.2.2_Tomcat-3.2.2\jboss\db
  4. Tell JBoss to use the P6Spy driver.  Modify the jboss.jcml file to use the P6 driver.  Make sure to modify the correct file. In the case of the integrated tomcat solution, you should modify the jboss.jcml file in the Tomcat conf directory. You should remove your existing driver from the drivers attribute and replace that driver with the P6Spy driver. This looks like:
  5. <mbean code="org.jboss.jdbc.JdbcProvider" name="DefaultDomain:service=JdbcProvider">
    <attribute name="Drivers">com.p6spy.engine.spy.P6SpyDriver</attribute>
    </mbean>

  6. Tell P6Spy about your real database driver.  Modify the spy.properties file and change the realdriver line to reflect your real database driver.  For example: realdriver = oracle.jdbc.driver.OracleDriver
  7. Installation is complete.  There is nothing else to change. You leave your connect string, username, password and other attributes exactly as they were before you installed P6Spy, the P6Spy driver does all of the work required to pass this information along to your real database driver.
  8. Run your application.  A spy.log file will be generated in the bin directory of the JBoss server such as: C:\JBoss-2.2.2_Tomcat-3.2.2\jboss\bin
  9. View the log file.  The log file contains a list of all of the database statements that are executed.

Orion Installation

  1. Unzip the distribution. Unjar the p6spy-install.jar file.  This contains the files: p6spy.jar, spy.properties, and spy.forms
  2. Put p6spy.jar, spy.properties, and spy.forms in your [orion-home]\lib directory. For example: c:\orion\lib
  3. Tell Orion to use the P6Spy driver.  Modify [orion-home]\config\data-sources.xml to use the P6 driver.  In the data-sources.xml file change connection-driver to specify the p6spy driver instead of the database driver you are using. For example:
  4. <data-source
    class="com.evermind.sql.DriverManagerDataSource"
    name="Hypersonic"
    location="jdbc/HypersonicCoreDS"
    xa-location="jdbc/xa/HypersonicXADS"
    ejb-location="jdbc/HypersonicDS"
    connection-driver="com.p6spy.engine.spy.P6SpyDriver"
    username="sa"
    password=""
    url="jdbc:HypersonicSQL:./database/defaultdb"
    inactivity-timeout="30"
    />

  5. Tell P6Spy about your real database driver.  Modify the spy.properties file and change the realdriver line to reflect your real database driver.  This is the value that was previously specified in connection-driver before you changed it in step 3. For example, if you were using Hypersonic (the default database) this would be: realdriver = org.hsql.jdbcDriver
  6. Installation is complete.  There is nothing else to change. You leave your connect string, username, password and other attributes exactly as they were before you installed P6Spy, the P6Spy driver does all of the work required to pass this information along to your real database driver.
  7. Run your application.  In the example installation, Orion will not be able to find spy.properties or spy.forms because of the classpath that Orion uses. The easiest way to overcome this is to use the -D option to tell Orion where the property file resides. For example, run Orion using java -Dp6.home=c:\orion\orion\lib -jar orion.jar

    A spy.log file will be generated in the [orion-home] directory. If for some reason the file does not appear in that directory, search for spy.log on your drive. You can change the location of this file by specifying a location using the property file (see Property File Settings).
  8. View the log file.  The log file contains a list of all of the database statements that are executed.

Tomcat Installation

These instructions have been tested with Tomcat 3.x.  Please note that since you typically write or otherwise obtain your own connection pool when using Tomcat, these instructions will differ by application.

  1. Unzip the distribution. Unjar the p6spy-install.jar file.  This contains the files: p6spy.jar, spy.properties, and spy.forms. Copy them to the lib\ directory of your web application. For example: C:\jakarta-tomcat-3.2.1\webapps\p6\WEB-INF\lib
  2. Tell your Tomcat application to use P6Spy as the database driver. Tomcat applications obtain their JDBC connection in a variety of ways. Most applications have the database driver name externalized as a configuration parameter. For example, in your web.xml file (C:\jakarta-tomcat-3.2.1\webapps\p6\WEB-INF\web.xml) you may have something like this:

    <init-param><param-name>databaseDriver</param-name><param-value>org.gjt.mm.mysql.Driver</param-value></init-param>

    In this case the "databaseDriver" parameter has a value of "org.gjt.mm.mysql.Driver", which is the MySQL driver. Wherever this configuration information is stored, you need to change the driver name ("org.gjt.mm.mysql.Driver" in our example) to com.p6spy.engine.spy.P6SpyDriver such as:

    <init-param><param-name>databaseDriver</param-name><param-value>com.p6spy.engine.spy.P6SpyDriver</param-value></init-param>
  3. Enable Tomcat to find spy.properties and spy.forms. While all of the .jar files in your webapps lib directory are, by default, included in Tomcat's classpath, the lib directory itself is not, which means Tomcat will not be able to find your spy.properties or spy.forms files, which will cause an error to occur. All this means is that you have to include spy.properties and spy.forms in the Tomcat classpath. The simplest way to do this is to use the TOMCAT_OPTS environment variable to specify the location of the spy.properties file. For example:

    SET TOMCAT_OPTS=-Dp6.home=C:\jakarta-tomcat-3.2.1\webapps\p6\WEB-INF\lib

  4. Change the spy.properties file to reference your database. You must tell P6Spy about your real database driver. The realdriver property is, by default, set to Oracle, such as: realdriver = oracle.jdbc.driver.OracleDriver, change this to reflect your database. In our previous example, where we were using MySQL, this would be changed to: realdriver = org.gjt.mm.mysql.Driver
  5. Run your application and view the logs. By default the spy.log file is written to the place you start Tomcat, such as C:\jakarta-tomcat-3.2.1\bin - you can change the location of this file by specifying a location using the property file (see Property File Settings).

JOnAS Installation

(Contributed by François Exertier, JOnAS Opensource EJB Server team)

These instructions have been tested with JOnAS 2.3.x.

  1. Unzip the distribution. Unjar the p6spy-install.jar file.  This contains the files: p6spy.jar, spy.properties, and spy.forms
  2. Put the p6spy.jar file in the classpath of the JOnAS EJB server. For example, if p6spy.jar is in the /opt/Provider6 directory, you may add /opt/Provider6/p6psy.jar to the XTRA_CLASSPATH environment variable (or directly edit the config_env script)
  3. Put spy.properties and spy.forms in a directory which is part of your classpath. The best may be to put it in the same directory as your jndi.properties file
  4. Tell JOnAS to use the P6Spy database driver.  Database access configuration within JOnAS is described in a DataSource property file, e.g. Oracle1.properties (see the JOnAS Bean Programmer's Guide for more details). Modify the <datasource>.properties file by replacing the database driver classname by the P6Psy database driver class name in the datasource.classname property. For example the Oracle1.properties file delivered with JOnAS should be updated by replacing oracle.jdbc.driver.OracleDriver by com.p6spy.engine.spy.P6SpyDriver, it then looks like
  5. datasource.name   jdbc_1
    datasource.description  "Standard jdbc driver for Oracle"
    datasource.url   jdbc:oracle:thin:scott/tiger@maltes:1521:ORA1
    datasource.classname com.p6spy.engine.spy.P6SpyDriver
    ...
  6. Tell P6Spy about your real database driver.  Modify the spy.properties file and change the realdriver line to reflect your real database driver.  In our example: realdriver = oracle.jdbc.driver.OracleDriver
  7. Installation is complete.  There is nothing else to change. You leave your other datasource properties, e.g. name, url, username, password and so on exactly as they were before you installed P6Spy, the P6Spy driver does all of the work required to pass this information along to your real database driver
  8. Run your application.  Run EJBServer. A spy.log file will be generated in the launching directory
  9. View the log file.  The log file contains a list of all of the database statements that are executed. You can change the location of this file and the nature of its content by using the spy property file (see Property File Settings)

WebLogic Portal 4.0 Installation

(Contributed by Philip Ogren, BEA)

These instructions have been tested with WebLogic Server 6.1 (Service Pack 2) with WebLogic Portal 4.0 (Service Pack 1) running on Windows using:

  • WebLogic jDriver for Oracle
  • Oracle thin driver for 8.1.7
  • WebLogic jDriver for SQL Server
  • Sybase jConnect-5_2 driver.
The default Stockportal ('Avitek') application is used in this example. Cloudscape does not work with P6Spy and WebLogic Portal. (Note, Cloudscape does work with P6Spy when using the J2EE reference implementation server - the following suggestion [not yet tested] was submitted by Adrian Fletcher: "I may be wrong but I suspect the problem with Cloudscape and Weblogic is that both drivers end up registered against the same URL. I seem to remember that the best trick for avoiding this is mangling the URL. If you instruct users to prepend "p6spy:" to their url and then remove it in your wrapper, it should work in more cases." You can do this with the useprefix option. If anyone has an opportunity to validate this, please drop me a line with updated information.)
  1. Unzip the distribution. Unjar the p6spy-install.jar file to a temporary directory. This contains the files: p6spy.jar, spy.properties, and spy.forms
  2. Copy p6spy.jar to <bea-home>\wlportal4.0\lib\ext.
  3. Modify set-environment.bat. Open <bea-home>\wlportal4.0\bin\win32\set-environment.bat for edit.
    Add ;%P13N_DIR%\lib\ext\p6spy.jar to EXT_CLASSPATH near the bottom of the file. Be sure there are no trailing spaces on the end of this line.
  4. Copy spy.properties and spy.forms to <bea-home>\wlportal4.0\config\portalDomain or to your application's domain directory.
  5. Open spy.properties for edit. Set realdriver=weblogic.jdbc.oci.Driver - or to the driver you are using. P6Spy does not work with Cloudscape!
  6. Open <bea-home>\wlportal4.0\config\portalDomain\startPortal.bat for edit. Add ;%P13N_DIR%\config\portalDomain to the classpath near the bottom of the file. This puts spy.properties and spy.forms in the classpath. Be sure there are no trailing spaces on the end of this line.
  7. Tell WebLogic Portal to use the P6Spy driver.   This can be done through the Administration Console or by modifying <bea-home>\wlportal4.0\config\portalDomain\config.xml. If using the Administration Console, start WebLogic Portal using <bea-home>\wlportal4.0\config\portalDomain\startPortal.bat. Start the Administration Console tool by navigating to http://<your-host>:7501/console. Navigate to the '<your-domain>->Services->JDBC->Connection Pools' node and set the Driver Classname for each Connection Pool to com.p6spy.engine.spy.P6SpyDriver. If a RDBMS Security Realm is being used, this should also be monitored with P6Spy. Using the Administration Console, navigate to the <your-domain>->Security->Realms node. Click on the security realm under this node and observe the configuration information on the right hand side. Click on the 'Database' tab and set the 'Driver' parameter to com.p6spy.engine.spy.P6SpyDriver. In this example, modifications to the 'commercePool', 'dataSyncPool' and 'wlcsRealm' should be made.
  8. Shut down WebLogic Portal using the Administration Console. This can be accomplished by right clicking on the '<your-domain>->Servers-><your-server>' node and selecting 'Stop this Server' in the popup menu.
  9. Start WebLogic Portal with the script <bea-home>\wlportal4.0\config\portalDomain\startPortal.bat.
  10. Observe log messages in the file specified by the logfile property set in spy.properties. The default location is <bea-home>\wlportal4.0.

WebLogic Server 6.1 Installation

(Contributed by Philip Ogren, BEA)

The instructions for installing P6Spy with WebLogic Server differ only slightly from the instructions for installation with WebLogic Portal.

  1. Unzip the distribution. Unjar the p6spy-install.jar file to a temporary directory. This contains the files: p6spy.jar, spy.properties, and spy.forms
  2. Copy p6spy.jar to <bea-home>\wlserver6.1\lib\ext.
  3. Copy spy.properties and spy.forms to <bea-home>\wlserver6.1\config\<your-domain> .
  4. Open spy.properties for edit. Set realdriver=weblogic.jdbc.oci.Driver - or to the driver you are using. P6Spy does not work with Cloudscape!
  5. Open <bea-home>\wlserver6.1\config\<your-domain>\startWebLogic.cmd for edit. Add ;.\ext\p6spy.jar;.\config\<your-domain> to the classpath near the bottom of the file. This puts p6spy.jar, spy.forms and spy.properties in the classpath. Be sure there are no trailing spaces on the end of this line.
  6. Tell WebLogic Server to use the P6Spy driver.   This can be done through the Administration Console or by modifying <bea-home>\wlserver6.1\config\<your-domain>\config.xml. If using the Administration Console, start WebLogic Portal using <bea-home>\wlportal4.0\config\portalDomain\startPortal.bat. Start the Administration Console tool by navigating to http://<your-host>:7501/console. Navigate to the '<your-domain>->Services->JDBC->Connection Pools' node and set the Driver Classname for each Connection Pool to com.p6spy.engine.spy.P6SpyDriver. If a RDBMS Security Realm is being used, this should also be monitored with P6Spy. Using the Administration Console, navigate to the <your-domain>->Security->Realms node. Click on the security realm under this node and observe the configuration information on the right hand side. Click on the 'Database' tab and set the 'Driver' parameter to com.p6spy.engine.spy.P6SpyDriver.
  7. Shut down WebLogic Server using the Administration Console. This can be accomplished by right clicking on the '<your-domain>->Servers-><your-server>' node and selecting 'Stop this Server' in the popup menu.
  8. Start WebLogic Server with the script <bea-home>\wlserver6.1\config\<your-domain>\startWebLogic.cmd.
  9. Observe log messages in the file specified by the logfile property set in spy.properties. The default location is <bea-home>\wlserver6.1.

WebLogic 5.1 Instructions

(Posted by Richard Delbert on April 10, 2002 on TheServerSide.com)

These instructions have been tested with WebLogic 5.1

  1. Unzip the distribution. Unjar the p6spy-install.jar file.  This contains the files: p6spy.jar, spy.properties, and spy.forms
  2. Edit startWeblogic.sh, put the p6spy.jar file in the JAVA_CLASSPATH.
  3. Edit startWeblogic.sh, put spy.properties in the JAVA_CLASSPATH.
  4. Edit startWeblogic.sh, put spy.forms in the JAVA_CLASSPATH.
  5. Edit weblogic.properties (connection pool). Replace the JDBC class driver with com.p6spy.engine.spy.P6SpyDriver.
  6. Tell P6Spy about your real database driver.  Modify the spy.properties file and change the realdriver line to reflect your real database driver.  In our example: realdriver = oracle.jdbc.driver.OracleDriver
  7. Installation is complete.
  8. Run your application.  A spy.log file will be created in the weblogic home directory.
  9. View the log file.  The log file contains a list of all of the database statements that are executed. You can change the location of this file and the nature of its content by using the spy property file (see Property File Settings)

ATG Installation

These instructions have been tested with ATG Dynamo 5.1.

  1. Unzip the distribution. Unjar the p6spy-install.jar file.  This contains the files: p6spy.jar, spy.properties, and spy.forms. Copy them to a directory. For this example, we will copy both files to c:\p6spy
  2. Start the ATG Dynamo server. Start the ATG application server and your database.
  3. Change the ATG Dynamo server to include both files in the classpath.
    • run the admin tool (in this example we will assume you are running ATG on your own machine, so we will use localhost as the machine name), go to http://localhost:8830 (the default user/pass is admin/admin)
    • click "configuration manager"
    • click "default configuration"
    • click "system paths"
    • in the section that says, "extend Dynamo's classpath" enter the full path of the p6 spy jar file, in our example this is: c:\p6spy\p6spy.jar;c:\p6spy
    • click the "append to classpath" button
    • confirm the new classpath value is correctly displayed in the extend Dynamo's classpath section
    • restart dynamo (you can do this by going to http://localhost:8830 and using the restart dynamo button at the bottom of the page)

  4. Change the spy.properties file to reference your database. You must tell P6Spy about your real database driver. The realdriver property is, by default, set to Oracle, such as: realdriver = oracle.jdbc.driver.OracleDriver, change this to reflect your database, if you are using the ATG demo this would be realdriver = solid.jdbc.SolidDriver
  5. Change ATG to use the P6Spy driver
    • run the admin tool by going to http://localhost:8830
    • click "configuration manager"
    • click "default configuration"
    • click "connection pools"
    • click "JTDataSource"
    • click "JDBC 1.x/2.x driver"
    • select the radio button "custom JDBC information"
    • enter the information exactly as you would to connect to your database, except for the driver box, which should be set to com.p6spy.engine.spy.P6SpyDriver
    • if you are using the ATG demonstration, the example setup would be:
    • URL: jdbc:solid://localhost:1313
      driver: com.p6spy.engine.spy.P6SpyDriver
      database: (blank)
      server: localhost:1313
      server name: (blank)
      user name: admin
      password: admin
      confirm password: admin

    • click the "try to connect" button, confirm ATG comes back with a success message (this appears at the top of the page)
    • if ATG comes back with a success message, click the "apply changes" button
    • restart dynamo (you can do this by going to http://localhost:8830 and using the restart dynamo button at the bottom of the page)
  6. Run your application and view the logs. By default the spy.log file is written to C:\ATG\Dynamo5.1\home - you can change the location of this file by specifying a location using the property file (see Property File Settings).

iPlanet Instructions

(Contributed by Michael Sgroi)

The following procedure has been tested against iPlanet 6.0 service pack 3 as of April 2002. It should work for any release of iPlanet 6.0.

  1. Unzip the distribution. Unjar the p6spy-install.jar file into a directory, call it <Spy Home>. This contains the files: p6spy.jar, spy.properties, and spy.forms
  2. Add P6Spy to your classpath. To put the p6spy.jar file in your classpath, run the command line executable "kregedit". Under Software\iPlanet, 6.0, Java, ClassPath, modify the entry adding <Spy Home> to the end of the classpath.
  3. Configure iPlanet. To tell your application or server to use the P6Spy database driver, run the command line executable "jdbcsetup". Add a new 3rd Party JDBC Configuration as follows...

    Driver Identifier: spy
    Driver Classname: com.p6spy.engine.spy.P6SpyDriver
    Driver Classpath: <Spy Home>\p6spy.jar
  4. Configure P6SPy. To tell P6Spy about your real database driver, register your DataSource by running the follwoing command line executable...

    iasdeploy regdatasource -user <iPlanet admin username> -password <iPlanet admin password> -host localhost -port <iPlanet Admin Port> <Config file name>

    where the <Config file name> is the full path to the file containing your DataSource configuration. It should look roughly like the following...

    <ias-resource>

    <resource>

    <jndi-name>jdbc/yourapp/ora-type4-spy</jndi-name>

    <jdbc>

    <driver-type>spy</driver-type>

    <database-url>jdbc:oracle:thin:@localhost:1521:orcl</database-url>

    <username>user</username>

    <password>password</password>

    </jdbc>

    </resource>

    </ias-resource>

    For more details parameters passed to iasdeploy and the format of the
    <Config file name> see the iPlanet docs

Known Issues

  1. Non-standard (driver specific) JDBC methods are not supported.  Many drivers provide methods that expose driver specific, non-standard functionality. Most developers do not use these features, but in the event that you do, they are not supported by P6Spy. For example, the MySQL JDBC drivers allow you to call the auto-increment function like so: ((org.gjt.mm.mysql.PreparedStatement)stmt).getLastInsertId(); that cast will fail when the P6SpyDriver is in place, since the class it will be trying to cast against is the P6Spy Driver itself, which does not expose that driver specific method. There are ways to get around this limitation, for example by using Dynamic Proxies instead of our current technique. However, these come at a performance cost, that while maybe slight, we have decided to avoid at this time.

Troubleshooting

  1. Application server does not start properly.  This is probably because the p6spy.jar, spy.properties, or spy.forms is not in the classpath.  In the case of JBoss look at the JBoss log file for clues:

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

    If you see a message like the above message, that most likely means the jar file cannot be found.  Otherwise you may see that the driver is loaded, but the message will say the spy.properties file is not found, and will list all of the directories it looked in for that file. If that is the case, move the spy.properties file to one of the directories listed.

  2. You see a message stating the spy.properties/spy.forms 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. If you still have problems, try using the -Dp6.home option to specify where you have placed the spy.properties file. E.g. java -Dp6.home=c:\orion\lib
  3. The spy.log file is not written.  Try searching your entire drive for spy.log, you may find it in a strange place. Remember, you can specify the default location using the spy.properties property file.
  4. The spy.log file is written, but nothing is being 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, a very common mistake is to have your real driver registered elsewhere. To avoid this, 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. This will cause P6Spy to fail if the prefix is not present and will assure the configuration is as expected.
  5. 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, you specified another class that extends P6SpyDriver, which is illegal.