Contents


Mapping an SQL statement to Java in WebSphere Commerce

Comments

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
Figure 1. findByMemberIdAndIdentifier Information Center page
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
Figure 2. Finding all references to a finder method          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
Figure 3. Declaring a method entry breakpoint in the outline view
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.


Downloadable resources


Related topics


Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=WebSphere
ArticleID=290562
ArticleTitle=Mapping an SQL statement to Java in WebSphere Commerce
publish-date=02202008