Re: Record lock issue - is it JT400 ? -- JAVA400-L

Here is the entire code. You could probably use your own tablereferences and see if you get the same result. (BTW, it would be niceto have a "Post Code Snippet" link on the midrange.com Home page. I'llbe darned if I can find the instructions on how to do it in thearchives...)I have been running this from Eclipse. If you step through this and usesomething like WRKODBCJOB to view the lock information on the iSeriesyou'll see the lock, then the lock gone, then the lock, and the lockremaining even after the recordset and statement is closed the secondtime. The member overrides aren't an issue because you can comment outthe method and the table still stays locked. Only when the programfinally terminates does the lock release.

The issue manifests itself when you have several selects and then ,perhaps, an update. The table is locked and cannot be updated andfails. I have tested this on a V5R2M0 box and a V5R3M0 box with thesame result.


I am not sure of the isolation level being used.

Thanks,

Pete


So, the whole tamale:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.NamingException;

/**

* Class Name: TestJDBC.java Description: Create Date: Jun 20, 2006Author: <a*----------------------------------------------------------------------------

* Change Log:

*----------------------------------------------------------------------------

* Date: Number: Author: Description:
*/
public class TestJDBC {

   private static String annual = "mic__05";

   private static String nonannual = "mic__00";

public static void memberOverride(Connection con, booleanannualFlag, String fileName) throws SQLException {


       StringBuffer sql = new StringBuffer();
       Statement st = null;
       int strSize = 0;
       sql.append("ovrdbf file(" + fileName.trim() + ") mbr(");
       if (annualFlag == true)
           sql.append(annual);
       else
           sql.append(nonannual);
       sql.append(") ovrscope(*JOB)");
       strSize = sql.length();
       if (strSize > 9 && strSize < 100) {
           sql.append("',00000000" + strSize + ".00000)");
       }
       else if (strSize > 99 && strSize < 1000) {
           sql.append("',0000000" + strSize + ".00000)");
       }
       else if (strSize > 999 && strSize < 10000) {
           sql.append("',000000" + strSize + ".00000)");
       }

       String sql1 = "call qcmdexc('" + sql;
       try {
           st = con.createStatement();
           st.execute(sql1);
       }
       finally {
           if (st != null) {
               st.close();
               st = null;
           }
       }
   }

   public static void main(String[] args) {

String url ="jdbc:as400://10.0.10.200;libraries=fmsmicf;naming=system;";

       String query = "select glkey from fmsmicf/pgnl601";
       Connection conn = null;
       try {

           Class.forName("com.ibm.as400.access.AS400JDBCDriver");
           // Class.forName("org.gjt.mm.mysql.Driver");

           conn = DriverManager.getConnection(url, "XXXX", "XXX");

           memberOverride(conn, true, "PGNL601");

// over here I will put the following in a for loop and runit twice// just to demo that there is a member lock on the file whenit is run the second

           // time.
           // debug the floowong peice of code
           // when i = 0 run and stop at the ps.close statement do
           // WRKOBJLCK OBJ(FMSSAMPLE/PGNL601) OBJTYPE(*FILE) MBR(CIMS391)

// you will notice that the there is no locks left behind.Now when i = 1

           // you will notice that there is a shared lock left on the file.
           // the lock will be removed when the connection is closed.

// but since we are using a connection pool in our servletapps, we really// do not close the connection. The closing means put itback in the pool// so what is happening is that the member lock is on thefile as far the// connection is in use or alive. But sooner or later thesepooled connections// will die and the lock will be removed. I am justwondering if this is a// typical behavior or there is something we can do toeleminate the member lockPreparedStatement ps = conn.prepareStatement(query);


           ResultSet rs = ps.executeQuery();

for (int i = 0; i < 2; i++) {


               if (rs.next())

System.out.println(rs.getString(1));

try {

               rs.close();
               ps.close();

               ps = null;
               rs = null;
           }
           catch (SQLException ex){
               ex.printStackTrace();
           }

           // LET'S DO IT AGAIN

           ps = conn.prepareStatement(query);

           rs = ps.executeQuery();

for (int i = 0; i < 2; i++) {


               if (rs.next())

System.out.println(rs.getString(1));

try {

               rs.close();

ps.close();

               ps = null;
               rs = null;
           }
           catch (SQLException ex){
               ex.printStackTrace();
           }
       } // end try

       catch (SQLException ex) {

           ex.printStackTrace();

       } // end catch SQLException
       catch (ClassNotFoundException e) {
           // TODO: handle exception
           e.printStackTrace();
       }
       finally {
           if (conn != null)
               try {
                   conn.close();
               }
               catch (SQLException e) {
                   // TODO Auto-generated catch block
                   e.printStackTrace();
               }
       }
   }

}


David Gibbs wrote:

Pete Helgren wrote:
I have a program that has pretty simple process of establishing a JDBCconnection, opening up a recordset with a simple select statement(prepared) and then closing the recordset and closing the statement like so:
A select statement should not lock a record in the database ... are you
SURE the record is locked by the jdbc job?

What isolation level are you connecting to the database with?

david