Here is the entire code. You could probably use your own table references and see if you get the same result. (BTW, it would be nice to have a "Post Code Snippet" link on the midrange.com Home page. I'll be darned if I can find the instructions on how to do it in the archives...) I have been running this from Eclipse. If you step through this and use something like WRKODBCJOB to view the lock information on the iSeries you'll see the lock, then the lock gone, then the lock, and the lock remaining even after the recordset and statement is closed the second time. The member overrides aren't an issue because you can comment out the method and the table still stays locked. Only when the program finally 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 and fails. I have tested this on a V5R2M0 box and a V5R3M0 box with the same 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, 2006 Author: <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, boolean annualFlag, 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 run it twice // just to demo that there is a member lock on the file when it 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 servlet apps, we really // do not close the connection. The closing means put it back in the pool // so what is happening is that the member lock is on the file as far the // connection is in use or alive. But sooner or later these pooled connections // will die and the lock will be removed. I am just wondering if this is a // typical behavior or there is something we can do to eleminate the member lock PreparedStatement 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 JDBC connection, 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



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.