Tracking JDBC Calls with WebLogic JDBC Spy

BEA WebLogic JDBC Spy is a wrapper that wraps a BEA WebLogic Type 4 JDBC driver. It logs detailed information about JDBC calls issued by an application and then passes the calls to the wrapped WebLogic Type 4 JDBC driver. It provides the following advantages:

  • Logging is JDBC 1.22-, JDBC 2.0-, and JDBC 3.0-compliant, including support for the JDBC 2.0 Optional Package.
  • Logging works with all BEA WebLogic Type 4 JDBC drivers.
  • Logging is consistent, regardless of which BEA WebLogic Type 4 JDBC driver is used.
  • All parameters and function results for JDBC calls can be logged.
  • Logging can be enabled without changing the application by changing the JDBC connection pool in your WebLogic Server configuration.
Note that the WebLogic JDBC Spy implements standard JDBC APIs only. It does not implement JDBC extensions implemented in other WebLogic Type 4 JDBC drivers. If your application uses JDBC extensions, you may see errors when using the WebLogic JDBC Spy.

 

Configuring WebLogic JDBC Connection Pools for WebLogic JDBC Spy

To use WebLogic JDBC Spy with WebLogic Server, you change the JDBC connection pool configuration to use the WebLogic JDBC Spy. Configuration requirements vary depending on whether you are using the XA or non-XA version of the WebLogic Type 4 JDBC driver in your JDBC connection pool.

 

Configuring WebLogic JDBC Spy with a Non-XA Driver

If your JDBC connection pool uses a non-XA version of a WebLogic Type 4 JDBC driver (weblogic.jdbc.dbms.DbmsDriver) to connect to the database, follow these instructions for modifying your connection pool configuration:

  1. Before you start the server, add WL_HOME/server/lib/wlspy.jar to your CLASSPATH, where WL_HOME is the directory in which you installed the WebLogic Server software, typically C:\bea\weblogic81.
  2. In the WebLogic Server Administration Console or in the config.xml file for your WebLogic domain, modify the JDBC connection pool configuration as follows:

    • Change the Driver Classname (DriverName in config.xml) to the WebLogic JDBC Spy driver class: weblogic.jdbcspy.SpyDriver.
    • Change the URL to the WebLogic JDBC Spy URL and append the URL with properties for WebLogic JDBC Spy. The WebLogic JDBC Spy URL uses the following format: 
      jdbc:spy:{original-url};[key=value]...

      where:

      original-url is the connection URL of the underlying JDBC driver, such as jdbc:bea:DB2://db2host:1433;user=john;password=johnspw

      key-value is a list of WebLogic JDBC Spy logging options. You must include the original driver classname for the load option. See BEA WebLogic JDBC Spy URL Attributes for a list of other options.

      For example: 

      URL="jdbc:spy:{jdbc:bea:DB2://db2host:1433;user=john;password=johnspw};log=(file)d:\spy.log;load=weblogic.jdbc.db2.DB2Driver"

    When you are finished modifying the configuration, the config.xml entry should look similar to the following entry: 

    <JDBCConnectionPool Name="testpool"



    Password="{3DES}0zvizFP1" Targets="myserver"



    InitialCapacity="10" MaxCapacity="10"



    DriverName="weblogic.jdbcspy.SpyDriver"



    Properties="user=john;DatabaseName=wls;PortNumber=1433;serverName=db2host;batchPerformanceWorkaround=True"



    URL="jdbc:spy:{jdbc:bea:DB2://db2host:1433;DatabaseName=wls;user=john};log=(file)d:\spy.log;load=weblogic.jdbc.db2.DB2Driver"



    />
  3. Stop and restart WebLogic Server.

 

Configuring WebLogic JDBC Spy with an XA Driver

If your JDBC connection pool uses an XA version of a WebLogic Type 4 JDBC driver (weblogic.jdbcx.dbms.DbmsDataSource) to connect to the database, follow these instructions for modifying your connection pool configuration:

  1. Before you start the server, add WL_HOME/server/lib/wlspy.jar to your CLASSPATH, where WL_HOME is the directory in which you installed the WebLogic Server software, typically C:\bea\weblogic81.
  2. In the WebLogic Server Administration Console or in the config.xml file for your WebLogic domain, append the WebLogic JDBC Spy options to the connection pool URL. Separate multiple options with a semi-colon.

    In the Administration Console on the JDBCConnectionPool - > Configuration - > General tab, add the spyAttributes to the end of the existing URL. For example: 

    jdbc:bea:DB2://db2host:50000;user=john;spyAttributes=log=(file)d:\spy.log;timestamp=yes

    Alternatively, in the config.xml file, update the properties entry for the JDBC connection pool. For example: 

    <JDBCConnectionPool Name="datasource"



    Password="{3DES}0zvizFP1" Targets="myserver"



    InitialCapacity="10" MaxCapacity="10"



    DriverName="weblogic.jdbcx.db2.DB2DataSource"



    Properties="user=john;DatabaseName=wls;PortNumber=50000;



    ServerName=db2host;batchPerformanceWorkaround=true"



    URL="jdbc:bea:DB2://db2host:50000;user=john;



    spyAttributes=log=(file)d:\spy.log;timestamp=yes"



    SupportsLocalTransaction="true"



    KeepXAConnTillTxComplete="true"



    />
  3. Stop and restart WebLogic Server.

 

BEA WebLogic JDBC Spy URL Attributes

Table D-1 lists the options available for configuring WebLogic JDBC Spy. Use these options as attributes for the spyAttributes property for an XA driver or in the URL for a non-XA driver.

Key-Value Pair

Description

log=System.out Redirects logging to the Java output standard.
log=(file)filename Redirects logging to the file specified by filename. By default, WebLogic JDBC Spy uses the stream specified in DriverManager.setLogStream().
load=classname Loads the driver specified by classname. For example, weblogic.jdbc.db2.DB2Driver.
linelimit=numberofchars The maximum number of characters, specified by numberofchars, that WebLogic JDBC Spy will log on one line. The default is 0 (no maximum limit).
logIS={yes | no | nosingleread} Specifies whether WebLogic JDBC Spy logs activity on InputStream and Reader objects.When logIS=nosingleread, logging on InputStream and Reader objects is active; however logging of the single-byte read InputStream.read() or single-character Reader.read() is suppressed. This avoids the generation of large log files containing single-byte / single character read messages.The default is no.
logTName={yes | no} Specifies whether WebLogic JDBC Spy logs the name of the current thread. The default is no.
timestamp={yes | no} Specifies whether a timestamp should be included on each line of the WebLogic JDBC Spy log.

 

BEA WebLogic JDBC Spy Log Example

The superscript Numbers are note indicators. See the notes following the example for the referenced text. 

All rights reserved.1

registerDriver:driver[className=weblogic.jdbcspy.SpyDriver,



context=null,weblogic.jdbcspy.SpyDriver@1ec49f]2

*Driver.connect(jdbc:spy:{jdbc:bea:sqlserver://QANT:4003;



databaseName=Test;})

trying driver[className=weblogic.jdbcspy.SpyDriver,



context=null,weblogic.jdbcspy.SpyDriver@1ec49f]3

spy>> Driver.connect(String url, Properties info)



spy>> url = jdbc:spy:{jdbc:bea:sqlserver://QANT:4003;databaseName=Test;



OSUser=qauser;OSPassword=null12}



spy>> info = {password=tiger, user=scott}



spy>> OK (Connection[1])4

getConnection returning driver[className=weblogic.jdbcspy.SpyDriver,



context=null,weblogic.jdbcspy.SpyDriver@1ec49f]5

spy>> Connection[1].getWarnings()



spy>> OK6



spy>> Connection[1].createStatement



spy>> OK (Statement[1])7

spy>> Statement[1].executeQuery(String sql)



spy>> sql = select empno,ename,job from emp where empno=7369



spy>> OK (ResultSet[1])8

spy>> ResultSet[1].getMetaData()



spy>> OK (ResultSetMetaData[1])9

spy>> ResultSetMetaData[1].getColumnCount()



spy>> OK (3)10

spy>> ResultSetMetaData[1].getColumnLabel(int column)



spy>> column = 1



spy>> OK (EMPNO)11

spy>> ResultSetMetaData[1].getColumnLabel(int column)



spy>> column = 2



spy>> OK (ENAME)12

spy>> ResultSetMetaData[1].getColumnLabel(int column)



spy>> column = 3



spy>> OK (JOB)13

spy>> ResultSet[1].next()



spy>> OK (true)14

spy>> ResultSet[1].getString(int columnIndex)



spy>> columnIndex = 1



spy>> OK (7369)15

spy>> ResultSet[1].getString(int columnIndex)



spy>> columnIndex = 2



spy>> OK (SMITH)16

spy>> ResultSet[1].getString(int columnIndex)



spy>> columnIndex = 3



spy>> OK (CLERK)17

spy>> ResultSet[1].next()



spy>> OK (false)18

spy>> ResultSet[1].close()



spy>> OK19

spy>> Connection[1].close()



spy>> OK20

NOTES:

1 The BEA WebLogic JDBC Spy driver is registered. The spy>> prefix indicates that this line has been logged by BEA WebLogic JDBC Spy.

2 The JDBC Driver Manager logs a message each time a JDBC driver is registered.

3 This is the logging of the JDBC Driver Manager. It logs a message each time a JDBC application makes a connection.

4 The application connects with the specified URL. The User Name and Password are specified using properties.

5 This is the logging of the JDBC Driver Manager. It logs a message each time a successful connection is made.

6 The application checks to see if there are any warnings. In this example, no warnings are present.

7, 8 The statement "select empno,ename,job from emp where empno=7369" is created.

9, 10, 11, 12, 13 Some metadata is requested.

14, 15, 16, 17 The first row is fetched and its data retrieved.

18 The application attempts to fetch the second row, but the database returned only one row for this query.

19 After fetching all data, the result set is closed.

20 The application finishes and disconnects.

Back to Top Previous Next