DB2/400 creating shared locks on files while using JDBC

We came across a problem when a RPG program was failing because it cannot take an Exclusive lock on a file/table. The only other program  that uses this file was a java program that uses JDBC to read and write into it. The file had a shared lock(SHRRD) on it.

When we further dug into the Java code, there was no explicit locking done. Eventually we were consistently able to reproduce the lock with a simple DB query tool.

We noticed that there is no lock when you query once and do a commit. When you do 2 or more queries on the same table and then do a commit the shared lock stays.

Q.  Is this a problem in the way you use JDBC/driver?
A.   No, this is not a JDBC or a driver Problem.

Q.  Is there a way to avoid it?
A.   No, this is how AS400 was designed to work.

Q.  How to work around it?
A.  The RPG program was to clear the locks before taking an exclusive lock.


Q.  Why does AS400 do it?
A.   AS400 does it for performance reasons. It reduces the overhead of opening and closing an open data path.

Delete duplicate rows in DB2/400 table

A simple way to delete duplicate rows using a query in a DB2/400 table is to use a query

DELETE FROM <TABLE NAME>  A
 WHERE RRN(A) >
   (SELECT MIN(RRN(B)) From <TABLE NAME> B
             Where A.COLUMN1 = B.COLUMN1
             and A.COLUMN2 = B.COLUMN2
             ... )

For Example use

delete From CUSTOMER A                        
    Where RRN(A) >                                     
          (Select Min(RRN(B)) From CUSTOMER B 
             Where A.ID= B.ID
             and A.NAME= B.NAME
             and A.CITY= B.CITY
             and A.STATE= B.STATE)