XA jtOpen and file locking - yet another connection pool configuration question - Websphere

This is a discussion on XA jtOpen and file locking - yet another connection pool configuration question - Websphere ; Hi, We are running into a strange connection pooling problem. We use the ToolBox XA driver: jtOpen on Windows and the jt400 that came with JDK 1.3.1 on AS400. We need to run it XA-enabled because we access a database ...

+ Reply to Thread
Results 1 to 2 of 2

Thread: XA jtOpen and file locking - yet another connection pool configuration question

  1. XA jtOpen and file locking - yet another connection pool configuration question

    Hi,
    We are running into a strange connection pooling problem. We use the ToolBox XA driver: jtOpen on Windows and the jt400 that came with JDK 1.3.1 on AS400. We need to run it XA-enabled because we access a database and a message queue at the same time. We can see some jobs started and running on the 400. The jobs seem to leave a bunch of locks on parts of files after they were accessed. When we try to click on a different page and do more database work, we may get an error, such as, 'Job XXXXX has locked the file...'. Actual error:

    java.sql.SQLException: [SQL0913] Row or object USER_00001 in SECURITY type *FILE in use.
    java/lang/Throwable.(Ljava/lang/StringV+4 (Throwable.java:85)
    java/lang/Exception.(Ljava/lang/StringV+1 (Exception.java:33)
    java/sql/SQLException.(Ljava/lang/String;Ljava/lang/String;I)V+1 (SQLException.java:34)
    com/ibm/as400/access/JDError.throwSQLException(Lcom/ibm/as400/access/AS400JDBCConnection;III)V+0 (JDError.java:517)
    com/ibm/as400/access/AS400JDBCStatement.commonExecute(Lcom/ibm/as400/access/JDSQLStatement;Lcom/ibm/as400/access/JDServerRowV+0 (AS400JDBCStatement.java:650)
    com/ibm/as400/access/AS400JDBCPreparedStatement.executeUpdate()I+0 (AS400JDBCPreparedStatement.java:1162)
    com/ibm/ws/rsadapter/jdbc/WSJdbcPreparedStatement.executeUpdate()I+0 (WSJdbcPreparedStatement.java:538)
    com/mccrackenfs/common/dal/DAL.executeUpdate()I+0 (DAL.java:734)
    org/apache/jsp/_home_2D_admin._jspService(Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponseV+0 (_home_2D_admin.java:116)

    Here is a snapshot of the job locks:
    Object Member ASP pt Object Library Type Lock Status Locks Device APP_D00001 SECURITY *FILE-PHY *SHRRD HELD YES *SHRRD HELD APPLI00001 SECURITY *FILE-PHY *SHRRD HELD YES *SHRRD HELD CANGELOV QSYS *USRPRF *SHRRD HELD *SHRRD HELD *SHRRD HELD DATASOURCE SECURITY *FILE-PHY *SHRRD HELD YES *SHRRD HELD The connections and the locks stay the way they are for many days!!!

    We have configured the pools, and we have created references everywhere: web.xml, under the EJBs in ejb-jar.xml, e.g. everywhere the data sources are used. The EJBs that use the data source have a transaction attribute 'Requred'. We also use the connection outside any EJBs, from the WAR application. When we create the resource references, we specify 'Shareable'. We leave the Transaction Isolation attribute (under WebSphere-specific) blank.

    The jt400 provider driver has many options and properties to configure, but we have used mostly the defaults:

    DB2 UDB for iSeries (Toolbox XA)
    Implementation Classname: com.ibm.as400.access.AS400JDBCXADataSource
    cursorHold false

    extendedDynamic false

    fullOpen false

    lazyClose false

    cursorSensitivity asensitive

    keepAlive



    There are a few properties that may be related to our problem, such as

    We obtain the connections through JNDI, never call commit() and always call close(). When we check the open transactions on the WebSphere console, it says, 'no transactions currently running' or something like this.

    So it seems that the connections are returned to the pool and the transactions, if any, are over. But why do the locks stay????

    Any help will be appreciated.
    Christo


  2. Re: XA jtOpen and file locking - yet another connection poolconfiguration question

    What OS/400 release is on the '400? Are you up to date PTFs on the
    '400? If you stop the application server, do the locks remain? (you may
    need to use STRQL, and issue LOCK TABLE lib/table IN EXCLUSIVE MODE to
    determine if the locks shown are real or just pseudo-locks).

    I believe that by leaving the transaction isolation level blank it will
    default to TRANSACTION_REPEATABLE_READ which maps to *RS on the '400.
    This is a strong lock - ensure that is what you need, if not specify
    something more appropriate for the transaction iosolation level.

    I don't know that it is correct to "never call commit()". Especially
    when outside of an CMP EJB.

    Christo wrote:

    > Hi,We are running into a strange connection pooling problem. We use
    > the ToolBox XA driver: jtOpen on Windows and the jt400 that came with
    > JDK 1.3.1 on AS400. We need to run it XA-enabled because we access a
    > database and a message queue at the same time. We can see some jobs
    > started and running on the 400. The jobs seem to leave a bunch of
    > locks on parts of files after they were accessed. When we try to click
    > on a different page and do more database work, we may get an error,
    > such as, 'Job XXXXX has locked the file...'. Actual
    > error: java.sql.SQLException: [SQL0913] Row or object USER_00001 in
    > SECURITY type *FILE in use.
    > java/lang/Throwable.(Ljava/lang/StringV+4
    > (Throwable.java:85)
    > java/lang/Exception.(Ljava/lang/StringV+1
    > (Exception.java:33)
    >
    > java/sql/SQLException.(Ljava/lang/String;Ljava/lang/String;I)V+1
    > (SQLException.java:34)
    >
    > com/ibm/as400/access/JDError.throwSQLException(Lcom/ibm/as400/access/AS400JDBCConnection;III)V+0
    > (JDError.java:517)
    >
    > com/ibm/as400/access/AS400JDBCStatement.commonExecute(Lcom/ibm/as400/access/JDSQLStatement;Lcom/ibm/as400/access/JDServerRowV+0
    > (AS400JDBCStatement.java:650)
    >
    > com/ibm/as400/access/AS400JDBCPreparedStatement.executeUpdate()I+0
    > (AS400JDBCPreparedStatement.java:1162)
    >
    > com/ibm/ws/rsadapter/jdbc/WSJdbcPreparedStatement.executeUpdate()I+0
    > (WSJdbcPreparedStatement.java:538)
    > com/mccrackenfs/common/dal/DAL.executeUpdate()I+0 (DAL.java:734)
    >
    >
    > org/apache/jsp/_home_2D_admin._jspService(Ljavax/servlet/http/HttpServletRequest;Ljavax/servlet/http/HttpServletResponseV+0
    > (_home_2D_admin.java:116)Here is a snapshot of the job locks:
    >
    > Object Member ASP
    > pt Object Library Type Lock Status Locks Device
    > APP_D00001 SECURITY *FILE-PHY *SHRRD HELD YES
    > *SHRRD HELD
    > APPLI00001 SECURITY *FILE-PHY *SHRRD HELD YES
    > *SHRRD HELD
    > CANGELOV QSYS *USRPRF *SHRRD HELD
    > *SHRRD HELD
    > *SHRRD HELD
    > DATASOURCE SECURITY *FILE-PHY *SHRRD HELD YES
    > *SHRRD HELD
    >
    > The connections and the locks stay the way they are for many
    > days!!! We have configured the pools, and we have created references
    > everywhere: web.xml, under the EJBs in ejb-jar.xml, e.g. everywhere
    > the data sources are used. The EJBs that use the data source have a
    > transaction attribute 'Requred'. We also use the connection outside
    > any EJBs, from the WAR application. When we create the resource
    > references, we specify 'Shareable'. We leave the Transaction Isolation
    > attribute (under WebSphere-specific) blank. The jt400 provider driver
    > has many options and properties to configure, but we have used mostly
    > the defaults: DB2 UDB for iSeries (Toolbox XA)Implementation
    > Classname: com.ibm.as400.access.AS400JDBCXADataSource
    >


    cursorHold false
    >
    >


    extendedDynamic false
    >
    >


    fullOpen false
    >
    >


    lazyClose false
    >
    >


    cursorSensitivity asensitive
    >
    >


    keepAlive
    > There are a few properties that may be related to our problem, such
    > as We obtain the connections through JNDI, never call commit() and
    > always call close(). When we check the open transactions on the
    > WebSphere console, it says, 'no transactions currently running' or
    > something like this. So it seems that the connections are returned to
    > the pool and the transactions, if any, are over. But why do the locks
    > stay???? Any help will be appreciated.Christo



+ Reply to Thread