D Tracking JDBC Calls with WebLogic JDBC Spy

WebLogic JDBC Spy is a wrapper that wraps a Oracle Type 4 JDBC driver. It logs detailed information about JDBC calls issued by an application and then passes the calls to the wrapped Oracle Type 4 JDBC driver. You can use the information in the logs to help troubleshoot problems in your application. WebLogic JDBC Spy provides the following advantages:

Configuring WebLogic JDBC Data Sources for WebLogic JDBC Spy

To use WebLogic JDBC Spy with WebLogic Server, you add JDBC Spy attributes to the end of the URL in the JDBC data source configuration.

Note:

The wlspy.jar has been deprecated. This functionality is now built into the driver jar files and can be enabled by setting the SpyAttributes connection parameter.

Follow these instructions for modifying your data source configuration:

  1. In the WebLogic Server Administration Console or in the configuration file for your WebLogic domain, append the WebLogic JDBC Spy options to the data source URL. Enclose all JDBC Spy options in one set of parentheses; separate multiple options with a semi-colon.

    In the Administration Console on the Domain Configurations > Data Sources, select the particular Data Source that you want to be spy enabled. Open the Connection Pool tab and add the spyAttributes to the end of the existing URL. For example:

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

    Alternatively, in the datasource_name-jdbc.xml file, update the URL in the JDBC data source entry. For example:

    <jdbc-driver-params>
    <url>jdbc:weblogic:db2://bangpcdb2:50000;spyAttributes=(log=(file)db2-spy.out;load=weblogic.jdbc.db2.DB2Driver;timestamp=yes)
    </url>
        <driver-name>weblogic.jdbc.db2.DB2Driver</driver-name>
        <properties>
          <property>
            <name>user</name>
            <value>john</value>
          </property>
          <property>
            <name>portNumber</name>
            <value>50000</value>
          </property>
          <property>
            <name>databaseName</name>
            <value>wls</value>
          </property>
          <property>
            <name>serverName</name>
            <value>db2host</value>
          </property>
          <property>
            <name>batchPerformanceWorkaround</name>
            <value>true</value>
          </property>
        </properties>
        <password-encrypted>{3DES}hqKps8ozo98=</password-encrypted>
      </jdbc-driver-params>
    
  2. Stop and restart WebLogic Server.

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.

Table D-1 WebLogic JDBC Spy URL Attributes

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 or no or 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 or no}

Specifies whether WebLogic JDBC Spy logs the name of the current thread. The default is no.

timestamp={yes or no}

Specifies whether a timestamp should be included on each line of the WebLogic JDBC Spy log.


WebLogic JDBC Spy Log Example

See the notes following the example for the referenced text.

Example D-1 WebLogic JDBC Spy Log Example

All rights reserved.Foot 1 
registerDriver:driver[className=weblogic.jdbcspy.SpyDriver,
context=null,weblogic.jdbcspy.SpyDriver@1ec49f]Foot 2 
*Driver.connect(jdbc:spy:{jdbc:weblogic:sqlserver://QANT:4003;
databaseName=Test;})
trying driver[className=weblogic.jdbcspy.SpyDriver,
context=null,weblogic.jdbcspy.SpyDriver@1ec49f]Foot 3 
spy>> Driver.connect(String url, Properties info)
spy>> url = jdbc:spy:{jdbc:weblogic:sqlserver://QANT:4003;databaseName=Test;
OSUser=qauser;OSPassword=null12}
spy>> info = {password=tiger, user=scott}
spy>> OK (Connection[1])Foot 4 
getConnection returning driver[className=weblogic.jdbcspy.SpyDriver,
context=null,weblogic.jdbcspy.SpyDriver@1ec49f]Foot 5 
spy>> Connection[1].getWarnings()
spy>> OKFoot 6 
spy>> Connection[1].createStatement
spy>> OK (Statement[1])Foot 7 
spy>> Statement[1].executeQuery(String sql)
spy>> sql = select empno,ename,job from emp where empno=7369
spy>> OK (ResultSet[1])Foot 8 
spy>> ResultSet[1].getMetaData()
spy>> OK (ResultSetMetaData[1])Foot 9 
spy>> ResultSetMetaData[1].getColumnCount()
spy>> OK (3)Foot 10 
spy>> ResultSetMetaData[1].getColumnLabel(int column)
spy>> column = 1
spy>> OK (EMPNO)Foot 11 
spy>> ResultSetMetaData[1].getColumnLabel(int column)
spy>> column = 2
spy>> OK (ENAME)Foot 12 
spy>> ResultSetMetaData[1].getColumnLabel(int column)
spy>> column = 3
spy>> OK (JOB)Foot 13 
spy>> ResultSet[1].next()
spy>> OK (true)Foot 14 
spy>> ResultSet[1].getString(int columnIndex)
spy>> columnIndex = 1
spy>> OK (7369)Foot 15 
spy>> ResultSet[1].getString(int columnIndex)
spy>> columnIndex = 2
spy>> OK (SMITH)Foot 16 
spy>> ResultSet[1].getString(int columnIndex)
spy>> columnIndex = 3
spy>> OK (CLERK)Foot 17 
spy>> ResultSet[1].next()
spy>> OK (false)Foot 18 
spy>> ResultSet[1].close()
spy>> OKFoot 19 
spy>> Connection[1].close()
spy>> OKFoot 20 


Footnote Legend

Footnote 1: The WebLogic JDBC Spy driver is registered. The spy>> prefix indicates that this line has been logged by WebLogic JDBC Spy.
Footnote 2: The JDBC Driver Manager logs a message each time a JDBC driver is registered.
Footnote 3: This is the logging of the JDBC Driver Manager. It logs a message each time a JDBC application makes a connection.
Footnote 4: The application connects with the specified URL. The User Name and Password are specified using properties.
Footnote 5: This is the logging of the JDBC Driver Manager. It logs a message each time a successful connection is made.
Footnote 6: The application checks to see if there are any warnings. In this example, no warnings are present.
Footnote 7: The statement "select empno,ename,job from emp where empno=7369" is created.
Footnote 8: The statement "select empno,ename,job from emp where empno=7369" is created.
Footnote 9: Some metadata is requested.
Footnote 10: Some metadata is requested.
Footnote 11: Some metadata is requested.
Footnote 12: Some metadata is requested.
Footnote 13: Some metadata is requested.
Footnote 14: The first row is fetched and its data retrieved.
Footnote 15: The first row is fetched and its data retrieved.
Footnote 16: The first row is fetched and its data retrieved.
Footnote 17: The first row is fetched and its data retrieved.
Footnote 18: The application attempts to fetch the second row, but the database returned only one row for this query.
Footnote 19: After fetching all data, the result set is closed.
Footnote 20: The application finishes and disconnects.