 | Level: Intermediate Andres Voldman (voldman@ca.ibm.com), Software Engineer, IBM
Sidy Doumbia (sdoumbia@ca.ibm.com), Software Developer, IBM
20 Feb 2008 To help you troubleshoot database-related problems with WebSphere® Commerce, this article shows you how to find the Java™ code that executes a particular SQL statement.
Introduction
WebSphere® Commerce developers and database administrators are often in a
situation where they need to identify the piece of Java code that is firing a
certain database operation, such as a SELECT, INSERT, UPDATE or DELETE. Questions
such as "Why is the product page updating the attribute table?" and "Which command
is executing this SQL?" are commonly asked during performance tuning or
troubleshooting. This article will help you find answers to those questions.
WebSphere Commerce data access model
WebSphere Commerce is a J2EE application that accesses the database through the
use of Enterprise JavaBeans (EJBs). There are two types of EJBs: entity beans and
session beans. The most common type of EJBs in WebSphere Commerce is
container-managed persistence (CMP) entity beans. Entity beans map to a database
table and provide the necessary interfaces to find, create, and update the data in
the database.
Instead of using the beans directly, WebSphere Commerce commands and JSPs
interact with access beans that wrap the CMP entity beans. Using access beans
provide several advantages to help performance, such as a simpler programming
interface, smaller number of lookups, an additional level of caching, and a
reduced number of calls to the EJB container.
As access beans are embedded in the logic of the WebSphere Commerce
commands, identifying which access bean is responsible for executing a particular
query gets you a step closer to locating the WebSphere Commerce command that is
using the access bean to update the database.
Mapping an SQL statement to an access bean
The following steps provide a guide for identifying the access bean and finder
method that is responsible for executing a particular query.
Mapping a database table to an access
bean and an entity bean
When the query is generated from the EJB container, the table uses the T1 alias. Using the table name,
you can find the list of access beans that map to the table. Narrowing down to one or a handful of access
beans greatly simplifies the process of finding the access bean that maps to the SQL statement.
The WebSphere Commerce Information Center includes detailed information
regarding those mappings in
Cross-reference of data beans, EJB beans, and tables.
The following example shows that for the table CATGROUP, you will have an access
bean called CatalogGroupAccessBean and an entity bean called CatalogGroupBean.
|
Data bean
|
EJB bean
|
Access bean
|
Database table
| | CategoryDataBean | CatalogGroupBean | CatalogGroupAccessBean | CATGROUP |
Queries that use the primary key
When the SELECT statement contains a single condition that matches the primary
key columns to values that are specified using parameter markers, most likely the
query was executed as a result of instantiating a bean by its primary key.
The following example shows how the CatalogGroupAccessBean (a catalog group is a
category) is used to find a catalog group by its catalog group reference number,
which is its primary key. The setInitKey method specifies the key to be used and
the refreshCopyHelper() method refreshes the values in the access bean from the
database.
CatalogGroupAccessBean cg = new CatalogGroupAccessBean();
cg.setInitKey_catalogGroupReferenceNumber(catgroupId);
cg.refreshCopyHelper();
|
When the previous code is executed, the following query is executed in the database:
SELECT T1.MEMBER_ID, T1.CATGROUP_ID, T1.FIELD1, T1.FIELD2, T1.LASTUPDATE,
T1.MARKFORDELETE, T1.IDENTIFIER, T1.OID, T1.OPTCOUNTER FROM CATGROUP T1
WHERE T1.CATGROUP_ID =?
|
If you do not know what the primary key definition for a table is, see the WebSphere
Commerce Information Center for detailed information about the out-of-the-box
schema, which mentions the default indexes, constraints, and primary
key definition. The
WebSphere Commerce database table: CATGROUP
page in the Information Center describes the CATGROUP table that is used in the
previous example.
Other ways of finding the primary key of a table
All the databases provide different ways to read the database catalog tables to
find the primary key definitions. The following examples show how to find the
primary key for the CATGROUP table in DB2 and Oracle.
In DB2®:
SELECT SUBSTR(TABSCHEMA,1,20) TABSCHEMA,SUBSTR(TABNAME,1,20) TABNAME, UNIQUERULE,
SUBSTR(COLNAMES,1,40) COLNAMES
FROM SYSCAT.INDEXES
WHERE TABNAME = 'CATGROUP' AND UNIQUERULE = 'P'
|
In Oracle®:
SELECT I.INDEX_NAME, I.TABLE_NAME, I.COLUMN_NAME
FROM ALL_CONSTRAINTS C, ALL_IND_COLUMNS I
WHERE C.TABLE_NAME = 'CATGROUP' AND C.CONSTRAINT_TYPE = 'P'
AND C.INDEX_NAME = I.INDEX_NAME
|
Queries that are created from finders
Finder support for EJB 1.1 CMP entity beans is a WebSphere Application Server
extension to the EJB specification. Using a finder allows you to select a number
of beans that match a particular criterion.
The following example show how the findByCatalogId() finder of the CatalogGroup
bean is used to retrieve all the catalog groups (categories) that belong to a
certain catalog:
CatalogGroupAccessBean cgbean = new CatalogGroupAccessBean();
Enumeration e = cgbean.findByCatalogId(nCatalogId);
while (e.hasMoreElements()) {
CatalogGroupAccessBean cataloggroup = (CatalogGroupAccessBean) e.nextElement();
}
|
Finding the query in the Information Center
You can reference find queries that are executed from most finders in the
WebSphere Commerce Information Center (unless the whereClause is
dynamic).
For example, let’s say you have the following query and you need to find the
access bean and finder method that uses it:
SELECT T1.MEMBER_ID, T1.CATGROUP_ID, T1.FIELD1, T1.FIELD2, T1.LASTUPDATE,
T1.MARKFORDELETE, T1.IDENTIFIER, T1.OID, T1.OPTCOUNTER FROM CATGROUP
WHERE T1.MEMBER_ID = ? AND T1.IDENTIFIER = ?
|
From the query, you know that the table is CATGROUP. You can use the
Cross-reference of data beans, EJB beans, and tables
to find the bean that maps to CatalogGroupBean and CatalogGroupAccessBean.
You can then open the Javadoc for the
CatalogGroupAccessBean.
This page lists all the finder methods and their corresponding SELECT statement.
If you look at the previous statement in detail, notice that it references two
fields: member_id and identifier. Following the finder’s naming convention, you
can tell that the finder will be called “findByMemberIdAndIndentifier()”. The
Information Center confirms that a finder with that name exists and it uses the
SELECT previously mentioned. Figure 1 shows the Information Center documentation
for the CatalogGroupAccessBean.findByMemberIdAndIdentifier() method.
Figure 1. findByMemberIdAndIdentifier Information Center page
Finding the query in the deployment descriptors
If the statement you are looking for it is not documented in the Information
Center, you can use the deployment descriptors to find how the finder is
implemented. The file that contains the finders is called ibm-ejb-jar-ext.xmi
and it is located in the following path:
AppServer/profiles/profileName/config/cells/cellName/applications/
WC_demo.ear/deployments/WC_demo/EJBJar/META-INF/ibm-ejb-jar-ext.xmi
|
For example:
/opt/WebSphere/AppServer/profiles/demo/config/cells/WC_demo_cell/applications
/WC_demo.ear/deployments/WC_demo/Catalog-ProductManagementData.jar/META-INF
/ibm-ejb-jar-ext.xmi
|
The following extract shows the definition of the
findByMemberIdAndIdentifier() finder. You can see the
where clause on the finderDescriptor/whereClause attribute:
<finderDescriptors xmi:type="ejbext:WhereClauseFinderDescriptor"
xmi:id="CatalogGroup_findByMemberIdAndIdentifier_WhereClauseID"
whereClause="T1.member_id = ? and T1.identifier = ?">
<finderMethodElements xmi:id="CatalogGroupBeanMethodElement_214"
name="findByMemberIdAndIdentifier" parms="java.lang.Long java.lang.String"
type="Home">
<enterpriseBean xmi:type="ejb:ContainerManagedEntity"
href="META-INF/ejb-jar.xml#CatalogGroup"/>
</finderMethodElements>
</finderDescriptors>
|
If the deployment descriptor does not include a whereClause, then the query is
generated in Java using a finder helper class. The following example shows a
finder that uses a finder class:
<finderDescriptors xmi:type="ejbext:UserFinderDescriptor"
xmi:id="CatalogGroupDescription_findByCatalogGroupIdsAndLang
uageId_UserDefined_ID">
<finderMethodElements
xmi:id="CatalogGroupDescriptionBeanMethodElement_261"
name="findByCatalogGroupIdsAndLanguageId"
parms="java.lang.Long [] java.lang.Integer" type="Home">
<enterpriseBean xmi:type="ejb:ContainerManagedEntity"
href="META-INF/ejb-jar.xml#CatalogGroupDescription"/>
</finderMethodElements>
</finderDescriptors>
|
The name of the finder will be <EJBName>FinderObject. For example,
in the case of the CatalogGroupBean, its corresponding finder is called
CatalogGroupBeanFinderObject. As most of the source code for the WebSphere
Commerce out-of-the-box classes is not available, you can use EJB Trace to print
the SQL statement and the name of the finder.
Using EJB trace to find the access bean and finder method
If the previous steps do not yield the query information, the
alternative is to use EJB tracing, which will show all queries executed by the EJB
Container. You can use the following WebSphere Application Server EJB traces to
locate the finder method that is executing the query:
EJBContainer=all=enabled:PMGR=all=enabled
|
After you collect the trace, use a text editor to locate the name of the finder
or the SELECT statement.
The following snippet shows the SQL query of a
finder in the CatalogGroupDescription access bean. The finder is called
findByCatalogGroupIdsAndLanguageId(). You can find the
SELECT statement at the end of the trace.
[11/10/06 18:09:59:228 EST] 790c2aad UncachedActiv > atActivate
(BeanId(_homeOfHomes#homeOfHomes#_homeOfHomes,
WC_demo#Catalog-ProductManagementData.jar
#CatalogGroupDescription))
com.ibm.ejs.container.ContainerTx@2fabeaa1
[11/10/06 18:09:59:228 EST] 790c2aad UncachedActiv
< atActivate StatelessBeanO(null,
state = POOLED)
[11/10/06 18:09:59:228 EST] 790c2aad EJSContainer <
EJBpreInvoke(2:findByCatalogGroupIdsAndLanguageId) Invoking
method
'findByCatalogGroupIdsAndLanguageId' on bean
'com.ibm.commerce.catalog.objects.EJSRemoteCMPCatalogGroup
DescriptionHome_6f241499
(BeanId(_homeOfHomes#homeOfHomes#_homeOfHomes,
WC_demo#Catalog-ProductManagementData.jar#CatalogGroupDescription))',
'com.ibm.ejs.container.ContainerTx@2fabeaa1',
isGlobalTx=true
[11/10/06 18:09:59:228 EST] 790c2aad EJSHome d preFind():
flush NOT required
[11/10/06 18:09:59:228 EST] 790c2aad EJSJDBCPersis >
getPreparedStatement SELECT
T1.FULLIMAGE, T1.KEYWORD, T1.DISPLAY, T1.LANGUAGE_ID, T1.SHORTDESCRIPTION,
T1.CATGROUP_ID, T1.PUBLISHED, T1.NAME, T1.THUMBNAIL, T1.LONGDESCRIPTION, T1.NOTE
FROM CATGRPDESC T1 WHERE T1.LANGUAGE_ID = ? AND T1.CATGROUP_ID IN (11499, 11593, 11507,
11498, 11502, 11505, 11501, 11506, 11503, 11626, 11504, 11497)
|
Finding why UPDATE statements are executed
You might encounter situations where UPDATE
statements are executed from pages that supposedly only read the database. For
example, you might have an executed product page that updates the ATTRIBUTE table.
These undesired UPDATE statements can create severe concurrency and locking
problems, such as lock waits and deadlocks. The two most common reasons for these
updates are:
- You are inadvertently using commitCopyHelper() instead of refreshCopyHelper(). While
refreshCopyHelper reads the database to update the access bean,
commitCopyHelper() takes the current state of the access bean and updates the
database with it.
- A read only method is not defined as read-only in the deployment descriptors,
and thus the bean is marked “dirty” when the method is executed. This forces the EJB
container to execute the update when the transaction is committed.
To diagnose the reason for the UPDATE statements, enable the following EJB trace
specification and then use grep to find all methods that are executed on the bean.
EJBContainer=all=enabled:PMGR=all=enabled
|
The following code sample shows that method _copyToEJB is executed for the
Attribute bean. _copyToEJB() is a method that receives all the values from an
access bean and updates the database with it. If _copyToEJB() is executed during
the transaction for a bean that is supposedly read-only, this means that you
invoked commitCopyHelper() instead of refreshCopyHelper().
|
Access Bean
|
EJB bean
|
Use
| | commitCopyHelper() | _copyToEJB() | Updates the database with the contents of the
access bean. | | refreshCopyHelper() | _copyFromEJB() | Refreshes the access bean with the contents in
the database. |
[5/19/06 15:45:13:907 PDT] 96ba4 EJSContainer <
EJBpreInvoke(5:_copyToEJB) Invoking
method '_copyToEJB' on bean
''com.ibm.commerce.catalog.objects.
EJSRemoteCMPAttribute_dc3d48a0(BeanId(WC_demo#Catalog-
ProductManagementData.jar#Attribute
, attributeReferenceNumber=123847 language_id=-1 ))',
'com.ibm.ejs.container.ContainerTx@60c4c4', isGlobalTx=true
|
This next example shows that the bean is marked dirty (and thus an update is
executed) when the getAttributeValues() method is used. getAttributeValues() is a
read-only method, and therefore, it should not be marking the bean as dirty. This
usually indicates a problem with the definition of the access intents for the
bean.
[5/19/06 15:45:13:914 PDT] 96ba4 EJSContainer >
EJBpreInvoke(2:getAttributeValues)
[5/19/06 15:45:13:914 PDT] 96ba4 WrapperManage d preInvoke :
pinned = true
[5/19/06 15:45:13:914 PDT] 96ba4 EJSContainer d Retrieving
bean meta data for bean
WC_demo#Catalog-ProductManagementData.jar#Attribute
[5/19/06 15:45:13:915 PDT] 96ba4 TransactionCo > preInvoke
[5/19/06 15:45:13:915 PDT] 96ba4 Required > preInvoke
[5/19/06 15:45:13:915 PDT] 96ba4 Required < preInvoke
[5/19/06 15:45:13:916 PDT] 96ba4 TransactionCo d TimeoutClock found :
com.ibm.ejs.csi.TimeoutClock: no delegate set
[5/19/06 15:45:13:916 PDT] 96ba4 TransactionCo < preInvoke :
com.ibm.ejs.csi.TxCookieImpl@5c1e4b
[5/19/06 15:45:13:916 PDT] 96ba4 EJSContainer > getCurrentTx (false)
[5/19/06 15:45:13:917 PDT] 96ba4 EJSContainer < getCurrentTx
[5/19/06 15:45:13:918 PDT] 96ba4 OptCEntityAct > atActivate
(BeanId(WC_demo#Catalog-ProductManagementData.jar#Attribute,
attributeReferenceNumber=123847 language_id=-1 ))
[5/19/06 15:45:13:918 PDT] 96ba4 OptCEntityAct d Found bean
in cache
[5/19/06 15:45:13:918 PDT] 96ba4 OptCEntityAct < atActivate
[5/19/06 15:45:13:919 PDT] 96ba4 EntityBeanO > preInvoke
[5/19/06 15:45:13:919 PDT] 96ba4 EntityBeanO d Bean marked dirty
[5/19/06 15:45:13:919 PDT] 96ba4 EntityBeanO < preInvoke
[5/19/06 15:45:13:920 PDT] 96ba4 EJSContainer
< EJBpreInvoke(2:getAttributeValues)
Invoking method 'getAttributeValues' on bean
'com.ibm.commerce.catalog.objects.EJSRemoteCMPAttribute_
dc3d48a0(BeanId
(WC_demo#Catalog-ProductManagementData.jar#Attribute,
attributeReferenceNumber=123847
language_id=-1 ))',
'com.ibm.ejs.container.ContainerTx@60c4c4', isGlobalTx=true
|
Access intents are defined in the file ibm-ejb-jar-ext.xmi. Its default location
is as follows:
AppServer/profiles/profileName/config/cells/cellName/applications/
WC_demo.ear/deployments
/WC_demo/EJBJar/META-INF/ibm-ejb-jar-ext.xmi
|
For example:
/opt/WebSphere/AppServer/profiles/demo/config/cells/WC_demo_cell/
applications/WC_demo.ear
/deployments/WC_demo/Catalog-ProductManagementData.jar/META-INF/
ibm-ejb-jar-ext.xmi
|
This access intent was corrected to properly set the method to read-only:
<accessIntents xmi:id="AttributeBeanAccessIntent_1"
intentType="READ">
<methodElements xmi:id="AttributeBeanMethodElement_9"
name="getAttributeValues"
parms="" type="Remote">
<enterpriseBean xmi:type="ejb:ContainerManagedEntity"
href="META-INF/ejb-jar.xml#Attribute"/>
</methodElements>
</accessIntents>
|
Inserts and deletes with access beans
You might also need to find the code that is producing INSERT or DELETE
statements in the database.
When you identify the bean that maps to the
database table for which the statements are executed, you can use tracing or the
techniques presented in
Finding where the access bean is used to find
the calls to the Create() or the Remove() methods.
Creating new beans
Access beans are also used to create new data. New records are created when a
constructor that calls the ejbHome().create(…) method is executed. The following
code sample creates a new fulfillment center:
FulfillmentCenterAccessBean fcab = new
FulfillmentCenterAccessBean(ownerId, fflname);
fcab.setInventoryOperationFlags(flags);
fcab.commitCopyHelper();
|
The constructor that is called uses the ejbHome().create() method to generate the
new bean:
public FulfillmentCenterAccessBean(
java.lang.Long newMemberId,
java.lang.String newName)
throws
javax.naming.NamingException,
javax.ejb.FinderException,
javax.ejb.CreateException,
java.rmi.RemoteException {
ejbRef = ejbHome().create(newMemberId, newName);
}
|
Here is an example trace of creating a new category:
[2/15/08 13:05:38:418 EST] 000000c9 EJSJDBCPersis >
create(EntityBean) Entry
com.ibm.commerce.catalog.objects.EJSJDBCPersisterCMPCatalog
GroupBean_8e5bb4d3@73b1ebfc
[2/15/08 13:05:38:419 EST] 000000c9 EJSJDBCPersis >
getPreparedStatement INSERT
INTO CATGROUP (CATGROUP_ID, MEMBER_ID, FIELD1, FIELD2,
LASTUPDATE, MARKFORDELETE,
IDENTIFIER, OID, OPTCOUNTER) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
Entry
……
[2/15/08 13:05:38:422 EST] 000000c9 ContainerMana 3 the
length of optimistic data
array on setDataCache is 1
ContainerManagedBeanO(BeanId(WC_demo#Catalog-
ProductManagementData.jar#CatalogGroup,
catalogGroupReferenceNumber=10551 ), state = ACTIVE)
[2/15/08 13:05:38:422 EST] 000000c9 ContainerMana <
setDataCache Exit
[2/15/08 13:05:38:422 EST] 000000c9 EJSJDBCPersis <
create(EntityBean) Exit
|
In the above example, you can see the create() method. You can also recognize
the new CatalogGroup entity bean being created. The table mapping to this bean
is CATGROUP, thus the INSERT statement in the CATGROUP table. Note that the
trace only shows the name of the EJBs.
Removing existing beans
Deletes are not common in WebSphere Commerce. The general practice is to
update the bean and to set the marfordelete flag to “true”. This means that
updates are done in the runtime code. If your code is removing a bean, the
tracing shows that the ejbRemove() method is executed.
Here is an example trace for a DELETE from the STORECGRP table:
[2/15/08 13:08:40:183 EST] 000000c9 EJSContainer >
removeBean(wrapperBase) Entry
BeanId(WC_demo#Catalog-
ProductManagementData.jar#StoreCatalogGroup,
storeEntryID=10001
catalogGroupReferenceNumber=10551 )
.....
[2/15/08 13:08:40:191 EST] 000000c9 EJSJDBCPersis 3
Calling Method [remove] DB
ForUpdate: true DBExt ForUpdate:false CF w/RW AI:false
FBPK:false read-only:false
Inherit:false Child:false
[2/15/08 13:08:40:191 EST] 000000c9 EJSJDBCPersis 3
SQL for prepareStatement:DELETE
FROM STORECGRP WHERE CATGROUP_ID = ? AND STOREENT_ID = ?
AND OPTCOUNTER = ?
…
[2/15/08 13:08:40:193 EST] 000000c9 WrapperManage >
unregister Entry
BeanId(WC_demo#Catalog-
ProductManagementData.jar#StoreCatalogGroup,
storeEntryID=10001
catalogGroupReferenceNumber=10551 )
true
...
[2/15/08 13:08:40:194 EST] 000000c9 EJSContainer <
removeBean(wrapperBase) Exit
|
Similar to the INSERT case, you can see and extract the usual information
from this trace: EJB name, SQL statement, and which entry to remove from the
database.
Finding where the access bean is used
When you have identified which access bean and its corresponding finder method is
responsible for executing a particular SELECT statement in the database, the next
step is to find all the commands that use the finder.
Rational® Application Developer can list all the places where the finder is
invoked, by highlighting the method name and selecting References > Workspace in the pop-up
menu. Figure 2 shows the pop-up menu option that finds the references to the finder in the workspace.
Figure 2. Finding all references to a finder method in the workspace
Another way of finding all the executions of a particular method is by starting
the server in debugging mode and adding a breakpoint at the method level. Remember
that you can do this when the source code is not available by defining the
breakpoint in the outline view as shown in Figure 3.
Figure 3. Declaring a method entry breakpoint in the outline view
Using database tools to capture the statements executed
While troubleshooting or doing performance analysis, you might need to list
all the statements that are executed when a particular request is processed. All
the databases supported by WebSphere Commerce offer different tools that help you
capture the statements executed. As these tools are intrusive, it is best if they
are used in development environments. The following points highlight the tools you can use
with the available databases to record SQL statements:
- In DB2, you can use
statement event monitors.
To
create a table statement event monitor:
- Create the table
script:
db2evtbl -schema <schema_name> -evm DEVSQLPRF statements,tables > create.sql
- Execute the script to create the event
monitor:
db2 -tvf create.sql
- Start the event
monitor:
db2 -v "set event monitor DEVSQLPRF state=1"
- Execute the command to analyze.
- Stop the event monitor:
db2 -v "set event monitor DEVSQLPRF state= 0"
- In Oracle, you can use the
Oracle SQL trace
facility.
- In Derby, enable trace by updating:
runtime\base_v6\derby\derby.properties:
derby.language.logStatementText=true
Conclusion
This article presented different techniques that help you find the Java code that
causes a particular SQL statement to be executed. You complete this task while
troubleshooting database-related problems or during a performance tuning exercise. By identifying these
SQL statements accurately, you can find and redesign the code that is running them to improve performance
and solve database problems.
Resources
About the authors | 
| |
Andres Voldman is a Software Engineer at the IBM Toronto Lab, Canada. He is part of the WebSphere Commerce Advanced Technical Services team, which specializes in performance and stability. |
| 
| |
Sidy Doumbia is a Software Developer on the WebSphere Commerce support team. He
graduated with a Masters degree (2005) with honours in Computer Science from St.
Cloud State University, Minnesota. Sidy’s research interests include Fast Multiple
Methods (FMM) and application middleware integration. |
Rate this page
| |
