InfoSphere Change Data Capture with DataDirect Spy for JDBC

How to trace JDBC calls

In this article, learn to trace JDBC calls using a data replication product from IBM®: InfoSphere® Change Data Capture (CDC) with DataDirect Spy for JDBC for Oracle, Sybase, and SQL Server.

Awajeet Arya (awajarya@in.ibm.com), Software Developer, IBM

Awajeet Arya is a software engineer with IBM India Software Labs, Bangalore. He is currently working on ensuring and controlling quality of Change Data Capture (CDC) technologies of IBM. His interests include Java middleware and server-side technologies.



12 July 2012

Also available in Chinese Russian

Overview

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


Prerequisites

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

About DataDirect Spy

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 attributes

DataDirect Spy supports the following attributes.

Table 1. Attributes of DataDirect Spy with descriptions
AttributeDescription
log=System.outDirects logging to the Java™ output standard: System.out.
log=(file)filenameRedirects logging to the file specified by filename.
linelimit=numberofcharsSets 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:

  1. 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
    Image shows invoking system parameter to add system parameter to connected data store
  2. 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
    Image shows adding required system parameter to connected data store
  3. Provide datadirect_spy_attributes as parameter name and set the value to log=(file)filename;logTName=yes;timestamp=yes, as shown in Figure 3. The system parameter datadirect_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
    Image shows providing 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]]

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

Conclusion

This article has shown how to configure IBM Change Data Capture to trace JDBC calls with DataDirect Spy for JDBC.

Acknowledgements

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.

Resources

Learn

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

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=824773
ArticleTitle=InfoSphere Change Data Capture with DataDirect Spy for JDBC
publish-date=07122012