Complete tracing of JDBC calls for WebSphere Adapter when interacting with a DB2 database

Learn to configure WebSphere® Adapter for JDBC to get complete tracing information for each JDBC call while interacting with a DB2® database, which results in faster problem analysis and resolution.

Vinod A. Valecha (vinod.valecha@in.ibm.com), Software Developer, IBM

Photo of Vinod A. ValechaVinod Valecha is a Software Developer with the WebSphere Cast Iron and WebSphere Adapters team at IBM India Software Labs. He is currently working on development and customer support of Cast Iron and Adapters. He has been with IBM for over 5 years working with various Java technologies, including Java Connector Architecture (JCA). He holds a Bachelor of Technology degree from the College of Engineering, Pune, India.



18 September 2013

Introduction

IBM® WebSphere Adapter for JDBC (hereafter, referred to as JDBC adapter) is a J2EE Connector Architecture (JCA) 1.5 compliant resource adapter that provides bidirectional connectivity between enterprise applications and databases. The JDBC adapter is widely used in business data monitoring in enterprise application and service integration scenarios.

The default amount of tracing information logged by the JDBC adapter is limited and is available at the adapter code trace level. JDBC adapter logs the SQL exception messages from the database and other method level traces. However, the default tracing does not log the low level request messages from adapter and responses messages from the database.

Detailed JDBC tracing provides information for each interaction of applications with the database, logging the corresponding request message from the application and response message from the database. This article will help you configure the JDBC adapter and IBM WebSphere Process Server to get complete JDBC call tracing information.

Prerequisites

  • IBM Integration Designer V7.5
  • IBM WebSphere Adapter for JDBC V7.5
  • IBM WebSphere Process Server V7.5
  • DB2 database V9.7

This article covers the following topics:


Configuring IBM Process Server (runtime) for JDBC trace setting

  1. Log into the IBM WebSphere Application Server Administration Console as shown in Figure 1.
    Figure 1. Login to WebSphere Application Server Console
    Login to WebSphere Application Server Console
  2. Select Servers > Server Types > WebSphere application servers > server1, as shown in Figure 2.
    Figure 2. Select WebSphere Application Server
    Select WebSphere Application Server
  3. Select Java and Process Management > Process definition under Server Infrastructure, as shown in Figure 3.
    Figure 3. Select process definition
    Select process definition
  4. Select Java Virtual Machine under Additional Properties as shown in Figure 4.
    Figure 4. Select Java Virtual Machine
    Select Java Virtual Machine
  5. Select Custom properties as shown in Figure 5.
    Figure 5. Select custom properties
    Select custom properties
  6. Click New under the Custom properties as shown in Figure 6.
    Figure 6. Select new property
    Select new property
  7. In the General Properties page, enter the name of the property as db2.jcc.propertiesFile and the absolute path of property file name in the Value field. In this case, the path of the property file name is C:\JDBC\jdbc.properties, as shown in Figure 7.
    Figure 7. General Properties
    General Properties
  8. Click Save as shown Figure 8.
    Figure 8. Save dialog
    Save dialog
  9. You can see the property in the list of available properties as shown in Figure 9. Restart the process server after setting the property.
    Figure 9. Properties list
    Properties list

Generating artifacts with the trace setting enabled

This section describes how to configure WebSphere Adapter for JDBC for outbound scenario with trace settings and to generate the trace file with detailed JDBC tracing.

Outbound scenario

WebSphere Adapter for JDBC supports outbound request processing. When the adapter receives the request in the form of a business object from a service, it processes the request and invokes the corresponding outbound action at the database. In the example below, we will show the creation of a record at the database end.

To configure the outbound scenario for WebSphere Adapter for JDBC:

  1. Import the JDBC Adapter RAR file (CWYBC_JDBC.rar) into the Integration Designer workspace.
  2. Select File > new > module to create and name a new module, such as outbound.
  3. Run the External Service wizard and select JDBCAdapter. Click Next as shown in Figure 10.
    Figure 10. Select JDBC Adapter under the External Service
    Select JDBC Adapter under the External Service
  4. Add the JDBC driver JAR that will be used to connect to the database, as shown in Figure 11.
    Figure 11. Add the JDBC driver
    Add the JDBC driver
  5. Select Outbound under "Select the Processing Direction".
  6. Configure the end point properties for the DB2 database and click Next as shown in Figure 12.
    Figure 12. End point configuration properties
    End point configuration properties
  7. Select the business object for outbound operation as shown in Figure 13.
    Figure 13. Select the business object
    Select the business object
  8. Complete the rest of the External Service wizard to generate the JDBC adapter outbound component.
  9. Deploy the outbound component to WebSphere Process Server.
  10. Execute the Create Outbound operation to create a new record on DB2.

Complete JDBC calls tracing log

Listing 1 shows a section of the generated spy log.

Listing 1. Generated spy log
[jcc][ParameterMetaData@47834783] BEGIN TRACE_PARAMETER_META_DATA
[jcc][ParameterMetaData@47834783] Parameter meta data for statement Statement@46ad46ad

[jcc][ParameterMetaData@47834783] Number of parameter columns: 4
isDescribed=true[jcc][ParameterMetaData@47834783] Column 1: { label=1, 
name=1, type name=VARCHAR, type=12, nullable=1, precision=10, scale=0, 
schema name=, table name=, writable=false, sqlPrecision=0, sqlScale=0, 
sqlLength=10, sqlType=449, sqlCcsid=1252, sqlArrExtent=0, sqlName=1, 
sqlLabel=null, sqlUnnamed=1, sqlComment=null, sqludtxType=<null>, 
sqludtRdb=<null>, sqludtSchema=<null>, sqludtName=<null>, 
sqlxKeymem=0, sqlxGenerated=0, sqlxParmmode=1, sqlxOptlck=0, sqlxCorname=null,
 sqlxName=null, sqlxBasename=null, sqlxUpdatable=0, sqlxSchema=null, 
sqlxRdbnam=, internal type=12, is locator parameter=false }

[jcc][ParameterMetaData@47834783] Column 2: { label=2, name=2, type name=VARCHAR, 
type=12, nullable=1, precision=20, scale=0, schema name=, table name=, 
writable=false, sqlPrecision=0, sqlScale=0, sqlLength=20, sqlType=449, 
sqlCcsid=1252, sqlArrExtent=0, sqlName=2, sqlLabel=null, sqlUnnamed=1, 
sqlComment=null, sqludtxType=<null>, sqludtRdb=<null>, 
sqludtSchema=<null>, sqludtName=<null>, sqlxKeymem=0, sqlxGenerated=0, 
sqlxParmmode=1, sqlxOptlck=0, sqlxCorname=null, sqlxName=null, sqlxBasename=null, 
sqlxUpdatable=0, sqlxSchema=null, sqlxRdbnam=, internal type=12, is locator 
parameter=false }

[jcc][ParameterMetaData@47834783] Column 3: { label=3, name=3, type name=VARCHAR, 
type=12, nullable=1, precision=20, scale=0, schema name=, table name=, 
writable=false, sqlPrecision=0, sqlScale=0, sqlLength=20, sqlType=449, 
sqlCcsid=1252, sqlArrExtent=0, sqlName=3, sqlLabel=null, sqlUnnamed=1, 
sqlComment=null, sqludtxType=<null>, sqludtRdb=<null>, 
sqludtSchema=<null>, sqludtName=<null>, sqlxKeymem=0, sqlxGenerated=0, 
sqlxParmmode=1, sqlxOptlck=0, sqlxCorname=null, sqlxName=null, sqlxBasename=null, 
sqlxUpdatable=0, sqlxSchema=null, sqlxRdbnam=, internal type=12, is locator 
parameter=false }

[jcc][ParameterMetaData@47834783] Column 4: { label=4, name=4, type name=VARCHAR, 
type=12, nullable=1, precision=10, scale=0, schema name=, table name=, 
writable=false, sqlPrecision=0, sqlScale=0, sqlLength=10, sqlType=449, sqlCcsid=1252, 
sqlArrExtent=0, sqlName=4, sqlLabel=null, sqlUnnamed=1, sqlComment=null, 
sqludtxType=<null>, sqludtRdb=<null>, sqludtSchema=<null>,
sqludtName=<null>, sqlxKeymem=0, sqlxGenerated=0, sqlxParmmode=1, sqlxOptlck=0, 
sqlxCorname=null, sqlxName=null, sqlxBasename=null, sqlxUpdatable=0, sqlxSchema=null, 
sqlxRdbnam=, internal type=12, is locator parameter=false }

[jcc][ParameterMetaData@47834783] { sqldHold=0, sqldReturn=0, sqldScroll=0, 
sqldSensitive=0, sqldFcode=50, sqldKeytype=0, sqldRdbnam=, sqldSchema=null }

[jcc][ParameterMetaData@47834783] END TRACE_PARAMETER_META_DATA

[jcc][Time:2013-07-22-03:51:54.185][Thread:WebContainer : 1][PreparedStatement@46ad46ad] 
execute () returned false

[jcc][SystemMonitor:stop] core: 13.848797999999999ms | network: 12.216168ms | 
server: 10.702ms

[jcc][Time:2013-07-22-03:51:54.185][Thread:WebContainer : 1][PreparedStatement@46ad46ad] 
getUpdateCount () called

[jcc][Time:2013-07-22-03:51:54.185][Thread:WebContainer : 1][PreparedStatement@46ad46ad] 
getUpdateCount () returned 1

[jcc][SystemMonitor:start]
[jcc][Time:2013-07-22-03:51:54.185][Thread:WebContainer : 1][PreparedStatement@46ad46ad] 
close () called
[jcc][SystemMonitor:stop] core: 0.040279999999999996ms | network: 0.0ms | server: 0.0ms

[jcc][SystemMonitor:start]
[jcc][Time:2013-07-22-03:51:54.185][Thread:WebContainer : 1][Connection@23b523b5] 
commit () called
[jcc] [t4][time:2013-07-22-03:51:54.185][Thread:WebContainer : 1][tracepoint:1]
[Request.flush]

Properties file parameter

Listing 2 shows the parameters for the properties file.

Listing 2. Parameters for the properties file
db2.jcc.traceDirectory = c:\\traceDir
db2.jcc.traceFile = Adapter.log
db2.jcc.traceFileAppend = false
db2.jcc.traceLevel = -1
  • The db2.jcc.traceDirectory parameter specifies the location at which the spy log is generated. The spy log will be created at location "C:\\traceDir".
  • The db2.jcc.traceFile parameter specifies the name of the spy log. The name of the spy log is "Adapter.log".
  • The db2.jcc.traceFileAppend parameter specifies if the new trace should be appended to the existing trace contents. The value specified is "false"; therefore, the trace will not be appended.
  • The db2.jcc.traceLevel parameter specifies the level of tracing to be used.

Conclusion

This article described how to configure WebSphere Adapter for JDBC to get complete tracing of information for all JDBC calls while interacting with a DB2 database. The generated tracing information provides you with a quicker analysis and resolves your problems faster.

Resources

Learn

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 WebSphere on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=WebSphere
ArticleID=945461
ArticleTitle= Complete tracing of JDBC calls for WebSphere Adapter when interacting with a DB2 database
publish-date=09182013