ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jeff Hibbs" <jhi...@bop.gov>
Subject Re: Idle Threads - Glassfish/DB2
Date Fri, 03 Apr 2009 16:04:01 GMT
Clinton - 
 
Thanks for taking the time.....This is a struts App.
 
Here are some snippets...Please let me know if you want anything
else.......Again, thanks for your time! 
 
Jeff Hibbs
 
Here's an example of a Read:
 
from within an action..instantiate the DAO:
 
    InmateDAO inmateDAO = new InmateDAO();
 
 
 //after other calls to the InmateDAO from within this action for other
data we have:
 
 // Build paramaterMap for SQL query 
        HashMap parameterMap = new HashMap();
        parameterMap.put("inmateKey", new
Integer(iform.getInmateKey()));        
        
        if(inmate.getPrimaryEmergencyContact() == null) {
            parameterMap.put("type",
Constants.EMERGENCY_CONTACT_PRIMARY);        
           
inmate.setPrimaryEmergencyContact(inmateDAO.getEmergencyContact(parameterMap));
        }
        
        if(inmate.getSecondaryEmergencyContact() == null) {
            parameterMap.put("type",
Constants.EMERGENCY_CONTACT_SECONDARY);        
           
inmate.setSecondaryEmergencyContact(inmateDAO.getEmergencyContact(parameterMap));
        }
 
Here's the DAO constructor:
 
 
public class InmateDAO {
 
    // Class logger
    private static Log log = LogFactory.getLog(InmateDAO.class);
    // Handle to IBatis sqlMap
    private SqlMapClient sqlMap;
 
    /**
     * Creates a new instance of the DAO
     *
     * @exception DAOException if sqlMap lookup fails
     */
    public InmateDAO() throws DAOException {
 
        try {
            sqlMap = ServiceLocator.getInstance().getSqlMapClient();
        } catch (ServiceLocatorException sle) {
            log.error("ServiceLocator lookup failed", sle);
            throw new DAOException(sle);
        }
    }
 
 
 
Here's the method within the DAO:
 
 public EmergencyContact getEmergencyContact(Map parameterMap) throws
DAOException {
        try {
            return (EmergencyContact)
sqlMap.queryForObject("getEmergencyContact", parameterMap);
        } catch (SQLException sqe) {
            log.error("getEmergencyContact() lookup error, inmateKey ["
+ parameterMap.get("inmateKey") + "]", sqe);
            throw new DAOException(sqe);
        }
    }
 
 
Here is the SQL Maps
 
<select id="getEmergencyContact" parameterClass="map"
resultMap="emergencyContactLookup">
        SELECT INTKEY_INMT, TYPE_CONTACT, STREET, CITY, STATE,
ZIP_CODE, 
               PHONE_AREACODE, PHONE_NUMBER, PHONE_EXT, NAME_POC,
POC_RELATION
        FROM @isds.db.schema.name@.IS_EMERGENCY_CONT
        WHERE INTKEY_INMT = #inmateKey# AND TYPE_CONTACT = #type#
 </select>
 
 
 <resultMap id="emergencyContactLookup" class="emergencyContact">
        <result property="inmateKey" column="INTKEY_INMT"/>
        <result property="type" column="TYPE_CONTACT"/>
        <result property="street" column="STREET"/>
        <result property="city" column="CITY"/>
        <result property="state" column="STATE"/>
        <result property="zip" column="ZIP_CODE"/>
        <result property="phoneArea" column="PHONE_AREACODE"/>
        <result property="phoneNumber" column="PHONE_NUMBER"/>
        <result property="phoneExt" column="PHONE_EXT"/>
        <result property="pocName" column="NAME_POC"/>
        <result property="pocRelation" column="POC_RELATION"/>
    </resultMap>
   
 
 
Here's an example of an insert and update:
 
 
in the action we instantiate the DAO and call the
saveEmergencyContact() method
 
        (new
InmateDAO()).saveEmergencyContact(formBean.isInsertRequired(),
formBean.getEmergencyContact());
 
 
here's the constructor of InmateDAO (same as above in the read
example):
 
 
public class InmateDAO {
 
    // Class logger
    private static Log log = LogFactory.getLog(InmateDAO.class);
    // Handle to IBatis sqlMap
    private SqlMapClient sqlMap;
 
    /**
     * Creates a new instance of the DAO
     *
     * @exception DAOException if sqlMap lookup fails
     */
    public InmateDAO() throws DAOException {
 
        try {
            sqlMap = ServiceLocator.getInstance().getSqlMapClient();
        } catch (ServiceLocatorException sle) {
            log.error("ServiceLocator lookup failed", sle);
            throw new DAOException(sle);
        }
    }
 
 
here's the method:
 
public void saveEmergencyContact(boolean insertRequired,
EmergencyContact emergencyContact) throws DAOException {
        try {
            if (insertRequired) {
                sqlMap.insert("insertEmergencyContact",
emergencyContact);
            } else {
                if (sqlMap.update("updateEmergencyContact",
emergencyContact) != 1) {
                    throw new SQLException("update failed");
                }
            }
        } catch (SQLException sqe) {
            log.error("saveEmergencyContact() error, inmateKey [" +
emergencyContact.getInmateKey() + "]", sqe);
            throw new DAOException(sqe);
        }
    }
 
Here are the SQL Maps:
 
 <insert id="insertEmergencyContact" parameterClass="emergencyContact">

        INSERT INTO @isds.db.schema.name@.IS_EMERGENCY_CONT
(INTKEY_INMT, TYPE_CONTACT, 
                    STREET, CITY, STATE, ZIP_CODE, PHONE_AREACODE, 
                    PHONE_NUMBER, PHONE_EXT, NAME_POC, POC_RELATION)
        VALUES (#inmateKey#, #type#, #street#, #city#, #state#, #zip#,
#phoneArea#, 
                #phoneNumber#, #phoneExt#, #pocName#, #pocRelation#)
    </insert>
    
    <update id="updateEmergencyContact"
parameterClass="emergencyContact"> 
        UPDATE @isds.db.schema.name@.IS_EMERGENCY_CONT
        SET STREET = #street#, CITY = #city#, STATE = #state#, ZIP_CODE
= #zip#, 
            PHONE_AREACODE = #phoneArea#, PHONE_NUMBER = #phoneNumber#,
PHONE_EXT = #phoneExt#, 
            NAME_POC = #pocName#, POC_RELATION = #pocRelation#
        WHERE INTKEY_INMT = #inmateKey# AND TYPE_CONTACT = #type#
    </update>
 
 
—---------------------------------------------------------

>>> Clinton Begin <clinton.begin@gmail.com> 4/3/2009 11:11 AM >>>
Can you provide some example code from your application, showing some
crud operations?

Clinton

On 2009-04-03, Jeff Hibbs <jhibbs@bop.gov> wrote:
> Hello All -
>
> Any help will be greatly appreciated...Thanks!!!
>
> Our iBatis-based application was running on Sun1 Server/DB2 Version 8
-
> z/OS with no problems.  When we migrated to Glassfish V2, the DB
folks
> noticed many idle threads coming from our application which uses
iBATIS
> 2.1.5 (July 2005 Build).  Other (non-iBATIS) applications that use
> straight JDBC (no ORM) on the same server, using the same connection
> pool, were not causing idle threads.  Below is a sample what the DBA
is
> seeing:
>
>
—---------------------------------------------------------------------------------------------------------------------
>   Primauth   Planname         name         ID              Status
> elapsed time       CPU time
>
> xxxxxxxxx   DISTSERV   SYSLN100     SERVER    *DB2     5:23.78195
> 0.000969
> xxxxxxxxx    DISTSERV   SYSLN100     SERVER    *DB2     5:23.67919
>  0.001146
> xxxxxxxxx    DISTSERV   SYSLN100     SERVER    *DB2     5:23.59251
>  0.000896
> xxxxxxxxx    DISTSERV   SYSLN100     SERVER    *DB2     5:18.40476
>  0.001567
> xxxxxxxxx    DISTSERV   SYSLN100     SERVER    *DB2     5:18.38349
>  0.001066
>
>
> 14.46.15 STC12568  DSNL028I  #J3P1 GAD00841.K6FE.C3F92EF69C21=157421
> 914
>    914                        ACCESSING DATA FOR
>
>    914                          LOCATION xx.xxx.x.xx
>
>    914                          IPADDR xx.xxx.x.xx
>
> 14.48.14 STC12568  DSNL027I  #J3P1 SERVER DISTRIBUTED AGENT WITH 
561
>
>    561                       
LUWID=GAD00840.PC1B.C3F92F10E401=157523
>
>    561
> THREAD-INFO=xxxxxx:genie4:xxxxxxx:db2jcc_applic
>    561                        RECEIVED ABEND=04E
>
>    561                        FOR REASON=00D3003B
>
> 14.48.14 STC12568  DSNL027I  #J3P1 SERVER DISTRIBUTED AGENT WITH 
562
>
>    562                       
LUWID=GAD00840.PC20.C3F92F1B5DDF=157544
>
>    562
> THREAD-INFO=xxxxxxx:genie4:xxxxxxx:db2jcc_applic
>    562                        RECEIVED ABEND=04E
>
>    562                        FOR REASON=00D3003B
>
>
—-----------------------------------------------------------------------------------------------------------------------
>
> I'm not going to pretend to know what all this means, but apparently
> iBATIS/Glassfish is not releasing the threads after the SQL
completes.
> Again, other non-iBATIS applications using the same connection pool
are
> not generating these ilde threads.  From a user's perspective the
system
> is running fine - the queries are returning quickly.  Also, we are
not
> exhausting the connections in the connection pool, but apparently
some
> resources in DB2 are incorrectly being left open.  I guess I'm not
sure
> of the difference between a "connection" and a "thread" from the DB2
> perspective.
>
> We have been able to replicate this in the Test env.  Here's what we
> know so far:
>
> - Tried iBATIS 2.3.3.720: same results
> - Used replaced glassfish with Tomcat and the problem went away
>
> Obvious questions:
>
> 1.  Why are the iBATIS queries keeping idle threads open on DB2
while
> the straight JDBC coded queries are not.
> 2.  Why does this only appear to happen with Glassfish?
>
> Here's our iBATIS config:
>
>  <settings
>         useStatementNamespaces="false"
>         cacheModelsEnabled="true"
>         enhancementEnabled="true"
>     />
>
>     <transactionManager type="JDBC" >
>         <dataSource type="JNDI">
>             <property name="DataSource"
> value="java:comp/env/@isds.datasource.name@"/>
>         </dataSource>
>     </transactionManager>
>
> .......
>
> TEST Connection Pool Info:
>
>
> Datasource Classname: com.ibm.db2.jcc.DB2DataSource (prod same)
> Resource Type:javax.sql.DataSource (prod same)
>
> Pool Settings:
> Initial and Minimum Pool Size:8 (prod = 0)
> Maximum Pool Size: 32 (prod = 300)
> Pool Resize Quantity: 2 (prod = 5)
> Idle Timeout: 300 (prod = 15)
> Max Wait Time:60000 (prod = 60000)
>
>
>
>
>
>
>
>
>
>

-- 
Sent from my mobile device

Mime
View raw message