![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
DataDirect Spy supports JDBC driver tracing, which can be useful for solving customer problems. IBM InfoSphere Change Data Capture engines use DataDirect JDBC driver for JDBC database access for Oracle, SQL Server, and Sybase only.
DataDirect Spy is a tool for tracing calls in running programs. The tool passes calls issued by an application to the underlying DataDirect JDBC driver and logs detailed information about those calls. The results passed back from the database to the application are also logged. The resulting log can be used to troubleshoot issues, if a problem is suspected in the communication between the driver and the database (i.e., application-to-driver problems are ruled out).
To follow along, you should have basic knowledge about the following:
- Windows®
- UNIX®
- Using the IBM InfoSphere CDC Management Console
- Using the IBM InfoSphere CDC for Oracle, Sybase, SQL Server
The JDBC API lets you see warnings and exceptions generated by the database and by
the JDBC driver. A SQLException object contains a description of the error, the
X/Open SQLState, and the driver vendor's error code number. To see exceptions, you
can use a catch block to print them. Warnings are a SQLException subclass that
deals with database access warnings. Warnings do not stop the execution of an
application; they simply alert the user that something did not happen as planned,
such as a privilege not revoked as requested. Warnings can be reported on a
Connection object, a Statement object (including PreparedStatement and
CallableStatement objects), and a ResultSet object. Warnings can be retrieved on
these objects using the getWarnings method.
DataDirect Technologies makes it easy to debug and troubleshoot your JDBC applications by offering a JDBC Software Developer's Kit (SDK) that provides the DataDirect Spy for JDBC traces and calls in running applications. This utility passes calls issued by an application to the underlying DataDirect JDBC driver and logs detailed call information. The results passed back from the database to the application are also logged. DataDirect Spy is used with running applications to trace the JDBC calls made by that application.
DataDirect Spy provides the following advantages:
- Logging is JDBC 4.0-compliant.
- Logging is consistent, regardless of the DataDirect Connect for JDBC driver used.
- All parameters and function results for JDBC calls can be logged.
- Logging works with all DataDirect Connect for JDBC drivers.
- Logging can be enabled without changing the application.
DataDirect Spy supports the following attributes.
Table 1. Attributes of DataDirect Spy with descriptions
| Attribute | Description |
|---|---|
| log=System.out | Directs logging to the Java™ output standard: System.out. |
| log=(file)filename | Redirects logging to the file specified by filename. |
| linelimit=numberofchars | Sets the maximum number of characters DataDirect Spy will log on any one line. The default is 0 (there is no maximum limit). |
| logIS={yes | no | nosingleread} | Specifies whether DataDirect 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 to prevent generating large log files that contain single-byte or single character read messages. The default is no. |
| logTName={yes | no} | Specifies whether DataDirect Spy logs the name of the current thread. The default is no. |
| timestamp={yes | no} | Specifies whether a timestamp is included on each line of the DataDirect Spy log. The default is no. |
Steps of enabling DataDirect Spy trace
To enable tracing, it's assumed that you have an instance created and connected through the data store in the InfoSphere CDC Management Console. The following steps will show you how to enable DataDirect Spy trace for JDBC with InfoSphere CDC:
- Add a system parameter to connected data store. Select
Connected datastore and Click File >
DataStore > Properties, as shown below.
Figure 1. Invoke system parameter option to add system parameter to connected data store
- Click the System Parameters tab, then click
Add to add parameter for enabling the DataDirect Spy
trace for JDBC, as shown below.
Figure 2. Add required system parameter to connected data store
- Provide
datadirect_spy_attributesas parameter name and set the value tolog=(file)filename;logTName=yes;timestamp=yes, as shown in Figure 3. The system parameterdatadirect_spy_attributes, available since V6.3, is undocumented. Enabling the JDBC Spy attribute to IBM InfoSphere CDC does not depend on database version or platform.
Figure 3. Provide value with parameter name
Alternatively, you can use the IBM InfoSphere Change Data Capture CLI tool dmset to set this parameter for a command-line instance.
For example:
dmset -I (Instance-name) property_name[=[property_value]] - Restart CDC instance and start replication.
Sample Spy trace log collected by InfoSphere CDC
You could see a trace log as values set for the datadirect_spy_attributes system parameter. See the partial content of the trace log in Listing 1.
Listing 1. Partial content of spyTrace.log
spy>> Connection[1].getMetaData()
spy>> OK (DatabaseMetaData[1])
spy>> DatabaseMetaData[1].getURL()
spy>> OK (jdbc:datamirror:oracle:;CATALOGOPTIONS=0;CONNECTIONRETRYDELAY=1;
BULKLOADBATCHSIZE=1000;SUPPORTLINKS=false;MAXPOOLEDSTATEMENTS=0;
SPYATTRIBUTES=log=(file)/scratch/arya/spytrace.log;KEYSTORE=;
STRINGPARAMSMUSTMATCHCHARCOLUMNS=true;PROGRAMID=;ENABLECANCELTIMEOUT=false;
TRUSTSTOREPASSWORD=;VALIDATESERVERCERTIFICATE=true;CODEPAGEOVERRIDE=;
REFCURSORSUPPORT=true;KEYSTOREPASSWORD=;EDITIONNAME=;CONNECTIONRETRYCOUNT=5;
SENDFLOATPARAMETERSASSTRING=false;COMMITBEHAVIOR=serverDefault;ENABLEBULKLOAD=false;
TNSSERVERNAME=UTF11G2B;BATCHPERFORMANCEWORKAROUND=true;INITIALIZATIONSTRING=;
FAILOVERPRECONNECT=false;REPORTRECYCLEBIN=true;ENABLESERVERRESULTCACHE=false;
RESULTSETMETADATAOPTIONS=0;CLIENTUSER=;QUERYTIMEOUT=0;HOSTNAMEINCERTIFICATE=;
FAILOVERGRANULARITY=nonAtomic;WIREPROTOCOLMODE=2;
APPLICATIONNAME=CDC-Update Instance Name;JAVADOUBLETOSTRING=false;LOGTNAME=yes;
LOADLIBRARYPATH=;INITIALCOLUMNBUFFERSIZE=-1;IMPORTSTATEMENTPOOL=;ALTERNATESERVERS=;
SDUSIZE=;TIMESTAMP=yes;ACTION=;KEYPASSWORD=;BULKLOADOPTIONS=0;
MODULE=CDC-Update Instance Name;ENCRYPTIONMETHOD=NoEncryption;ACCOUNTINGINFO=main;
CONVERTNULL=1;TRUSTSTORE=;TNSNAMESFILE=/db/oracle/11.2.0/network/admin/tnsnames.ora;
JDBCBEHAVIOR=1;FAILOVERMODE=connect;AUTHENTICATIONMETHOD=auto;LOGINTIMEOUT=30;
SERVICENAME=;SERVERTYPE=;LOADBALANCING=false;WORKAROUNDS=0;SID=;
INSENSITIVERESULTSETBUFFERSIZE=2048;SYSLOGINROLE=;CLIENTHOSTNAME=;
FETCHTSWTZASTIMESTAMP=true;CLIENTID=CDC-Update Instance Name)
spy>> DatabaseMetaData[1].getDriverName()
spy>> OK (Oracle)
spy>> DatabaseMetaData[1].getDriverVersion()
spy>> OK (4.2.1.030249 (F044244.U015812))
spy>> DatabaseMetaData[1].getDatabaseProductName()
spy>> OK (Oracle)
spy>> DatabaseMetaData[1].getDatabaseProductVersion()
spy>> OK (11.2.0.1.0)
spy>> Connection Options :
spy>> CATALOGOPTIONS=0
spy>> CONNECTIONRETRYDELAY=1
spy>> BULKLOADBATCHSIZE=1000
spy>> SUPPORTLINKS=false
spy>> MAXPOOLEDSTATEMENTS=0
spy>> SPYATTRIBUTES=log=(file)/scratch/arya/spytrace.log
spy>> KEYSTORE=
spy>> STRINGPARAMSMUSTMATCHCHARCOLUMNS=true
spy>> PROGRAMID=
spy>> ENABLECANCELTIMEOUT=false
spy>> TRUSTSTOREPASSWORD=
spy>> VALIDATESERVERCERTIFICATE=true
spy>> CODEPAGEOVERRIDE=
spy>> REFCURSORSUPPORT=true
spy>> KEYSTOREPASSWORD=
spy>> EDITIONNAME=
spy>> CONNECTIONRETRYCOUNT=5
spy>> SENDFLOATPARAMETERSASSTRING=false
spy>> COMMITBEHAVIOR=serverDefault
spy>> ENABLEBULKLOAD=false
spy>> TNSSERVERNAME=UTF11G2B
spy>> BATCHPERFORMANCEWORKAROUND=true
spy>> INITIALIZATIONSTRING=
spy>> FAILOVERPRECONNECT=false
spy>> REPORTRECYCLEBIN=true
spy>> ENABLESERVERRESULTCACHE=false
spy>> RESULTSETMETADATAOPTIONS=0
spy>> CLIENTUSER=
spy>> QUERYTIMEOUT=0
spy>> HOSTNAMEINCERTIFICATE=
spy>> FAILOVERGRANULARITY=nonAtomic
spy>> WIREPROTOCOLMODE=2
spy>> APPLICATIONNAME=CDC-Update Instance Name
spy>> JAVADOUBLETOSTRING=false
spy>> LOGTNAME=yes
spy>> LOADLIBRARYPATH=
spy>> INITIALCOLUMNBUFFERSIZE=-1
spy>> IMPORTSTATEMENTPOOL=
spy>> ALTERNATESERVERS=
spy>> SDUSIZE=
spy>> TIMESTAMP=yes
spy>> ACTION=
spy>> KEYPASSWORD=
spy>> BULKLOADOPTIONS=0
spy>> MODULE=CDC-Update Instance Name
spy>> ENCRYPTIONMETHOD=NoEncryption
spy>> ACCOUNTINGINFO=main
spy>> CONVERTNULL=1
spy>> TRUSTSTORE=
spy>> TNSNAMESFILE=/db/oracle/11.2.0/network/admin/tnsnames.ora
spy>> JDBCBEHAVIOR=1
spy>> FAILOVERMODE=connect
spy>> AUTHENTICATIONMETHOD=auto
spy>> LOGINTIMEOUT=30
spy>> SERVICENAME=
spy>> SERVERTYPE=
spy>> LOADBALANCING=false
spy>> WORKAROUNDS=0
spy>> SID=
spy>> INSENSITIVERESULTSETBUFFERSIZE=2048
spy>> SYSLOGINROLE=
spy>> CLIENTHOSTNAME=
spy>> FETCHTSWTZASTIMESTAMP=true
spy>> CLIENTID=CDC-Update Instance Name
spy>> Driver Name = Oracle
spy>> Driver Version = 4.2.1.030249 (F044244.U015812)
spy>> Database Name = Oracle
spy>> Database Version = 11.2.0.1.0
spy>> Connection[1].setTransactionIsolation(int level)
spy>> level = 2
spy>> OK
spy>> Connection[1].createStatement()
spy>> OK (Statement[1])
spy>> Statement[1].execute(String sql)
spy>> sql = ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,'
spy>> OK (false)
spy>> Statement[1].close()
spy>> OK
spy>> Connection[1].createStatement()
spy>> OK (Statement[2])
spy>> Statement[2].executeQuery(String sql)
spy>> sql = select distinct sid from v$mystat
spy>> OK (ResultSet[1])
spy>> ResultSet[1].next()
spy>> OK (true)
spy>> ResultSet[1].getInt(int columnIndex)
spy>> columnIndex = 1
spy>> OK (398)
spy>> ResultSet[1].close()
spy>> OK
spy>> Statement[2].close()
spy>> OK
spy>> Connection[1].createStatement()
spy>> OK (Statement[3])
spy>> Statement[3].executeQuery(String sql)
spy>> sql = SELECT * FROM "SRC".TS_AUTH
spy>> OK (ResultSet[2])
spy>> ResultSet[2].next()
spy>> OK (true)
spy>> ResultSet[2].close()
spy>> OK
spy>> Statement[3].executeQuery(String sql)
spy>> sql = SELECT * FROM "SRC".TS_BOOKMARK
spy>> OK (ResultSet[3])
spy>> ResultSet[3].next()
spy>> OK (false)
spy>> ResultSet[3].close()
spy>> OK
spy>> Statement[3].executeQuery(String sql)
spy>> sql = SELECT PROP, VAL FROM "SRC".TS_AUTH
spy>> OK (ResultSet[4])
spy>> ResultSet[4].next()
spy>> OK (true)
spy>> ResultSet[4].getString(int columnIndex)
spy>> columnIndex = 1
spy>> OK (INSTANCEID)
spy>> ResultSet[4].getString(int columnIndex)
spy>> columnIndex = 2
spy>> OK (1323376560727)
spy>> ResultSet[4].next()
spy>> OK (true)
spy>> ResultSet[4].getString(int columnIndex)
spy>> columnIndex = 1
spy>> OK (INSTANCENAME)
spy>> ResultSet[4].getString(int columnIndex)
spy>> columnIndex = 2
spy>> OK (AW)
spy>> ResultSet[4].next()
spy>> OK (true)
spy>> ResultSet[4].getString(int columnIndex)
spy>> columnIndex = 1
spy>> OK (INSTALLDIR)
spy>> ResultSet[4].getString(int columnIndex)
spy>> columnIndex = 2
spy>> OK (/scratch/arya/Pack177)
spy>> ResultSet[4].next()
spy>> OK (true)
spy>> ResultSet[4].getString(int columnIndex)
spy>> columnIndex = 1
spy>> OK (INSTALLTIME)
spy>> ResultSet[4].getString(int columnIndex)
spy>> columnIndex = 2
spy>> OK (Thu Dec 08 15:36:13 EST 2011)
spy>> ResultSet[4].next()
spy>> OK (true)
spy>> ResultSet[4].getString(int columnIndex)
spy>> columnIndex = 1
spy>> OK (INSTALLVER)
spy>> ResultSet[4].getString(int columnIndex)
spy>> columnIndex = 2
spy>> OK (V6R5M1T0)
spy>> ResultSet[4].next()
spy>> OK (true)
spy>> ResultSet[4].getString(int columnIndex)
spy>> columnIndex = 1
spy>> OK (INSTALLUSER)
spy>> ResultSet[4].getString(int columnIndex)
spy>> columnIndex = 2
spy>> OK (awajarya)
spy>> ResultSet[4].next()
spy>> OK (true)
spy>> ResultSet[4].getString(int columnIndex)
spy>> columnIndex = 1
spy>> OK (TESTSTRING)
spy>> ResultSet[4].getString(int columnIndex)
spy>> columnIndex = 2
spy>> OK (1341f681399)
spy>> ResultSet[4].next()
spy>> OK (false)
spy>> Statement[3].close()
spy>> OK
spy>> Connection[1].close()
spy>> OK
|
This article has shown how to configure IBM Change Data Capture to trace JDBC calls with DataDirect Spy for JDBC.
The author sincerely thanks his colleagues Glen Sakuth, Elaine Pang, Alec Beaton, Xu Lin, Sunil Kumar Perla, and Shailesh Jamloki for reviewing and providing valuable feedback that helped to refine this article.
Learn
- Learn more about how IBM
InfoSphere Change Data Capture integrates information across heterogeneous
data stores in real time.
- Learn more about
DataDirect Spy.
- Learn more about Information Management at the developerWorks Information Management
zone. Find technical documentation,
how-to articles, education, downloads, product information, and
more.
- Stay current with
developerWorks technical events and webcasts.
- Follow developerWorks on
Twitter.
Get products and technologies
- Build your next
development project with
IBM trial software,
available for download directly from developerWorks.
- Now you can use
DB2 for free. Download DB2 Express-C, a no-charge
version of DB2 Express Edition for the community that offers the same core
data features as DB2 Express Edition and provides a solid base to build
and deploy applications.
Discuss
- Check out the
developerWorks
blogs and get involved in the
developerWorks community.
