Unable to create spy.log - stand-alone Java

Help
2008-10-27
2013-04-25
  • oceanbreeze
    oceanbreeze
    2008-10-27

    I am trying to use p6spy (downloaded from the official site of p6spy) for logging sql for a stand-alone Java program (main class that calls data access class).

    I have done the following configurations:

    1. Placed p6spy.jar in the classpath of the project.

    2. Places spy.properties is in the class path. In fact, spy.properties is under the same package as that of Java main class that I call at this time.

    The Java program at this time only queries database using a single select from a single DB2 database table. The Java program successfully executes the query (prepared stamenet, usses iBATIS and Spring) and returns query result and also terminates successfully (without any exception).

    However, the spy.log file does not get created.

    Any help will be appreciated.

    Following is the configuration of spy.properties.

    #################################################################

    # 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.P6LogFactory

    #module.outage=com.p6spy.engine.outage.P6OutageFactory

    #################################################################

    # 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. #

    # #

    #################################################################

    # oracle driver

    # realdriver=oracle.jdbc.driver.OracleDriver

    # mysql Connector/J driver

    # realdriver=com.mysql.jdbc.Driver

    # informix driver

    # realdriver=com.informix.jdbc.IfxDriver

    # ibm db2 driver

    realdriver=COM.ibm.db2.jdbc.app.DB2Driver

    #realdriver=com.p6spy.engine.P6SpyDriver

    # the mysql open source driver

    #realdriver=org.gjt.mm.mysql.Driver

    #specifies another driver to use

    #realdriver2=

    #specifies a third driver to use

    #realdriver3=

    #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 following property to "true" to cause p6spy to

    #explicitily deregister the realdrivers

    deregisterdrivers=true

    ################################################################

    # P6LOG SPECIFIC PROPERTIES #

    ################################################################

    # no properties currently available

    ################################################################

    # EXECUTION THRESHOLD PROPERTIES #

    ################################################################

    # This feature applies to the standard logging of P6Spy. #

    # While the standard logging logs out every statement #

    # regardless of its execution time, this feature puts a time #

    # condition on that logging. Only statements that have taken #

    # longer than the time specified (in milliseconds) will be #

    # logged. This way it is possible to see only statements that #

    # have exceeded some high water mark. #

    # This time is reloadable. #

    #

    # executionthreshold=integer time (milliseconds)

    #

    executionthreshold=

    ################################################################

    # 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 #

    ################################################################

    # filter what is logged

    filter=false

    # comma separated list of tables to include when filtering

    include =

    # comma separated list of tables to exclude when filtering

    exclude =

    # sql expression to evaluate if using regex filtering

    sqlexpression =

    # turn on tracing

    autoflush = true

    # sets the date format using Java's SimpleDateFormat routine

    dateformat=

    #list of categories to explicitly include

    #includecategories=

    includecategories=info,debug,result,batch

    #list of categories to exclude: error, info, batch, debug, statement,

    #commit, rollback and result are valid values

    #excludecategories=info,debug,result,batch

    excludecategories=

    #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=true

    # determines how often should be reloaded in seconds

    reloadpropertiesinterval=60

    #if=true then url must be prefixed with p6spy:

    useprefix=false

    #specifies the appender to use for logging

    #appender=com.p6spy.engine.logging.appender.Log4jLogger

    #appender=com.p6spy.engine.logging.appender.StdoutLogger

    appender=com.p6spy.engine.logging.appender.FileLogger

    # name of logfile to use, note Windows users should make sure to use forward slashes in their pathname (e:/test/spy.log) (used for file logger only)

    #logfile = spy.log

    logfile=c:\\spy.log

    # append to the p6spy log file. if this is set to false the

    # log file is truncated every time. (file logger only)

    append=true

    #The following are for log4j logging only

    #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

    #################################################################

    # DataSource replacement #

    # #

    # Replace the real DataSource class in your application server #

    # configuration with the name com.p6spy.engine.spy.P6DataSource,#

    # then add the JNDI name and class name of the real #

    # DataSource here #

    # #

    # Values set in this item cannot be reloaded using the #

    # reloadproperties variable. Once it is loaded, it remains #

    # in memory until the application is restarted. #

    # #

    #################################################################

    #realdatasource=/RealMySqlDS

    #realdatasourceclass=com.mysql.jdbc.jdbc2.optional.MysqlDataSource

    #################################################################

    # DataSource properties #

    # #

    # If you are using the DataSource support to intercept calls #

    # to a DataSource that requires properties for proper setup, #

    # define those properties here. Use name value pairs, separate #

    # the name and value with a semicolon, and separate the #

    # pairs with commas. #

    # #

    # The example shown here is for mysql #

    # #

    #################################################################

    #realdatasourceproperties=port;3306,serverName;ibmhost,databaseName;mydb

    #################################################################

    # JNDI DataSource lookup #

    # #

    # If you are using the DataSource support outside of an app #

    # server, you will probably need to define the JNDI Context #

    # environment. #

    # #

    # If the P6Spy code will be executing inside an app server then #

    # do not use these properties, and the DataSource lookup will #

    # use the naming context defined by the app server. #

    # #

    # The two standard elements of the naming environment are #

    # jndicontextfactory and jndicontextproviderurl. If you need #

    # additional elements, use the jndicontextcustom property. #

    # You can define multiple properties in jndicontextcustom, #

    # in name value pairs. Separate the name and value with a #

    # semicolon, and separate the pairs with commas. #

    # #

    # The example shown here is for a standalone program running on #

    # a machine that is also running JBoss, so the JDNI context #

    # is configured for JBoss (3.0.4). #

    # #

    #################################################################

    #jndicontextfactory=org.jnp.interfaces.NamingContextFactory

    #jndicontextproviderurl=localhost:1099

    #jndicontextcustom=java.naming.factory.url.pkgs;org.jboss.nameing:org.jnp.interfaces

    #jndicontextfactory=com.ibm.websphere.naming.WsnInitialContextFactory

    #jndicontextproviderurl=iiop://localhost:900

     
    • Ralph Harnden
      Ralph Harnden
      2008-10-27

      Are you sure that your standlone J2SE application is using p6spy?
      What did you specify for system property "jdbc.drivers"?

       
    • oceanbreeze
      oceanbreeze
      2008-10-27

      Yes, you are right. I am not being able to use p6spy at all. My stand-alone Java program looks up user name, password, driver etc. from a separate XML file (myapp.xml) using Spring.

      I was under the impression that if I added p6spy.jar and spy.properties in classpath with proper configurations, I should be able to use p6spy.

      But as you said, it seems that I am not using p6spy. When running the program, the program ignores p6spy totally.

      I have a separate file (myapp.xml) which specifies driver, user name, password etc. My app loads myapp.xml using Spring ApplicationContext and runs it successfully.

      Nowhere in my program I have mentioned spy.properties file. The only thing that I have done is, adding p6spy.jar file and spy.properties in classpath. I thought that will do the magic.

      Is there anything else I need to to use p6spy?

      My driver (Java main) class loads myapp.xml using Spring as:

      ApplicationContext ctx =  new ClassPathXmlApplicationContext("myapp.xml");

      myapp.xml looks like:

      <bean id="transactionManager"
              class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
              <property name="dataSource" ref="dataSource"/>
          </bean>
         
          <!-- JDBC DataSource -->
          <bean
              id="dataSource"
              class="org.springframework.jdbc.datasource.SingleConnectionDataSource"
              destroy-method="destroy"
          >
              <property
                  name="driverClassName"
                  value="COM.ibm.db2.jdbc.app.DB2Driver"
              />
              <property
                  name="url"
                  value="jdbc:db2:DB2E"
              />
              <property
                  name="username"
                  value="myuser"
              />
              <property
                  name="password"
                  value="xxx"
              />
             <property
                  name="suppressClose"
                  value="true"
              />
          </bean>
             
          <!-- iBATIS -->
       
          <bean
              id="sqlMapClient"
              class="org.springframework.orm.ibatis.SqlMapClientFactoryBean"
          >
              <property
                  name="configLocation"
                  value="/org/poc/domain/SqlMapConfig.xml"
              />
              <property name="dataSource">
                  <ref local="dataSource" />
              </property>
          </bean>   

       
      • Ralph Harnden
        Ralph Harnden
        2008-10-27

        My guess would be to change the value you specify in myapp.xml for driverClassName to com.p6spy.engine.spy.P6SpyDriver, which I belive is the P6Spy JDBC Driver class name....

         
    • oceanbreeze
      oceanbreeze
      2008-10-28

      In the myapp.xml file (that I load from Java main class) in for driverClassName, I changed it to use p6spy, instead of DB2 driver. Snippet of code from myapp.xml is as below:

      <property
                  name="driverClassName"
                  value="com.p6spy.engine.P6SpyDriver"
              />
              <property
                  name="url"
                  value="jdbc:db2:DB2E"
              />
              <property
                  name="username"
                  value="abc"
              />
              <property
                  name="password"
                  value="abc"
              />

      Also, note that spy.properties, useprefix=false

      No luck. I do not get spy.log file getting created anywhere. On the contrary, after changing the driver to p6spy in myapp.xml, I am getting an exception being thrown. Following is the exception stacktrace:

      Property 'driverClassName' threw exception; nested exception is java.lang.IllegalStateException: Could not load JDBC driver class [p6spy:com.p6spy.engine.P6SpyDriver]

      p6spy.jar is in classpath. Also, spy.properties in classpath. In fact, spy.properties is in the same package as that of the Java main class (driver class that I am running).

       
    • oceanbreeze
      oceanbreeze
      2008-10-29

      It is working.
      I did not specify the name of the driver correctly - I was missing spy from the package name for P6SpyDriver.

      My incorrect value was: com.p6spy.engine.P6SpyDriver

       
    • Pat
      Pat
      2009-03-13

      yes .. known issue with old docs .. fixed in new docs  that are in svn repo

       
  • Pat
    Pat
    2010-12-29

    Hi there -

    Sorry for the late response. I think I am the only one who has recently done anything with p6spy. I got some basic access to this project from the only original developer I could reach. Unfortunately, not full project admin access. So I have moved over to github for further development.

    You are welcome to help out there by forking and then sending me a pull request.

    Further development at : https://github.com/patmoore/p6spy